Update a table using a form

[Lynniebud]

Hi,

I'm a relative beginner at VBA and I'm trying to use a form to update a table. The form is used to get an agent name, start and end date. When they hit the "update" button, I want to go to the table, select all records where the agent name (called Fullname in the table) matches and the field called "event_date" is between the start and end dates. I have the below code, which isn't working. Any hints would be gratefully received!


Set rst = CurrentDb.OpenRecordset(Table)
rst.MoveFirst

Do While rst.EOF = False
If rst.Fields("Fullname").Value = Me.Agent And Me.StartDate <= rst.Fields("event_date") And rst.Fields("event_date") <= Me.EndDate Then
rst.Edit
rst.Fields("CompleteDate").Value = DATE
rst.Fields("Comments").Value = "Autofilled"
rst.Update
End If
rst.MoveNext
Loop

rst.Close

 

[rzw0wr]

If your tables are set up properly this should happen automatically.
Take a good look at your tables to make sure they are well normalized.
Google normalization.

Dale

 

[Lynniebud]

Sorry Dale, I don't understand what you think I should do. Could you explain a little more please?

Thanks for your help.

 

[JHB]

... Any hints would be gratefully received!

Instead of using a recordset to update the table, use an update query.
Use the QBE (Query By Example) window (and select the Query type: Update), to get the right syntax first, the copy the SQL string to the code behind your button.
Use the keyword "BETWEEN" for the dates.

BETWEEN 1.date criteria and 2.date criteria