Auto-populate next record number in number field 5

[goo]

I am looking for a way to auto-populate a number field with the record number that is currently showing on the form. My users would rather not enter the next number in the number field and have it just populate with the new record number.

Is this possible? Can anyone help?

 

[kathryn]

Is there a reason that you can't use AutoNumber as the datatype for the field?

I'm not clear about your question: the form shows a new record number, but the textbox that holds that record number is not bound to a field in the table?

Kathryn

 

[goo]

Yes - the data field is actually a number that is not part of the key in that table and cannot be an autonumber since the values will start over frequently. It is a number that will be consistent with the record number though. Maybe it is some line of code that defaults that field value to 1 + the value of the last record. Basically I would like the field value to increment as new records are added - but it cannot be an autonumber I dont think.

Thanks for your help in all this confusion!

 

[THoey]

Why not create a table with a field in it just to hold the current or next value of your number. As you use it, you could increment it by one. You could also have a routine to "reset" it. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[LonnieJohnson]

Or you can set the default value of this field on your form using the DMax function and increment by 1 like so:

= DMax ("[YourFieldName]","YourTableName&quot + 1

Hope this helps. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[kastaman]

I hope LonnieJohnson sees this inquiry...

How can I use your function if I have about 5 forms that different users use. I was trying it and the forms will pick the next available number for both forms this causing
duplicate record #'s. I placed "No duplicates" but I would get an error when saving the form due to the duplicate # used by the form.

Maybe a script to pull the new record # when the save button is depressed?

Your advise is appreciated.

Kastaman.

 

[LonnieJohnson]

In your case I would leave the field blank until just before the record was about to be updated. Thus, in the form's Before Update event, I would put...


Dim intNextNum as Variant
intNextNum = DMax ("[YourFieldName]","YourTableName&quot + 1
TheTextBox = intNextNum
MsgBox "The number for this record will be " & intNextNum

This messgae box thing allows your user to write down the nmumber if need be. Now the number does not get assigned and saved to file until the user is thru with the records and eliminating any dups.

ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[kastaman]

LonnieJohnson - I'm getting a compile error: variable not defined & the "TheTextBox = intNextNum" is highlighted.

I was hoping that you can help me in scripting so that the next number is created only when the save button is depressed.

Thanks,

Kastaman

 

[LonnieJohnson]

You should replace "TheTextBox" with the name of the text box that has this number to incremented. If it is not a text box on your form, you should add it and make it invisible.

You can still put it in the Before Update of the form, this way it gets picked up just before the record is saved (which is event of the clicking of your save button). Also if you change the way the record gets saved (maybe just moving to a new record) the code still fires. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[Haitham]

Hi goo,

Just go to my cheat.
thread181-168859

Good Luck

 

[San22]

u can even use a sql query using MAX function
then increment the field value by one.