Creating a sequential number (NOT AUTONUMBER) (1 Viewer)

[ChrisSedgwick]

Hi,

I'm trying to create a simple command button that will generate a sequential number using the DMAX function. The database will be used only by one user so I wont have the issue of 2 users attempting to use the same number.

I've spent some time looking through this forum to try and find the one that relates to my issue.

So at present I have a table - "Fitter Order" which contains the following fields:

FitterOrderID (PK)(Auto)
OrderNo
Fitter
InputDate
Product ID (FK)

I want the "OrderNo" field to generate a sequential number on the click of a button at form level. I've uploaded some images so you can get an idea of the form and what I'm trying to achieve.

I'm using the DMAX function only on the advice of other forums and some theads on here. However if there's an easier way, I'm open to that. I've never really used VBA, so if this is needed, I'd appreciate it if you could make it as simple as possible to follow.

In the form I have a text box which is bound to the OrderNo field. I've included a command button with an event procedure when the button is clicked. This is the code I have behind it which I copied off another thread.

[FONT=Tahoma]Me.Text24 = Nz(DMax(“[OrderNo]”,”Fitter Order”),0)+1[/FONT]

However when I try to leave the module, I receive the following error message:

"Compile error: Expected list separator" (Again, I have uploaded images.)

I'm kind of stuck now and I'm not sure when to being troubleshooting the issue. Could someone offer some support?

Many thanks,

 

[Ranman256]

you are wasting your time. Autonumber exists and is effortless.
Theres no need to go around it.

 

[gemma-the-husky]

the code looks OK, although normally you would put this in the forms beforeupdate event, so it is automatic. Maybe the table name needs to be in square brackets

Me.Text24 = Nz(DMax(“[OrderNo]”,”[Fitter Order]”),0)+1

it gets the largest value of field orderno in table "fitter order", and increments it by one. (Assuming orderno is a numeric field)

I expect this is not the cause of the error.

 

[gemma-the-husky]

out of interest, if you want a sequential number, then you can get one in 2 ways

a) dmax, as you are doing
b) read the next value from a separate table.

An autonumber is not always the best way. You will need a sequential invoice number, say, and an autonumber is not guaranteed to give you a sequential result.

 

[ChrisSedgwick]

Hey,

Thanks for the reply.

I've changed the expression to the beforeupdate of the form and changed to square brackets for the table. However when I try to close the form, It opens up the debugger and says:

Error: Syntax error and highlights the entire expression.

Any ideas?

 

[ChrisSedgwick]

Here's a snapshot of the error...

 

[ChrisSedgwick]

out of interest, if you want a sequential number, then you can get one in 2 ways

a) dmax, as you are doing
b) read the next value from a separate table.

An autonumber is not always the best way. You will need a sequential invoice number, say, and an autonumber is not guaranteed to give you a sequential result.

Also out of interest, how would I do option b?

Thanks,

 

[JHB]

Here's a snapshot of the error...

Look at the quotes they are not identical!!
Delete them and type in the correct one from the keyboard.

 

[zpy2]

zip and upload Your mdb file.

 

[Tieval]

Look at the quotes they are not identical!!
Delete them and type in the correct one from the keyboard.

Yes, this is true, you have a closing quote before [Fitter Order] rather than an opening quote.

 

[ChrisSedgwick]

Hi,

Thanks for this!

Apologies for the stupidity. I've changed the code and it now works by generating a sequential number, as desired.

However, It shows me this error:

"A data macro resource limit was hit. This may be caused by a data macro recursively calling itself. The Updated (<field>) function may be used to detect which field in a record has been updated to help prevent recursive calls."

Is this connected in any way?

Thanks.

 

[ChrisSedgwick]

I've found it also creates 5 blank records in the source table. Directly underneath the record I created?

 

[gemma-the-husky]

as I said, the place to put this is in the beforeupdate, or maybe beforeinsert (or even afterinsert) event. They all have slightly different nuances.

maybe by using a button click the code doesn't work quite right, and you are inserting multiple records.