Access - macro type thing!

[SorrelForbes]

I'm helping DH develop a simple db for work which he can use to record all their CD-Roms etc.

There is one table and one form. There is a field (a) which is going to be filled via a bar code scanner. DH wants the next field (b) to update with the current day's date when field (a) is entered/amended.

I've had a little play with building expressions using the After event menu but to no avail.

Can anyone help?

TIA

Happy to talk you through this in more detail if you need it...

You need to copy the below code into the VBA code for the sheet you want this to work on. To do this click Alt + F11 and paste the code into the correct sheet. Then make these changes to the code;

Where I have put "A:A" this needs to reflect the column which will be filled in by the bar code scanner.
Where i have put "B" you need to change the letter B to reflect the column which you want filled in by the date

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
Else
Application.EnableEvents = False
thiscel = "B" & Target.Row
ActiveSheet.Range(thiscel).Value = Now()
Application.EnableEvents = True
End If
End Sub

Essentially what the code I have given you does is;
Starts whenever a change is made to a cell in column A
Puts todays date/time in column B on the same row that the cell in column A was changed in

In your example you need to replace column A (A:A) with the column that the bar code scanning results will enter into, and you need to replace column B with the place you want the date entered into.

Thank you so much for replying. I've followed your instructions (to the letter I think) but it doesn't work.

When you say:
You need to copy the below code into the VBA code for the sheet you want this to work on. To do this click Alt + F11 and paste the code into the correct sheet.

Do you simply mean to open up the table in worksheet view and then click Alt + F11?

That code is for Excel. Won't work in Access.

Presumably the form will be open when you scan the bar code? In which case you want a text box linked to the time/date field which defaults to the current date/time when the bar code field is changed.

So try using the Change event of the control on the form which is linked to the bar code details, something like:

Private Sub txtBarCode_Change()
txtDate = Now
End Sub

Where txtBarCode is the name of the bar code text box control on the form and txtDate is the name of the date and time text box.

Incidentally, if you select the bar code text box, then go the the properties pane, select Events and then next to Change click the little button with the three dots it will open up the code window for you in the right place.

It works!

Thank you both so much. It's such a long time since I've had to do anything in using Access or VBA! I'm a graphics person really. Adobe PS5 is my world

I'm going to be very cheeky and ask another (related) question.

DH has to be able to carry out a muster of all the items every three months. To do this he would like to just re-scan the bar code to find the relevant record and then have the Date Mustered field automatically update to today's date.

I have created a new form using the data fields, BarCode and DateMustered and he can find the item by clicking in the search field and then scanning the bar code. But he then has to click into the data field iteself and press return to get the date to update. He's not happy .

I know I can add an actual search button to the switchboard but that only does the same thing.

Is there anyway to do this?

I don't have any experience using barcode scanners with Access. But I would perhaps have a form which triggers the input to the barcode field and then trigger an update query which updates the current date to the matching record.