append a list of barcodes to a table and run VBA code for each of them

[killerflappy]

Hi guys,
I have a program in Access 2016 for barcode scanning and matching.

I receive files with orders (barcode, product, quantity, shipdate) and make an orders-table of it. Then I have a form/scantable for scanning the barcode and fill some columns with the information of the orders-table. There is VBA code after scanning each barcode to do some checks and to get data from the orders-table. With this form/scantable we can make query’s and reports.

Now sometimes we don’t receive the orders-file but we receive the physical products with the order information printed on it. At this moment we scan these barcodes and put the products in the right location. After we receive the file(s), we are matching the barcodes in Excel.

I want to be able to do this in Access. So I think I have to create a separate form/table for scanning these barcodes without the order information. After receiving the file I want to process the barcodes of this table to the form/scantable with the VBA code run for each barcode.

I’m a newbe in VBA. Can some-one put me in the right direction?

Maybe I can just scan them as if there would be data, and afterwards run the VBA-code for this barcodes again someway.

 

[Ranman256]

Would it HAVE to be VB? Most everything can be done via queries.
(just like loading the codes)

 

[killerflappy]

Hi Ranman. No. If it can be done with queries, that would be nice. Can you put me in the right direction? The VBA code per line make some checks. For instance, if the barcode does not match an order it shows an error. It would be nice to keep these kind of things.

 

[killerflappy]

I made a frmScans/tblScans where I put the barcodes.
Via a VBA Module I read the barcodes from the tblScans and write them with the rest of the barcodes in tblMutations with the code below.

Function tblScansRead()
Dim StrSQL As String
StrSQL = "INSERT INTO tblMutations (barcode)SELECT barcode FROM tblScans;"
DoCmd.RunSQL (StrSQL)
End Function

The barcodes are placed in the tblMutations. But I want to run the VBA code that run for each barcode in the frmMutation (with "After update"-event).
Is there a way to do this?

 

[arnelgp]

create a Form that is bound to tblMutations
in that way there is no need for you
to run an Insert Query against this table.
all you have to do is put your cursor
in the barcode textbox, scan the barcode.
when you move to another record or to new record
on the form, the record will be automatically
saved.

 

[killerflappy]

Hi arnelgp,
Thanks for the reply. This part I understand and this is the normal way of scanning.
But now I want to save some scanned barcodes and read them later (when I receive the matching order-file). Then I want to be able to put the barcodes in the tblMutations and run the VBA code for each of them.

Maybe there is a way to edit my code and use a "for each" barcode in tblScans and use the same code that is used when directly scanned to frmMutaions/tblMutations with the "after update"-VBA?

 

[Minty]

Cross posted here http://www.accessforums.net/showthread.php?t=69010

Flappy please have a read here https://www.excelguru.ca/content.php?184

 

[arnelgp]

Yea there is add code to the form's afterupdate event:

Private Sub Form_AfterUpdate()
Docmd. Runsql "insert into tblMutations (barcode) select " & """" & me. Barcode & """"