Preload a text box field in a data entry form

Posted on 2008-11-06
Last Modified: 2013-11-28
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.

Question by:ChickenFarmer
    LVL 7

    Expert Comment

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

    Expert Comment

    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.

    Author Comment

    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?


    Author Comment

    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).
    LVL 7

    Accepted Solution

    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

    Author Closing Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now