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?
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

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!
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.