Entering incremental value into text box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Essexbug - 13 Mar 2007 14:56 GMT

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

Reply to this Message

Stefan Hoffmann - 13 Mar 2007 15:24 GMT

hi,

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

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.

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 <--

Reply to this Message

Jenni - 13 Mar 2007 15:59 GMT

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?

> hi,
>
[quoted text clipped - 13 lines]
> mfG
> --> stefan <--

Reply to this Message

Jenni - 13 Mar 2007 16:02 GMT

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

> hi,
>
[quoted text clipped - 13 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 13 Mar 2007 16:29 GMT

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 "="?

You use it as ControlSource of your TextBox or in the DefaultValue.

mfG
--> stefan <--

Reply to this Message

Jenni - 13 Mar 2007 17:12 GMT

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)

> hi Jenni,
>
[quoted text clipped - 5 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 14 Mar 2007 10:52 GMT

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.

Try =Nz(DMax("[Quiz Number]","[tbl_Questions]"),0)+1

mfG
--> stefan <--

Reply to this Message