Access 07 VBA

Hello all,

Some of you may have seen this post in the accessforums.net queries thread, but figured I would expand to see what you folks could come up with.

I have run into a wall in writing the final piece of my DB. This DB is a photo archive that is linked to mapping software. The issue that I have is in creating the final table that the mapping software needs to read in order to display a correct layer that includes file path links to the associated images.

The final table does not really follow standard Access DB rules for a table since it forces multiple sets of images into the same record row. Here is the basics for my DB:

Table: Photo_File (stores the Unique_ID [FK], and file base for each entry)
Table: Project (Stores the Unique_ID [PK], project name, and project area)
Table: Spatial (Stores the Unique_ID[FK], UTM coordinates (2), LAT/LON, FSVeg_Loc (specific to the map layer), and Stand_Num (forest stand))

Queries: Photo_Link Query (SQL: creates the table Photo_Link and builds the file paths to the images)

I have 2 other queries that I am trying to rid myself of since they only lead to problems with duplication. Hence VBA coding. BUT, I am not a VBA officionado, so I am asking for some serious help.

I need to write VBA code that accomplishes this:

1. Searches the Photo_Link table for Easting_UTM AND Northing_UTM coordinates (there will be duplicate values for these).
2. Writes the values into a new table.
3. Writes the values of the location, stand, photo year and image paths into the same row.
4. THEN, matches other records with the same coordinate set and writes the second (third, fourth, etc) photo year and image paths into the same row IF the photo year is different.
5. Loops through until end of records.

I can't use a concatenate function to do this since all the image paths for a coordinate set and years need to be in separate fields, but the same row.

The final table columns should look something like this:

[Easting_UTM][Northing_UTM][FSveg_Loc][Stand_Num][Photo_Year][IMG_North][IMG_East][IMG_South][IMG_West][Photo_Year2][IMG_North2][IMG_East2][IMG_South2][IMG_West2][.....etc

Here is the code for what I have managed so far, but still have issues:

Code:

---

Function Get_DB_Values()
'Get values from a table using a query in VBA.
'Process values row by row.
'Insert processed row into another Table.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim intEasting_UTM, intNorthing_UTM, intPhoto_Year, intPhoto_Year2 As Integer
Dim strFSVeg_Location, strFSVeg_Stand_No, strIMG_North, strIMG_East, strIMG_South, strIMG_West, strIMG_North2, strIMG_East2, strIMG_South2, strIMG_West2 As String
Dim intPrevEasting_UTM, intPrevNorthing_UTM, intPrevPhoto_Year As Integer
Dim strPrevFSVeg_Location, strPrevFSVeg_Stand_No, strPrevIMG_North, strPrevIMG_East, strPrevIMG_South, strPrevIMG_West As String
Dim intNewEasting_UTM, intNewNorthing_UTM, intNewPhoto_Year As Integer
Dim strNewFSVeg_Location, strNewFSVeg_Stand_No, strNewIMG_North, strNewIMG_East, strNewIMG_South, strNewIMG_West As String
Dim intSQL As Integer
Dim strSQL As String
Dim intRecordCount As Integer

On Error GoTo Error_Handle

Set db = CurrentDb

    strSQL = "Select * From Photo_Link ORDER BY Easting_UTM "
    intRecordCount = 1
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs

'This Do While loop goes through all the records in strSQL.

Do While Not rs.EOF

If intRecordCount = 1 Then
    intEasting_UTM = rs![Easting_UTM]
    intNorthing_UTM = rs![Northing_UTM]
    strFSVeg_Location = rs![FSVeg_Location]
    strFSVeg_Stand_No = rs![FSVeg_Stand_No]
    intPhoto_Year = rs![Photo_Year]
    strIMG_North = rs![IMG_North]
    strIMG_East = rs![IMG_East]
    strIMG_South = rs![IMG_South]
    strIMG_West = rs![IMG_West]
    intRecordCount = intRecordCount + 1
Else 'Not first record.
    intNewEasting_UTM = rs![Easting_UTM]
   
If intNewEasting_UTM = intPrevEasting_UTM And intNewNorthing_UTM = intPrevNorthing_UTM Then
'Same Field1 - concatenate values.
    strNewFSVeg_Location = rs![FSVeg_Location]
    strNewFSVeg_Stand_No = rs![FSVeg_Stand_No]
    intNewPhoto_Year = rs![Photo_Year2]

    'This is where the FUN begins.....Yeah Right

If strNewPhoto_Year <> strPrevPhoto_Year Then
    strNewPhoto_Year = strPhoto_Year2
End If

If strNewIMG_North <> strPrevIMG_North Then
    strNewIMG_North = strIMG_North2
End If

If strNewIMG_East <> strPrevIMG_East Then
    strNewIMG_East = strIMG_East2
End If

If strNewIMG_South <> strPrevIMG_South Then
    strNewIMG_South = strIMG_South2
End If

If strNewIMG_West <> strPrevIMG_West Then
    strNewIMG_West = strIMG_West2
End If

Else 'Field1 changed - Write the record to other table.

'Create Insert SQL.
strSQL = "INSERT INTO Photo_Link_Combined (Easting_UTM, Northing_UTM, FSVeg_Location, FSVeg_Stand_No, Photo_Year, IMG_North, IMG_East, IMG_South, IMG_West, Photo_Year2, IMG_North2, IMG_East2, IMG_South2, IMG_West2) "

strSQL = strSQL & "VALUES (" & "'" & intNewEasting_UTM & "'" & ", " & "'" & intNorthing_UTM & "'" & ", " & "'" & strFSVeg_Location & "'" & ", " & "'" & strFSVeg_Stand_No & "'" & ", " & "'" & intNewPhoto_Year & "'" & ", " & "'" & strIMG_North & "'" & ", " & "'" & strIMG_East & "'" & ", " & "'" & strIMG_South & "'" & ", " & "'" & strIMG_West & "'" & ", " & "'" & intNewPhoto_Year2 & "'" & ", " & "'" & strIMG_North2 & "'" & ", " & "'" & strIMG_East2 & "'" & ", " & "'" & strIMG_South2 & "'" & ", " & "'" & strNewIMG_West2 & "'" & "); "

'Execute Insert SQL
DoCmd.RunSQL strSQL

'Populate current row values into variables.
intEasting_UTM = rs![Easting_UTM]
intNorthing_UTM = rs![Northing_UTM]
strFSVeg_Location = rs![FSVeg_Location]
strFSVeg_Stand_No = rs![FSVeg_Stand_No]
intPhoto_Year = rs![Photo_Year]
strIMG_North = rs![IMG_North]
strIMG_East = rs![IMG_East]
strIMG_South = rs![IMG_South]
strIMG_West = rs![IMG_West]

End If 'End If strNewField1 = strField1 Then
End If 'End If intRecordCount = 1

intPrevEasting_UTM = intNewEasting_UTM
intPrevNorthing_UTM = intNewNorthing_UTM
strPrevFSVeg_Location = strNewFSVeg_Location
strPrevFSVeg_Stand_No = intNewFSVeg_Stand_No
intPrevPhoto_Year = intNewPhoto_Year
strPrevIMG_North = strNewIMG_North
strPrevIMG_East = strNewIMG_East
strPrevIMG_South = strNewIMG_South
strPrevIMG_West = strNewIMG_West

.MoveNext 'Move to next record in recordset.

Loop 'Back to 'Do While' to check if we are at the end of the file.

'Create SQL for Last Row of data that is still stored even though Access found the EOF.
strSQL = "INSERT INTO Photo_Link_Combined (Easting_UTM, Northing_UTM, FSVeg_Location, FSVeg_Stand_No, Photo_Year, IMG_North, IMG_East, IMG_South, IMG_West, Photo_Year2, IMG_North2, IMG_East2, IMG_South2, IMG_West2) "

strSQL = strSQL & "VALUES (" & "'" & intEasting_UTM & "'" & ", " & "'" & intNorthing_UTM & "'" & ", " & "'" & strFSVeg_Location & "'" & ", " & "'" & strFSVeg_Stand_No & "'" & ", " & "'" & intPhoto_Year & "'" & ", " & "'" & strIMG_North & "'" & ", " & "'" & strIMG_East & "'" & ", " & "'" & strIMG_South & "'" & ", " & "'" & strIMG_West & "'" & ", " & "'" & intPhoto_Year2 & "'" & ", " & "'" & strIMG_North2 & "'" & ", " & "'" & strIMG_East2 & "'" & ", " & "'" & strIMG_South2 & "'" & ", " & "'" & strIMG_West2 & "'" & "); "

'Execute Insert SQL.
DoCmd.RunSQL strSQL

Exit_Get_DB_Values:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

Error_Handle:
Resume Exit_Get_DB_Values
End With
End Function

---

Where am I going off the deep end with this?

Let me know if I need to post the sample DB.

I need to get this done ASAP.

Thanks,

Adam