Can i do a search on a value in a form and a subform?

G

Guest

I have a main form created which has a subform allowing multiple inputs of
Trustee names.

I have created search form which has an unbound box used for the search on a
unique ID on the main form. But what I need to do is be able to search by the
Surname from the Trustee subform and also by the unique ID on occasions.

For example. the user would search by the Surname of Smith to show the main
form with all Trustee details shown in the subform for the Surname Smith.
Should they want to narrow the search down they would use both the Surname
and the unique ID.

Is there a way of doing this?
 
G

Guest

Hi

Not really sure it is you are trying to do but I "think" you are looking for
a name search.

Private Sub cboNameSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDField] = " & Str(Me![cboNameSearch])
Me.Bookmark = rs.Bookmark
End If
Me.cboNameSearch = ""
Me.IDField.SetFocus
End Sub

Create an unbound combo (cboNameSearch) and have the columns set as
0cm;2cm;2cm
the 1st column (bound coulm) is the ID field and the 2nd column is the
surname. The 3rd coumn could be the first name
Use the wizard to create the combo source query if you need to.

As I said sure exactley what you're doing so if I'm am wrong post back to
see it you get another answer :)
 
G

Guest

I want to have a form which will allow me to search for a unique ID and/or
the Surname of a Trustee.

I have two tables - a Trusts table and a Trustees table with the information
from these being displayed in a form. The Trusts table makes up the main form
whilst the Trustees makes up the subform on the main form.

Is there a way to enter either the ID or Trustee Surname into unbound boxes
on a form so that when you click the search command button it displays the
above mentioned main form (with subform) but only for records which has
Trustees with the surname Smith for example.

I appreciate your quick response but unfortunately I have only dabbled
slightly with VBA so not to clued up. Dummies VBA would work for me :)


Wayne-I-M said:
Hi

Not really sure it is you are trying to do but I "think" you are looking for
a name search.

Private Sub cboNameSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDField] = " & Str(Me![cboNameSearch])
Me.Bookmark = rs.Bookmark
End If
Me.cboNameSearch = ""
Me.IDField.SetFocus
End Sub

Create an unbound combo (cboNameSearch) and have the columns set as
0cm;2cm;2cm
the 1st column (bound coulm) is the ID field and the 2nd column is the
surname. The 3rd coumn could be the first name
Use the wizard to create the combo source query if you need to.

As I said sure exactley what you're doing so if I'm am wrong post back to
see it you get another answer :)


--
Wayne
Manchester, England.



Daveed1973 said:
I have a main form created which has a subform allowing multiple inputs of
Trustee names.

I have created search form which has an unbound box used for the search on a
unique ID on the main form. But what I need to do is be able to search by the
Surname from the Trustee subform and also by the unique ID on occasions.

For example. the user would search by the Surname of Smith to show the main
form with all Trustee details shown in the subform for the Surname Smith.
Should they want to narrow the search down they would use both the Surname
and the unique ID.

Is there a way of doing this?
Click to expand...
Click to expand...
 
G

Guest

Sorry, forgot to mention that the Unique ID comes from the Trusts table to
uniquly identify the Trust record.

So it would be tbl_Trusts - Unique_ID and/or tbl_Trustees - Surname I would
need to search on.

Daveed1973 said:
I want to have a form which will allow me to search for a unique ID and/or
the Surname of a Trustee.

I have two tables - a Trusts table and a Trustees table with the information
from these being displayed in a form. The Trusts table makes up the main form
whilst the Trustees makes up the subform on the main form.

Is there a way to enter either the ID or Trustee Surname into unbound boxes
on a form so that when you click the search command button it displays the
above mentioned main form (with subform) but only for records which has
Trustees with the surname Smith for example.

I appreciate your quick response but unfortunately I have only dabbled
slightly with VBA so not to clued up. Dummies VBA would work for me :)


Wayne-I-M said:
Hi

Not really sure it is you are trying to do but I "think" you are looking for
a name search.

Private Sub cboNameSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDField] = " & Str(Me![cboNameSearch])
Me.Bookmark = rs.Bookmark
End If
Me.cboNameSearch = ""
Me.IDField.SetFocus
End Sub

Create an unbound combo (cboNameSearch) and have the columns set as
0cm;2cm;2cm
the 1st column (bound coulm) is the ID field and the 2nd column is the
surname. The 3rd coumn could be the first name
Use the wizard to create the combo source query if you need to.

As I said sure exactley what you're doing so if I'm am wrong post back to
see it you get another answer :)


--
Wayne
Manchester, England.



Daveed1973 said:
I have a main form created which has a subform allowing multiple inputs of
Trustee names.

I have created search form which has an unbound box used for the search on a
unique ID on the main form. But what I need to do is be able to search by the
Surname from the Trustee subform and also by the unique ID on occasions.

For example. the user would search by the Surname of Smith to show the main
form with all Trustee details shown in the subform for the Surname Smith.
Should they want to narrow the search down they would use both the Surname
and the unique ID.

Is there a way of doing this?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It's not too hard to do. But if you don't have the basics of coding you ay
be best using the wizard.

Open the form in design mode and view the tool box. Click the combo tool
and use the wizard to create the lookup as you want it. I would suggest you
create 2 combos one for ech search.


--
Wayne
Manchester, England.



Daveed1973 said:
Sorry, forgot to mention that the Unique ID comes from the Trusts table to
uniquly identify the Trust record.

So it would be tbl_Trusts - Unique_ID and/or tbl_Trustees - Surname I would
need to search on.

Daveed1973 said:
I want to have a form which will allow me to search for a unique ID and/or
the Surname of a Trustee.

I have two tables - a Trusts table and a Trustees table with the information
from these being displayed in a form. The Trusts table makes up the main form
whilst the Trustees makes up the subform on the main form.

Is there a way to enter either the ID or Trustee Surname into unbound boxes
on a form so that when you click the search command button it displays the
above mentioned main form (with subform) but only for records which has
Trustees with the surname Smith for example.

I appreciate your quick response but unfortunately I have only dabbled
slightly with VBA so not to clued up. Dummies VBA would work for me :)


Wayne-I-M said:
Hi

Not really sure it is you are trying to do but I "think" you are looking for
a name search.

Private Sub cboNameSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[IDField] = " & Str(Me![cboNameSearch])
Me.Bookmark = rs.Bookmark
End If
Me.cboNameSearch = ""
Me.IDField.SetFocus
End Sub

Create an unbound combo (cboNameSearch) and have the columns set as
0cm;2cm;2cm
the 1st column (bound coulm) is the ID field and the 2nd column is the
surname. The 3rd coumn could be the first name
Use the wizard to create the combo source query if you need to.

As I said sure exactley what you're doing so if I'm am wrong post back to
see it you get another answer :)


--
Wayne
Manchester, England.



:

I have a main form created which has a subform allowing multiple inputs of
Trustee names.

I have created search form which has an unbound box used for the search on a
unique ID on the main form. But what I need to do is be able to search by the
Surname from the Trustee subform and also by the unique ID on occasions.

For example. the user would search by the Surname of Smith to show the main
form with all Trustee details shown in the subform for the Surname Smith.
Should they want to narrow the search down they would use both the Surname
and the unique ID.

Is there a way of doing this?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Searching a Main Form & Subform 1
Using a subform to show newrecord on the main form 1
A little form to search main from 3
Please help - I need to separate data to two tables using one form. 0
Customer Search Query and from 1
Create a Subform to Search Main forms records 3
Search Form - SubForm Advanced 5
Sync Main Form with Subform after Combo Selection 2

Top