Best way to handle Find in Subform

[Robert]

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

 

[Albert D.Kallal]

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/AlbertKallal/Search/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/AlbertKallal/Articles/Grid.htm

 

[Allen Browne]

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]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?

 

[Robert]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?

 

[Robert]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?

 

[Robert]

Dear Allen,

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

Regards,
Robert

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?

 

[Robert]

Dear Allen,

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

Regards,
Robert

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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 ?