How do I Manage Record Locking

[EdAROC]

I hope I provide enough information here.

I am revising a program that gets info from ERP data source and prints labels that go on shipment pallets. Here's the key issue - a unique number assigned to each label for some customers. A few customers require this, no problem. There is a table CustLblData. One field is the CustomerID and the other is NextNum. In the query, you'll see in the code below, the table is linked by the CustomerID field. They run a batch of labels at a time, not necessarily just one label as needed.

This works great ... as long as we have only 1 printer. We are adding a 2nd printer.

Here's my main issue - the code needs to be modified so that, if each Printer/PC were to run a set of labels for the same customer at the same time no duplicate IDs can be issued. This would happen with the existing code. I moved the CustLblData table to a separate file, each printer's PC will have its own copy of the Access file to print the labels. Here's the existing coding, after which is my logic for the new procedure:

* iNum is the # of labels (each with a unique #) to print.
* Each pass through the For loop creates another record/row.
* "LabelData" is then saved to a text file and barcode software is launched, it uses the exported text file.

For i = 1 To iNum
   ' Increment the sequential number.
   CurrentDb.Execute ("Update CustLblData SET NextNum=NextNum+1")
   
   'Build data for labels
   DoCmd.SetWarnings False
   DoCmd.OpenQuery ("PalletLabels")
   DoCmd.SetWarnings True
Next

'Export label data to text file
DoCmd.SetWarnings False
DoCmd.TransferText acExportDelim, , "LabelData", "c:\CaseLabels\bartend.txt"
DoCmd.SetWarnings True

Here is my new logic/notes:
> Move the NextNum Update outside the loop
> Keep label generating within the loop

'Read/Lock the CustLblData for CustomerID
(If record locked display "Waiting..." msg until unlocked)
'Get the NextNum for the Customer (CustomerID)
'Update NextNum, adding iNum
'Unlock the CusLblData for CustomerID
iFirst = NextNum
iLast = NextNum + iNum
For i = iFirst to iLast
:
: 'Build data for labels code block
: 'Use i for the unique #
Next i

I don't know the coding to read/lock the record, wait if locked, then release the record.
Help.

Actually, my first question is: Is my logic OK?

Hope this makes sense.

 

[SkipVought]

Hi,

You ought to repost in

Microsoft: Access Modules (VBA Coding) Forum705 in order to get a better answer.

Skip,
[sub]
[red][/red]
[/sub]

 

[EdAROC]

Oops. Thanks.