Allen Browne Search Code

[Allen Browne]

Dee, you may need to take this one step at a time.

The basic steps are:
a) Find out which Household you need to load into the main form so that it
has the right persons in the subform. The DLookup() will give you the house
number.

b) Now you have the house number, FindFirst in the RecordsetClone of the
form to see if it exists.

c) Once you have found it in the clone set (testing NoMatch), set the form's
Bookmark to that of the clone set (which makes that house the current one in
the form.)

d) Once the right house is showing in the main form, the person will be in
the subform.


Sometimes you have a many-to-many relation to solve. Here's a completely
different solution:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

There's usually several possible approaches: Access is very powerful, but it
can take a bit of time to investigate/develop the solutions.

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

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

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.

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.

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?

 

[Guest]

Hi there,

Well, after all of the hair-pulling and trying to figure out how to do this,
I *think* I have a solution that's embarrassingly easy (which is why I wonder
if it's correct).

I based my main form on a query instead of tbl_dwellings. The query
includes all fields from tbl_dwellings, plus the ppt_no from the
tbl_participants.

I created a regular search combo box using the ppt_no field. It seems to
work fine. I can type the number in or select it. If no number is found, it
just doesn't move to another record. It would be nice to have a message
saying No Record Found, but at this point, I'm just happy to be able to do
this.

If I'm missing something that may cause a problem, please let me know.
Otherwise, this would work.

I thank you so very, very much for your help and patience. I've actually
learned a lot!


--
Thanks!

Dee

Dee, you may need to take this one step at a time.

The basic steps are:
a) Find out which Household you need to load into the main form so that it
has the right persons in the subform. The DLookup() will give you the house
number.

b) Now you have the house number, FindFirst in the RecordsetClone of the
form to see if it exists.

c) Once you have found it in the clone set (testing NoMatch), set the form's
Bookmark to that of the clone set (which makes that house the current one in
the form.)

d) Once the right house is showing in the main form, the person will be in
the subform.


Sometimes you have a many-to-many relation to solve. Here's a completely
different solution:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

There's usually several possible approaches: Access is very powerful, but it
can take a bit of time to investigate/develop the solutions.

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

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

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.

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.

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

 

[Allen Browne]

Good: that's essentially what I was suggesting as your search form.

Not great for entering new dwelling and new people etc, but good for
searching.

 

[Guest]

Hi again Allen,

Well, it seems to work fine for one form/subform. There is a form that
contains questionnaire ID/name and subform that contains questions. In this
one, I added:
Private Sub Combo69_Enter()
Me![Combo69].Requery
End Sub

So all seems well.

However, on the form/subform we have been discussing, I seem to have to
close the form and re-open it when I add a new dwellling and participant(s)
for that dwelling if the search combo box "repopulates" the new ppt_no. It
shows it, but doesn't respond when I select it as search criterion.

I'm sure this is just a small glitch. If you can point me in the right
direction to update the data, I'd really appreciate it. Perhaps after
working for well over12 hours on this, I'm getting a brain freeze!

Thanks so very much!
--
Thanks!

Dee

Good: that's essentially what I was suggesting as your search form.

Not great for entering new dwelling and new people etc, but good for
searching.

 

[Allen Browne]

You may be able to use the Afterupdate event procedure of the *form* to
Requery the combo.

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

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

Hi again Allen,

Well, it seems to work fine for one form/subform. There is a form that
contains questionnaire ID/name and subform that contains questions. In
this
one, I added:
Private Sub Combo69_Enter()
Me![Combo69].Requery
End Sub

So all seems well.

However, on the form/subform we have been discussing, I seem to have to
close the form and re-open it when I add a new dwellling and
participant(s)
for that dwelling if the search combo box "repopulates" the new ppt_no.
It
shows it, but doesn't respond when I select it as search criterion.

I'm sure this is just a small glitch. If you can point me in the right
direction to update the data, I'd really appreciate it. Perhaps after
working for well over12 hours on this, I'm getting a brain freeze!

Thanks so very much!