form editing mode

[alpine82]

I am designing a traqcking database for one of my clients in which they use barcodes. I have form which using the barcodes and some typing they can enter either "IN" or "OUT" for a secific thing. Pretty simple. The only catch is that I do not want to see everytime it was checked out or in, I want to see only the last time it was checked out or in. Basically I need a current listing, not a history. Can anyone see how I can do this? Is there a way for the form to update the record that has the same barcode? I just have no idea.

 

[dcx693]

Do you want to capture every In and Out of each item (to keep a history)? Or do you only want to retain the most recent transaction?

The former is easiest. Just record the item tracking number using the barcode, whether it's an In or Out transaction and the date/time. Finding the most recent transacation is just a matter of running looking for the most recent date.

If you want to only retain the most recent transaction, you can do that also. Easiest would be through a form interface. Scan the barcode, have the form find the matching record, and overwrite the record with the "In" or "Out" and the date/time.

 

[illy2k]

How?

How can you have it lookup the record and edit it? Without a human person going in and doing it manually?

 

[dcx693]

To find the record:

You can do a FindFirst for your record on a clone of the form's underlying recordset and then setting the form's bookmark to the same bookmark value that the found record has.

Here's an example. Say you've got a combo box with employee names and IDs (where ID is the bound column) and you want to select a name from the combo box and have the form pull up the matching record. Here is some sample code:

Private Sub cboSelectID_AfterUpdate()
Dim rst As DAO.Recordset
 Set rst = Me.RecordsetClone
 rst.FindFirst "[EmployeeID]=" & Me.cboSelectID
 Me.Bookmark = rst.Bookmark
 rst.Close
 Set rst = Nothing
End Sub


Another way to handle this is to filter the current form for the record that you're looking for. That's simpler, but it produces a different result. It only shows the matching record. It doesn't move the form to that record while still showing other records. That may be what's best here though. Here's some sample code:
Private Sub cboSelectID_AfterUpdate()
 Me.Filter = "[EmployeeID]=" & Me.cboSelectID
 Me.FilterOn = True
End Sub

In this case, you'd have an input field for the barcode data. Just put some code like that above into the AfterUpdate event.

 

[dcx693]

Actually, you could also just skip everything with the form if you didn't want to deal with the transactions at all. You could just write an update query that looks at the last transaction for the item, finds if it was an "In" or "Out" then update it without any real "human" intervention.

You might want the intervention at some point though, if you're checking something in, and it was already marked "in" or vice versa.