Delete and/or Select Distinct records query

[Robeen]

In your code - there should be a column [margin] to the left of the code.



If you click in there on a line of executable code - you should see a brown dot appear.

That is where the program will halt execution when you run it.
At that point - you can use the buttons to the top right to step through the code.

When Help doesn't give you what you need - Google it.
There's a lot of info available.

I usually google something along these lines:
ms access debugging

[admessing]

No luck....I get the same type of errors, just to differeing degrees no matter where I break the code. There aren't many variables to change in either case, so I am at a loss here.

[admessing]

Okay...I have worked on the code that I developed based on what Robeen gave me, and have some results. I can get the records down to unique easting coordinate. BUT, the northing coordinate is wrong, and I can't get it to fill in the records for the second set of images....

Here's the 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?

Is there a way to modify the code so that it creates the Photo_Link_Combined table instead of appending to an empty Photo_Link_Combined table?

[admessing]

Okay...made a few changes based upon feedback from other sources, but still having problems....now it doesn't run at all and I can't seem to figure out where the issue is.

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 As Integer
Dim intNorthing_UTM As Integer
Dim intPhoto_Year As Integer
Dim intPhoto_Year2 As Integer
Dim strFSVeg_Location As String
Dim strFSVeg_Stand_No As String
Dim strIMG_North As String
Dim strIMG_East As String
Dim strIMG_South As String
Dim strIMG_West As String
Dim intPrevEasting_UTM As Integer
Dim intPrevNorthing_UTM As Integer
Dim intPrevPhoto_Year As Integer
Dim strPrevFSVeg_Location As String
Dim strPrevFSVeg_Stand_No As String
Dim strPrevIMG_North As String
Dim strPrevIMG_East As String
Dim strPrevIMG_South As String
Dim strPrevIMG_West As String
Dim intNewEasting_UTM As Integer
Dim intNewNorthing_UTM As Integer
Dim intNewPhoto_Year As Integer
Dim strNewFSVeg_Location As String
Dim strNewFSVeg_Stand_No As String
Dim strNewIMG_North As String
Dim strNewIMG_East As String
Dim strNewIMG_South As String
Dim 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.....
 
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

[Robeen]

When you say 'it doesn't run at all' . . . how do you know that it doesn't run?

Can you put a Break Point in the code and step through it one line at a time to see where it is exiting the code?

[admessing]

I got it to run through again with a suggestion from another user...had to comment out the error handle...now I get debug options. It's getting there...just da**ed frustrating looking at the same code for hours on end...:\

[admessing]

Here is how the coding stands right now.....Line 84 (?) is where my current issue is. The error code is noted in the comment above it. Thoughts?

Option Compare Database
Option Explicit
 
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 As Long
Dim intNorthing_UTM As Long
Dim intPhoto_Year As Integer
Dim strFSVeg_Location As String
Dim strFSVeg_Stand_No As String
Dim strIMG_North As String
Dim strIMG_East As String
Dim strIMG_South As String
Dim strIMG_West As String
Dim intPrevEasting_UTM As Long
Dim intPrevNorthing_UTM As Long
Dim intPrevPhoto_Year As Integer
Dim strPrevFSVeg_Location As String
Dim strPrevFSVeg_Stand_No As String
Dim strPrevIMG_North As String
Dim strPrevIMG_East As String
Dim strPrevIMG_South As String
Dim strPrevIMG_West As String
Dim intNewEasting_UTM As Long
Dim intNewNorthing_UTM As Long
Dim strNewFSVeg_Location As String
Dim strNewFSVeg_Stand_No As String
Dim intNewPhoto_Year As Integer
Dim strNewIMG_North As String
Dim strNewIMG_East As String
Dim strNewIMG_South As String
Dim strNewIMG_West As String
Dim intPhoto_Year2 As Integer
Dim strIMG_North2 As String
Dim strIMG_East2 As String
Dim strIMG_South2 As String
Dim strIMG_West2 As String
Dim intSQL As Integer
Dim strSQL As String
Dim intRecordCount As Integer
 
'On Error GoTo Error_Handle
 
Set db = CurrentDb
strSQL = "Select Easting_UTM, Northing_UTM, FSVeg_Location, FSVeg_Stand_No, Photo_Year, IMG_North, IMG_East, IMG_South, IMG_West 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 intEasting_UTM = intPrevEasting_UTM Then
 
intNewNorthing_UTM = rs![Northing_UTM]
strNewFSVeg_Location = rs![FSVeg_Location]
strNewFSVeg_Stand_No = rs![FSVeg_Stand_No]
intNewPhoto_Year = rs![Photo_Year]
 
'This is where the FUN begins.....Receive Run-time error 3265: Item not found in this collection
 
If intNewPhoto_Year <> intPrevPhoto_Year Then
intNewPhoto_Year = rs![Photo_Year2]
End If
If strNewIMG_North <> strPrevIMG_North Then
strNewIMG_North = rs![IMG_North2]
End If
If strNewIMG_East <> strPrevIMG_East Then
strNewIMG_East = rs![IMG_East2]
End If
If strNewIMG_South <> strPrevIMG_South Then
strNewIMG_South = rs![IMG_South2]
End If
If strNewIMG_West <> strPrevIMG_West Then
strNewIMG_West = rs![IMG_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) "
 
strSQL = strSQL & "VALUES (" & "'" & intNewEasting_UTM & "'" & ", " & "'" & intNorthing_UTM & "'" & ", " & "'" & strFSVeg_Location & "'" & ", " & "'" & strFSVeg_Stand_No & "'" & ", " & "'" & intNewPhoto_Year & "'" & ", " & "'" & strIMG_North & "'" & ", " & "'" & strIMG_East & "'" & ", " & "'" & strIMG_South & "'" & ", " & "'" & strIMG_West & "'" & "); "
 
'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 = strNewFSVeg_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 & "'" & "); "
 
'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

[Robeen]

The problem is that your recordset [see your 'strSQL = ' string] does not have a field named "[Photo_Year2]".
It has a field named "[Photo_Year]" - and you should be getting your value from there.

By the way - you will have the same error with the other fields you are trying to retrieve:
rs![IMG_North2], rs![IMG_East2], rs![IMG_South2], rs![IMG_West2] . . . do not exist in your strSQL either.

I know how you feel about getting this to work.
This logic is painstaking.
But . . . I have always found that the reward of seeing it run like it should makes the effort worthwhile.

I'm a little concerned that you had to take out the error routine.
Typically, that will give your program a way to tell you that there is an error AND you can get Access to tell you WHY the program went to Error - using a built-in Error Message that is generated when the program encounters an error. All you have to do is tap into that error message.
Check this page when you get a chance. It will give you better details than my code had in it on how to REALLY use the Error handling mechanism in Access.
http://allenbrowne.com/ser-23b.html

[Robeen]

P.S. June7 posted this on another thread that I was involved in.
You might find this more helpful and reliable than the code I gave you.
Check it out.
http://allenbrowne.com/func-concat.html
Let me know if this is any good - so I'll know to recommend the site in future.
All the best!!

[admessing]

Okay...working with a DB SQL/VBA officcionado in the UK....I may have an SQL solution...Stay Tuned for a possible public "How to" if it works.