Update subform with search results from other form, using primary key ID number

[SQLNovice]

I am developing a database for a white water rafting company.



I've created a reservation form for incoming phone calls.
This main form uses a link table to show previous reservations (who was the paying client), and subform to use this link table, showing the client's info.

For a new incoming phone call, the user clicks a button, which opens an address form.
The user can search for a previous client (a repeat customer).

My question to this forum:
I've tried and tried to populate the main form's subform with the "previous client" info, but no luck. Can you help?

(See attached)

I have tried six different methods in VBA, but no luck. Any help?
Seems simple to just use the "Client ID" from the found search.

' Method 1

'Set db = CurrentDb
'Set rst = Me.RecordsetClone
'strCriteria = "[Client ID] = " & Me.Client_ID
'rst.FindFirst strCriteria

' ==============================
' Method 2

'Set db = CurrentDb
'Set rst = Me.RecordsetClone
'Me.Bookmark = rst.Bookmark
'Forms![frm Call Sheet 3]![frm Address Book short].Bookmark = rst.Bookmark

' ==============================
' Method 3


'needed_client = Me.Client_ID
' Trip_De = Forms![frm Call Sheet 3].[Trip Detail ID]

'Forms![frm Call Sheet 3]![frm Address Book short].Form![Client ID].SetFocus
'Forms![frm Call Sheet 3]![frm Address Book short].Form![Client ID].Recordset.FindFirst "Client ID=" & needed_client
' or
'Forms![frm Call Sheet 3]![frm Address Book short].Recordset.FindFirst "Client ID=" & needed_client


' ===================================

' Method 4

'  strSQL = "[Client ID] = " & Me.Client_ID
'  DoCmd.ApplyFilter wherecondition:=strSQL

'strSQL = "[Client ID] = " & Me.Client_ID
'Forms![frm Call Sheet 3].SetFocus
'Forms![frm Call Sheet 3]![frm Address Book short].SetFocus
'Forms![frm Call Sheet 3]![frm Address Book short].Form![Client ID].SetFocus
'DoCmd.ApplyFilter wherecondition:=strSQL

' ===================================

'Method 5

'Help with sytax - DoCmd.FindRecord StaffNo, , , , , acCurrent
'DoCmd.FindRecord needed_client, , , , , acCurrent

' Me.Filter = "[Envelope Full Name] like ""*" & Me.txtFilter1 & "*"""
' Me.FilterOn = True

'DoCmd.OpenForm "frm Call Sheet 3", acNormal, "", "", , acNormal

'Call frm_Address_Book_short.Command152_Click

' =======================================

' Method 6

'strSQL = "SELECT [tbl Client contact list].[Client ID], [tbl Client contact list].[Last Name], [tbl Client contact list].[First Name], [tbl Client contact list].Address, [tbl Client contact list].City, [tbl Client contact list].State, [tbl Client contact list].ZIP, [tbl Client contact list].[Cell Phone], [tbl Client contact list].[Home phone], [tbl Client contact list].[E-mail], [tbl Client contact list].[Emergency contact name], [tbl Client contact list].[Emergency contact relation], [tbl Client contact list].[Emergency contact phone], [tbl Client contact list].[Medical conditions], [tbl Client contact list].Sex, [tbl Client contact list].Age, [tbl Client contact list].[Meal preference], [tbl Client contact list].Vegetarian, [tbl Client contact list].[Organization name]"
'strSQL = strSQL & " FROM [tbl Client contact list]"
'strSQL = strSQL & " WHERE [tbl Client contact list].[Client ID]=" & needed_client & ";"
'Forms![frm Call Sheet 3]![frm Address Book short].Form.RecordSource = strSQL


' ==============================
' Update table for "frm Call Sheet 3

'Set db = CurrentDb
'Set rst = db.OpenRecordset("tbl lnk Trip n Clients")
'rst.AddNew
'rst![Trip Detail ID] = Trip_De
'rst.Update

'Help with sytax - DoCmd.RunSQL "UPDATE tableName SET fieldName=444 WHERE pk=FORMS!formName!ControlName "

'DoCmd.RunSQL "UPDATE [tbl lnk Trip n Clients] SET [Client ID]=" & needed_client & " WHERE [Trip Detail ID]=" & Trip_De



Forms![frm Call Sheet 3]![frm Address Book short].Form.Requery

DoCmd.OpenForm "frm Call Sheet 3", acNormal, "", "", , acNormal

[ItsMe]

If the Main Form and the Subform are bound to the same table/query why not use the Link Master Fields and Link Child Fields properties of the Subform Control?

[SQLNovice]

Thanks, ItsMe.

I have already done that.

The problem is that when you go to "new record" on the main form, the subform also goes to "new record" (as it should), and I have a problem putting in the client info from the 'address book search' into the subform.

Brian

[June7]

Main form and subform are bound to the same data source? I find that this seldom works nicely. This is essentially attempting to emulate a split form.

A record must exist in main form to allow data entry of related records in subform. Your form/subform arrangement is backwards. Main form should be 'parent' record and subform display the 'children' side of relationship. Example: Orders and OrderDetails.

Why is client info repeated into every reservation record? This is duplication of data.

You really should have a Clients table (the 'parent' record) and a Reservations table (the 'children' records).

[ItsMe]

Avoiding the creation of duplicate client accounts is nothing new. I advised using an unbound form to query the DB. When the phone rings the user can query the DB by searching for names, addresses, etc. Operating procedures should dictate how the phones are answered and what questions are asked to determine if the caller is an existing client. The unbound search form will aid the user.

After determining the new client thing, the user can navigate to a form to append records in the Client table or move to a form to append records in the reservations table.

[orange]

@sqlnovice,

Before getting into forms and sub-forms etc, write a clear description in plain English of what your business and requirements are.

Along the lines of --- (what ever the facts are)
we are a white water rafting company. We take customers on rafting trips in any of X locations. Customers must reserve in advance.
We offer meals and lodging. Customers must record any conditions --medical, food allergies...... that may require special consideration...

This will give readers the 30000 ft view of what you are dealing with; rather than some form/subform issue.

[SQLNovice]

Thanks.
The main form & subform are not bound to the same source.
The main form is bound to a "Trip Details" table, and the subform to an "Address book" table.

I previously had unbound controls for the client info, wrote all the code, and this was working OK.
I then had the idea that, since there is a separate link table that links "Trip Details" to "Address Book", I thought I'd create a subform that uses this link table info.
I have spent a lot of time trying to get this to work. I think I will go back to the unbound method.

Thanks for your help here.

Brian

[June7]

Somewhat confused. No VBA should be needed just to display related info. I presume client ID is saved into trip record.

What is structure of Address Book table? Does it have client ID field? Or is address ID saved into client record?

[orange]

Can you provide a clear description in plain English of what your business and requirements are?

[SQLNovice]

Thanks orange & June.

I got it to work, and I no longer need your help. I really appreciate your replies, time, and willingness to help.

For other in the future that may read this thread, I got rid of the subform, and went with unbound controls on the main form.

I wrote VBA code to populate the unbound controls, after the user finds a previous client who has used the white water business before.

Brian