Record Search within a Form

[Corinne]

I have a single table with dozens of fields. I used the input mask for
things such as SSN (social security number-also my Primary Key), phone
numbers, and birthdates. Through this Discussion Group I found a reference
to site that gave me code to put a search for record box at the top of a form
I created. The first time I put in my SSN that I knew matched one in my
table it told me it couldn't find a matching record, but it displayed that
"person's" record on the screen below my search field like it was supposed
too. Unfortunately, since that first record, when I put in an SSN that I
*KNOW* is in my table, it says it can't find a value. Then randomly, I'll
enter a different SSN and it will find it, not listing any errors. Since my
search is based around SSN I used the input mask on the search box thinking
maybe my search wasn't able to find the records because of mask, but that
didn't work either.

Below is the code I'm using for my Search button's event proceedure:

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 ("SSN")
DoCmd.findrecord Me!txtsearch

SSN.SetFocus
strStudentRef = SSN.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!"
SSN.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

Any help you can offer as to why this search isn't consistently returning
records that ARE in the table, I would really appreciate. I have spent well
over 50 hours trying to create a search button, and to be so close is
extremly frustrating.

Thank you

 

[Jeff Boyce]

Corinne

A couple of thoughts...

I'm not sure from your description whether you are looking for a SINGLE
record or any number of records that match the criteria.

Also, if your underlying table literally has "dozens of fields", there's a
good chance your data structure could benefit from further normalization.
Another way to look at it is "did you import an Excel spreadsheet without
first designing a relational data structure?"

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a single table with dozens of fields. I used the input mask for
things such as SSN (social security number-also my Primary Key), phone
numbers, and birthdates. Through this Discussion Group I found a
reference
to site that gave me code to put a search for record box at the top of a
form
I created. The first time I put in my SSN that I knew matched one in my
table it told me it couldn't find a matching record, but it displayed that
"person's" record on the screen below my search field like it was supposed
too. Unfortunately, since that first record, when I put in an SSN that I
*KNOW* is in my table, it says it can't find a value. Then randomly, I'll
enter a different SSN and it will find it, not listing any errors. Since
my
search is based around SSN I used the input mask on the search box
thinking
maybe my search wasn't able to find the records because of mask, but that
didn't work either.

Below is the code I'm using for my Search button's event proceedure:

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 ("SSN")
DoCmd.findrecord Me!txtsearch

SSN.SetFocus
strStudentRef = SSN.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!"
SSN.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

Any help you can offer as to why this search isn't consistently returning
records that ARE in the table, I would really appreciate. I have spent
well
over 50 hours trying to create a search button, and to be so close is
extremly frustrating.

Thank you

 

[Corinne]

I am looking for a single record. Basically I built an additional form so I
could input new data. The new form has some information that's being pulled
from the table, such as the phone number, address, etc, and it has new
information I'm going to input.

I built this database from scratch, and feel confident that it was
constructed in such a way that it would easily pass a beginners to
intermediate level assessment. I however have not been able to sucessfully
make multiple tables and "relationship" them together which is why this one
table has so many fields.

What I can say is that as I was inputting data into the database I would
occassionally make tweeks. For example, with the SSN primary key field, it
originally didn't have an input max and then later I added one. Now some of
my SSNs have the formatted dashes, while others do not.

Hopefully this will be enough information to get some help.

Thank you so much.

Corinne

A couple of thoughts...

I'm not sure from your description whether you are looking for a SINGLE
record or any number of records that match the criteria.

Also, if your underlying table literally has "dozens of fields", there's a
good chance your data structure could benefit from further normalization.
Another way to look at it is "did you import an Excel spreadsheet without
first designing a relational data structure?"

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a single table with dozens of fields. I used the input mask for
things such as SSN (social security number-also my Primary Key), phone
numbers, and birthdates. Through this Discussion Group I found a
reference
to site that gave me code to put a search for record box at the top of a
form
I created. The first time I put in my SSN that I knew matched one in my
table it told me it couldn't find a matching record, but it displayed that
"person's" record on the screen below my search field like it was supposed
too. Unfortunately, since that first record, when I put in an SSN that I
*KNOW* is in my table, it says it can't find a value. Then randomly, I'll
enter a different SSN and it will find it, not listing any errors. Since
my
search is based around SSN I used the input mask on the search box
thinking
maybe my search wasn't able to find the records because of mask, but that
didn't work either.

Below is the code I'm using for my Search button's event proceedure:

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 ("SSN")
DoCmd.findrecord Me!txtsearch

SSN.SetFocus
strStudentRef = SSN.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!"
SSN.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

Any help you can offer as to why this search isn't consistently returning
records that ARE in the table, I would really appreciate. I have spent
well
over 50 hours trying to create a search button, and to be so close is
extremly frustrating.

Thank you

 

[Jeff Boyce]

Corinne

If you are using a form to display a "found" (single) record, one way to do
this is to use an unbound combobox in the Header of the form. That combobox
lists something distinctive (i.e., what you would have searched on, like,
say, LastName, FirstName, or SSN, or ...) that makes it easier to pick the
one record you want to see more details on.

The form itself needs to be based on a query that uses the aforementioned
combobox as a selection criterion. That is, the query is based on the
underlying table, and points to the form's combobox to provide the selection
criterion for the relevant field.

Finally, in the combobox's AfterUpdate event, you tell the form to requery
its source with something like:
Me.Requery

The net effect is that the form is empty when opened (there's nothing in the
combobox, so the query finds the record matching that). You start in the
combobox and select the one you want to see. The combobox's AfterUpdate
event tells the form to recheck its source (using the combobox for
criterion), and loads the one record.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am looking for a single record. Basically I built an additional form so
I
could input new data. The new form has some information that's being
pulled
from the table, such as the phone number, address, etc, and it has new
information I'm going to input.

I built this database from scratch, and feel confident that it was
constructed in such a way that it would easily pass a beginners to
intermediate level assessment. I however have not been able to
sucessfully
make multiple tables and "relationship" them together which is why this
one
table has so many fields.

What I can say is that as I was inputting data into the database I would
occassionally make tweeks. For example, with the SSN primary key field,
it
originally didn't have an input max and then later I added one. Now some
of
my SSNs have the formatted dashes, while others do not.

Hopefully this will be enough information to get some help.

Thank you so much.

Corinne

A couple of thoughts...

I'm not sure from your description whether you are looking for a SINGLE
record or any number of records that match the criteria.

Also, if your underlying table literally has "dozens of fields", there's
a
good chance your data structure could benefit from further normalization.
Another way to look at it is "did you import an Excel spreadsheet without
first designing a relational data structure?"

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a single table with dozens of fields. I used the input mask for
things such as SSN (social security number-also my Primary Key), phone
numbers, and birthdates. Through this Discussion Group I found a
reference
to site that gave me code to put a search for record box at the top of
a
form
I created. The first time I put in my SSN that I knew matched one in
my
table it told me it couldn't find a matching record, but it displayed
that
"person's" record on the screen below my search field like it was
supposed
too. Unfortunately, since that first record, when I put in an SSN that
I
*KNOW* is in my table, it says it can't find a value. Then randomly,
I'll
enter a different SSN and it will find it, not listing any errors.
Since
my
search is based around SSN I used the input mask on the search box
thinking
maybe my search wasn't able to find the records because of mask, but
that
didn't work either.

Below is the code I'm using for my Search button's event proceedure:

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 ("SSN")
DoCmd.findrecord Me!txtsearch

SSN.SetFocus
strStudentRef = SSN.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!"
SSN.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

Any help you can offer as to why this search isn't consistently
returning
records that ARE in the table, I would really appreciate. I have spent
well
over 50 hours trying to create a search button, and to be so close is
extremly frustrating.

Thank you