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

How to save a record and when

Hello!

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.

MC
0
MCaliebe
Asked:
MCaliebe
  • 3
  • 2
2 Solutions
 
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?
0
 
BobOxfordCommented:
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
0
 
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.
0
Industry Leaders: 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!

 
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.
0
 
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)
'Me.Repaint
 
        
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.

Thanks,
MC
Points Increased to 500
0
 
BobOxfordCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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