Update a record automatically (1 Viewer)

[N1029676]

I'm not entirely sure on how to work this but,

How would i create a form/script that would update a record with the current date+time a field based upon the data in another field.

----------------------------------------------------------------
Example:

I type in a PGID into a form drop down box and press enter.
The form finds that record and enters current Time and Date.
----------------------------------------------------------------

We are using a barcode scanner to input the PGID into the form.
This form is being used to turn in items, and we want to keep a history of all items turned in and when they were turned in.

Thank you for your help!

EDIT: Using Access 2003 on WindowsXP Pro

 

[statsman]

The easiest way would be to set the default value for the date and time to Date() and Time() respectivly.
The problem is, the system would overwrite the current values every time you opened the form to that record. If for example you do a search and the wrong record was on the form or you just want to view the record, the date and time would still be updated.

 

[boblarson]

Hey statsman, not quite. If you set a value as default within the TABLE, it will only write on new records. If something is already in there it won't overwrite it.

 

[N1029676]

Yep, i need some sort of script that will do it automatically, i had one that does this when i press a button, but when i do get this running, i will be scanning about 200 items each time, so adding a button press could complicate/ruin the routine. I cant really set that script to run untill after i press enter, (The barcode scanner presses enter automatically). Once you press enter the record is displayed, and then i dont know what to do from there other than pushing that scripted button...

 

[Moniker]

Umm, Bob told you exactly how to do it. In the table that contains ID, PGID, Date, and Time, go to the design view and set the default value for Date to Date() and the default value for time to Time().

No script necessary.

 

[N1029676]

These records already exist, they just do not have time + date values yet.
That default value you are talking about does not apply when you want to modify a record...

 

[Moniker]

So you have an existing table with the aforementioned fields? Assuming your barcode scanning into a form, you'd then update the fields in the BeforeUpdate event of the PGID control. It'd be something like this:

Sub PGID_FieldName_BeforeUpdate (Cancel as Integer)
    
    Dim PGID As String
    
    PGID = YourScannedInBarCode
    CurrentDb.Execute "UPDATE YourTableName SET DateFieldName=#" & Date() & "#, TimeFieldName=" & Time() & " WHERE PGID='" & PGID & "';"

End Sub

Note that this will overwrite the record each time the same PGID is scanned in.

 

[N1029676]

This is what i placed in the code section:

Private Sub PGID_Field_BeforeUpdate(Cancel As Integer)
    
    Dim PGID As String
    
    PGID = YourScannedInBarCode
    Band2.Execute "UPDATE InOut Status SET DateIN=" & Date & ", TimeIN=" & Time() & " WHERE PGID='" & PageID & "';"

End Sub

I am not sure what the point of "PGID = YourScannedInBarCode" is for. Is it a comment/reference note for me or are you defining a variable, and if so how do i set PGID equal to my scanned in bar code value?

 

[Moniker]

I'm trying to tell you to set PGID to be equal to whatever your scanned in bar code is. It's being stored somewhere when it comes in. You have to set the PGID variable to that value for it to work.

 

[N1029676]

Ty

Thank you, this helps me out very much!