New record on open

Posted on 2012-09-06
Last Modified: 2012-09-07
I have a table for invoices.  The invoice form on load event has this code:

DoCmd.GoToRecord , , acNewRec

I have a combo box on the invoice form that shows Invoice Numbers that should pull a record based on the combo box.  But it doesn't work. Nothing happens.

How can I have a blank record load and then be able to use a combo box to find a record.

Question by:Sasha42
    LVL 39

    Expert Comment

    Can you upload sample DB with this form and some dummy data in tables?
    LVL 84
    Can you explain why you (a) open to a New Record and then (b) want to FIND a record? That doesn't really make much sense - generally, if you want to enter a New Record you'd move to a new, blank record and enter data.

    If you're trying to fill in defaults based on a Combo choice, or something along those lines, then please let us know.
    LVL 2

    Expert Comment

    A common issue here is that the underlying table has a field for the InvoiceID and you have Bound the ComboBox to that field.  That strategy sounds like to should work but it does not.  All it does is change the invoice Number of the current record.

    What you need to do is to create an Unbound combobox on the form (you can place it anywhere but I would place it in the header of the form) have it get a unique list of invoices (usually from the Invoice table) for its RowSource.  Give it a name (cboFind will work).  In the AfterUpdate event of cboFind you would then need perform a find based on the invoice you chose in the Unbound combo box.

    This can be done either using a macro object or writing some VBA code.  In order to give you an example, we would need to know the data type of the Invoice Number

    I hope that gives you enough to go on.

    Bob Oxford
    LVL 20

    Expert Comment

    Open the form to view all data.  In the form header section, use the control wizard to create a combo box to "find record".  Follow the wizard...  

    Scott C

    Author Comment

    I built it so that when the Invoice form opens, it opens to the first record.  The form has a New button that creates a new record and automatically populates several fields.  It also has a Find combo box to find a record based on an Invoice Number.  This works fine.

    My client doesn't want to see any data in the fields when the form is opened.  So now I have it that when the form opens it creates a blank record.  If the user wants to enter a new record they still have to use the New button so that some of the fields automatically populate.   I have code that says if the form is dirty then automatically populate some fields.  If it is not dirty then create a blank record and populate.  This works fine.

    When the form opens to a new record and is dirty the Find combox doesn't work and does not return an error.  Should I put the Find combo box on a subform?
    LVL 2

    Expert Comment

    I am not quite clear about how you are doing this but let me give you an option here...The Form has a DataEntry property.  Go into Design view and set the dataEntry Property to true.  Every time the form is opened up from then on, it will be on a New Record.  No code or macro required.  (remove your code that goes to a New Record if you have it)

    Now, create an ebedded macro in the afterUpdate event of the ComboBox if you like, that will search the form.  It should have 2 steps:


    The SearchForRecords action shold look for the First Record and the Where condition shold be something like:

    ="[InvoiceNumber] = " & "'" & [cboFind] & "'"

    Do NOT use the [Screen].[ActiveControl] that the wizard creates.  For whatever reason that does not seem to work consistantly.  Using Active Control is risky since Access will sometime change the Active Control

    Good Luck!

    Bob Oxford
    LVL 20

    Accepted Solution

    You must force the new record to "not dirty" by saving the record.  In the event of your dropdown... try

    docmd.runcommand accmdsaverecord.

    This will save the record, release the buffer, and should allow you to move to any existing record.

    Now, if you have any mandatory fields, etc. or the record is incomplete... (or any other unusual scenarios).. you may want to "encase" this command inside an "Ignore error" handler:

    on error resume next
    docmd.runcommand accmdsaverecord
    on error [go back to your original error handler]

    Scott C
    LVL 84
    When the form opens to a new record and is dirty the Find combox doesn't work and does not return an error.
    A New Record generally will not be dirty unless the user enters data.

    What do you mean by "does not work". What is it supposed to do? If you're at a New Record, what exactly are you "finding"?

    If you could describe your process in more detail, we could very likely help you much better. Don't use vague terms - tell us exactly what you're doing.
    LVL 20

    Expert Comment

    Also, don't forget about this command.
    Sometimes as we build apps... we create a record.. and then via buttons or such, move to another recordset that may relate to or depend on this original record.   Well.... this "accmdsaverecord" command makes sure your original record is created/saved prior to going anywhere else.

    Yeah.. sometimes we build stuff and 'break rules'.  We learn as we go and try to improve with the next app we write.  In the meantime.....  as said.... don't forget this command.  It may come in handy again.

    Good luck and have fun!

    Scott C

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    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

    15 Experts available now in Live!

    Get 1:1 Help Now