Best search method?

[Jery J.]

I am trying to create a form that would search for and display existing
records and then the user is able to enter notes into these records once they
are pulled up. I asked a consultant that works with our office to help me out
with this and the code he setup for some reason causes an error once the user
closes the form or tries saving it. the information is saved in the data base
but you have to go thru a number of error prompts to close it. I was
wondering what is the easy and simplest way to search for a record, have it
display and then have the user add notes to that record (which is on the same
table).

here is the code i've currently been trying:
Private Sub Search_Button_Click()

Dim AccessConnect As String
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=ETyler_Db.mdb;" & _
"DefaultDir=C:\Users\J\Desktop\New DB\ETyler_Db;" & _
"Uid=Admin;Pwd=;"

Dim cn As New ADODB.Connection

cn.ConnectionString = AccessConnect
cn.Open
Dim rs As New ADODB.Recordset
rs.Open ("Select * from EXET_Case_Data where clm_nbr='" & Me.txtIdFind &
"'"), cn '& Me.txtIdFind.Text)

If rs.EOF Then

CLM_NBR = ""
CLMNT_SSN = ""
CLMNT_FRST_NME = ""
CLMNT_MID_NME = ""
CLMNT_LAST_NME = ""
CLMNT_AGE = ""
CLMNT_DTE_OF_INJR = ""
CLM_EX_NAME = ""
DTE_OF_INT_REV = ""

MsgBox ("Wrong claim number. Please try again")

Else


CLM_NBR = rs("CLM_NBR")
CLMNT_SSN = rs("CLMNT_SSN")
CLMNT_FRST_NME = rs("CLMNT_FRST_NME")
CLMNT_MID_NME = rs("CLMNT_MID_NME")
CLMNT_LAST_NME = rs("CLMNT_LAST_NME")
CLMNT_AGE = rs("CLMNT_AGE")
CLMNT_DTE_OF_INJR = rs("CLMNT_DTE_OF_INJR")
CLM_EX_NAME = rs("CLM_EX_NAME")
DTE_OF_INT_REV = rs("DTE_OF_INT_REV")
' ADD THESE LINES TO FETCH ADDITION INFO ABOUT COMMENT AND INACC TYPE
CORR_COMMENTS = rs("CORR_COMMENTS")
INACC_TYPE.Value = rs("INACC_TYPE")

End If
cn.Close

End Sub

 

[Allen Browne]

Jerry, it would be much easier to use a bound form, and filter it so it
shows only the record(s) that match the user's criteria.

There's a downloadable example here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html