Hello, here is the question. I have two tables, a tempProperty and a Property table. The TempProperty table gets created while being imported from an excel sheet. The TempProperty table will hold the current data and it needs to be compared to the old data. The fields on them will be the same. What I am attempting to do run two record sets to pull the information from both to match simultaneously and to compare the data while running my code. I know this can be done with an update query, but I have a lot more I want to do with this if I can get it to work. Any help would be greatly appreciated.
I will post the starter code I have below. Oh, and I apologize if I don't respond back right away, I'm 8 hours ahead of the U.S.
I will post the starter code I have below. Oh, and I apologize if I don't respond back right away, I'm 8 hours ahead of the U.S.
Code:
Dim TempGPNbr, TempAssignedEmp, TempRecDate, TempSiteID, TempTransFromSite, TempStatus, TempComments, TempShops As String
Dim PropGPNbr, PropAssignedEmp, PropRecDate, PropSiteID, PropTransFromSite, PropStatus, PropComments, PropShops As String
Dim rs, rs2 As Object
Dim ddbase, ddbase2 As Database
str = "SELECT tbl_PropertyImport.GPNbr, tbl_PropertyImport.SubCategory, tbl_PropertyImport.Description,"
str = str & " tbl_PropertyImport.UI, tbl_PropertyImport.SN, tbl_PropertyImport.AssignedPerson,"
str = str & " tbl_PropertyImport.RecDate, tbl_PropertyImport.TurnInDate, tbl_PropertyImport.SiteID,"
str = str & " tbl_PropertyImport.TransferFromSite, tbl_PropertyImport.CatID, tbl_PropertyImport.Comments,"
str = str & " tbl_PropertyImport.Status, tbl_PropertyImport.Shops"
str = str & " FROM tblProperty RIGHT JOIN tbl_PropertyImport ON tblProperty.GPNbr = tbl_PropertyImport.GPNbr"
str = str & " WHERE (((tbl_PropertyImport.GPNbr)=[tblProperty].[GPNbr]));"
str2 = "SELECT tblProperty.GPNbr, tblProperty.SubCategory, tblProperty.Description, tblProperty.UI,"
str2 = str2 & " tblProperty.SN, tblProperty.AssignedPerson, tblProperty.RecDate, tblProperty.TurnInDate,"
str2 = str2 & " tblProperty.SiteID, tblProperty.TransferFromSite, tblProperty.CatID, tblProperty.Comments,"
str2 = str2 & " tblProperty.Status, tblProperty.Shops"
str2 = str2 & " FROM tblProperty"
Set ddbase = CurrentDb
Set rs = ddbase.OpenRecordset(str, dbOpenSnapshot)
''rs.MoveLast
''rs.MoveFirst
Set ddbase2 = CurrentDb
Set rs2 = ddbase2.OpenRecordset(str2, dbOpenSnapshot)
''rs2.MoveLast
''rs2.MoveFirst
TempGPNbr = rs(0)
TempAssignedEmp = rs(5)
TempRecDate = rs(6)
TempSiteID = rs(8)
TempTransFromSite = rs(9)
TempComments = rs(11)
TempStatus = rs(12)
TempShops = rs(13)
PropGPNbr = rs2(0)
PropAssignedEmp = rs2(5)
PropRecDate = rs2(6)
PropSiteID = rs2(8)
PropTransFromSite = rs2(9)
PropComments = rs2(11)
PropStatus = rs2(12)
PropShops = rs2(13)
example:
If rs(0) = rs2(0) And rs(5) = rs2(5) And rs(6) = rs2(6) And rs(8) = rs2(8) And rs(9) = rs2(9) And rs(12) = rs2(12) And rs(11) <> rs2(11) Or rs(13) <> rs2(13) Then
MsgBox "gotOne"
End If