Question Trigger a query or report from bar code input?

[G1ZmO]

I have serial numbers and other details as records on my 2003 database.

What I have done is run a report which asks for a serial number and thereafter prints a report for that serial number.

However, I'd like to set this up so that there is no keyboard/mouse input required.

Can I somehow cause Access to run a report triggered on a specific input from a bar code scanner?

My thought was to have a barcode which reads "NEW UNIT" so that the user could just scan that barcode then scan the barcode on the unit and a report would print automatically then scan "NEW UNIT" again and then the next one.

Any ideas if this can be done?

Thanks

Paul

 

[neileg]

Most scanners can be configured to add a return or a tab to the end of the barcode. This will trigger the After Update event of the control that is populated by the barcode. Use this to run some VBA code to test the value.

 

[G1ZmO]

Yes, but the problem isnt adding the return character after the barcode it's getting access to trigger a report with ONLY the barcode input.

Just a thought (which might be an easier solution): Could I get the query which asks for the serial number then prints the report to run in a loop? so that it just asks for another serial straight away?

I don't have any coding knowledge btw :s

This is the current code for the button which runs the report:

Private Sub Command393_Click()
On Error GoTo Err_Command393_Click

    Dim stDocName As String

    stDocName = "Small Label"
    DoCmd.OpenReport stDocName, acNormal

Exit_Command393_Click:
    Exit Sub

Err_Command393_Click:
    MsgBox Err.Description
    Resume Exit_Command393_Click
    
End Sub

 

[neileg]

That's what the After Update event does. Put your code into the barcode text box After Update event and it will run when the return character is processed.

 

[G1ZmO]

Umm I dont seem to have an After Update option in the properties of the button for that report.

I'm probably missunderstanding you

 

[neileg]

You said you wanted it to run automatically so you don't use a button. As I said, put your code into the barcode text box After Update event and it will run when the return character is processed.

 

[G1ZmO]

Sorry Neil I'm having difficulty understanding what you mean.

What code?

And the barcode field on the report is an activeX control which takes it's data from the serial number entered from the query.

The activeX control has no After Update option

Wouldnt getting the report to run in a loop be easier? (not that I know how to do that either lol)

 

[neileg]

This code

Private Sub Command393_Click()
On Error GoTo Err_Command393_Click

    Dim stDocName As String

    stDocName = "Small Label"
    DoCmd.OpenReport stDocName, acNormal

Exit_Command393_Click:
    Exit Sub

Err_Command393_Click:
    MsgBox Err.Description
    Resume Exit_Command393_Click
    
End Sub

Not the barcode field in the report, the barcode control on the form where you have your button currently.

Let's start again.

You need to have a form with a textbox to capture your barcode. Your report should be based on a query that uses the barcode in the form as a criterion so that the report is based on the barcode. To capture the barcode you need the barcode text box to have focus. This text box has a series of events that you can see in its property sheet. The After Update event is the one that 'fires' when you have entered data and committed it, in this case with the 'enter' that the scanner provides. When you scan the barcode it populates the text box, triggers the after update event which runs the code that prints the report.

 

[DCrake]

If your barcodes are all the same length then you need to setup an input mask for the acceptance of the barcode in a textbox on a form. when the form opens set the tab index for this control to 0 or use the SetFocus command. In addition one of the properties of a textbox is AutoTab, by default it is set to No, change this to Yes. What happens then is as soon as the barcode is scanned it automatically sends a tab keystroke to the control, effectively moving to the next control on the form. Then on the LostFocus for the control use the DoCmd.OpenReport command to print the relevant report based on the contents of the said textbox.

David

 

[G1ZmO]

The After Update event is the one that 'fires' when you have entered data and committed it, in this case with the 'enter' that the scanner provides. When you scan the barcode it populates the text box, triggers the after update event which runs the code that prints the report.

But I already have this! I open the form which requests the Serial number which I enter via the barcode scanner and it automatically prints the report.
I already have this bit working however my original question was if there was some way to have ms access trigger the opening of the form in the first place by using a specific barcode scanned entry thus cutting out the need for mouse/keyboard input altogether.

There are a LOT of units to scan so I was looking for a solution that would avoid the need to click the button for the form to open. This way someone with a wireless scanner can open the form remotely, scan a serial and the report for that unit is printed automatically without him having to go back to the pc and click to open the form each time.

My other suggestion about a looping form would be a workable solution also so that the form button would only need triggered once and thereafter serial after serial could be scanned and reports printed automatically.

Thanks for your help,

Paul

 

[neileg]

With respect, this is the first mention of opening the form automatically. Everything you have said up to now has referred to a report. I have been answering the question you asked even if this is not the question you wanted answered.

So far as I know, and I don't claim any particular expertise on this, Access will only accept an input into a field or a control on a form if it has focus. So for the scanner input to be accepted, you need a form open. I now understand your comment about a looping form (although you referred to a report). The practical problem I can see there is that the user will have no indication that the previous report has run and the form refreshed and ready to accept input. I would have thought that it would be better to populate a table with the barcodes you want, and then return to the PC and run the report(s) at one go.

 

[DCrake]

Neil

I totally agree with your comments this is the first instance where the mention of a wireless scanner was introduced. And I concur that if the scanner was configured to add information to a holding table or some sort of file then the user would only have to return to the pc once to fire of a command to transfer the information from the holding table/file to a query which inturn would be the underlying recordsource of the report with a page break after each barcode.


I can understand the theory of Least Administrative Effort (LAE) and condone its implicitly but there are times when human intervention is required. What happens if the PC is switched off or someone is using another part of the application? It's like printing to report in another room/building what happens if their is no paper in the bin? you may think that it's printed ok but with phyically walking to the printer to pick up the output its all an assumption.

David

 

[G1ZmO]

Sorry Neil, I perhaps didnt make clear my intentions at the start of the post properly. Didn't mean to mislead you.

Yes, I admit that this was the first mention of the wireless scanner, the reason being that I only just considered this as an option.

Normally I (or other operator) would need to pull the pallet to the station where the database was running to scan & print the reports.

I do see the potential problem with regards to remote printing and I suppose this can be tested later and scrapped if there are problems with it.

Ok, assuming that triggering a form from a barcode input is not an option, is there a simple way of making the form LOOP so that the users intervention with the pc is minimised?

Thanks Neil and David.

Paul

 

[neileg]

Yes, but...

You can add a line in your code that deletes the current entry in the barcode control and sets the focus on the control, ready for the next input. However, the running of the report and resetting the form takes a finite length of time. This time can be extended if, for instance the printer jams, or something like that. There's a risk, therefore, that the form is not ready to accept data when you take the next scan. The first you know of this will be when the label isn't at the printer.

You seem to suggest that the printer and the PC are at the same location. That reinforces my view that you should stack the scanned codes and then print in a batch.

 

[G1ZmO]

Thanks Neil

You can add a line in your code that deletes the current entry in the barcode control and sets the focus on the control, ready for the next input.

Afraid I don't know how to go about this as I have no coding knowledge

There's a risk, therefore, that the form is not ready to accept data when you take the next scan. The first you know of this will be when the label isn't at the printer.
You seem to suggest that the printer and the PC are at the same location. That reinforces my view that you should stack the scanned codes and then print in a batch.

Yes, the printer is at the same station as the PC and scanner (assuming were not using a wireless scanner now). I don't see a huge problem with just having the form loop as, if a label doesn't print then the user would just stop, fix the printer issue and rescan the serial barcode. This seems to me to be the easiest solution however I don't know how to go about it. I saw an "Auto Repeat" option in the properties for the form but it didn't do anything when set to "yes"

Afraid I don't know what you mean about stacking the scanned codes.

Thanks again,

 

[roelandkim]

Another option is to use the scanner to pass a function key to access. Not all scanners have this ability, and the particular model that my offices uses actually can't do this. If the scanner does have the ability to pass a function key, you could code an On Key Down event to open the new form. In the case below, pressing the F9 key while the form is open will open another form. You would need to figure out how how to represent F9 as a barcode (probably a hexidecimal) assuming your reader has that capability. You could create a form that remains open but hidden in the background, and assign F1-F12 to a specific function in the database. Whenever a barcode is scanned that represents one of those functions, you can code in an action (opening a form, running a macro, etc...)

The below code would be replaced on one form, and when F9 is pressed would call another form.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Form_KeyDown
Select Case KeyCode

Case vbKeyF9
DoCmd.OpenForm "Frm_MyForm"
KeyCode = 0

Case Else
KeyCode = KeyCode

End Select

Err_Form_KeyDown:
Exit Sub
End Sub

Another option would be to select a regular key like the P key (VbKeyP), anytime a barcode is scanned with the letter P in the barcode while the form is open would trigger whatever even you want.



Not sure how well this would work, but it may be worth looking into.