Updating Field Value from Checkbox 1

[MontgomeryPete]

I have a checkbox on a form that is used to indicate that an item has been dispatched. We want to update a field in a table with the time when the box is checked. I have the code in an event session "On Click."

Private Sub Form_Click()
Update Ride, TimeDispatched
SET TimeDispatched =
IIf
([Ride].[Dispatched]= -1,
[Ride.TimeDispatched] = Time()
End Sub

The code fails, and would appreciate some help on how to execute this subroutine.

Thanks, Pete

 

[Pampers]

Maybe you can put it in the before-insert event.

Pampers
Keeping it simple can be complicated

 

[TheAceMan1]

How are ya MontgomeryPete . . .

The code should reside in the [blue]AfterUpdate[/blue] event of the combobox and should look like:

[blue]   Dim SQL As String
   
   SQL = "UPDATE Ride " & _
         "SET [TimeDispatched] = " & Time() & ";"
   DoCmd.RunSQL SQL[/blue]

However be aware, because there's [blue]no criteria in the SQL you provided[/blue] you'll update all records! I don't believe this is what you wanted. If I'm right, then theSQL changes to:

[blue]   SQL = "UPDATE Ride " & _
         "SET [TimeDispatched] =" & Time() & " " & _
         "WHERE ([[purple][b]PrimaryKeyName[/b][/purple]] = [red][b]'[/b][/red]" & Me![purple][b]PrimaryKeyName[/b][/purple] & "[red][b]'[/b][/red]);"
   DoCmd.RunSQL SQL[/blue]

Here the [purple]PrimaryKey[/purple] of the current record is used to ping the actual record to change in the table. Note: if the primarykey is text, remove the single quotes in [red]red[/red].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

MontgomeryPete . . .

You need to consider what happens [blue]when a user unchecks the checkbox[/blue] (checkbox set by mistake)!

See Ya! . . . . . .

 

[MontgomeryPete]

Thanks for the additional thoughts. We will have to set the field back to null. Naturally, when I asked the user, he said "Oh, sure, we change that all the time."

Thanks again, Pete