Seaching using text box

R

Richard

Hello,

Is it possible to modify the following code to search an entire table
instead of just the studentid? I have a single table named tblscanlog with 9
fields including a PK. I would use the same control names.
Thanks



Option Compare Database
'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
 
J

Jeff Boyce

Richard

If you would need to "search an entire table" instead of just a single
field, that sounds like what you are looking for could be anywhere. A
well-normalized table in a relational database (like Access) stores data so
that you'd only be looking in one field.

If you'll describe a bit more specifically what you have to work with, the
folks here in the newsgroup may be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Richard

Hi Jeff,

Sorry for the confusion, yes I would like to be able to search one "field"
in the database, but I don't know which field might want to be searched at
any given moment. They may want to search dates, purchase orders, vendors and
so on.
I don't want the search to be limited to one field like the example code.
Hope this makes sence


Jeff Boyce said:
Richard

If you would need to "search an entire table" instead of just a single
field, that sounds like what you are looking for could be anywhere. A
well-normalized table in a relational database (like Access) stores data so
that you'd only be looking in one field.

If you'll describe a bit more specifically what you have to work with, the
folks here in the newsgroup may be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Richard said:
Hello,

Is it possible to modify the following code to search an entire table
instead of just the studentid? I have a single table named tblscanlog with
9
fields including a PK. I would use the same control names.
Thanks



Option Compare Database
'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Click to expand...
Click to expand...
 
J

Jeff Boyce

Have you looked into using the Filter capabilities?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Richard said:
Hi Jeff,

Sorry for the confusion, yes I would like to be able to search one "field"
in the database, but I don't know which field might want to be searched at
any given moment. They may want to search dates, purchase orders, vendors
and
so on.
I don't want the search to be limited to one field like the example code.
Hope this makes sence


Jeff Boyce said:
Richard

If you would need to "search an entire table" instead of just a single
field, that sounds like what you are looking for could be anywhere. A
well-normalized table in a relational database (like Access) stores data
so
that you'd only be looking in one field.

If you'll describe a bit more specifically what you have to work with,
the
folks here in the newsgroup may be able to offer more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Richard said:
Hello,

Is it possible to modify the following code to search an entire table
instead of just the studentid? I have a single table named tblscanlog
with
9
fields including a PK. I would use the same control names.
Thanks



Option Compare Database
'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
R

Richard

I'll keep looking thanks

Jeff Boyce said:
Have you looked into using the Filter capabilities?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Richard said:
Hi Jeff,

Sorry for the confusion, yes I would like to be able to search one "field"
in the database, but I don't know which field might want to be searched at
any given moment. They may want to search dates, purchase orders, vendors
and
so on.
I don't want the search to be limited to one field like the example code.
Hope this makes sence


Jeff Boyce said:
Richard

If you would need to "search an entire table" instead of just a single
field, that sounds like what you are looking for could be anywhere. A
well-normalized table in a relational database (like Access) stores data
so
that you'd only be looking in one field.

If you'll describe a bit more specifically what you have to work with,
the
folks here in the newsgroup may be able to offer more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

Is it possible to modify the following code to search an entire table
instead of just the studentid? I have a single table named tblscanlog
with
9
fields including a PK. I would use the same control names.
Thanks



Option Compare Database
'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
E

Evi

A multi-criteria search form would do this.

See the one on Allen Browne's page for an example
http://allenbrowne.com/ser-62.html

Evi

Richard said:
Hi Jeff,

Sorry for the confusion, yes I would like to be able to search one "field"
in the database, but I don't know which field might want to be searched at
any given moment. They may want to search dates, purchase orders, vendors and
so on.
I don't want the search to be limited to one field like the example code.
Hope this makes sence


Jeff Boyce said:
Richard

If you would need to "search an entire table" instead of just a single
field, that sounds like what you are looking for could be anywhere. A
well-normalized table in a relational database (like Access) stores data so
that you'd only be looking in one field.

If you'll describe a bit more specifically what you have to work with, the
folks here in the newsgroup may be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Richard said:
Hello,

Is it possible to modify the following code to search an entire table
instead of just the studentid? I have a single table named tblscanlog with
9
fields including a PK. I would use the same control names.
Thanks



Option Compare Database
'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
R

Richard

Hi Evi,

Thanks for your responce, I took Jeffs advice and setup a filter/sort.

Evi said:
A multi-criteria search form would do this.

See the one on Allen Browne's page for an example
http://allenbrowne.com/ser-62.html

Evi

Richard said:
Hi Jeff,

Sorry for the confusion, yes I would like to be able to search one "field"
in the database, but I don't know which field might want to be searched at
any given moment. They may want to search dates, purchase orders, vendors and
so on.
I don't want the search to be limited to one field like the example code.
Hope this makes sence


Jeff Boyce said:
Richard

If you would need to "search an entire table" instead of just a single
field, that sounds like what you are looking for could be anywhere. A
well-normalized table in a relational database (like Access) stores data so
that you'd only be looking in one field.

If you'll describe a bit more specifically what you have to work with, the
folks here in the newsgroup may be able to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

Is it possible to modify the following code to search an entire table
instead of just the studentid? I have a single table named tblscanlog with
9
fields including a PK. I would use the same control names.
Thanks



Option Compare Database
'--------------------------------------------------------------
'Graham Thorpe 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID

DoCmd.ShowAllRecords
DoCmd.GoToControl ("strStudentID")
DoCmd.FindRecord Me!txtSearch

strStudentID.SetFocus
strStudentRef = strStudentID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strStudentID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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

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

Ask a Question

Similar Threads

Enter new record if search returns no results? 17
Record Search within a Form 3
Search Button 2
Invalid Qualifier 0
cmdSearch_Click 1
Creating a new a record after search 3
DoCmd.FindRecord Me! 6
DoCmd.GoToControl "[MODEL]" 1

Top