Stupid Question

[Zebble]

I know there is a simple solution, but can't get my brain around it. Here's
the deal:

I have two tables, Clients and Services. Clients has Client_ID, Client_Name
and some other identifying information. Services has Client_ID, Service_ID
and details about the service provided.

I'm trying to have a listbox with the Client_ID and Name. When a client is
selected, I would like my app to search the Services table, and return all
services for that client (simple enough). However, if the app doesn't find
the Client_ID in the Services table (hits EOF), I want to create a new record
in the Services table and record the Client_ID in the new record.

So far, I've tried setting the control source in the listbox to the
Client_ID in the Services table, but all that does is change the Client_ID in
the first record.

I added some after_update code that I thought would do it (forgive me I'm a
bit of a newbie on VBA and I copied from some of the canned code)...List0 is
my listbox:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Client_ID] = '" & Me![List0] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
End If

This doesn't cut it. As it does not add the new record with the Client_ID.

Any suggestions would be greatly appreciated.

 

[DoveArrow]

I know there is a simple solution, but can't get my brain around it.  Here's
the deal:

I have two tables, Clients and Services.  Clients has Client_ID, Client_Name
and some other identifying information.  Services has Client_ID, Service_ID
and details about the service provided.

I'm trying to have a listbox with the Client_ID and Name.  When a client is
selected, I would like my app to search the Services table, and return all
services for that client (simple enough).  However, if the app doesn't find
the Client_ID in the Services table (hits EOF), I want to create a new record
in the Services table and record the Client_ID in the new record.

So far, I've tried setting the control source in the listbox to the
Client_ID in the Services table, but all that does is change the Client_ID in
the first record.

I added some after_update code that I thought would do it (forgive me I'ma
bit of a newbie on VBA and I copied from some of the canned code)...List0is
my listbox:

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Client_ID] = '" & Me![List0] & "'"
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    Else
        DoCmd.GoToRecord , , acNewRec
    End If

This doesn't cut it.  As it does not add the new record with the Client_ID.

Any suggestions would be greatly appreciated.

If I understand your problem correctly, this page should help you out.

http://www.fontstuff.com/access/acctut20.htm

 

[Damon Heron]

Why not just have a form with the client table as the source, and a
subform with services? Link would be the ClientID. When no record exists,
you could
simply add one in the subform.

Damon