Retrieving a unique number

[EdAROC]

Help! I looked at FAQ700-184 on how to retrieve a unique number (vs using AutoNumber). Problem I'm having is getting from the current design to a new design.

The need for the change is that we are adding a 2nd PC/printer to generate labels and need to avoid duplicate numbers (NextNum - unique # on each pallet/skid label).

Current design includes an append query that includes linking to a table containing the NextNum value. Each cycle creates a new record in the label data table. Afterwards the label data is TransferText to a text file and a barcoding app pulls in the text file and generates the labels.

For i = 1 To iNum
                
    'Increment the sequential number.
    strUpdate = "Update CustLblData Set NextNum=NextNum+1 "
    strUpdate = strUpdate & "Where CSCODE = " & Chr(34) & "138" & Chr(34)
    CurrentDb.Execute (strUpdate)
                
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("Ship To Info (Skids)")
    DoCmd.SetWarnings True
Next i

The query "Ship to Info (Skids)" links to CustLblData and "grabs" the NextNum. This query creates (appends) a record to the table that gets exported to a text file for the barcode app to use.

I can anticipate that if both PCs/Printers are running at the same time (heaven forbid if we go to 3 of them) that duplicate numbers would be generated.

I'm not sure of what design changes would be good. My thinking is that the timing needs to be : (1) Lock NextNum record, (2)Increment NextNum, (3) Read and use the NextNum value on a label (table record), (4) Unlock NextNum record.
Correct me if I'm wrong.

Just to add a little twist to all this. The ideal solution would be to "grab a batch of NextNum's" so that the string of labels from a PC/Printer during a run are consecutive.

Hope this is enough info and presented so that you can help.

 

[MichaelRed]

i think you do not understand the faq. please review it thoroughly, and post specific questione regarding lines of code which you do not undestand.

overall, the faq is at least reasonably commented and provides an explination of the sections of the function. numerous individuals have managed to implement this without significant change and without more than an occasssional question.

p.s. it is also considered polite to contact the author via the facillity on the faq page.




MichaelRed

 

[EdAROC]

Thanks. I've been reviewing my design issue and the faq and have figured out how to incorporate the FAQ's coding into the current design of this app.

Got a syntax question. There's a statement:
Const NumReTries = 20#

Can't find any resource explaining the # sign. Help.

 

[PHV]

The # sign is to coerce a Double value.