VBA coding help (1 Viewer)

[admessing]

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 and file base for each entry)
Table: Project (Stores the Unique_ID, project name, and project area)
Table: Spatial (Stores the Unique_ID, UTM coordinates (2), LAT/LON, R2Veg_Loc (specific to the map layer), and Stand_Num (forest stand))

Queries: Photo_Link Query (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.
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.
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 should look something like this:

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

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

I need to get this done ASAP.

Thanks,

Adam

 

[admessing]

Anyone with thoughts?

 

[jdraw]

What version of Access? Do you have a data model and test data?

Could you describe/expand on this
Photo_File (stores the Unique_ID and file base for each entry)

I would think this is the unique_id of the image. I'm tryiing to sort out why you need to build Photo_link and build the file paths to the images. Wouldn't the full path to the image be in Photo_file?

Perhaps it's just a little confusion -- you are familiar with your set up, we are not.

 

[admessing]

What version of Access?

I am using Access 2007, but the DB is in 2002-03 format.

Do you have a data model and test data?

Yes. I will upload in my next post.

Could you describe/expand on this
Photo_File (stores the Unique_ID and file base for each entry)

I would think this is the unique_id of the image. I'm tryiing to sort out why you need to build Photo_link and build the file paths to the images. Wouldn't the full path to the image be in Photo_file?

Perhaps it's just a little confusion -- you are familiar with your set up, we are not.

The Photo_File table holds the Unique_ID which is linked to the other tables. The file base is the common portion of the file name that applies to 4 photos associated with the record. All data is entered using a data entry form that guides them through the process one record at a time. Using just the common portion of the file name allows for faster data entry on the part of my end-users. A make-table query is used to create and populate the file paths and complete the file name and extension of each photo. This is necessary since the photos are stored on a central server and the path is rather long. We use a standardized naming structure that allows for this:

Example File Name: 2011CP0802006201N.jpg

The direction, in this case North ("N"), and the image type (.jpg), are the unique elements of the photo name. Whereas the rest of the name is common to all 4 photos associated with a coordinate set. You can see why a query is used...this is a sample file path for a single image:

T:\Service\National\ArapahoRoosevelt\Program\2400TimberMgmt\CLRD\2470Silviculture\PhotoArchive\ARNF_Photo_Archive\2011\2011CP0802006201N.jpg

This is a lot to type when you have to do it for four images per coordinate set, not to mention when you have 120 photos a day to enter and an hour to do it. Also, my end-users are not exactly the office type...they are field technicians with very little spare time, so simplicity in data entry on their part leads to fewer errors and time savings.

I will upload my sample DB this afternoon after I remove the sensitive stuff and chop out the bits that you folks don't need to worry about.

Adam

 

[admessing]

Here is my sample DB. The only query remaining is the Photo_Link query, since this is the one that builds the links.

I have left out the other 2 since they don't work the way that I want/need.

The tables have _MOD appended to the names to denote that this is the test data to build and debug queries/scripts without compromising the real DB.

Adam

 

[smig]

why storing all pictures under the same record? you will need new field for every new picture.
make no sense

 

[admessing]

Each Unique_ID links to the same ID in several tables. Spatial locations (map coordinates), Project, and Photo files. The photo paths that get built are essentially hyperlinks that are read by our mapping software. The coordinates put a point on the map...you click the point in the map and a popup window appears with the relevant information for that point including the links, one for each direction. Click a photo link and the photo appears. Some points will have multiple sets of photos from different years. This DB and the associated photos are used to create map layers, and to aid in the management of a forest ecosystem over time by allowing visual tracking of changes in the forest structure and health.

And yes, this makes it so that there is a field for each picture. It has to be this way since our mapping software will only display a single record row for a coordinate set. Hence breaking the "rules" of Access DBs.

 

[admessing]

Please Assist

Anyone with ideas? I am willing to try anything at this point. If what I ask cannot be done in VBA please tell me. Can it be done via SQL queries, without having duplication?

Do I need to restructure the tables/DB? (one note on this...it must remain in the .mdb format so that our mapping software can read it, and as such cannot use the rather handy feature of attachments or embedded images).

If you need any info please let me know.

 

[admessing]

Okay folks.... I have made a little headway, but am still experiencing issues with my VBA.


The good results:

1. It appends a table.
2. I can isolate the unique easting coordinate.

The bad results:

1. The northing coordinate is wrong, and the other fields may be wrong too.
2. The second set of images and the photo year won't come in.

Here's the code (note that it refers to Photo_Link table...this is the same table as Photo_Link_MOD....I just renamed it in my copy of the test DB so that I wouldn't have to mess with it once in the real DB):

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

Anyone with a solution/suggestion?

 

[jdraw]

Your Dim statements as you have them do NOT do what you think. You MUST explicitly Dim a variable as a specific datatype OR that variable will be a Variant by default.

You can
Dim i as Integer, s as String, k as Integer
or
Dim i as Integer
Dim s as String
Dim k as Integer

But you CAN NOT
Dim i,k as Integer

This will result in i as Variant and k as Integer

 

[admessing]

Your Dim statements as you have them do NOT do what you think. You MUST explicitly Dim a variable as a specific datatype OR that variable will be a Variant by default.

You can
Dim i as Integer, s as String, k as Integer
or
Dim i as Integer
Dim s as String
Dim k as Integer

But you CAN NOT
Dim i,k as Integer

This will result in i as Variant and k as Integer

Could you please expand upon this? I'm not sure what exactly you are referring to in your statement.

 

[tehNellie]

The variable declaration:

Dim [b]strTest[/b], strTest2 as string

Does not result in strTest being a string datatype, it is a variant.

Dim strTest as string, strTest2 as string

OR

dim strTest as string
dim strTest2 as string

Does result in strTest being a string datatype.

i.e. in your code:

Dim intEasting_UTM, intNorthing_UTM, intPhoto_Year As Integer

Only intPhoto_year is an integer datatype, the other two are Variants.

 

[admessing]

The variable declaration:

Dim [B]strTest[/B], strTest2 as string

Does not result in strTest being a string datatype, it is a variant.

Dim strTest as string, strTest2 as string
 
OR
 
dim strTest as string
dim strTest2 as string

Does result in strTest being a string datatype.

i.e. in your code:

Dim intEasting_UTM, intNorthing_UTM, intPhoto_Year As Integer

Only intPhoto_year is an integer datatype, the other two are Variants.

Okay...fixed that. Now the code does nothing. I am at a loss

 

[tehNellie]

Turn off the error handling, it's telling your code to close everything up and exit the sub (as it stands it's not a Function, it doesn't return anything) when it encounters a problem without doing anything to warn the user that there's a problem.

Add OPTION EXPLICIT under Option compare Database in your module and things will start to become apparent. You have undeclared variables for a kickoff because it looks like you intended to declare them as integers at one point but compare them as strings elsewhere (just in the variable name, they're not actually declared at all, let alone as anything).

Option Compare Database
[b]Option Explicit[/b] ---- add this line

Function Get_DB_Values()


[b]On Error GoTo Error_Handle[/b] --- comment this out


<snip>
[b]
Error_Handle:
  Resume Exit_Get_DB_Values
  End With
[/b]  --- If you're going to add error handling, have it actually, 
you know, handle the error rather than just exit with no warning.

Once you've resolved the variable declaration and with the error handling not dumping you out of code when it encounters a problem it should be a lot easier to determine what isn't working and why.

 

[admessing]

Okay...got that commented out and added the Explicit to the top. But, am receiving Run-Time error '6': Overflow on line 56 (red) of the code:

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 Integer
Dim intNorthing_UTM As Integer
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 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 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 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, 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
[COLOR=red]intEasting_UTM = rs![Easting_UTM][/COLOR]
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
'Same Field1 - concatenate values.
intNewNorthing_UTM = rs![Northing_UTM]
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 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, 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 & "'" & "); "
 
'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

 

[tehNellie]

What's the actual value in that field?

It's probably too big for an integer value which in VBA is -32,768 to 32,767.

A Long Integer can hold from -2,147,483,648 to 2,147,483,647.

Check what the Data type is on the column in the table, and declare your variables to match for all your variables that will hold Table Data.

 

[admessing]

Okay....starting to get somewhere, but still a ways off from where I need to be....

I made adjustments based upon your suggestions, as well as a couple from what the error(s) returned. Now I am getting a Run-time error 3265: "Item not found in this collection". The affected line is in red below, based upon how it is acting...I assume that the successive declarations below it will yield the same error:

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
    'Same Field1 - concatenate values.
        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.....Yeah Right
        If intNewPhoto_Year > intPrevPhoto_Year Then
            [COLOR=red][B]intNewPhoto_Year = rs![Photo_Year2]
[/B][/COLOR]        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

 

[tehNellie]

The clue is in the error message.

That query doesn't have a field called Photo_Year2 in it.

 

[admessing]

Okay....if that is the case....how do I adjust the code to insert the "new" Photo_Year (and the other "new" attributes) into the table in the right row??

Please add coments to the code I pasted in my last post so I know where to go and can see what is happening.

 

[tehNellie]

I'm going to start charging you by the line

 strSQL = "
SELECT 
Easting_UTM, 
Northing_UTM, 
FSVeg_Location, 
FSVeg_Stand_No, 
Photo_Year, 
[b]Photo_year2[/b], 'Assuming that field exists in the table, include it in your recordset
IMG_North, 
IMG_East, 
IMG_South, 
IMG_West 
FROM Photo_Link 
ORDER BY Easting_UTM "

There is the query SQL that your recordset is based on, there is no field Photo_Year2 in it.

If photo_year2 isn't a field in that table/Query, how should that value be arrived at?

I'm not trying to be snotty, but while Access has more than its fair share of obscure error messages it does occasionally manage to pretty accurately describe the problem.

Your code is a little overcomplicated for what it's actually doing, but we'll worry about that later, get it working first and then figure out how to make it work efficiently.