combo box and error 2147352567!

[mack225]

Hi,

I'm, getting an error when I run the following code, only on some records though (the same ones).

The code is used to take a result from a combo box, take the primary key from the combo selection, and use this to find a single record from the database. The the form displays this via textboxes set up for this purpose (prefixed with txt in the form code). It gernally does this successfully with most records, no problem, but some records throw up the following:

Run time error 2147352567 (80020009)
The value you entered isn't valid for this field

Code as below, error is highlighted in the debugger on the first textbox assignment (when I deleted this, it went on to throw the error up on the next textbox assignment in the code!):


Private Sub cmbClient_AfterUpdate()

Dim cmbString As String
Dim intStrLength As Integer
Dim intStart As Integer
Dim mySQL As String

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

myRecordSet.CursorType = adOpenKeyset
myRecordSet.LockType = adLockOptimistic


cmbString = cmbClient.Value
intStrLength = Len(cmbString)
intStart = InStr(1, cmbString, "*")


intStart = intStart + 1
cmbString = Mid(cmbString, intStart)
Debug.Print cmbString

mySQL = "SELECT TblCOMPANY.*, TblCONTACT.* " _
& "FROM TblCOMPANY INNER JOIN TblCONTACT ON TblCOMPANY.CompanyID = TblCONTACT.CompanyID " _
& "WHERE (((TblCONTACT.CompanyID)=" & cmbString & "))"

myRecordSet.Open mySQL

txtTitle = myRecordSet("Title")
txtForename2 = myRecordSet("forename")
txtSurname2 = myRecordSet("surname")
txtPosition = myRecordSet("position")
txtDept = myRecordSet("Dept")
txtTelNo = myRecordSet("TelephoneNo")
txtMobNo = myRecordSet("MobilePhone")
txtHomeNo = myRecordSet("HomeTelNo")
txtFaxNo = myRecordSet("faxnumber")
'txtURL = myRecordSet("URL")
txtemail1 = myRecordSet("email1")
txtemail2 = myRecordSet("email2")
'txtsource = myRecordSet("source")
txtsms = myRecordSet("sms")
txtBusName = myRecordSet("BusinessName")
txtBusType = myRecordSet("BusinessType")
txtAdd1 = myRecordSet("address1")
txtAdd2 = myRecordSet("address2")
txtadd3 = myRecordSet("address3")
txtAdd4 = myRecordSet("address4")
txtTownCity = myRecordSet("TownCity")
txtRegion = myRecordSet("Region")
txtPostcode2 = myRecordSet("Postcode")
txtEmployeeNo = myRecordSet("EmployeeNo")
txtSIC = myRecordSet("SIC")
txtSICDescription = myRecordSet("SICCategory")
txtTurnover = myRecordSet("Turnover")


Set myRecordSet = Nothing
Set cnn1 = Nothing

End Sub

 

[SamDeMan]

change this line & "WHERE (((TblCONTACT.CompanyID)=" & cmbString & "))"

to & "WHERE (((TblCONTACT.CompanyID)= '" & cmbString & "' ))"

in other words, add the single quotes.

good luck,

sam

 

[mack225]

I'm afraid this didn't work. Do I need to perhaps cast the cmbString as a LONG, since it may be returning a string value? If yes, can anyone advise on how to cast in VBA (PROBLEM: Is the SQL is searching on a LONG number field, perhaps with a definded string value, rather than long)?

Regards and thanks

Mack

 

[mack225]

I am still having difficulty with this code, and would appreciate any help that readers may be able to provide:

I have isolated the problem further, I think. When I copy the SQL result via the debug.print option, the SQL code gives a blank field when run as query. When I write the code out, exactly the same, in a new query, it returns a populated field. I'm a little perplexed!

Help appreciated!