CharlesDavenport
New member
- Local time
- Today, 18:37
- Joined
- Dec 7, 2020
- Messages
- 26
OK....
Im sure that everyone will tell me I should do this in a different way than what i have come up with.
I have a query which is appending a table and fills out two columns with the data from the query being a result of a search (which I have already had help with in the query forum).
However, I need the Primary Key ID number from the current form to be added to a column for each record appended to the table.
I have therefore written the below VBA (Which works other than Access crashes, which i assume is due to the loop not closing.
I think it is due to the "Do Until .EOF" but what other loop can I use?
Im sure that everyone will tell me I should do this in a different way than what i have come up with.
I have a query which is appending a table and fills out two columns with the data from the query being a result of a search (which I have already had help with in the query forum).
However, I need the Primary Key ID number from the current form to be added to a column for each record appended to the table.
I have therefore written the below VBA (Which works other than Access crashes, which i assume is due to the loop not closing.
I think it is due to the "Do Until .EOF" but what other loop can I use?
Code:
Private Sub Command30_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConflictResultsClients Query"
DoCmd.SetWarnings True
Dim FrmNo As String
Dim rs As DAO.Recordset
FrmNo = Forms![ConflictsofInterestForm]!Form_Number.Value 'set variable as text box on form
Set rs = CurrentDb().OpenRecordset("Conflict Results", dbOpenDynaset) 'Open table that contains Apended Records from Query
With rs
Do Until .EOF 'do this until End of Function
.FindFirst "[Form Number] = 0" 'Find the first record that = 0
.Edit
![Form Number] = FrmNo 'Change field of that record to the new value
.Update
Loop
End With
rs.Close
Set rs = Nothing
End Sub