Allen Browne Search Code

[Guest]

Hi,

I have tried a couple of the extremely useful search options furnished by
Allen Browne, but am encountering one problem.

I have a main form, with a sub form and a sub-sub form.

On the sub-sub form, I am trying to search for a specific ppt number that I
would like to enter in a text control. If a matching record is found, I
would like to make it the active record. If not, I would like a message to
display indicating that it was not found.

The problem is that the 1st subform filters by a dwelling id to display only
the ppts that reside in that particular dwelling in the 2nd subform in which
I wish to execute the result.

So, I am able to search only the currently displayed dwelling participants,
as opposed to all participants, regardless of their dwelling.

I have found many posts which provide code regarding selecting criteria from
a bound combo box, which doesn't work for my needs, as I need to search for
what is typed "freehand" in a text box.

I hope that this is clear. Thanks for any guidance!

 

[AccessVandal via AccessMonster.com]

Hi dee,

It is difficult to guess your table structures, relationship and form design
with seeing the database.

Have you look into one of the problems with subform filtering from Allen
Browne site?

http://allenbrowne.com/bug-02.html

This might help.

 

[Guest]

I have a one to many main form that contains the community, etc. info. Call
this MainForm.

I have a subform SubForm1 that is the many side that contains housing
information, such as address and phone number.

I have another subform SubForm2 that contains the resident information for
people who reside in the houses.

I have been trying for 2 days to create a search box wherein the user can
type in a participant code, after which Access will either jump to the record
if it exists, or display a message indicating that the record doesn't exist.

Most of what I have seen applies to creating a search combo box that lists
the existing data from which one can choose. This is not what I am looking
for, as we wish to type what we are looking for.

I had found a couple of options on Allen Browne's Web site that work
beautifully, except when I use them on the SubForm2, they only search for a
resident in the currently selected household in SubForm1. I need a way to
search the entire SubForm2 for a particular resident, not just in the
currently selected household.

As you can probably tell, coding is not my strength (understatement), so it
makes it that much more frustrating.

Any and all help will be GREATLY appreciated before I pull all my hair out!

 

[Allen Browne]

Dee, I take it you have 3 tables like this:
- Community table, with CommunityID primary key;
- House table, with HouseID primary key, and CommunityID foreign key;
- Person table, with PersonID primary key, and HouseID foreign key.

You interfaced this with:
- MainForm bound to Community table;
- Subform1 bound to House table;
- Subform2 (a sub-subform) bound to Person table.

Now you want to add a text box to MainForm where the user can enter a
PersonID number, and it will load the correct community into the main form,
the correct House into the subform, and choose the correct person in the
sub-subform. Is that the idea?

This example shows how you might respond to unbound text box named
txtFIndPersonID. You will need a query that uses all 3 tables, and shows the
3 key fields. The example below assumes it is named Query1.

Private Sub txtFindPersonID_AfterUpdate
Dim strWhere As String
Dim varResult As Variant

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txtFindPersonID) Then
strWhere = "PersonID = " & Me.txtFindPersonID
varResult = DLookup("CommunityID", "Query1", strWhere)
If IsNull(varResult) then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "CommunityID = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
'Repeat the same logic to find the house in the subform.
End If
End With
End If
End If
End Sub

 

[Guest]

Thank you so much for your response. Yes, you have the structure down
correctly.

I have been going crazy trying to search - even with a combo box based on
the participant code I can't get it to search past the currently displayed
family members.

So, if I understand it correctly, that just isn't possible - I must include
from all three tables.

I had also tried and really liked your find as you type, but, again, it
wouldn't work on the subform.

I will try what you have written. Thank you very, very much.

 

[Guest]

Very stupid question. Do I put the search txt box on the main form, or no
the subform? Also, where do I add the Event Procedure of TxtFindPersonID?

Thank you for your patience.

 

[Allen Browne]

I've assumed you want the search box on the main form.

In form design view, right-click the unbound search box, and choose
Properties.

Set its On Click property to:
[Event Procedure]
The click the Build button (...) beside this.
Access opens the code window.
The code goes in there.

 

[Guest]

I tried the code, placing the text box in the header of the main form.

I get a Variable not defined error message which stops at:
Me.Bookmark = rs.Bookmark
Highlighting rs.

I don't want to start fooling around with your code. Any suggestions?

Thank you.

 

[Allen Browne]

The form is bound to a table, I presume.

You may need to add a reference to the DAO library:
http://allenbrowne.com/ser-38.html
That reference is present by default in all versions of Access except 2000
and 2002.

Ultimiately you will need to modify (and extend) the code to handle your
field names etc. I've assumed these are Number fields; if they are text you
need extra quotes:
http://allenbrowne.com/casu-17.html

 

[Guest]

Yes, the form is buond to tbl_master_lists.

The master_list_id is an autonumber pk
The dwelling_id is an auto number pk
The ppt_id is an autonumber pk
The ppt_no is a text field

I am a little confused, because it looked like this was an After Update
event, but perhaps this is just my lack of knowlege. I will move it to the
On Click as suggested.

My code is as follows:

You say to repeat for house, etc. From what point do I repeat?
Thank you so very much.

Private Sub txt_find_ppt_no_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = " & Me.txt_find_ppt_no
varResult = DLookup("master_list_id", "qry_find_ppt_no", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "master_list_id = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
'Repeat the same logic to find the house in the subform.
End If
End With
End If
End If
End Sub

 

[Guest]

Just as an aside, Allen. Is there any way that I can modify your
FindAsYouType code so that I can use this in my subforms? I love the
flexibility it offers in terms of searching any of the form fields.

Thanks again!

 

[Allen Browne]

Presumably you have the main form working, so that when you enter a
PersonID, it shows the correct community in the main form (but not yet the
right house in the subform)?

Once you have that working, you can add further code for the house.
Basically you add this line at the time with the Dim statements:
Dim frm As Form
Then add this line where indicated:
Set frm = Me![House].Form
Then repeat, using frm instead of Me.
The lookup will be:
varResult = DLookup("House_ID", "qry_find_ppt_no", strWhere)
and you use:
With frm.RecordsetClone
instead of:
With Me.RecordsetClone
etc

 

[Allen Browne]

It would not be simple to do this for the subform, since it only finds the
records that are loaded there. As you already found, if the wrong community
is loaded, it won't find the desired record back in the main form (which is
what you need in order to load the right record into the subform.)

 

[Guest]

I've been busy at work on this and have worked out the error messages I was
getting from not declaring rs variable and the text field quotes (I think!).

Now, when I input a ppt number that exists, though, it just says not found.

I have removed the Communities main form, so am now working with 1 form and
1 subform. (Main form is based on tbl_dwelling and contains address, etc.,
subform is based on tbl_ppts and contains fields relating to each individual
in the dwelling.)

Here is my code. I imagine I'm missing something here, of course, and
apologize for my lack of knowledge. Thanks for your patience.

Private Sub txt_find_ppt_no_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim rs As Object

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = "" & Me.txt_find_ppt_no &"""
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "dwelling_id = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

 

[Allen Browne]

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

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = """ & Me.txt_find_ppt_no & """"
Debug.Print strWhere
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
strWhere = "dwelling_id = """ & varResult & """"
Debug.Print strWhere
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

You only need those extra quotes if ppt_no is a Text field (not a Number
field.) Presumably dwelling_id is a Text field also.

For an explanation of the quotes:
http://allenbrowne.com/casu-17.html

If it doesn't work, press Ctrl+G to open the Immediate Window, and see what
it printed there. Does the expression look right? To get an example of what
it should look like, mock up a query, use anything for criteria under ppt_no
and dwelling_id, then switch it to SQL View (View menu in query design), and
look at the WHERE clause.

 

[Guest]

Hi Allen - thanks for your response.

The intermediate windows displays:
ppt_no = "12345"

I get a run time error 2001 saying You Canceled the Previous Operation.
When I click Debug, the
varResult = DLookup("house_no", "qry_find_ppt", strWhere)
line of code is highlighted.

I wasn't sure what you meant re the query, but ran my parameter query that I
was using in some other tries. It has the house_no (I should have put this
earlier instead of the PK) and the ppt_no. Both are text fields, BTW. This
is the resulting SQL:
SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id=tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)=[Enter PPt No]));

I have also tried two other methods (have been working like crazy on this,
as under a deadline of tomorrow).

One is creating a combo box based on a parameter query. It pulls up the
ppt_no and the related house_no. If I could just figure out how to "find"
those into the corresponding fields on the main and sub form, I would be
fine. Actually, even if it would just find the house number, it would be
fine, because it would then be easy to scrolll through to find a ppt in that
house. (House numbers are unique - they aren't the standard house numbers)

The other is using a search form that open my form. I tried this and it
worked great, but only opens the subform. I really would like it to open the
main form with the subform inside of it. Have no idea if this is possible.
If so, could just modify my code to openform main and sub instead of openform
sub only.

I hope I'm making sense - a bit overleaded here and my head is spinning!
Thanks for any assistance. I really appreciate it.

 

[Allen Browne]

Dee, I guess we should ask if you can just use a search form bound to a
*query* that uses all 3 tables, instead of a form/subform? If that would
work, you bypass the whole issue of how to load the correct record into the
the main form so you get the record you want in the subform.

Error 2001 indicates that the WHERE clause is not right. Perhaps ppt_no is
not in the query? Or perhaps there are 2 tables that have a field with that
name, in which case you should include the table name, e.g.:
strWhere = "tbl_participants.ppt_no = """ & ...

Re the example query, try typing 12345 instead of [Enter PPt No] as the
critiera, and see if the SQL contains the quote marks.

You will need to get the house number so you can load the right house into
the main form. The DLookup() is the simplest way to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Hi Allen - thanks for your response.

The intermediate windows displays:
ppt_no = "12345"

I get a run time error 2001 saying You Canceled the Previous Operation.
When I click Debug, the
varResult = DLookup("house_no", "qry_find_ppt", strWhere)
line of code is highlighted.

I wasn't sure what you meant re the query, but ran my parameter query that
I
was using in some other tries. It has the house_no (I should have put
this
earlier instead of the PK) and the ppt_no. Both are text fields, BTW.
This
is the resulting SQL:
SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id=tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)=[Enter PPt No]));

I have also tried two other methods (have been working like crazy on this,
as under a deadline of tomorrow).

One is creating a combo box based on a parameter query. It pulls up the
ppt_no and the related house_no. If I could just figure out how to "find"
those into the corresponding fields on the main and sub form, I would be
fine. Actually, even if it would just find the house number, it would be
fine, because it would then be easy to scrolll through to find a ppt in
that
house. (House numbers are unique - they aren't the standard house
numbers)

The other is using a search form that open my form. I tried this and it
worked great, but only opens the subform. I really would like it to open
the
main form with the subform inside of it. Have no idea if this is
possible.
If so, could just modify my code to openform main and sub instead of
openform
sub only.

I hope I'm making sense - a bit overleaded here and my head is spinning!

Thanks for any assistance. I really appreciate it.
--
Thanks!

Dee

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

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = """ & Me.txt_find_ppt_no & """"
Debug.Print strWhere
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
strWhere = "dwelling_id = """ & varResult & """"
Debug.Print strWhere
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

You only need those extra quotes if ppt_no is a Text field (not a Number
field.) Presumably dwelling_id is a Text field also.

For an explanation of the quotes:
http://allenbrowne.com/casu-17.html

If it doesn't work, press Ctrl+G to open the Immediate Window, and see
what
it printed there. Does the expression look right? To get an example of
what
it should look like, mock up a query, use anything for criteria under
ppt_no
and dwelling_id, then switch it to SQL View (View menu in query design),
and
look at the WHERE clause.

 

[Guest]

Hi again,

Here is the SQL:

SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON tbl_dwelling.dwelling_id =
tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)="12345"));

Regarding your Search Form question... Could you explain in a bit more detail?

Thanks so much.

--
Thanks!

Dee

Dee, I guess we should ask if you can just use a search form bound to a
*query* that uses all 3 tables, instead of a form/subform? If that would
work, you bypass the whole issue of how to load the correct record into the
the main form so you get the record you want in the subform.

Error 2001 indicates that the WHERE clause is not right. Perhaps ppt_no is
not in the query? Or perhaps there are 2 tables that have a field with that
name, in which case you should include the table name, e.g.:
strWhere = "tbl_participants.ppt_no = """ & ...

Re the example query, try typing 12345 instead of [Enter PPt No] as the
critiera, and see if the SQL contains the quote marks.

You will need to get the house number so you can load the right house into
the main form. The DLookup() is the simplest way to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Hi Allen - thanks for your response.

The intermediate windows displays:
ppt_no = "12345"

I get a run time error 2001 saying You Canceled the Previous Operation.
When I click Debug, the
varResult = DLookup("house_no", "qry_find_ppt", strWhere)
line of code is highlighted.

I wasn't sure what you meant re the query, but ran my parameter query that
I
was using in some other tries. It has the house_no (I should have put
this
earlier instead of the PK) and the ppt_no. Both are text fields, BTW.
This
is the resulting SQL:
SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id=tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)=[Enter PPt No]));

I have also tried two other methods (have been working like crazy on this,
as under a deadline of tomorrow).

One is creating a combo box based on a parameter query. It pulls up the
ppt_no and the related house_no. If I could just figure out how to "find"
those into the corresponding fields on the main and sub form, I would be
fine. Actually, even if it would just find the house number, it would be
fine, because it would then be easy to scrolll through to find a ppt in
that
house. (House numbers are unique - they aren't the standard house
numbers)

The other is using a search form that open my form. I tried this and it
worked great, but only opens the subform. I really would like it to open
the
main form with the subform inside of it. Have no idea if this is
possible.
If so, could just modify my code to openform main and sub instead of
openform
sub only.

I hope I'm making sense - a bit overleaded here and my head is spinning!

Thanks for any assistance. I really appreciate it.
--
Thanks!

Dee

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

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = """ & Me.txt_find_ppt_no & """"
Debug.Print strWhere
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
strWhere = "dwelling_id = """ & varResult & """"
Debug.Print strWhere
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

You only need those extra quotes if ppt_no is a Text field (not a Number
field.) Presumably dwelling_id is a Text field also.

For an explanation of the quotes:
http://allenbrowne.com/casu-17.html

If it doesn't work, press Ctrl+G to open the Immediate Window, and see
what
it printed there. Does the expression look right? To get an example of
what
it should look like, mock up a query, use anything for criteria under
ppt_no
and dwelling_id, then switch it to SQL View (View menu in query design),
and
look at the WHERE clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I've been busy at work on this and have worked out the error messages I
was
getting from not declaring rs variable and the text field quotes (I
think!).

Now, when I input a ppt number that exists, though, it just says not
found.

I have removed the Communities main form, so am now working with 1 form
and
1 subform. (Main form is based on tbl_dwelling and contains address,
etc.,
subform is based on tbl_ppts and contains fields relating to each
individual
in the dwelling.)

Here is my code. I imagine I'm missing something here, of course, and
apologize for my lack of knowledge. Thanks for your patience.

Private Sub txt_find_ppt_no_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim rs As Object

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = "" & Me.txt_find_ppt_no &"""
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "dwelling_id = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub
--
Thanks!

Dee


:

It would not be simple to do this for the subform, since it only finds
the
records that are loaded there. As you already found, if the wrong
community
is loaded, it won't find the desired record back in the main form
(which
is
what you need in order to load the right record into the subform.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Just as an aside, Allen. Is there any way that I can modify your
FindAsYouType code so that I can use this in my subforms? I love
the
flexibility it offers in terms of searching any of the form fields.

Thanks again!
--
Thanks!

Dee


:

The form is bound to a table, I presume.

You may need to add a reference to the DAO library:
http://allenbrowne.com/ser-38.html
That reference is present by default in all versions of Access
except
2000
and 2002.

Ultimiately you will need to modify (and extend) the code to handle
your
field names etc. I've assumed these are Number fields; if they are
text
you
need extra quotes:
http://allenbrowne.com/casu-17.html

I tried the code, placing the text box in the header of the main
form.

I get a Variable not defined error message which stops at:
Me.Bookmark = rs.Bookmark
Highlighting rs.

I don't want to start fooling around with your code. Any
suggestions?

Thank you.
--
Thanks!

Dee


:

Dee, I take it you have 3 tables like this:
- Community table, with CommunityID primary key;
- House table, with HouseID primary key, and CommunityID foreign
key;
- Person table, with PersonID primary key, and HouseID foreign
key.

You interfaced this with:
- MainForm bound to Community table;
- Subform1 bound to House table;
- Subform2 (a sub-subform) bound to Person table.

Now you want to add a text box to MainForm where the user can
enter
a
PersonID number, and it will load the correct community into the
main
form,
the correct House into the subform, and choose the correct
person
in
the
sub-subform. Is that the idea?

This example shows how you might respond to unbound text box
named
txtFIndPersonID. You will need a query that uses all 3 tables,
and
shows
the
3 key fields. The example below assumes it is named Query1.

Private Sub txtFindPersonID_AfterUpdate
Dim strWhere As String
Dim varResult As Variant

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txtFindPersonID) Then
strWhere = "PersonID = " & Me.txtFindPersonID
varResult = DLookup("CommunityID", "Query1", strWhere)
If IsNull(varResult) then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "CommunityID = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
'Repeat the same logic to find the house in
the
subform.
End If
End With
End If

 

[Allen Browne]

That query looks fine. The ppt_no field does appear to be text. You can
practice in the Immediate Window until you get the DLookup() expression
working Here's the basics of DLookup():
http://allenbrowne.com/casu-07.html

If you create a query that contains fields from multiple tables, and then
create a form that uses the query as its source, you don't need a subform.
Generally this is not good for data entry, but it may be suitable for a
search form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Hi again,

Here is the SQL:

SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON tbl_dwelling.dwelling_id
=
tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)="12345"));

Regarding your Search Form question... Could you explain in a bit more
detail?

Thanks so much.

--
Thanks!

Dee

Dee, I guess we should ask if you can just use a search form bound to a
*query* that uses all 3 tables, instead of a form/subform? If that would
work, you bypass the whole issue of how to load the correct record into
the
the main form so you get the record you want in the subform.

Error 2001 indicates that the WHERE clause is not right. Perhaps ppt_no
is
not in the query? Or perhaps there are 2 tables that have a field with
that
name, in which case you should include the table name, e.g.:
strWhere = "tbl_participants.ppt_no = """ & ...

Re the example query, try typing 12345 instead of [Enter PPt No] as the
critiera, and see if the SQL contains the quote marks.

You will need to get the house number so you can load the right house
into
the main form. The DLookup() is the simplest way to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Hi Allen - thanks for your response.

The intermediate windows displays:
ppt_no = "12345"

I get a run time error 2001 saying You Canceled the Previous Operation.
When I click Debug, the
varResult = DLookup("house_no", "qry_find_ppt", strWhere)
line of code is highlighted.

I wasn't sure what you meant re the query, but ran my parameter query
that
I
was using in some other tries. It has the house_no (I should have put
this
earlier instead of the PK) and the ppt_no. Both are text fields, BTW.
This
is the resulting SQL:
SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id=tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)=[Enter PPt No]));

I have also tried two other methods (have been working like crazy on
this,
as under a deadline of tomorrow).

One is creating a combo box based on a parameter query. It pulls up
the
ppt_no and the related house_no. If I could just figure out how to
"find"
those into the corresponding fields on the main and sub form, I would
be
fine. Actually, even if it would just find the house number, it would
be
fine, because it would then be easy to scrolll through to find a ppt in
that
house. (House numbers are unique - they aren't the standard house
numbers)

The other is using a search form that open my form. I tried this and
it
worked great, but only opens the subform. I really would like it to
open
the
main form with the subform inside of it. Have no idea if this is
possible.
If so, could just modify my code to openform main and sub instead of
openform
sub only.

I hope I'm making sense - a bit overleaded here and my head is
spinning!

Thanks for any assistance. I really appreciate it.
--
Thanks!

Dee


:

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

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = """ & Me.txt_find_ppt_no & """"
Debug.Print strWhere
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
strWhere = "dwelling_id = """ & varResult & """"
Debug.Print strWhere
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

You only need those extra quotes if ppt_no is a Text field (not a
Number
field.) Presumably dwelling_id is a Text field also.

For an explanation of the quotes:
http://allenbrowne.com/casu-17.html

If it doesn't work, press Ctrl+G to open the Immediate Window, and see
what
it printed there. Does the expression look right? To get an example of
what
it should look like, mock up a query, use anything for criteria under
ppt_no
and dwelling_id, then switch it to SQL View (View menu in query
design),
and
look at the WHERE clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I've been busy at work on this and have worked out the error
messages I
was
getting from not declaring rs variable and the text field quotes (I
think!).

Now, when I input a ppt number that exists, though, it just says not
found.

I have removed the Communities main form, so am now working with 1
form
and
1 subform. (Main form is based on tbl_dwelling and contains
address,
etc.,
subform is based on tbl_ppts and contains fields relating to each
individual
in the dwelling.)

Here is my code. I imagine I'm missing something here, of course,
and
apologize for my lack of knowledge. Thanks for your patience.

Private Sub txt_find_ppt_no_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim rs As Object

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = "" & Me.txt_find_ppt_no &"""
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "dwelling_id = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub
--
Thanks!

Dee


:

It would not be simple to do this for the subform, since it only
finds
the
records that are loaded there. As you already found, if the wrong
community
is loaded, it won't find the desired record back in the main form
(which
is
what you need in order to load the right record into the subform.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Just as an aside, Allen. Is there any way that I can modify your
FindAsYouType code so that I can use this in my subforms? I love
the
flexibility it offers in terms of searching any of the form
fields.

Thanks again!
--
Thanks!

Dee


:

The form is bound to a table, I presume.

You may need to add a reference to the DAO library:
http://allenbrowne.com/ser-38.html
That reference is present by default in all versions of Access
except
2000
and 2002.

Ultimiately you will need to modify (and extend) the code to
handle
your
field names etc. I've assumed these are Number fields; if they
are
text
you
need extra quotes:
http://allenbrowne.com/casu-17.html

I tried the code, placing the text box in the header of the
main
form.

I get a Variable not defined error message which stops at:
Me.Bookmark = rs.Bookmark
Highlighting rs.

I don't want to start fooling around with your code. Any
suggestions?

Thank you.
--
Thanks!

Dee


:

Dee, I take it you have 3 tables like this:
- Community table, with CommunityID primary key;
- House table, with HouseID primary key, and CommunityID
foreign
key;
- Person table, with PersonID primary key, and HouseID
foreign
key.

You interfaced this with:
- MainForm bound to Community table;
- Subform1 bound to House table;
- Subform2 (a sub-subform) bound to Person table.

Now you want to add a text box to MainForm where the user can
enter
a
PersonID number, and it will load the correct community into
the
main
form,
the correct House into the subform, and choose the correct
person
in
the
sub-subform. Is that the idea?

This example shows how you might respond to unbound text box
named
txtFIndPersonID. You will need a query that uses all 3
tables,
and
shows
the
3 key fields. The example below assumes it is named Query1.

Private Sub txtFindPersonID_AfterUpdate
Dim strWhere As String
Dim varResult As Variant

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txtFindPersonID) Then
strWhere = "PersonID = " & Me.txtFindPersonID
varResult = DLookup("CommunityID", "Query1",
strWhere)
If IsNull(varResult) then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "CommunityID = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
'Repeat the same logic to find the house
in
the
subform.
End If
End With
End If

 

[Guest]

Hi again,

I guess the search query option is out because I really need to be able to
input data in the forms.

I'm really at the end of my rope with this. I will keep trying, but am
running out of options and time. I would really need to somehow have the
user be able to search for a ppt_no on either the main or sub form and then
have the related house_no be pulled up in the main form...

I will keep trying the dLookup(), I guess.

Thanks.
--
Thanks!

Dee

That query looks fine. The ppt_no field does appear to be text. You can
practice in the Immediate Window until you get the DLookup() expression
working Here's the basics of DLookup():
http://allenbrowne.com/casu-07.html

If you create a query that contains fields from multiple tables, and then
create a form that uses the query as its source, you don't need a subform.
Generally this is not good for data entry, but it may be suitable for a
search form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Hi again,

Here is the SQL:

SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON tbl_dwelling.dwelling_id
=
tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)="12345"));

Regarding your Search Form question... Could you explain in a bit more
detail?

Thanks so much.

--
Thanks!

Dee

Dee, I guess we should ask if you can just use a search form bound to a
*query* that uses all 3 tables, instead of a form/subform? If that would
work, you bypass the whole issue of how to load the correct record into
the
the main form so you get the record you want in the subform.

Error 2001 indicates that the WHERE clause is not right. Perhaps ppt_no
is
not in the query? Or perhaps there are 2 tables that have a field with
that
name, in which case you should include the table name, e.g.:
strWhere = "tbl_participants.ppt_no = """ & ...

Re the example query, try typing 12345 instead of [Enter PPt No] as the
critiera, and see if the SQL contains the quote marks.

You will need to get the house number so you can load the right house
into
the main form. The DLookup() is the simplest way to do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Hi Allen - thanks for your response.

The intermediate windows displays:
ppt_no = "12345"

I get a run time error 2001 saying You Canceled the Previous Operation.
When I click Debug, the
varResult = DLookup("house_no", "qry_find_ppt", strWhere)
line of code is highlighted.

I wasn't sure what you meant re the query, but ran my parameter query
that
I
was using in some other tries. It has the house_no (I should have put
this
earlier instead of the PK) and the ppt_no. Both are text fields, BTW.
This
is the resulting SQL:
SELECT tbl_dwelling.house_no, tbl_participants.ppt_no
FROM tbl_dwelling INNER JOIN tbl_participants ON
tbl_dwelling.dwelling_id=tbl_participants.dwelling_id
WHERE (((tbl_participants.ppt_no)=[Enter PPt No]));

I have also tried two other methods (have been working like crazy on
this,
as under a deadline of tomorrow).

One is creating a combo box based on a parameter query. It pulls up
the
ppt_no and the related house_no. If I could just figure out how to
"find"
those into the corresponding fields on the main and sub form, I would
be
fine. Actually, even if it would just find the house number, it would
be
fine, because it would then be easy to scrolll through to find a ppt in
that
house. (House numbers are unique - they aren't the standard house
numbers)

The other is using a search form that open my form. I tried this and
it
worked great, but only opens the subform. I really would like it to
open
the
main form with the subform inside of it. Have no idea if this is
possible.
If so, could just modify my code to openform main and sub instead of
openform
sub only.

I hope I'm making sense - a bit overleaded here and my head is
spinning!

Thanks for any assistance. I really appreciate it.
--
Thanks!

Dee


:

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

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = """ & Me.txt_find_ppt_no & """"
Debug.Print strWhere
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
strWhere = "dwelling_id = """ & varResult & """"
Debug.Print strWhere
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

You only need those extra quotes if ppt_no is a Text field (not a
Number
field.) Presumably dwelling_id is a Text field also.

For an explanation of the quotes:
http://allenbrowne.com/casu-17.html

If it doesn't work, press Ctrl+G to open the Immediate Window, and see
what
it printed there. Does the expression look right? To get an example of
what
it should look like, mock up a query, use anything for criteria under
ppt_no
and dwelling_id, then switch it to SQL View (View menu in query
design),
and
look at the WHERE clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

I've been busy at work on this and have worked out the error
messages I
was
getting from not declaring rs variable and the text field quotes (I
think!).

Now, when I input a ppt number that exists, though, it just says not
found.

I have removed the Communities main form, so am now working with 1
form
and
1 subform. (Main form is based on tbl_dwelling and contains
address,
etc.,
subform is based on tbl_ppts and contains fields relating to each
individual
in the dwelling.)

Here is my code. I imagine I'm missing something here, of course,
and
apologize for my lack of knowledge. Thanks for your patience.

Private Sub txt_find_ppt_no_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim rs As Object

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.txt_find_ppt_no) Then
strWhere = "ppt_no = "" & Me.txt_find_ppt_no &"""
varResult = DLookup("dwelling_id", "qry_find_ppt", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
.FindFirst "dwelling_id = " & varResult
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub
--
Thanks!

Dee


:

It would not be simple to do this for the subform, since it only
finds
the
records that are loaded there. As you already found, if the wrong
community
is loaded, it won't find the desired record back in the main form
(which
is
what you need in order to load the right record into the subform.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Just as an aside, Allen. Is there any way that I can modify your
FindAsYouType code so that I can use this in my subforms? I love
the
flexibility it offers in terms of searching any of the form
fields.

Thanks again!
--
Thanks!

Dee


:

The form is bound to a table, I presume.

You may need to add a reference to the DAO library:
http://allenbrowne.com/ser-38.html
That reference is present by default in all versions of Access
except
2000
and 2002.

Ultimiately you will need to modify (and extend) the code to
handle
your
field names etc. I've assumed these are Number fields; if they
are
text
you
need extra quotes:
http://allenbrowne.com/casu-17.html

I tried the code, placing the text box in the header of the
main
form.

I get a Variable not defined error message which stops at:
Me.Bookmark = rs.Bookmark
Highlighting rs.

I don't want to start fooling around with your code. Any
suggestions?