Using button in Access Form to search text field

[Erica]

Hello,
I am trying to create a database for a book collection. I have a form
based on the table containing the book information that I would like
to use for circulation, so the librarian will need to be able to
search by ResourceID, author, and title. I successfully created a
text box and search button to search the ResourceID, which is an
AutoNumber field, with the following code ( I am completely unfamiliar
with Visual Basic, and am just working with code I copied from the
web):

Private Sub Command64_Click()
If IsNull(Text60) = False Then
Me.Recordset.FindFirst "ResourceID=" & Text60
Me!Text60 = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!Text60 = Null
End If
End If
End Sub

I tried to create the same code for the Title field, but it doesn't
work. I substituted the correct button name (Command70) and text box
name (Text68), as well as the field name ("Title="), but I get a
syntax error saying that I'm missing an operator. The Title field is
a text field. When I tried the same code for a Yes/No field, it
worked fine. Does this code not work with text fields?

Ideally I would like the librarian to be able to enter just a portion
of the title rather than the exact value, so rather than = I want to
use LIKE or something of the sort, but if I can't even get the = to
work I don't know how I'd go about that.

Any help would be sincerely appreciated!

 

[Marshall Barton]

Hello,
I am trying to create a database for a book collection. I have a form
based on the table containing the book information that I would like
to use for circulation, so the librarian will need to be able to
search by ResourceID, author, and title. I successfully created a
text box and search button to search the ResourceID, which is an
AutoNumber field, with the following code ( I am completely unfamiliar
with Visual Basic, and am just working with code I copied from the
web):

Private Sub Command64_Click()
If IsNull(Text60) = False Then
Me.Recordset.FindFirst "ResourceID=" & Text60
Me!Text60 = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!Text60 = Null
End If
End If
End Sub

I tried to create the same code for the Title field, but it doesn't
work. I substituted the correct button name (Command70) and text box
name (Text68), as well as the field name ("Title="), but I get a
syntax error saying that I'm missing an operator. The Title field is
a text field. When I tried the same code for a Yes/No field, it
worked fine. Does this code not work with text fields?

Ideally I would like the librarian to be able to enter just a portion
of the title rather than the exact value, so rather than = I want to
use LIKE or something of the sort, but if I can't even get the = to
work I don't know how I'd go about that.

Text values must be quoted so Access can figure out where it
starts and ends.

Me.Recordset.FindFirst "Title='" & Text60 * "' "

 

[Erica]

Hello,
I am trying to create a database for a book collection.  I have a form
based on the table containing the book information that I would like
to use for circulation, so the librarian will need to be able to
search by ResourceID, author, and title.  I successfully created a
text box and search button to search the ResourceID, which is an
AutoNumber field, with the following code ( I am completely unfamiliar
with Visual Basic, and am just working with code I copied from the
web):

Private Sub Command64_Click()
  If IsNull(Text60) = False Then
     Me.Recordset.FindFirst "ResourceID=" & Text60
     Me!Text60 = Null
     If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
           Me!Text60 = Null
     End If
  End If
End Sub

I tried to create the same code for the Title field, but it doesn't
work.  I substituted the correct button name (Command70) and text box
name (Text68), as well as the field name ("Title="), but I get a
syntax error saying that I'm missing an operator.  The Title field is
a text field.  When I tried the same code for a Yes/No field, it
worked fine.  Does this code not work with text fields?

Ideally I would like the librarian to be able to enter just a portion
of the title rather than the exact value, so rather than = I want to
use LIKE or something of the sort, but if I can't even get the = to
work I don't know how I'd go about that.

Text values must be quoted so Access can figure out where it
starts and ends.

Me.Recordset.FindFirst "Title='" & Text60 * "' "

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks for your quick response!
When I did that, I got this error:
Type mismatch (Error 13)
I have no idea what this means. When I click "debug" Access
highlights the line of code that has the problem, but that's where the
help ends. I sort of feel like throwing my computer out the window

Thanks again,
Erica

 

[Marshall Barton]

When I did that, I got this error:
Type mismatch (Error 13)
I have no idea what this means. When I click "debug" Access
highlights the line of code that has the problem, but that's where the
help ends.

Which line was highlighted. If it was the Me.Filter line,
then it mean that Title is not a text field. Do you have a
separate table with all the titles in it and the title field
as a Lookup field?

 

[Erica]

When I did that, I got this error:
Type mismatch (Error 13)
I have no idea what this means.  When I click "debug" Access
highlights the line of code that has the problem, but that's where the
help ends.

Which line was highlighted.  If it was the Me.Filter line,
then it mean that Title is not a text field.  Do you have a
separate table with all the titles in it and the title field
as a Lookup field?

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

I don't actually have a Me.Filter line, but rather a
Me.RecordSet.FindFirst line. Is that my first problem?
Title is definitely a text field, and it doesn't come from a separate
table, so I don't know why it won't work. My Author field is a lookup
field with its own table though, and I figured that would be a problem
so I tried the Title field instead. Is there a way to make this work
for a Lookup field as well?

Thanks,
Erica

 

[Marshall Barton]

I don't actually have a Me.Filter line, but rather a
Me.RecordSet.FindFirst line. Is that my first problem?
Title is definitely a text field, and it doesn't come from a separate
table, so I don't know why it won't work. My Author field is a lookup
field with its own table though, and I figured that would be a problem
so I tried the Title field instead. Is there a way to make this work
for a Lookup field as well?

Right, I was confusing this with another question in my
mind. To help me avoid confusing myself, please include
your code as it is at the time you post.

I noticed that I fumble fingered my suggested line of code.
It was supposed to be:
Me.Recordset.FindFirst "Title='" & Text60 & "' "

The asterick would cause the error you saw. If there is a
chance that the title might have a value with an apostrophe,
then try using:
Me.Recordset.FindFirst "Title=""" & Text60 & """ "
If it might contain " and/or ' then post back.

To search for a match on the first few characters in the
title, use:
Me.Recordset.FindFirst "Title Like'" & Text60 & "*' "

To deal with a Lookup field, the best thing you can do is
change it back to a normal field and adjust the queries to
deal with it in the usual way using a combo box with its
bound column set to correspond to the author ID field.

 

[Erica]

Right, I was confusing this with another question in my
mind.  To help me avoid confusing myself, please include
your code as it is at the time you post.

I noticed that I fumble fingered my suggested line of code.
It was supposed to be:
        Me.Recordset.FindFirst "Title='" & Text60 & "' "

The asterick would cause the error you saw.  If there is a
chance that the title might have a value with an apostrophe,
then try using:
        Me.Recordset.FindFirst "Title=""" & Text60 & """ "
If it might contain " and/or ' then post back.

To search for a match on the first few characters in the
title, use:
        Me.Recordset.FindFirst "Title Like'" & Text60 & "*' "

To deal with a Lookup field, the best thing you can do is
change it back to a normal field and adjust the queries to
deal with it in the usual way using a combo box with its
bound column set to correspond to the author ID field.

That worked! I can now search the title field with a portion of the
title and there are no errors. Thank you so much, this is EXTREMELY
helpful!

 

[Erica]

That worked!  I can now search the title field with a portion of the
title and there are no errors.  Thank you so much, this is EXTREMELY
helpful!- Hide quoted text -

- Show quoted text -

Hello again,

Is there a simple way to change this code to make the form filter for
the value entered into the text box, rather than just finding the
first match through the FindFirst command? If I search for an author,
it is likely that I will want to see all of the books written by that
author - not just the first one in the database. I'm guessing that
the command would have to also first clear the filter so that I would
not be searching on the filtered results of my last search. Is that
true? My current code is as follows:

Private Sub Command78_Click()
If IsNull(Text76) = False Then
Me.Recordset.FindFirst "Title Like'" & Text76 & "*' "
Me!Text76 = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!Text76 = Null
End If
End If
End Sub

Thanks for all your help!
Erica

 

[Marshall Barton]

Is there a simple way to change this code to make the form filter for
the value entered into the text box, rather than just finding the
first match through the FindFirst command? If I search for an author,
it is likely that I will want to see all of the books written by that
author - not just the first one in the database. I'm guessing that
the command would have to also first clear the filter so that I would
not be searching on the filtered results of my last search. Is that
true? My current code is as follows:

Private Sub Command78_Click()
If IsNull(Text76) = False Then
Me.Recordset.FindFirst "Title Like'" & Text76 & "*' "
Me!Text76 = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!Text76 = Null
End If
End If
End Sub

If you want to filter for all records that match the
(partial) title, the code would be something like:

Private Sub Command78_Click()
If Not IsNull(Text76) Then
Me.Filter = "Title Like'" & Text76 & "*' "
Me.FilterOn = True
If Me.Recordset.RecordCount = 0 Then
MsgBox "No records found", vbOKOnly + vbInformation,
"Sorry"
End If
End If
End Sub

Note that I removed the Text76 = Null lines so users could
edit their entry instead of having to retype everything. If
you have a good reason for putting it backm go ahead.

Originally, you said you wanted to search/filter for some
other fields too. To do that I suggest that you first take
a look at Allen Browne's article at:
http://allenbrowne.com/ser-62.html