rincewind_wizzard
Registered User.
- Local time
- Today, 01:06
- Joined
- Feb 7, 2018
- Messages
- 23
HI all,
I'm trying to assign a sequence number to certain rows in a table - only those where the Migrate? field a mapping table is marked as True. Can't get it to work. I get 'Invalid use of property' on the strsql at the start of the sql statement:
sub testit()
'Now add the sequence number
Dim d As Database, r As Recordset, incnum As Long, strsql As Recordset
incnum = 1
Set d = CurrentDb
Set r = d.OpenRecordset("tblTrentSalaryElementsAll", dbOpenDynaset)
r.MoveFirst
Do Until r.EOF
strsql = "UPDATE tblTrentSalaryElementsAll LEFT JOIN tblMAPElements ON tblTrentSalaryElementsAll.[Permanent Element]" & _
"= tblMAPElements.TrentElement SET tblTrentSalaryElementsAll.SequenceNumber = incnum WHERE (((tblMAPElements.[Migrate?])=True));"
DoCmd.RunSQL strsql
incnum = incnum+1
r.MoveNext
Loop
r.Close
End Sub
I'm trying to assign a sequence number to certain rows in a table - only those where the Migrate? field a mapping table is marked as True. Can't get it to work. I get 'Invalid use of property' on the strsql at the start of the sql statement:
sub testit()
'Now add the sequence number
Dim d As Database, r As Recordset, incnum As Long, strsql As Recordset
incnum = 1
Set d = CurrentDb
Set r = d.OpenRecordset("tblTrentSalaryElementsAll", dbOpenDynaset)
r.MoveFirst
Do Until r.EOF
strsql = "UPDATE tblTrentSalaryElementsAll LEFT JOIN tblMAPElements ON tblTrentSalaryElementsAll.[Permanent Element]" & _
"= tblMAPElements.TrentElement SET tblTrentSalaryElementsAll.SequenceNumber = incnum WHERE (((tblMAPElements.[Migrate?])=True));"
DoCmd.RunSQL strsql
incnum = incnum+1
r.MoveNext
Loop
r.Close
End Sub
