Searching and displaying records in another form

[Guest]

I am pretty new to this, and have been driving myself crazy trying to figure
this out. Any help is tremendously appreciated! I am trying to make what I
thought was a simple recipe database. I have a main table and connected form
for each recipe. I am trying to create a second form that has various search
options for different fields on the Recipes form. They are: MealType, Chef,
etc. I have those working pretty well by just the command button wizard
(except whenever I change something in the search form, it seems to change
the record in the Recipe form, I'm not sure why but I think I fixed it by
creating a new table that I now used as the record source for the Search
form). What I cannot seem to do is a search for the field Calories. I would
like to input a number, and have all the records less than or equal to that
value come up. I cannot get it. Please, can someone help? Thank you!
Also, is there a way I can add a command button to the Search form that will
search for all the fields on the Search form (or any that are filled in)?
Thank you.
Jen

 

[Marshall Barton]

I am pretty new to this, and have been driving myself crazy trying to figure
this out. Any help is tremendously appreciated! I am trying to make what I
thought was a simple recipe database. I have a main table and connected form
for each recipe. I am trying to create a second form that has various search
options for different fields on the Recipes form. They are: MealType, Chef,
etc. I have those working pretty well by just the command button wizard
(except whenever I change something in the search form, it seems to change
the record in the Recipe form, I'm not sure why but I think I fixed it by
creating a new table that I now used as the record source for the Search
form). What I cannot seem to do is a search for the field Calories. I would
like to input a number, and have all the records less than or equal to that
value come up. I cannot get it. Please, can someone help? Thank you!
Also, is there a way I can add a command button to the Search form that will
search for all the fields on the Search form (or any that are filled in)?

A search form should not be bound to any table (because you
do not want to save the search criteria values). I think
the answer to your specific question is just to remove
whatever you have in the search form's Record Source
property.

You will probably want to make the searching more versatile
than the wizards can handle, so I suggest that you take a
look at the example at http://allenbrowne.com/ser-62.html

 

[Guest]

A search form should not be bound to any table (because you

do not want to save the search criteria values). I think
the answer to your specific question is just to remove
whatever you have in the search form's Record Source
property.

You will probably want to make the searching more versatile
than the wizards can handle, so I suggest that you take a
look at the example at http://allenbrowne.com/ser-62.html

Thanks for the help, and the quick response. Just removing the record
source was a huge help. I had a difficult time getting the Calories search
to work, even with the link you provided, which was very helpful. After
going over the site many times, I was finally able to get it working. Thank
you.

I'm still unable to search through all the fields on the form (or any
combination). I've tried going over the copy on the web site, and others
I've found (including this one), but no use. The allenbrowne site has that
total search open differently than I'm looking for. I want it to open each
record in form view that qualifies under the search, not merely a list of
records on the same search page. Does anyone know how to help with this?
What I've gotten down to is:

Dim stDocName As String
Dim stLinkCriteria As String
Dim strWhere As String

stDocName = "Recipes"

If Not IsNull(Me.MealType) Then
stLinkCriteria = stLinkCriteria & "([MealType] = """ & Me.MealType &
""") AND "
End If

And I go on through each search parameter, according to it's format, ending
with:

If Not IsNull(Me.Calories_Per_Serving) Then
stLinkCriteria = stLinkCriteria & "([Calories Per Serving] <= " &
Me.Calories_Per_Serving & ") "

DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
MsgBox "No criteria selected", vbInformation

End If

I've just replaced the strWhere with the stLinkCriteria because the strWhere
wasn't working, so I thought I'd try it. No good. Please help. Thank you!

 

[Guest]

I'm still lost. Here's what I've come up with so far... the code is below.

The CaloriesPerHour part works alone and with Recipes &/or Ingredients.
However, recipes nor ingreds works without calories. If I try it, say for
chicken, I get Syntax error (missing operator) in query expression '([Recipe]
Like "*chicken*") And'.

I get the same message with all the others, too. But if I try the others
with CaloriesPerHour, I get prompted to 'enter parameter value.'

When I changed Recipe to:
stLinkCriteria = stLinkCriteria & "[Recipe]=" & "'" & Me![Recipe] & "'" And ""
I received a type mismatch error, even when paired with Calories.

I have no idea what more to try. Please help. I'm sorry this post is so
long. I thought it would be easier to help if you knew it all. Thank you
very much.
Jen

Private Sub SearchAll_Click()
On Error GoTo Err_SearchAll_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strWhere As String
Dim strWhere1 As Long

stDocName = "Recipes"
strWhere1 = 0

If Not IsNull(Me.Recipe) Then
stLinkCriteria = stLinkCriteria & "([Recipe] Like ""*" & Me![Recipe]
& "*"") And "
End If

If Not IsNull(Me.MealType) Then
stLinkCriteria = stLinkCriteria & "([MealType] = """ & Me.MealType &
""") AND "
End If

If Not IsNull(Me.From_the_Kitchen_of) Then
stLinkCriteria = stLinkCriteria & "([From_the_Kitchen_of] = """ &
Me.From_the_Kitchen_of & """) AND "
End If

If Not IsNull(Me.Good_for_Parties) Then
stLinkCriteria = stLinkCriteria & "([Good_for_Parties] = """ &
Me.Good_for_Parties & """) AND "
End If

If Not IsNull(Me.Ingredients) Then
stLinkCriteria = stLinkCriteria & "([Ingredients] Like ""*" &
Me![Ingredients] & "*"") And "
End If

If Not IsNull(Me.Calories_Per_Serving) Then
stLinkCriteria = stLinkCriteria & "([Calories Per Serving] <= " &
Me.Calories_Per_Serving & ") "
End If

If IsNull(Me.Recipe & Me.MealType & Me.From_the_Kitchen_of &
Me.Good_for_Parties & Me.Ingredients & Me.Calories_Per_Serving) Then
MsgBox "No criteria selected", vbInformation

Exit Sub

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SearchAll_Click:
Exit Sub

Err_SearchAll_Click:
MsgBox Err.Description
Resume Exit_SearchAll_Click

End Sub

 

[Marshall Barton]

I'm still lost. Here's what I've come up with so far... the code is below.

The CaloriesPerHour part works alone and with Recipes &/or Ingredients.
However, recipes nor ingreds works without calories. If I try it, say for
chicken, I get Syntax error (missing operator) in query expression '([Recipe]
Like "*chicken*") And'.

I get the same message with all the others, too. But if I try the others
with CaloriesPerHour, I get prompted to 'enter parameter value.'

When I changed Recipe to:
stLinkCriteria = stLinkCriteria & "[Recipe]=" & "'" & Me![Recipe] & "'" And ""
I received a type mismatch error, even when paired with Calories.

I have no idea what more to try. Please help. I'm sorry this post is so
long. I thought it would be easier to help if you knew it all. Thank you
very much.
Jen

Private Sub SearchAll_Click()
On Error GoTo Err_SearchAll_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strWhere As String
Dim strWhere1 As Long

stDocName = "Recipes"
strWhere1 = 0

If Not IsNull(Me.Recipe) Then
stLinkCriteria = stLinkCriteria & "([Recipe] Like ""*" & Me![Recipe]
& "*"") And "
End If

If Not IsNull(Me.MealType) Then
stLinkCriteria = stLinkCriteria & "([MealType] = """ & Me.MealType &
""") AND "
End If

If Not IsNull(Me.From_the_Kitchen_of) Then
stLinkCriteria = stLinkCriteria & "([From_the_Kitchen_of] = """ &
Me.From_the_Kitchen_of & """) AND "
End If

If Not IsNull(Me.Good_for_Parties) Then
stLinkCriteria = stLinkCriteria & "([Good_for_Parties] = """ &
Me.Good_for_Parties & """) AND "
End If

If Not IsNull(Me.Ingredients) Then
stLinkCriteria = stLinkCriteria & "([Ingredients] Like ""*" &
Me![Ingredients] & "*"") And "
End If

If Not IsNull(Me.Calories_Per_Serving) Then
stLinkCriteria = stLinkCriteria & "([Calories Per Serving] <= " &
Me.Calories_Per_Serving & ") "
End If

If IsNull(Me.Recipe & Me.MealType & Me.From_the_Kitchen_of &
Me.Good_for_Parties & Me.Ingredients & Me.Calories_Per_Serving) Then
MsgBox "No criteria selected", vbInformation

Exit Sub

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SearchAll_Click:
Exit Sub

Err_SearchAll_Click:
MsgBox Err.Description
Resume Exit_SearchAll_Click

End Sub

Instead of:

If IsNull(Me.Recipe & Me.MealType _
& Me.From_the_Kitchen_of _
& Me.Good_for_Parties & Me.Ingredients _
& Me.Calories_Per_Serving) Then
MsgBox "No criteria selected", vbInformation
Exit Sub
End If

You should have:

If Len(stLinkCriteria) = 0 Then
MsgBox "No criteria selected", vbInformation
Exit Sub
Else 'trim trailing AND
stLinkCriteria = Left(stLinkCriteria _
, Len(stLinkCriteria) - 5
End If

The type mismatch error is probably coming from having
quotes around a numeric type field (in the table). If the
type of a field in the table is Number, then the criteria
for that field should not have quotes around it. Instead, it
should be the way you have it for the calories field.

 

[Guest]

Instead of:

If IsNull(Me.Recipe & Me.MealType _
& Me.From_the_Kitchen_of _
& Me.Good_for_Parties & Me.Ingredients _
& Me.Calories_Per_Serving) Then
MsgBox "No criteria selected", vbInformation
Exit Sub
End If

You should have:

If Len(stLinkCriteria) = 0 Then
MsgBox "No criteria selected", vbInformation
Exit Sub
Else 'trim trailing AND
stLinkCriteria = Left(stLinkCriteria _
, Len(stLinkCriteria) - 5
End If

The type mismatch error is probably coming from having
quotes around a numeric type field (in the table). If the
type of a field in the table is Number, then the criteria
for that field should not have quotes around it. Instead, it
should be the way you have it for the calories field.

For the no criteria message, your advice sounds much simpler. However, that
was the only part of the code that I had working properly, so I'm a bit
nervous changing it. As for the mismatch error, the only field that is a
number is the calories field. All the others are text. Did you see any
other reason I am having a difficult time with this? Any idea how can I fix
it? Thank you.
Jen

Also, I know it's not related to the above, but what are the ' used for and
how? I can't even find them listed in my help file. Thanks.

 

[Guest]

I forgot...

What operator am I missing in each of the lines, besides the calories line?
I can't figure that out.

If I try it, say for
chicken, I get Syntax error (missing operator) in query expression '([Recipe]
Like "*chicken*") And'.

And why would I get prompted to enter parameter value when there is
something, say chicken, listed.

But if I try the others
with CaloriesPerHour, I get prompted to 'enter parameter value.'

Thank you so much!
Jen

 

[Marshall Barton]

For the no criteria message, your advice sounds much simpler. However, that
was the only part of the code that I had working properly, so I'm a bit
nervous changing it.

Give it a try and see if it works.

As for the mismatch error, the only field that is a
number is the calories field. All the others are text. Did you see any
other reason I am having a difficult time with this? Any idea how can I fix
it?

I think the missing operator error is because of the extra
AND at the end of the criteria. See the Else part that I
added to the last If block.

To try to chase down the type mismatch error, add a another
line of code for debugging purposes:

Debug.Print stLinkCriteria

right before the OpenForm line. It should help if we can
see the result of all the code that constructs the criteria.

Also, I know it's not related to the above, but what are the ' used for and
how?

The rule about putting a quote inside quotes is to use two
quotes. E.g. "this is a "" mark" will result in the
string:
this is a " mark

Unlike in VBA code, you can use either " or ' in an SQL
statement to enclose a text string. Whichever one you use
to enclose the whole string must be doubled up inside the
string. E.g. You can use either
"Fred said, ""That's O'Brian's book""."
or
'Fred said, "That''s O''Brian''s book".'
to get the string
Fred said, "That's O'Brian's book".

This is a very confusing subject (especially where you are
using VBA code to create SQL), so don't beat yourself up
over it.

 

[Guest]

Thanks for all the help. I'm still getting a little messed up with the
quotes, but I think I'm beginning to get the hang of it (with lots of double
checking... guess it's more my typing).

With the end code you suggested:

<If Len(stLinkCriteria) = 0 Then
<MsgBox "No criteria selected", vbInformation
<Exit Sub
<Else 'trim trailing AND
<stLinkCriteria = Left(stLinkCriteria _
<, Len(stLinkCriteria) - 5
<End If

What is Len?
And what does the following do? (and how)
<Else 'trim trailing AND
<stLinkCriteria = Left(stLinkCriteria _
<, Len(stLinkCriteria) - 5

Can you also please tell me how the following works:
<Debug.Print stLinkCriteria
I really have no clue how to work the debugging, and the help on access
isn't showing me anything.

I actually got the form working late yesterday, before you replied again.
Yours sounds better, and probably more efficient, but I don't want to mess
with fate and change what I have. If it's not broken.... (except with the
above exceptions after I know how they work)

I don't remember which site I found the AND at the end of lines, but I did
take them out. I couldn't see how they worked.

I thought I'd post what I ended up with, in case anyone else has this
problem and sees this post.

Thanks again. And I love this site!
Jen

Private Sub SearchAll_Click()
On Error GoTo Err_SearchAll_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Recipes"
stLinkCriteria = ""

If Not IsNull(Me![MealType]) Then
stLinkCriteria = "[Meal Type]=" & "'" & Me![MealType] & "'"
End If

If Not IsNull(Me![Favorites]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[Favorites]=" & "'" & Me![Favorites] & "'"
Else
stLinkCriteria = stLinkCriteria & " AND " & "[Favorites]=" & "'"
& Me![Favorites] & "'"
End If
End If

If Not IsNull(Me![From the Kitchen of]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[From the Kitchen of] Like ""*" & Me![From the
Kitchen of] & "*"")"
Else
stLinkCriteria = stLinkCriteria & " AND " & " ([From the Kitchen
of] Like ""*" & Me![From the Kitchen of] & "*"")"
End If
End If

If Not IsNull(Me![Good for Parties]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[Good for Parties] = " & "'" & Me![Good for
Parties] & "'"
Else
stLinkCriteria = stLinkCriteria & " AND " & "[Good for
Parties]=" & "'" & Me![Good for Parties] & "'"
End If
End If

If Not IsNull(Me![Recipe]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "([Recipe] Like ""*" & Me![Recipe] & "*"")"
Else
stLinkCriteria = stLinkCriteria & " AND " & "([Recipe] Like ""*"
& Me![Recipe] & "*"")"
End If
End If

If Not IsNull(Me.Calories_Per_Serving) Then
If stLinkCriteria = "" Then
stLinkCriteria = "([Calories Per Serving] <= " &
Format(Me.Calories_Per_Serving) & ")"
Else
stLinkCriteria = stLinkCriteria & " AND " & "([Calories Per
Serving] <= " & Format(Me.Calories_Per_Serving) & ")"
End If
End If

If Not IsNull(Me![Ingredients]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "([Ingredients] Like ""*" & Me![Ingredients] &
"*"" )"
Else
stLinkCriteria = stLinkCriteria & " AND " & "([Ingredients] Like
""*" & Me![Ingredients] & "*"")"
End If
End If

If stLinkCriteria = "" Then
MsgBox "No criteria selected", vbInformation

Exit Sub

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SearchAll_Click:
Exit Sub

Err_SearchAll_Click:
MsgBox Err.Description
Resume Exit_SearchAll_Click

End Sub

 

[Marshall Barton]

Thanks for all the help. I'm still getting a little messed up with the
quotes, but I think I'm beginning to get the hang of it (with lots of double
checking... guess it's more my typing).

With the end code you suggested:

<If Len(stLinkCriteria) = 0 Then
<MsgBox "No criteria selected", vbInformation
<Exit Sub
<Else 'trim trailing AND
<stLinkCriteria = Left(stLinkCriteria _
<, Len(stLinkCriteria) - 5
<End If

What is Len?

When you are looking at code in the VB Editor window, click
on Help and search for the term you want to learn more
about. In this case, Len is a function that returns the
length of its argument string.

The line:
If Len(stLinkCriteria) = 0 Then
is just checking if the criteria string is empty (i.e.
nothing was specified).

And what does the following do? (and how)
<Else 'trim trailing AND
<stLinkCriteria = Left(stLinkCriteria _
<, Len(stLinkCriteria) - 5

Your original code added each criteria with a trailing AND,
which means that the stLinkCriteria string will have an
extra AND at the end. The line of code in the Else clause
is setting stLinkCriteria to itself except the Left function
(check it in VBA Help) is clipping off the last 5 characters
(i.e. a space, the letters "AND", and the trailing space).

Can you also please tell me how the following works:
<Debug.Print stLinkCriteria
I really have no clue how to work the debugging, and the help on access
isn't showing me anything.

That will display the argument string, stLinkCriteria to the
Immediate/Debug window (display the Immediate window by
typing Ctrl+G). You can then view the stLinkCriteria strin
as it will be used in the OpenForm method. Hopefully one
of us will be able to see what's wrong with it.

I actually got the form working late yesterday, before you replied again.
Yours sounds better, and probably more efficient, but I don't want to mess
with fate and change what I have. If it's not broken.... (except with the
above exceptions after I know how they work)

Actually, without the ANDs, it will work if you only specify
one thing to search for. It will fail if you try to search
for two things such as calories and an ingrediant.

I don't remember which site I found the AND at the end of lines, but I did
take them out. I couldn't see how they worked.

I suggested Allen Browne's web site in my first reply and
his example uses code much like what you have.

To get all the capabilities you originally wanted, put the
ANDs back and then we'll work out whatever stumbling blocks
you run into.

 

[Guest]

Thanks again. The below makes perfect sense to me now. I think I was
forgeting that the ' at the beginning (with none at the end) is just for
explanation, not code. That is right, isn't it? If not, I'm worse off than
I thought!

<If Len(stLinkCriteria) = 0 Then
<MsgBox "No criteria selected", vbInformation
<Exit Sub
<Else 'trim trailing AND
<stLinkCriteria = Left(stLinkCriteria _
<, Len(stLinkCriteria) - 5
<End If

Your info on debugging, and the line Debug.Print stLinkCriteria is a
godsend! For some reason the help in my VB editor does not work. I keep
getting a message sayine it is missing... have no idea why as I haven't used
it in ages. Thanks to you, I don't need it as much!

<Actually, without the ANDs, it will work if you only specify
<one thing to search for. It will fail if you try to search
<for two things such as calories and an ingrediant.

I was so confident before, now I'm worried. I don't know why, but it does
work exactly as I wanted. I can search for multiple criteria with the code I
have. I can search from one to all, and any in between. I was having one
problem with the From the Kitchen of, but with your debug line, I fixed it
easily... missing one (. When I read over the code, it seems like it should
work, so I'm not sure why you don't think it would. The way I read it:

If Not IsNull(Me![Favorites]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[Favorites]=" & "'" & Me![Favorites] & "'"
Else
stLinkCriteria = stLinkCriteria & " AND " & "[Favorites]=" & "'" &
Me![Favorites] & "'"
End If
End If

Doesn't this say, if stLinkCriteria is not blank, then it equals the current
stLinkCriteria and the Favorites? Since there is an AND between them, why
wouldn't that be good enough?

Please let me know, as I don't want to start having problems down the line.

Thank you again.
Jen

 

[Marshall Barton]

Thanks again. The below makes perfect sense to me now. I think I was
forgeting that the ' at the beginning (with none at the end) is just for
explanation, not code. That is right, isn't it? If not, I'm worse off than
I thought!

<If Len(stLinkCriteria) = 0 Then
<MsgBox "No criteria selected", vbInformation
<Exit Sub
<Else 'trim trailing AND
<stLinkCriteria = Left(stLinkCriteria _
<, Len(stLinkCriteria) - 5
<End If

Your info on debugging, and the line Debug.Print stLinkCriteria is a
godsend! For some reason the help in my VB editor does not work. I keep
getting a message sayine it is missing... have no idea why as I haven't used
it in ages. Thanks to you, I don't need it as much!

<Actually, without the ANDs, it will work if you only specify
<one thing to search for. It will fail if you try to search
<for two things such as calories and an ingrediant.

I was so confident before, now I'm worried. I don't know why, but it does
work exactly as I wanted. I can search for multiple criteria with the code I
have. I can search from one to all, and any in between. I was having one
problem with the From the Kitchen of, but with your debug line, I fixed it
easily... missing one (. When I read over the code, it seems like it should
work, so I'm not sure why you don't think it would. The way I read it:

If Not IsNull(Me![Favorites]) Then
If stLinkCriteria = "" Then
stLinkCriteria = "[Favorites]=" & "'" & Me![Favorites] & "'"
Else
stLinkCriteria = stLinkCriteria & " AND " & "[Favorites]=" & "'" &
Me![Favorites] & "'"
End If
End If

Doesn't this say, if stLinkCriteria is not blank, then it equals the current
stLinkCriteria and the Favorites? Since there is an AND between them, why
wouldn't that be good enough?

Yes, that is just fine. It takes four more lines for each
criteria instead of the two lines I was suggesting, but is a
valid alternative to what we were working with before (and
without the training AND the Else block I said you needed
should not be used).

You really need to fix your Help file problem. Try using
repair or reinstall Access using Control Panel - Add/Remove
programs.