Search for records in a form and display

  • Thread starter Thread starter Jery J.
  • Start date Start date
J

Jery J.

Good Morning, First i'd like to thank everyone out there helping all those
people like myself who are just working hard on trying to learn. We couldn't
do it with out you, I know i couldn't!!

I am trying to create a form that has a search function. I tried some other
suggestions i've had but they only used the search function within Access
itself not the form. I need it to search for the record by a unique
identifier CLM_NBR. The user would enter in the Claim number in a
field(txtIdFind), click the search command button(cmdFind), the record would
be pulled up and then they could add more data to the record.

I've created an unbound text box(txtIdFind), a Command button(cmdFind) but i
don't know how to put everything together to have it pull up\display already
existing information from specific fields from the records. Those fields
which are all in the same table(EXET Case Review Data) are:
CLMNT_SSN
CLMNT_FRST_NME
CLMNT_MID_NME
CLMNT_LAST_NME
CLMNT_AGE
CLMNT_DTE_OF_INJR
CLM_EX_NAME
DTE_OF_INT_REV

And then the user would add additional information into that record from the
other fields provided. The above mention fields do not have to be editable
they can just be displayed, so that doesn't matter since that data is locked
down. i just need the user to be able to pull up the record and then input
some comments into that record. Thank again for the help.
 
Jery,

Generally the code behind your cmdFind button would look something like:

Private Sub cmdFind_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[CLM_NBR] = " & me.txtIDFind
Set rs = me.recordsetclone

rs.findfirst strCriteria
if rs.nomatch then
msgbox "Claim number was not found"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing

End Sub


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
The easy way to do this is with a combo box and let the wizard do the work
for you. It will almost always save your users keystrokes over a text box .
Even if you want to use a text box, I'd suggest starting with the combo box.
Then go into the events and look at the code it writes.
 
I placed the code behind the command button, tried the form and nothing
happened. how would i get it to display the record data? Do i use a combo
box? How can i get it to display the records data for those previously
mentioned fields? currently i have the text boxes bound to those specific
fields on the form but nothing is displayed.

Dale Fye said:
Jery,

Generally the code behind your cmdFind button would look something like:

Private Sub cmdFind_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[CLM_NBR] = " & me.txtIDFind
Set rs = me.recordsetclone

rs.findfirst strCriteria
if rs.nomatch then
msgbox "Claim number was not found"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing

End Sub


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Jery J. said:
Good Morning, First i'd like to thank everyone out there helping all those
people like myself who are just working hard on trying to learn. We couldn't
do it with out you, I know i couldn't!!

I am trying to create a form that has a search function. I tried some other
suggestions i've had but they only used the search function within Access
itself not the form. I need it to search for the record by a unique
identifier CLM_NBR. The user would enter in the Claim number in a
field(txtIdFind), click the search command button(cmdFind), the record would
be pulled up and then they could add more data to the record.

I've created an unbound text box(txtIdFind), a Command button(cmdFind) but i
don't know how to put everything together to have it pull up\display already
existing information from specific fields from the records. Those fields
which are all in the same table(EXET Case Review Data) are:
CLMNT_SSN
CLMNT_FRST_NME
CLMNT_MID_NME
CLMNT_LAST_NME
CLMNT_AGE
CLMNT_DTE_OF_INJR
CLM_EX_NAME
DTE_OF_INT_REV

And then the user would add additional information into that record from the
other fields provided. The above mention fields do not have to be editable
they can just be displayed, so that doesn't matter since that data is locked
down. i just need the user to be able to pull up the record and then input
some comments into that record. Thank again for the help.
Click to expand...
Click to expand...
 
I assumed that the RecordSource of your form is a table or query and that
when you open the form, it displays data from the fields you indicated in
your original post. Is that the case? Do you have the forms navigation
buttons enabled?

Setting the Forms bookmark to the bookmark of the recordset in the code is
what should move the record pointer on the form to the CLM_NBR you entered,
if there is a match.

Did you put a breakpoint in the code to make sure it was actually firing?
If not, display your code, then click on the grey vertical bar just to the
left of the line that reads Private Sub cmdFind_Click.

Open your form, type something in txtIDFind, and click the command button.
The code window should appear with the subroutines declaration statment
highlighted. If not, then you need to stop the code, open the form in design
view, and make sure that the Click event of the cmdFind button says [Event
Procedure].

If it does highlight the code, then step through the code and see what is
happening. Mouse over the variable "strCriteria" after you have passed that
line and see what its value is.

You might even want to add an error handler and an On Error Goto FindError
to check to see whether an error is occuring which is just being bypassed.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Jery J. said:
I placed the code behind the command button, tried the form and nothing
happened. how would i get it to display the record data? Do i use a combo
box? How can i get it to display the records data for those previously
mentioned fields? currently i have the text boxes bound to those specific
fields on the form but nothing is displayed.

Dale Fye said:
Jery,

Generally the code behind your cmdFind button would look something like:

Private Sub cmdFind_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[CLM_NBR] = " & me.txtIDFind
Set rs = me.recordsetclone

rs.findfirst strCriteria
if rs.nomatch then
msgbox "Claim number was not found"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing

End Sub


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Jery J. said:
Good Morning, First i'd like to thank everyone out there helping all those
people like myself who are just working hard on trying to learn. We couldn't
do it with out you, I know i couldn't!!

I am trying to create a form that has a search function. I tried some other
suggestions i've had but they only used the search function within Access
itself not the form. I need it to search for the record by a unique
identifier CLM_NBR. The user would enter in the Claim number in a
field(txtIdFind), click the search command button(cmdFind), the record would
be pulled up and then they could add more data to the record.

I've created an unbound text box(txtIdFind), a Command button(cmdFind) but i
don't know how to put everything together to have it pull up\display already
existing information from specific fields from the records. Those fields
which are all in the same table(EXET Case Review Data) are:
CLMNT_SSN
CLMNT_FRST_NME
CLMNT_MID_NME
CLMNT_LAST_NME
CLMNT_AGE
CLMNT_DTE_OF_INJR
CLM_EX_NAME
DTE_OF_INT_REV

And then the user would add additional information into that record from the
other fields provided. The above mention fields do not have to be editable
they can just be displayed, so that doesn't matter since that data is locked
down. i just need the user to be able to pull up the record and then input
some comments into that record. Thank again for the help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top