How to save a record and when

Posted on 2012-09-06
Last Modified: 2012-09-07

I have a form which I am using to enter employee data.  I have a drop down box at "company" which when selected will poopulate text boxes on the form associated with the selection which are then linked back to the employees table.  The only thing written to the employee table is the ID associated with the company.  

However, if I want to make a different selection...say I selected the wrong company, then I get stopped and the DB states "To make changes to this field, first save the record".  This is fine...I can add a Save Record command after the update event but then, as I have required fields on the form which the user hasn't yet entered into, I get the error that those fields need to be completed.

So, how can I update these text fields from the Combo box selection without saving to the employee records table until the very end?  What could be firing off the command to write the record when all I'm doing is selecting from the combo box?

I would apprecaite any advise.

Question by:MCaliebe

    Author Comment

    I've been playing with this further and the best I can tell is the error is directed around the combo box.  How I see the process is

    Cmb_123 is based on a query of a table

    Select Cmb_123 control
    Query results are displayed
    Selection is made

    After Update Event sets the values for txtb_1, txtb_2,. and txt_3.
    ISSUE 1: How do I get the current text box values to repopulate in the form?

    (After intitial selection and before the record is saved by the user)

    Select Cmb_123 control
    Query results are displayed
    Another selection is made
    "beep" and an error in the window footer "To make changes to this field, first save the record".  
    ISSUE 2: Why am I getting an error to save the record?
    LVL 2

    Expert Comment

    I am just guessing here.  It would help if there were a bit more descriptive information like what is the control Sourrce of the form?  Are the text boxes that you are populating in the afterupdate event of the combobox bound to something?  It sounds like those other text boxes that you are populating are bound to an underlying field.  Since you are only saving the CompanyID into the Employee table, make sure that the fileds you are populating from the combobox are not bound to anything.

    If that doesn't work, create a small database that illustrates the issue and post it up here.  We can take a closer look.

    Hope that helps

    Author Comment

    That was a silly mistake on my part.  You wre exactly right...I had the text fields bound to the query field.  I fixed that however, what proceedure do I use to keep this information populated all the time on the form?  Now that the fields are unbound again, they will only populate when the combo box is updated.  Existing records now show a blank field with the exception of the Company ID which was written back to the Employees table.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    You need to better describe what you're doing. If you're using bound controls (except for the combo, of course) then Access will manage the filling of the forms.

    If you're using other methods, you'll need to let us know how you're doing this - for example, are you using a true unbound form (i.e. no form Recordsource, and the controls are all set with a blank ControlSource)?

    What is your goal with this form? If you're trying to search for a specific record, then as BobOxford said you should make sure the COMBO you're using for searching is unbound. The other controls can remain bound, and you can use the Filter method to show the record you want after the combo box selection.

    Author Comment

    Well, I'll try to explain the best I can.

    My form is designed to collect and display all details about an employee.

    The form is bound to an Employees table.  To keep from storing redundant data like the company name and phone numbers, I've stored just the COMPANY_ID in the employees table.  This is related to the tbl_companies.  So, when I am deciding what company this employee will be held in, my combo box queries the companies table and populates the fields.

    For whatever reason, as I cycle through the current records, the COMPANY_ID is filled in from the saved record in the Employees table, however the unbound text fields do not repopulate.

    I've thought to place some code under the current recod to point back to the same After Update event code used in the combo box.  I'm not great at writing but this seems to be working.

      Private Sub cmb_Business_ID_AfterUpdate()
    On Error GoTo EH
    If Dirty Or Not IsNull(Me.cmb_Business_ID) Then
            Me.Company = Me.cmb_Business_ID.Column(1)
            Me.Business_Phone = Me.cmb_Business_ID.Column(2)
            Me.Fax_Number = Me.cmb_Business_ID.Column(3)
    ElseIf IsNull(cmb_Business_ID) Then
    Me.Company = ""
    Me.Business_Phone = ""
    Me.Fax_Number = ""
    End If
    Exit Sub
    EH:  '<--- label to begin error handling code
       MsgBox "Error " & Err.Number & ":  " & Err.Description  '<-- Display error number and message
       ' ** You can also condistionally handle specific errors here 

    Open in new window

    What I'm doing here is firing off the code when the record is dirty or when the record cycles to another record in the table with company information already selected.

    Otherwise new records or records with no company are left to be blank.

    If there is a better way to handle these conditions, I'd like some pointers.

    Points Increased to 500
    LVL 2

    Accepted Solution

    You are basically correct.  To get those unbound controls populated you will need to populate those controls both in the AfterUpdate Event of the Combo Box as well as in the Current Event of the form.

    When you use the Current Event, instead of insepcting the Dirty property, just check the form's NewRecord property to see if you are on a New Record.  if you are then you would not execute the code that populates the controls.  Otherwise, I think in every other instance you would want to populate the controls.

    Hope that helps.

    Bob Oxford

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    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…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now