• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

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.

Help?
0
ChickenFarmer
Asked:
ChickenFarmer
  • 3
  • 3
1 Solution
 
UniqueDataCommented:
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?
0
 
UniqueDataCommented:
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.
0
 
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?

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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).
0
 
UniqueDataCommented:
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
0
 
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!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now