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 As Integer
Dim strFSVeg_Location, strFSVeg_Stand_No, strIMG_North, strIMG_East, strIMG_South, strIMG_West 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_Year]
'This is where the FUN begins.....Yeah Right
If strNewPhoto_Year <> strPrevPhoto_Year Then
strPhoto_Year = strPhoto_Year2
End If
If strNewIMG_North <> strPrevIMG_North Then
strNewIMG_North = strIMG_North2
End If
If strNewIMG_East <> strPrevIMG_East Then
strIMG_East = strIMG_East2
End If
If strNewIMG_South <> strPrevIMG_South Then
strIMG_South = strIMG_South2
End If
If strNewIMG_West <> strPrevIMG_West Then
strIMG_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
I modified the Photo_Link_MOD table name (by taking off the _MOD) so that I could build this script and not have to change field names when I take it into the real DB...any thougts?