Whats Wrong With This Code (1 Viewer)

[Ashfaque]

I placed a combo box named CboSearchItem on form to select and bring the record onto the form. Its working smoothly.

Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= " & """" & CboSearchItem & """")

But when data in field 'ProductName' contains " mark, it produces error 3075 :

Syntax error in string in query expression "ProductName = "Hose White 3/4"'"

When I replaced all " mark from the data / record, it works fine.
For example Product Name are as follows:

Elbow 2 Way
ABC Pipe 3"
Shower Pipe 1 1/5 Meter

Reading all the above record but not the second one because it contains " mark.

I can not tell my client not to use " symbol while entering the product name. There should be a solution.

Please extend your help.

With kind regards,
Ashfaque

 

[Sergeant]

Make a variable to hold cboSearchItem's value.
strSearch = cboSearchItem

Do a replace on it...
strSearch = Replace(strSearch, Chr(34), "")
strSearch = Replace(strSearch, Chr(39), "")

Then run your query against the variable strSearch.

 

[Ashfaque]

Thanks Sergeant,

But where to put it in the code. My code lines After update event of the combo are as follows:

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim fld As DAO.Field

Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= " & """" & CboSearchItem & """")

For Each fld In rst1.Fields
Me(fld.Name) = rst1(fld.Name)
Next fld
rst1.Close
Set rst1 = Nothing

It runs well when there are no " mark in the data.

Please advice.

Thanks for the quick response.

Ashfaque

 

[Ashfaque]

I tried using following way But problem still remain.

Dim db As DAO.Database
Dim rst1 As DAO.Recordset
Dim fld As DAO.Field

strSearch = cboSearchItem

strSearch = Replace(strSearch, Chr(34), "")
strSearch = Replace(strSearch, Chr(39), "")

Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= " & strSearch)

For Each fld In rst1.Fields
Me(fld.Name) = rst1(fld.Name)
Next fld
rst1.Close
Set rst1 = Nothing

Please advice.
Ashfaque

 

[Ashfaque]

Yet another problem;

I am using DAO in the code for which I selected proper library reference file. Also I have some code in which I used following:

Dim rst as ADODB.Recordset, cnt As Long, Rct As Long

Before it was working well but now due to the explicitly declairation of DAO, it produces following error.

Compile Error: User-defined type not defined

Please help me to come over this error.



Ashfaque

 

[Sergeant]

Actually, I'm having second thoughts about what I recommended for you. The problem is:
If you have an entry that has a quote or two in it, and you take out the quotes and then search for that, you won't get a match. Perhaps someone else has handled this and will buzz in with some better advice.

 

[SamDeMan]

Please view this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=101403

what i recommend is that you create a query that will call your split function and would replace the ". and they you can use the replace on CboSearchItem.

good luck,

sam