Assign the next number to a field not using autonumber

[straderran]

Auto number will not work for what I want to do. I am creating a simple database that will assign the user with the next incremental number in a field. The intent of the database is for the user to enter three text fields and then obtain the number. I believe that Dmax would work but do not know how to make populate the table. Help! Any suggestions are appreciated.

Thanks,
Randy

 

[RuralGuy]

Maybe this link will help: http://www.baldyweb.com/CustomAutonumber.htm

 

[straderran]

This looks like it may work. I am new to this and am also looking at where I enter the function. Is it in the form for the associated field? Is it in a query?
Also does the field and table have to be in brackets[]?

 

[RuralGuy]

I would do the work in the Current Event of a form. Brackets are needed if the name has embedded spaces.

 

[straderran]

I am still missing something. I have placed the followng function in the "on current" event of the form and it is not assigning a number.
=Nz(DMax("ECRnum","ECRNumIndex"),0)+1
The filed name is ECRnum and the table name is ECRNumIndex"
Am I missing something?
Thanks,
Randy

 

[RuralGuy]

Try: ECRnum = Nz(DMax("ECRnum","ECRNumIndex"),0)+1
Is ECRnum the Recordsource of your form?

 

[N1755L]

Maybe one of the experienced programmers can confirm this, or correct me, but if ECRnum is not the Recordsource of your form, I believe you can still reference the ECRNumIndex field by formatting one of these way:

ECRnum = Nz(DMax([Tables]![ECRnum],"ECRNumIndex"),0)+1

or

ECRnum = Nz(DMax([Tables]![ECRnum],[Tables]![ECRnum]![ECRNumIndex]),0)+1

If someone confirms that the above is wrong, I'll edit this post or delete it so's it doesn't end up confusing someone in the future. I'm new to Access as well.

Since you said you're new to Access, you can check what the Recordsource of your form is by opening your form in Design view, make sure the properties are open on the right, if not, right click on the form and click Properties at the bottom of the context menu, then select the Data tab in said properties to the right of the screen, at the top of the Data tab is Record Source. Notice just above that the drop-down/combo selection type, make sure it says Form.

 

[straderran]

Tried both to no avail. the record source is ECRNumIndex. Should it be something else?

 

[RuralGuy]

In the Form's Current event I would put:

If Me.NewRecord Then
   Me!ECRnum = Nz(DMax("ECRnum","ECRNumIndex"),0)+1
End If

 

[straderran]

Thanks! I think that will work.

 

[RuralGuy]

Let us know how you make out.