Entering incremental value into text box

E

Essexbug

Hi,

I need to find someway of entering a value (starting with 1 and then
incrementing) into a text box.

I can't use autonumber in the table as I need to be able have duplicates in
the table.

I need it to say something like if the field is null or is 0 then 1, if the
previous record is 1 or > then previous +1.

I hope this makes sense. Many thanks
 
S

Stefan Hoffmann

hi,
I need to find someway of entering a value (starting with 1 and then
incrementing) into a text box.
Click to expand...
You can use

=Nz(DMax("field";"Table");0)+1

to display it.
I can't use autonumber in the table as I need to be able have duplicates in
the table.
Click to expand...
In a multiuser environment you need a unique index on this field and you
should recalculate the value in the Form_BeforeUpdate event.


mfG
--> stefan <--
 
J

Jenni

OK,

The reason I have not got a unique index is that the text box in question
has to be able to take duplicate values.

The record is to list a series of questions. There is a field which has the
quiz number (taken from another table) and the quiz name. The record is
unique in that there will only be 1 Quiz1, Question 1; Quiz 1, Question2;
Quiz 2; Question1 and so on. Does this help any?
 
J

Jenni

Stefan,

Also where would I use this code? What event would I put it in? In the
form itself OnOpen? BeforeUpdate?

Do I need to enter anything before the "="?

Rgds
 
S

Stefan Hoffmann

hi Jenni,
Also where would I use this code? What event would I put it in? In the
form itself OnOpen? BeforeUpdate?
Do I need to enter anything before the "="?
Click to expand...
You use it as ControlSource of your TextBox or in the DefaultValue.


mfG
--> stefan <--
 
J

Jenni

hi stefan

I have entered it as =Nz(DMax("Quiz Number";"tbl_Questions");0)+1 in default
value. however i get the following error...

"The expression you entered contains invalid syntax.

You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks."

I have no idea what I need to change.

rgs, Jenni (aka Essexbug)
 
S

Stefan Hoffmann

hi Jenni,
I have entered it as =Nz(DMax("Quiz Number";"tbl_Questions");0)+1 in default
value. however i get the following error...
"The expression you entered contains invalid syntax.
Click to expand...
Try =Nz(DMax("[Quiz Number]","[tbl_Questions]"),0)+1


mfG
--> stefan <--
 
Top