Add a new record based on criteria from another form

I'm not sure I'm going about this the right way, but here's what I'm trying to do.  I have a form with a combo box listing all of our clients.  The user selects a client and then clicks a button to open another form to display all of the office locations for that client with the client name listed at the top of the form.  On this form they are able to update any office information like address.  I also want them to be able to add a record if a new office is opened for that client.  When the new record selector is clicked, it clears out the client listed at the top of the form.  How do I get the already selected client to stay for the new record?  Maybe there's a better way to go about this.  Any suggestions are appreciated.  Thanks!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helen FeddemaCommented:
I think you need a main form with a subform.  There should be a one-to-many link between the Clients table and the Office Locations table.  The main form should be bound to the Clients table, and the subform to the Office Locations table, with the linking key field (say, ClientID) in the LinkChildFields and LinkMasterFields properties of the subform.  Then you can select a client from a combo box in the form header, using code like below, and enter as many locations as you need per client.
Private Sub cboSelect_AfterUpdate()
'Written by Helen Feddema 29-Jun-2009
'Last modified 11-Feb-2010

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
looks like you need a new code to add records associated with the current client.
are you using subform in the form that was open?

better if you can upload a copy of the db.
Helen FeddemaCommented:
The record selector combo box in the header should be unbound, with a row source of the Clients table (or a few fields from it).  Don't use a bound combo box, because that will just change the Client for the current record.
Helen FeddemaCommented:
With a linked subform, there is no need for code, since when you add a new office location record, it will automatically be assigned the ClientID from the main record.
nfstrongAuthor Commented:
Thanks, Helen!  It works great!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.