Data Mismatch error on search button click

[JackBurton07]

I am trying to search via ID_NO which is an autonumber in my access db but am running into a data mismatch error when I click the search button.

Please help!

Thanks

JB


++++++++++++++++++++++++++++++++++++++++++++++++++++++++



Private Sub cnd_search1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cnd_search1.Click


myRecordSet.Open("Select* from client where id_no='" & TXT_IDSEARCH.Text & "'", myDb)

If Not myRecordSet.EOF Then ' if it's not the EOF (records are returned) set values
TXT_IDSEARCH.Text = txtID_No.Text
txtFirstName.Text = myRecordSet.Fields("firstname").Value

'txtID_No.Text = myRecordSet.Fields("id_no").Value()

txtdob.Text = myRecordSet.Fields("dob").Value()
txthometel.Text = myRecordSet.Fields("hometel").Value
txtbustel.Text = myRecordSet.Fields("businesstel").Value
txtcelltel.Text = myRecordSet.Fields("celltel").Value
txthomeemail.Text = myRecordSet.Fields("homeemail").Value
txtbusinessemail.Text = myRecordSet.Fields("businessemail").Value
txtcellemail.Text = myRecordSet.Fields("cellemail").Value
txtOnInit.Text = myRecordSet.Fields("oninit").Value
TXTSTARTDATE.Text = myRecordSet.Fields("clientstart").Value
txtInvestment1.Text = myRecordSet.Fields("investment1").Value
txthomecode.Text = myRecordSet.Fields("homecode").Value
txtbuscode.Text = myRecordSet.Fields("businesscode").Value
txtcellcode.Text = myRecordSet.Fields("cellcode").Value

cboContactPref.Text = myRecordSet.Fields("prefcontact").Value
cboProduct1.Text = myRecordSet.Fields("product1").Value


cboCountry.Text = myRecordSet.Fields("country").Value
cboPrivateCorp.Text = myRecordSet.Fields("privatecorp").Value
txtHouseNo.Text = myRecordSet.Fields("houseno").Value
txtAddress1.Text = myRecordSet.Fields("address1").Value
txtAddress2.Text = myRecordSet.Fields("address2").Value
txtTown.Text = myRecordSet.Fields("address3").Value
txtPcode.Text = myRecordSet.Fields("address4").Value
'searchidNo.Text = myRecordSet.Fields("id_no").Value
searchfirstname.Text = myRecordSet.Fields("firstname").Value
searchlastname.Text = myRecordSet.Fields("surname").Value
searchhometel.Text = myRecordSet.Fields("hometel").Value
searchbustel.Text = myRecordSet.Fields("businesstel").Value
searchceltel.Text = myRecordSet.Fields("celltel").Value
searchhomeemail.Text = myRecordSet.Fields("homeemail").Value
searchbusinessemail.Text = myRecordSet.Fields("businessemail").Value
searchdob.Text = myRecordSet.Fields("dob").Value
searchcellemail.Text = myRecordSet.Fields("cellemail").Value
searchonboarderinit.Text = myRecordSet.Fields("oninit").Value
searchcountry.Text = myRecordSet.Fields("country").Value
searchhouseno.Text = myRecordSet.Fields("houseno").Value
searchaddress1.Text = myRecordSet.Fields("address1").Value
searchaddress2.Text = myRecordSet.Fields("address2").Value
searchtown.Text = myRecordSet.Fields("address3").Value
searchcontactpref.Text = myRecordSet.Fields("prefcontact").Value
searchprivatecorp.Text = myRecordSet.Fields("privatecorp").Value
searchpcode.Text = myRecordSet.Fields("address4").Value

myRecordSet.Close() ' close rs when values added to form

'ENABLE the edit and delete buttons if records found (i assume you don't want them enabled if there's no data?)
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True

Else ' no records returned
MsgBox("No Client Records Found")
ClearFields() 'function clears all fields'
myRecordSet.Close() 'close rs

End If
End Sub

 

[ZmrAbdulla]

Your SQL defined the search string as String but the field you are searching for is integer

 myRecordSet.Open("Select*  from client where id_no='" & TXT_IDSEARCH.Text & "'", myDb)

to change like...

 myRecordSet.Open("Select*  from client where id_no='" & TXT_IDSEARCH.Text, myDb)

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[JackBurton07]

Hi thanks for this,


its now generating a sysntax error
Syntax error in string in query expression 'id_no='4'.

 

[ZmrAbdulla]

and this

myRecordSet.Open("Select*  from client where id_no=" & TXT_IDSEARCH.Text, myDb)

________________________________________________________
Zameer Abdulla
Help to find Missing people