Subform headaches

[Stapes]

Hi
I have a form that contains a combo box from which a selection is made,
and I want the selection chosen to be shown in the subform. The main
form is Customers. The subform is for their Boats. A customer can have
any number of Boats. The customer table & the boat table are linked
with a one to many join on the customer number field. The boat is
uniquely identified by the boat name. The subform always just shows the
first boat.
How can I get it to show the boat selected?

Stapes

 

[Rick Brandt]

Hi
I have a form that contains a combo box from which a selection is
made, and I want the selection chosen to be shown in the subform. The
main form is Customers. The subform is for their Boats. A customer
can have any number of Boats. The customer table & the boat table are
linked with a one to many join on the customer number field. The boat
is uniquely identified by the boat name. The subform always just
shows the first boat.
How can I get it to show the boat selected?

Stapes

The MasterLink and ChildLink properties of the subform control should be set
to make this happen. The MasterLink would be set to the name of the
ComboBox and the ChildLink would be set to the name of the customer number
field.

 

[Stapes]

The MasterLink and ChildLink properties of the subform control should be set
to make this happen. The MasterLink would be set to the name of the
ComboBox and the ChildLink would be set to the name of the customer number
field.

--

I don't think this is the answer. The customer number is already being
passed across, and I am getting a boat belonging to the same customer,
just not the particulat boat selected. I have tried concatenating the 2
fields (i.e customer number & boat name) together in the bound column
of the combo box, and unstringing them in the code. I want the child
link to be both Customer number (which field is common to both tables)
and Boat name (which field is only in the Boats table).
Can I do this?

Stapes

 

[Rick Brandt]

I don't think this is the answer. The customer number is already being
passed across, and I am getting a boat belonging to the same customer,
just not the particulat boat selected. I have tried concatenating the
2 fields (i.e customer number & boat name) together in the bound
column of the combo box, and unstringing them in the code. I want the
child link to be both Customer number (which field is common to both
tables) and Boat name (which field is only in the Boats table).
Can I do this?

My understanding is that you wanted to choose a customer in the parent form
and see all of the boats for that customer in the subform. So now you want
the subform to just show a particular boat for that customer? That would
mean that you need to choose a boat in the ComboBox, not the customer.

You could have a ComboBox that lists all boats for the current customer and
then you choose a boat from that list. The MasterLink and ChildLink would
then be set to both fields separated by a semi-colon like.

 

[Stapes]

My understanding is that you wanted to choose a customer in the parent form
and see all of the boats for that customer in the subform. So now you want
the subform to just show a particular boat for that customer? That would
mean that you need to choose a boat in the ComboBox, not the customer.

You could have a ComboBox that lists all boats for the current customer and
then you choose a boat from that list. The MasterLink and ChildLink would
then be set to both fields separated by a semi-colon like.

Hi Again
Sorry - I haven't explained myself very well. The user might not know
who is the owner of a boat without looking. The idea is that he can
select the boat name from the top of the main form, and his chosen boat
appears in the subform with the correct customer details in the main
form. The customer details in the main form are coming out OK, but the
subform and the combo box keep reverting back to the first boat for the
customer. Unfortunately, the boat name field is NOT in both tables, so
I cannot link it using MasterLink and ChildLink.

Stapes

 

[Rick Brandt]

Hi Again
Sorry - I haven't explained myself very well. The user might not know
who is the owner of a boat without looking. The idea is that he can
select the boat name from the top of the main form, and his chosen
boat appears in the subform with the correct customer details in the
main form. The customer details in the main form are coming out OK,
but the subform and the combo box keep reverting back to the first
boat for the customer. Unfortunately, the boat name field is NOT in
both tables, so I cannot link it using MasterLink and ChildLink.

Stapes

So your basic requirement is that you want to navigate to a certain parent
record based on filter criteria applied to data in the subform correct? In
other words you want to enter a boat name and have the parent form filtered
to the customer that owns that boat and also have the subform filtered to
that specific boat.

To do that you need to either base you main form on a query that includes
both the Customers table and the Boats table so you can filter on the fields
in the Boats table or else you need to use an In() clause for your filter.
The former might affect the editability of your main form or cause multiple
records to appear per customer so I don't think you would like that
approach.

In the AfterUpdate event of the ComboBox where you select a boat name you
have code...

Me.Filter = "[CustomerNumber] In(SELECT CustomerNUmber FROM Boats WHERE
BoatName = '" & Me!ComboBoxName & "'"

Me.FilterOn = True

That should filter the main form so that it shows the correct Customer.
Then if you set the MasterLnk and ChildLink properties to both the customer
number and the boat name the subform should display that particular boat.

ChildLink properties are required to be based on field names in the
subform's RecordSource, but there is no such requirement for MasterLink
properties. The MasterLink property can just refer to the name of the
ComboBox where you select a boat name. You do not need a boat name field in
the main form. You might get fooled here because the builder form for
entering those properties will only allow field names to be entered. Just
don't use that and type the properties directly into the property boxes.

 

[Stapes]

Hi Again

This is what I have in the AfterUpdate event of my Combo Box:

Private Sub Combo48_AfterUpdate()
' Find Boat
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
' This recordset refers to the Customer table.
rs.FindFirst "[PK_Customer] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This gets the correct customer record & displays it. Not a problem.
The bound column of the Combo Box is the Customer Number. The boat name
is another field in the Combo Box. I have tried concatenating the two
together & unstringing them in the code. If I were to unstring the Boat
Name into a public variable - could the ChildLink refer to that?

Stapes

 

[Stapes]

Me.Filter = "[CustomerNumber] In(SELECT CustomerNUmber FROM Boats WHERE
BoatName = '" & Me!ComboBoxName & "'"

This line of code appears to have a syntax error - a missing ) maybe?