How to save a record and when


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.

Who is Participating?
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
MCaliebeAuthor Commented:
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?
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

MCaliebeAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
MCaliebeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.