We recently changed a database to using subform.

After the change, when end user attempts to search a record in the subform,
as it only shows those records related to that in main form, he is not
successfully to search as before. That is fully understandable.

However, he would like to find the record even though by searching a certain
field in the subform so that both record in main form and subform changes
(according to the record searched in the subform).

Is there any way to do so OR just tell him that it is not possible ?

Your advice is sought.

Thanks

Hum, that is going to be a bit hard to do...and, likely a bit confusing
also...

I would consider building a nice search form. You could join the main table
+ child table, and display the results in a grid...

(so, main table fields, or child tables fields could be searched in the
form...you could even use ctrl-f in this case..).

Or, you could build somthing that prompts the two fields...

Here is a few screen shots of what I mean:

http://www.members.shaw.ca/AlbertKal...rch/index.html

And, you could even have some type of drill down...here is some more screens
that might give you some ideas....

http://www.members.shaw.ca/AlbertKal...icles/Grid.htm


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

The data structure behind a form/subform usually consists of 2 tables:
a) the main form's table, with a primary key field;
b) the subform's table, with a foreign key field to the main form's table.

Since the subform's table relates to the primary key of the main form's
table, the first thing you need to do is discover what the foreign key value
is for the record you want to match. You can then find that record in the
main form. At that point Access loads all the related records in the
subform, so you can then find the record you want in the subform.

The example below works with the Customer Orders form in Northwind. This
form is bound to the Customers table, with Orders for the customer in the
subform. If you add an unbound text box to the main form to enter the order
number you want to see, it has to lookup which customer this is for, locate
that record in the main form, and then choose the right order in the
subform. The code assumed you added a text box named txtFindOrder to the
main form (and set the form's AllowEdits property to Yes so you can enter a
number to find):

Private Sub txtFindOrder_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
Dim varResult As Variant

If Not IsNull(Me.txtFindOrder) Then
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
'Lookup the customer for this order.
strWhere = "OrderID = " & Me.txtFindOrder
varResult = DLookup("CustomerID", "Orders", strWhere)
If IsNull(varResult) Then
MsgBox "No such order."
Else

'Find the record in the main form.
strWhere = "CustomerID = """ & varResult & """"
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Customer not found. Is form filtered?"
Else
Me.Bookmark = rs.Bookmark
Set rs = Nothing

'Now find in the subform.
strWhere = "OrderID = " & Me.txtFindOrder
With Me.[Customer Orders Subform1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found in subform"
Else
.Bookmark = rs.Bookmark
End If
End With
End If
End If
End If
Set rs = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> We recently changed a database to using subform.
>
> After the change, when end user attempts to search a record in the
> subform, as it only shows those records related to that in main form, he
> is not successfully to search as before. That is fully understandable.
>
> However, he would like to find the record even though by searching a
> certain field in the subform so that both record in main form and subform
> changes (according to the record searched in the subform).
>
> Is there any way to do so OR just tell him that it is not possible ?
>
> Your advice is sought.
>
> Thanks

Dear Allen,

Thank you for your reply and I have tried your suggestion in the Northwind
database.

However, my case is more complicated than the sample. It is a database with
Equipment and Lessee information (1:M). End user wants to use Control + F
in the subform (Lessee) to find the Equipment in the main form. Of course,
we can provide a text box in the main form just like the sample.

However, I find that I am not able to amend your sample to one that search a
text field (Like - They enter "Browne" and find that Equipment is leased by
him.

Your advice is sought

Robert

"Allen Browne" <(E-Mail Removed)> wrote in message
news:%23%(E-Mail Removed)...
> The data structure behind a form/subform usually consists of 2 tables:
> a) the main form's table, with a primary key field;
> b) the subform's table, with a foreign key field to the main form's table.
>
> Since the subform's table relates to the primary key of the main form's
> table, the first thing you need to do is discover what the foreign key
> value is for the record you want to match. You can then find that record
> in the main form. At that point Access loads all the related records in
> the subform, so you can then find the record you want in the subform.
>
> The example below works with the Customer Orders form in Northwind. This
> form is bound to the Customers table, with Orders for the customer in the
> subform. If you add an unbound text box to the main form to enter the
> order number you want to see, it has to lookup which customer this is for,
> locate that record in the main form, and then choose the right order in
> the subform. The code assumed you added a text box named txtFindOrder to
> the main form (and set the form's AllowEdits property to Yes so you can
> enter a number to find):
>
> Private Sub txtFindOrder_AfterUpdate()
> Dim rs As DAO.Recordset
> Dim strWhere As String
> Dim varResult As Variant
>
> If Not IsNull(Me.txtFindOrder) Then
> If Me.Dirty Then 'Save first
> Me.Dirty = False
> End If
> 'Lookup the customer for this order.
> strWhere = "OrderID = " & Me.txtFindOrder
> varResult = DLookup("CustomerID", "Orders", strWhere)
> If IsNull(varResult) Then
> MsgBox "No such order."
> Else
>
> 'Find the record in the main form.
> strWhere = "CustomerID = """ & varResult & """"
> Set rs = Me.RecordsetClone
> rs.FindFirst strWhere
> If rs.NoMatch Then
> MsgBox "Customer not found. Is form filtered?"
> Else
> Me.Bookmark = rs.Bookmark
> Set rs = Nothing
>
> 'Now find in the subform.
> strWhere = "OrderID = " & Me.txtFindOrder
> With Me.[Customer Orders Subform1].Form
> Set rs = .RecordsetClone
> rs.FindFirst strWhere
> If rs.NoMatch Then
> MsgBox "Not found in subform"
> Else
> .Bookmark = rs.Bookmark
> End If
> End With
> End If
> End If
> End If
> Set rs = Nothing
> End Sub
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Robert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> We recently changed a database to using subform.
>>
>> After the change, when end user attempts to search a record in the
>> subform, as it only shows those records related to that in main form, he
>> is not successfully to search as before. That is fully understandable.
>>
>> However, he would like to find the record even though by searching a
>> certain field in the subform so that both record in main form and subform
>> changes (according to the record searched in the subform).
>>
>> Is there any way to do so OR just tell him that it is not possible ?
>>
>> Your advice is sought.
>>
>> Thanks
>
>

So is the Browne in the foreign key, or do you need to look it up?

You can look up a text value if you add quotes the the Criteria string,
e.g.:
strWhere = "ClientID = """ & Me.txtName & """"

Unless you have many thousands of clients, you could use an unbound combo
for the user to select the name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Dear Allen,
>
> Thank you for your reply and I have tried your suggestion in the Northwind
> database.
>
> However, my case is more complicated than the sample. It is a database
> with Equipment and Lessee information (1:M). End user wants to use
> Control + F in the subform (Lessee) to find the Equipment in the main
> form. Of course, we can provide a text box in the main form just like the
> sample.
>
> However, I find that I am not able to amend your sample to one that search
> a text field (Like - They enter "Browne" and find that Equipment is leased
> by him.
>
> Your advice is sought
>
> Robert
>
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:%23%(E-Mail Removed)...
>> The data structure behind a form/subform usually consists of 2 tables:
>> a) the main form's table, with a primary key field;
>> b) the subform's table, with a foreign key field to the main form's
>> table.
>>
>> Since the subform's table relates to the primary key of the main form's
>> table, the first thing you need to do is discover what the foreign key
>> value is for the record you want to match. You can then find that record
>> in the main form. At that point Access loads all the related records in
>> the subform, so you can then find the record you want in the subform.
>>
>> The example below works with the Customer Orders form in Northwind. This
>> form is bound to the Customers table, with Orders for the customer in the
>> subform. If you add an unbound text box to the main form to enter the
>> order number you want to see, it has to lookup which customer this is
>> for, locate that record in the main form, and then choose the right order
>> in the subform. The code assumed you added a text box named txtFindOrder
>> to the main form (and set the form's AllowEdits property to Yes so you
>> can enter a number to find):
>>
>> Private Sub txtFindOrder_AfterUpdate()
>> Dim rs As DAO.Recordset
>> Dim strWhere As String
>> Dim varResult As Variant
>>
>> If Not IsNull(Me.txtFindOrder) Then
>> If Me.Dirty Then 'Save first
>> Me.Dirty = False
>> End If
>> 'Lookup the customer for this order.
>> strWhere = "OrderID = " & Me.txtFindOrder
>> varResult = DLookup("CustomerID", "Orders", strWhere)
>> If IsNull(varResult) Then
>> MsgBox "No such order."
>> Else
>>
>> 'Find the record in the main form.
>> strWhere = "CustomerID = """ & varResult & """"
>> Set rs = Me.RecordsetClone
>> rs.FindFirst strWhere
>> If rs.NoMatch Then
>> MsgBox "Customer not found. Is form filtered?"
>> Else
>> Me.Bookmark = rs.Bookmark
>> Set rs = Nothing
>>
>> 'Now find in the subform.
>> strWhere = "OrderID = " & Me.txtFindOrder
>> With Me.[Customer Orders Subform1].Form
>> Set rs = .RecordsetClone
>> rs.FindFirst strWhere
>> If rs.NoMatch Then
>> MsgBox "Not found in subform"
>> Else
>> .Bookmark = rs.Bookmark
>> End If
>> End With
>> End If
>> End If
>> End If
>> Set rs = Nothing
>> End Sub
>>
>> "Robert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> We recently changed a database to using subform.
>>>
>>> After the change, when end user attempts to search a record in the
>>> subform, as it only shows those records related to that in main form, he
>>> is not successfully to search as before. That is fully understandable.
>>>
>>> However, he would like to find the record even though by searching a
>>> certain field in the subform so that both record in main form and
>>> subform changes (according to the record searched in the subform).
>>>
>>> Is there any way to do so OR just tell him that it is not possible ?

Dear Allen,

Thank you for your advice.

I have two more questions
1) How to handle the case if there is more than 1 lessee- Like there are
more than 1 Allen ?
2) If I create an unbound combo box, how can I populate the newly added
lessee to that combo box ?

Thanks
"Allen Browne" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> So is the Browne in the foreign key, or do you need to look it up?
>
> You can look up a text value if you add quotes the the Criteria string,
> e.g.:
> strWhere = "ClientID = """ & Me.txtName & """"
>
> Unless you have many thousands of clients, you could use an unbound combo
> for the user to select the name.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Robert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Dear Allen,
>>
>> Thank you for your reply and I have tried your suggestion in the
>> Northwind database.
>>
>> However, my case is more complicated than the sample. It is a database
>> with Equipment and Lessee information (1:M). End user wants to use
>> Control + F in the subform (Lessee) to find the Equipment in the main
>> form. Of course, we can provide a text box in the main form just like
>> the sample.
>>
>> However, I find that I am not able to amend your sample to one that
>> search a text field (Like - They enter "Browne" and find that Equipment
>> is leased by him.
>>
>> Your advice is sought
>>
>> Robert
>>
>> "Allen Browne" <(E-Mail Removed)> wrote in message
>> news:%23%(E-Mail Removed)...
>>> The data structure behind a form/subform usually consists of 2 tables:
>>> a) the main form's table, with a primary key field;
>>> b) the subform's table, with a foreign key field to the main form's
>>> table.
>>>
>>> Since the subform's table relates to the primary key of the main form's
>>> table, the first thing you need to do is discover what the foreign key
>>> value is for the record you want to match. You can then find that record
>>> in the main form. At that point Access loads all the related records in
>>> the subform, so you can then find the record you want in the subform.
>>>
>>> The example below works with the Customer Orders form in Northwind. This
>>> form is bound to the Customers table, with Orders for the customer in
>>> the subform. If you add an unbound text box to the main form to enter
>>> the order number you want to see, it has to lookup which customer this
>>> is for, locate that record in the main form, and then choose the right
>>> order in the subform. The code assumed you added a text box named
>>> txtFindOrder to the main form (and set the form's AllowEdits property to
>>> Yes so you can enter a number to find):
>>>
>>> Private Sub txtFindOrder_AfterUpdate()
>>> Dim rs As DAO.Recordset
>>> Dim strWhere As String
>>> Dim varResult As Variant
>>>
>>> If Not IsNull(Me.txtFindOrder) Then
>>> If Me.Dirty Then 'Save first
>>> Me.Dirty = False
>>> End If
>>> 'Lookup the customer for this order.
>>> strWhere = "OrderID = " & Me.txtFindOrder
>>> varResult = DLookup("CustomerID", "Orders", strWhere)
>>> If IsNull(varResult) Then
>>> MsgBox "No such order."
>>> Else
>>>
>>> 'Find the record in the main form.
>>> strWhere = "CustomerID = """ & varResult & """"
>>> Set rs = Me.RecordsetClone
>>> rs.FindFirst strWhere
>>> If rs.NoMatch Then
>>> MsgBox "Customer not found. Is form filtered?"
>>> Else
>>> Me.Bookmark = rs.Bookmark
>>> Set rs = Nothing
>>>
>>> 'Now find in the subform.
>>> strWhere = "OrderID = " & Me.txtFindOrder
>>> With Me.[Customer Orders Subform1].Form
>>> Set rs = .RecordsetClone
>>> rs.FindFirst strWhere
>>> If rs.NoMatch Then
>>> MsgBox "Not found in subform"
>>> Else
>>> .Bookmark = rs.Bookmark
>>> End If
>>> End With
>>> End If
>>> End If
>>> End If
>>> Set rs = Nothing
>>> End Sub
>>>
>>> "Robert" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> We recently changed a database to using subform.
>>>>
>>>> After the change, when end user attempts to search a record in the
>>>> subform, as it only shows those records related to that in main form,
>>>> he is not successfully to search as before. That is fully
>>>> understandable.
>>>>
>>>> However, he would like to find the record even though by searching a
>>>> certain field in the subform so that both record in main form and
>>>> subform changes (according to the record searched in the subform).
>>>>
>>>> Is there any way to do so OR just tell him that it is not possible ?
>
>

Dear Allen,

Thank you for your advice.

I have two more questions
1) How to handle the case if there is more than 1 lessee- Like there are
more than 1 Allen ?
2) If I create an unbound combo box, how can I populate the newly added
lessee to that combo box ?

Thanks
"Allen Browne" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> So is the Browne in the foreign key, or do you need to look it up?
>
> You can look up a text value if you add quotes the the Criteria string,
> e.g.:
> strWhere = "ClientID = """ & Me.txtName & """"
>
> Unless you have many thousands of clients, you could use an unbound combo
> for the user to select the name.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Robert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Dear Allen,
>>
>> Thank you for your reply and I have tried your suggestion in the
>> Northwind database.
>>
>> However, my case is more complicated than the sample. It is a database
>> with Equipment and Lessee information (1:M). End user wants to use
>> Control + F in the subform (Lessee) to find the Equipment in the main
>> form. Of course, we can provide a text box in the main form just like
>> the sample.
>>
>> However, I find that I am not able to amend your sample to one that
>> search a text field (Like - They enter "Browne" and find that Equipment
>> is leased by him.
>>
>> Your advice is sought
>>
>> Robert
>>
>> "Allen Browne" <(E-Mail Removed)> wrote in message
>> news:%23%(E-Mail Removed)...
>>> The data structure behind a form/subform usually consists of 2 tables:
>>> a) the main form's table, with a primary key field;
>>> b) the subform's table, with a foreign key field to the main form's
>>> table.
>>>
>>> Since the subform's table relates to the primary key of the main form's
>>> table, the first thing you need to do is discover what the foreign key
>>> value is for the record you want to match. You can then find that record
>>> in the main form. At that point Access loads all the related records in
>>> the subform, so you can then find the record you want in the subform.
>>>
>>> The example below works with the Customer Orders form in Northwind. This
>>> form is bound to the Customers table, with Orders for the customer in
>>> the subform. If you add an unbound text box to the main form to enter
>>> the order number you want to see, it has to lookup which customer this
>>> is for, locate that record in the main form, and then choose the right
>>> order in the subform. The code assumed you added a text box named
>>> txtFindOrder to the main form (and set the form's AllowEdits property to
>>> Yes so you can enter a number to find):
>>>
>>> Private Sub txtFindOrder_AfterUpdate()
>>> Dim rs As DAO.Recordset
>>> Dim strWhere As String
>>> Dim varResult As Variant
>>>
>>> If Not IsNull(Me.txtFindOrder) Then
>>> If Me.Dirty Then 'Save first
>>> Me.Dirty = False
>>> End If
>>> 'Lookup the customer for this order.
>>> strWhere = "OrderID = " & Me.txtFindOrder
>>> varResult = DLookup("CustomerID", "Orders", strWhere)
>>> If IsNull(varResult) Then
>>> MsgBox "No such order."
>>> Else
>>>
>>> 'Find the record in the main form.
>>> strWhere = "CustomerID = """ & varResult & """"
>>> Set rs = Me.RecordsetClone
>>> rs.FindFirst strWhere
>>> If rs.NoMatch Then
>>> MsgBox "Customer not found. Is form filtered?"
>>> Else
>>> Me.Bookmark = rs.Bookmark
>>> Set rs = Nothing
>>>
>>> 'Now find in the subform.
>>> strWhere = "OrderID = " & Me.txtFindOrder
>>> With Me.[Customer Orders Subform1].Form
>>> Set rs = .RecordsetClone
>>> rs.FindFirst strWhere
>>> If rs.NoMatch Then
>>> MsgBox "Not found in subform"
>>> Else
>>> .Bookmark = rs.Bookmark
>>> End If
>>> End With
>>> End If
>>> End If
>>> End If
>>> Set rs = Nothing
>>> End Sub
>>>
>>> "Robert" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> We recently changed a database to using subform.
>>>>
>>>> After the change, when end user attempts to search a record in the
>>>> subform, as it only shows those records related to that in main form,
>>>> he is not successfully to search as before. That is fully
>>>> understandable.
>>>>
>>>> However, he would like to find the record even though by searching a
>>>> certain field in the subform so that both record in main form and
>>>> subform changes (according to the record searched in the subform).
>>>>
>>>> Is there any way to do so OR just tell him that it is not possible ?
>
>

A1: Set the RowSource of your combo to query such as this:
SELECT ClientID, Surname & ", " & FirstName AS FullName FROM tblClient
ORDER BY Surname, FirstName;
The query combines the name fields into one field for the user choose from.

A2: Use the AfterUpdate event of the form where new entries are made to
Requery the combo. Example:
Private Sub Form_AfterUpdate()
Forms!Form2.MyCombo.Requery
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Dear Allen,
>
> Thank you for your advice.
>
> I have two more questions
> 1) How to handle the case if there is more than 1 lessee- Like there are
> more than 1 Allen ?
> 2) If I create an unbound combo box, how can I populate the newly added
> lessee to that combo box ?
>
> Thanks
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> So is the Browne in the foreign key, or do you need to look it up?
>>
>> You can look up a text value if you add quotes the the Criteria string,
>> e.g.:
>> strWhere = "ClientID = """ & Me.txtName & """"
>>
>> Unless you have many thousands of clients, you could use an unbound combo
>> for the user to select the name.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Robert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Dear Allen,
>>>
>>> Thank you for your reply and I have tried your suggestion in the
>>> Northwind database.
>>>
>>> However, my case is more complicated than the sample. It is a database
>>> with Equipment and Lessee information (1:M). End user wants to use
>>> Control + F in the subform (Lessee) to find the Equipment in the main
>>> form. Of course, we can provide a text box in the main form just like
>>> the sample.
>>>
>>> However, I find that I am not able to amend your sample to one that
>>> search a text field (Like - They enter "Browne" and find that Equipment
>>> is leased by him.
>>>
>>> Your advice is sought
>>>
>>> Robert
>>>
>>> "Allen Browne" <(E-Mail Removed)> wrote in message
>>> news:%23%(E-Mail Removed)...
>>>> The data structure behind a form/subform usually consists of 2 tables:
>>>> a) the main form's table, with a primary key field;
>>>> b) the subform's table, with a foreign key field to the main form's
>>>> table.
>>>>
>>>> Since the subform's table relates to the primary key of the main form's
>>>> table, the first thing you need to do is discover what the foreign key
>>>> value is for the record you want to match. You can then find that
>>>> record in the main form. At that point Access loads all the related
>>>> records in the subform, so you can then find the record you want in the
>>>> subform.
>>>>
>>>> The example below works with the Customer Orders form in Northwind.
>>>> This form is bound to the Customers table, with Orders for the customer
>>>> in the subform. If you add an unbound text box to the main form to
>>>> enter the order number you want to see, it has to lookup which customer
>>>> this is for, locate that record in the main form, and then choose the
>>>> right order in the subform. The code assumed you added a text box named
>>>> txtFindOrder to the main form (and set the form's AllowEdits property
>>>> to Yes so you can enter a number to find):
>>>>
>>>> Private Sub txtFindOrder_AfterUpdate()
>>>> Dim rs As DAO.Recordset
>>>> Dim strWhere As String
>>>> Dim varResult As Variant
>>>>
>>>> If Not IsNull(Me.txtFindOrder) Then
>>>> If Me.Dirty Then 'Save first
>>>> Me.Dirty = False
>>>> End If
>>>> 'Lookup the customer for this order.
>>>> strWhere = "OrderID = " & Me.txtFindOrder
>>>> varResult = DLookup("CustomerID", "Orders", strWhere)
>>>> If IsNull(varResult) Then
>>>> MsgBox "No such order."
>>>> Else
>>>>
>>>> 'Find the record in the main form.
>>>> strWhere = "CustomerID = """ & varResult & """"
>>>> Set rs = Me.RecordsetClone
>>>> rs.FindFirst strWhere
>>>> If rs.NoMatch Then
>>>> MsgBox "Customer not found. Is form filtered?"
>>>> Else
>>>> Me.Bookmark = rs.Bookmark
>>>> Set rs = Nothing
>>>>
>>>> 'Now find in the subform.
>>>> strWhere = "OrderID = " & Me.txtFindOrder
>>>> With Me.[Customer Orders Subform1].Form
>>>> Set rs = .RecordsetClone
>>>> rs.FindFirst strWhere
>>>> If rs.NoMatch Then
>>>> MsgBox "Not found in subform"
>>>> Else
>>>> .Bookmark = rs.Bookmark
>>>> End If
>>>> End With
>>>> End If
>>>> End If
>>>> End If
>>>> Set rs = Nothing
>>>> End Sub
>>>>
>>>> "Robert" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> We recently changed a database to using subform.
>>>>>
>>>>> After the change, when end user attempts to search a record in the
>>>>> subform, as it only shows those records related to that in main form,
>>>>> he is not successfully to search as before. That is fully
>>>>> understandable.
>>>>>
>>>>> However, he would like to find the record even though by searching a
>>>>> certain field in the subform so that both record in main form and
>>>>> subform changes (according to the record searched in the subform).
>>>>>
>>>>> Is there any way to do so OR just tell him that it is not possible ?

A1: Set the RowSource of your combo to query such as this:
SELECT ClientID, Surname & ", " & FirstName AS FullName FROM tblClient
ORDER BY Surname, FirstName;
The query combines the name fields into one field for the user choose from.

A2: Use the AfterUpdate event of the form where new entries are made to
Requery the combo. Example:
Private Sub Form_AfterUpdate()
Forms!Form2.MyCombo.Requery
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Dear Allen,
>
> Thank you for your advice.
>
> I have two more questions
> 1) How to handle the case if there is more than 1 lessee- Like there are
> more than 1 Allen ?
> 2) If I create an unbound combo box, how can I populate the newly added
> lessee to that combo box ?
>
> Thanks
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> So is the Browne in the foreign key, or do you need to look it up?
>>
>> You can look up a text value if you add quotes the the Criteria string,
>> e.g.:
>> strWhere = "ClientID = """ & Me.txtName & """"
>>
>> Unless you have many thousands of clients, you could use an unbound combo
>> for the user to select the name.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Robert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Dear Allen,
>>>
>>> Thank you for your reply and I have tried your suggestion in the
>>> Northwind database.
>>>
>>> However, my case is more complicated than the sample. It is a database
>>> with Equipment and Lessee information (1:M). End user wants to use
>>> Control + F in the subform (Lessee) to find the Equipment in the main
>>> form. Of course, we can provide a text box in the main form just like
>>> the sample.
>>>
>>> However, I find that I am not able to amend your sample to one that
>>> search a text field (Like - They enter "Browne" and find that Equipment
>>> is leased by him.
>>>
>>> Your advice is sought
>>>
>>> Robert
>>>
>>> "Allen Browne" <(E-Mail Removed)> wrote in message
>>> news:%23%(E-Mail Removed)...
>>>> The data structure behind a form/subform usually consists of 2 tables:
>>>> a) the main form's table, with a primary key field;
>>>> b) the subform's table, with a foreign key field to the main form's
>>>> table.
>>>>
>>>> Since the subform's table relates to the primary key of the main form's
>>>> table, the first thing you need to do is discover what the foreign key
>>>> value is for the record you want to match. You can then find that
>>>> record in the main form. At that point Access loads all the related
>>>> records in the subform, so you can then find the record you want in the
>>>> subform.
>>>>
>>>> The example below works with the Customer Orders form in Northwind.
>>>> This form is bound to the Customers table, with Orders for the customer
>>>> in the subform. If you add an unbound text box to the main form to
>>>> enter the order number you want to see, it has to lookup which customer
>>>> this is for, locate that record in the main form, and then choose the
>>>> right order in the subform. The code assumed you added a text box named
>>>> txtFindOrder to the main form (and set the form's AllowEdits property
>>>> to Yes so you can enter a number to find):
>>>>
>>>> Private Sub txtFindOrder_AfterUpdate()
>>>> Dim rs As DAO.Recordset
>>>> Dim strWhere As String
>>>> Dim varResult As Variant
>>>>
>>>> If Not IsNull(Me.txtFindOrder) Then
>>>> If Me.Dirty Then 'Save first
>>>> Me.Dirty = False
>>>> End If
>>>> 'Lookup the customer for this order.
>>>> strWhere = "OrderID = " & Me.txtFindOrder
>>>> varResult = DLookup("CustomerID", "Orders", strWhere)
>>>> If IsNull(varResult) Then
>>>> MsgBox "No such order."
>>>> Else
>>>>
>>>> 'Find the record in the main form.
>>>> strWhere = "CustomerID = """ & varResult & """"
>>>> Set rs = Me.RecordsetClone
>>>> rs.FindFirst strWhere
>>>> If rs.NoMatch Then
>>>> MsgBox "Customer not found. Is form filtered?"
>>>> Else
>>>> Me.Bookmark = rs.Bookmark
>>>> Set rs = Nothing
>>>>
>>>> 'Now find in the subform.
>>>> strWhere = "OrderID = " & Me.txtFindOrder
>>>> With Me.[Customer Orders Subform1].Form
>>>> Set rs = .RecordsetClone
>>>> rs.FindFirst strWhere
>>>> If rs.NoMatch Then
>>>> MsgBox "Not found in subform"
>>>> Else
>>>> .Bookmark = rs.Bookmark
>>>> End If
>>>> End With
>>>> End If
>>>> End If
>>>> End If
>>>> Set rs = Nothing
>>>> End Sub
>>>>
>>>> "Robert" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> We recently changed a database to using subform.
>>>>>
>>>>> After the change, when end user attempts to search a record in the
>>>>> subform, as it only shows those records related to that in main form,
>>>>> he is not successfully to search as before. That is fully
>>>>> understandable.
>>>>>
>>>>> However, he would like to find the record even though by searching a
>>>>> certain field in the subform so that both record in main form and
>>>>> subform changes (according to the record searched in the subform).
>>>>>
>>>>> Is there any way to do so OR just tell him that it is not possible ?

Dear Allen,

I have setup the combo box successfully. Thank you for your help.

Regards,
Robert

"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> A1: Set the RowSource of your combo to query such as this:
> SELECT ClientID, Surname & ", " & FirstName AS FullName FROM tblClient
> ORDER BY Surname, FirstName;
> The query combines the name fields into one field for the user choose
> from.
>
> A2: Use the AfterUpdate event of the form where new entries are made to
> Requery the combo. Example:
> Private Sub Form_AfterUpdate()
> Forms!Form2.MyCombo.Requery
> End Sub
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Robert" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Dear Allen,
>>
>> Thank you for your advice.
>>
>> I have two more questions
>> 1) How to handle the case if there is more than 1 lessee- Like there are
>> more than 1 Allen ?
>> 2) If I create an unbound combo box, how can I populate the newly added
>> lessee to that combo box ?
>>
>> Thanks
>> "Allen Browne" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> So is the Browne in the foreign key, or do you need to look it up?
>>>
>>> You can look up a text value if you add quotes the the Criteria string,
>>> e.g.:
>>> strWhere = "ClientID = """ & Me.txtName & """"
>>>
>>> Unless you have many thousands of clients, you could use an unbound
>>> combo for the user to select the name.
>>>
>>> --
>>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>>> Tips for Access users - http://allenbrowne.com/tips.html
>>> Reply to group, rather than allenbrowne at mvps dot org.
>>>
>>> "Robert" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Dear Allen,
>>>>
>>>> Thank you for your reply and I have tried your suggestion in the
>>>> Northwind database.
>>>>
>>>> However, my case is more complicated than the sample. It is a database
>>>> with Equipment and Lessee information (1:M). End user wants to use
>>>> Control + F in the subform (Lessee) to find the Equipment in the main
>>>> form. Of course, we can provide a text box in the main form just like
>>>> the sample.
>>>>
>>>> However, I find that I am not able to amend your sample to one that
>>>> search a text field (Like - They enter "Browne" and find that Equipment
>>>> is leased by him.
>>>>
>>>> Your advice is sought
>>>>
>>>> Robert
>>>>
>>>> "Allen Browne" <(E-Mail Removed)> wrote in message
>>>> news:%23%(E-Mail Removed)...
>>>>> The data structure behind a form/subform usually consists of 2 tables:
>>>>> a) the main form's table, with a primary key field;
>>>>> b) the subform's table, with a foreign key field to the main form's
>>>>> table.
>>>>>
>>>>> Since the subform's table relates to the primary key of the main
>>>>> form's table, the first thing you need to do is discover what the
>>>>> foreign key value is for the record you want to match. You can then
>>>>> find that record in the main form. At that point Access loads all the
>>>>> related records in the subform, so you can then find the record you
>>>>> want in the subform.
>>>>>
>>>>> The example below works with the Customer Orders form in Northwind.
>>>>> This form is bound to the Customers table, with Orders for the
>>>>> customer in the subform. If you add an unbound text box to the main
>>>>> form to enter the order number you want to see, it has to lookup which
>>>>> customer this is for, locate that record in the main form, and then
>>>>> choose the right order in the subform. The code assumed you added a
>>>>> text box named txtFindOrder to the main form (and set the form's
>>>>> AllowEdits property to Yes so you can enter a number to find):
>>>>>
>>>>> Private Sub txtFindOrder_AfterUpdate()
>>>>> Dim rs As DAO.Recordset
>>>>> Dim strWhere As String
>>>>> Dim varResult As Variant
>>>>>
>>>>> If Not IsNull(Me.txtFindOrder) Then
>>>>> If Me.Dirty Then 'Save first
>>>>> Me.Dirty = False
>>>>> End If
>>>>> 'Lookup the customer for this order.
>>>>> strWhere = "OrderID = " & Me.txtFindOrder
>>>>> varResult = DLookup("CustomerID", "Orders", strWhere)
>>>>> If IsNull(varResult) Then
>>>>> MsgBox "No such order."
>>>>> Else
>>>>>
>>>>> 'Find the record in the main form.
>>>>> strWhere = "CustomerID = """ & varResult & """"
>>>>> Set rs = Me.RecordsetClone
>>>>> rs.FindFirst strWhere
>>>>> If rs.NoMatch Then
>>>>> MsgBox "Customer not found. Is form filtered?"
>>>>> Else
>>>>> Me.Bookmark = rs.Bookmark
>>>>> Set rs = Nothing
>>>>>
>>>>> 'Now find in the subform.
>>>>> strWhere = "OrderID = " & Me.txtFindOrder
>>>>> With Me.[Customer Orders Subform1].Form
>>>>> Set rs = .RecordsetClone
>>>>> rs.FindFirst strWhere
>>>>> If rs.NoMatch Then
>>>>> MsgBox "Not found in subform"
>>>>> Else
>>>>> .Bookmark = rs.Bookmark
>>>>> End If
>>>>> End With
>>>>> End If
>>>>> End If
>>>>> End If
>>>>> Set rs = Nothing
>>>>> End Sub
>>>>>
>>>>> "Robert" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> We recently changed a database to using subform.
>>>>>>
>>>>>> After the change, when end user attempts to search a record in the
>>>>>> subform, as it only shows those records related to that in main form,
>>>>>> he is not successfully to search as before. That is fully
>>>>>> understandable.
>>>>>>
>>>>>> However, he would like to find the record even though by searching a
>>>>>> certain field in the subform so that both record in main form and
>>>>>> subform changes (according to the record searched in the subform).
>>>>>>
>>>>>> Is there any way to do so OR just tell him that it is not possible ?
>
>