Preload a text box field in a data entry form

I'm building a form to enter a new record in a table. The records in the table have a key which was manually entered (not autonumbered). To create the next number, I run a little query which finds the maximum key value and then adds one to it.  It is this value that I want to preload into the data entry form. Then when the data entry form is completed an closed/saved a logical number is placed in the new record's key.

I've done this other places with a combo box, but don't see how to do it with a simple text box.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How about:

FieldName = Dmax("FieldName","tblName")+1

Put it in the forms BeforeInsert event.  And on the form you will have the textbox who's Control Source is "FieldName"

Will that work?
One thing to be concerned about, it is possible if there are mutliple users that they could both grab the same number.  The chances are less if you grab the number when the user clicks a "Save" button (then put the DMax code on the button's click event as well as docmd.RunCommand acCmdSaveRecord ) but it is still possible.

You could do some error trapping on the Save click event to catch for an error, assuming that you have made the field a primary with no duplicates.  If an error happens then add one more and try again.
ChickenFarmerAuthor Commented:
Okay, I wrote
CommID = Dmax("CommID","tblCommittees")+1
in the beforeInsert Event. Got an error immediately on entering data in another field on the form. "Can't find the object 'CommID = Dmax("CommID","tblCommittees")+1'

CommID is the name of the key field in the table. I have the text box named that so it feeds to the correct place in the table.  Is this just a syntax issue or am I going about it wrong?

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ChickenFarmerAuthor Commented:
BTW I'm not worried about multiple users colliding on this. of the 4 users, 2 are just lookers, 2 are data enterers, but only one at a time (they back up each other).
sorry, you have to create an event procedure.  You can't put that line straight in the Properties window.

So click in the Before Insert box and you will see a pull down appear at the right.  Pull it down and select Event Procedure.  Then click the three 'dots' to the right of the pull down arrow.  This will take you to the VBA window where you will put in the code: CommID = Dmax("CommID","tblCommittees")+1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChickenFarmerAuthor Commented:
Thanks for your insigntful advice. It's working now. I did have to go back and change the datatype on CommID. It had been "Text" and needed to be a number for the DMax to work properly.  Hooray!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.