Sorry if this is long-winded but here goes:
I have the following (example) recordset called "Import Query" that I am trying to update using code:
What I need to do is check each record to see which field had the last "Y" and enter a "Y" into the next field. For example James last had a "Y" in M3 so now needs one in M4. Once it is inserted I need to move to the next record and repeat the process (in this case insert a "Y" in M2 for Peter and so on).
I have written the following code but keep getting Run-time Error 3020. Also I think I need to put some code between the End If statements so that once a field is updated it moves on to the next record:
Any advice would be much appreciated.
Cheers
I have the following (example) recordset called "Import Query" that I am trying to update using code:
PHP:
First Name M1 M2 M3 M4 M5
James Y Y Y
Peter Y
George Y Y Y YWhat I need to do is check each record to see which field had the last "Y" and enter a "Y" into the next field. For example James last had a "Y" in M3 so now needs one in M4. Once it is inserted I need to move to the next record and repeat the process (in this case insert a "Y" in M2 for Peter and so on).
I have written the following code but keep getting Run-time Error 3020. Also I think I need to put some code between the End If statements so that once a field is updated it moves on to the next record:
Code:
Public Function fImportdata()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Import Query", dbOpenDynaset)
With rs
.MoveFirst
.Edit
While .EOF = False
If rs![M1] = "Y" And rs![M2] <> "Y" Then
rs![M2] = "Y" ' Will update M2 with a Y if M1=Y and M2 is null.
End If
If rs![M2] = "Y" And rs![M3] <> "Y" Then
rs![M3] = "Y" 'Will update M3 with a Y if M1=Y and M2 is null.
End If
If rs![M3] = "Y" And rs![M4] <> "Y" Then
rs![M4] = "Y"
End If
If rs![M4] = "Y" And rs![M5] <> "Y" Then
rs![M5] = "Y"
End If
rs.Update
rs.MoveNext
Wend
End With
rs.Close
End FunctionAny advice would be much appreciated.
Cheers
