Producing a unique serial number for labels

[jasonmac]

Hey everyone,

I have an Access app that produces skid labels for our shipping department. These labels are produced in a report and I need a unique serial number to display on each label. The table the information comes from is updated by a query that refrences an external data source every time the app runs. I tried to add an auto number field to the table but every time I run the app the field is wiped out. Does anyone see any way around this?

Thanks in advance,
Jay

 

[GenieTR]

You can try using DMAX to generate you serial number, you will need to create a query table to track your serial number.

Example:
Me.InvoiceNo = "XXXX" & Format(Val(Right(Nz(DMax("[InvoiceNo]", "[QryCounter]&quot), 6)) + 1, "000000&quot

Hope this is helpful

 

[jasonmac]

That looks like what I'm trying to do. So what you're saying is I shoudl create a table that contains the field InvoiceNo and refrence that table through a query called QryCounter?

In what event should I add the code above?

 

[jasonmac]

Can anyone please help me with this? I'm still not getting it. So FRUSTRATED!!! I can't believe it's this hard to create a number that wil increase by 1 every time the report prints!

 

[bdmclennan]

Jasonmac,

I have to do the the same sort of thing. I have included a function which I use.


'===================================
'Create tblInvoiceNo with 2 fields
'Invoice_No = LONG
'Date = DATE/TIME
'Call the function from your report e.g. =fncInvoiceNo
'Each time you call it you will get a new invoice no
'If you need to create multiple copies of the invoice no
'you will have to modify the code.
'===================================


Public Function fncInvoiceNo() As Long
Dim RS As Recordset
Dim DB as database


Set DB = CurrentDb
Set RS = DB.OpenRecordset("tblInvoiceNo",dbOpenDynaset)

If Not RS.EOF Then RS.MoveLast
With RS
fncInvoiceNo = RS.RecordCount + 1
.AddNew
RS!Invoice_No = RS.RecordCount + 1
RS!Date = Now()
.Update
End With
End Function

 

[koresnordic]

I have the same need. What I have done is use a seperate table that holds the number. Then an update query adds 1 to this number for each label. The sequence being, query to update the number to zero (Our numbers are started from 1 every time), start of loop, add 1 to number, generate label, if not printed number of labels required - goto loop start. I could give you the module I use, but it also does other things that are not related to this problem.


Graham