Need VBA Code help (1 Viewer)

[Ashfaque]

Hello Gentlemen,

I placed a combo (Combo7) box onto a form to read data from a field of table called Product_Master. The data contains " & ' and many more symbols like follows.

Hose 3/4" White UK standard
Hose 1/2" While USA standard
Strip 1' Brown 1/2" Italian Std


So for the first 2 items, it reads the data thru following code line

Set rst2 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= '" & Replace(Combo7, Chr(39), Chr(34) & Chr(34)) & "'")

But for the third name as contains (') it doesn't work.

Have somebody faced this kind of error?

Please advice if you have any solution for this.

With kind regards,
Ashfaque

 

[Oldsoftboss]

Your combo box should contain 2 columns, the first column containing the Primary key, bound and hidden. That way the item will always be unique

Dave

 

[Oldsoftboss]

Set rst2 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where ProductNameID= " & Me.Combo7)

 

[Ashfaque]

Thanks Boss,

But my combo7 does not contain any record sourse. To get the data following code is placed.

Private Sub Combo7_AfterUpdate()
On Error Resume Next

Dim cbo As ComboBox
Set cbo = Me.Combo7
If Not IsNull(cbo.Value) Then
If cbo.Value = cbo.Column(0) Then
If Len(cbo.Column(0)) > 0 Then
'Me.Text2 = cbo.Column(0)
End If
'If Len(cbo.Column(1)) > 0 Then
' Me.Text2 = cbo.Column(1)
'End If
Else
' Me.PostCode = Null
Exit Sub
End If
End If

Set cbo = Nothing

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

Set rst2 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= '" & Replace(Combo7, Chr(39), Chr(34) & Chr(34)) & "'")

Me.Text0 = rst2!ProductCode
Me.Text2 = rst2!Productname

rst2.Close
Set rst2 = Nothing

'Another sub routine called here

P_CloseProg

On Error GoTo 0
End Sub

This code is written to limit the records of the combo. Means initially the combo will be blank. But once you type the first character of the item you need, it would generate the data of the same names that start with the character you typed in it.

There are another 2-3 functions written for this combo which helps to generate the data like once you choose the item and press enter, after transferring data the combo7 will be again empty to get ready for next item to generate. This is all done to only remove the traffic.

I somehow managed to get rid of " symbol by using Replace function with Chr(34) but now my problem is that above code stucked me if the item name contains ' symbol.

Please advice.

With kind regards,
Ashfaque

 

[Oldsoftboss]

This code is written to limit the records of the combo. Means initially the combo will be blank. But once you type the first character of the item you need, it would generate the data of the same names that start with the character you typed in it.

That is exactly the way the combo box works with the sample I posted. It has no control source, thus nothing is displayed when the form is opened. If your users dont know what they are looking for, ths may be a better way to go....

 

[FireStrike]

I used the following code to get rid of that

While InStrRev(arrData(3), Chr(44))
DoEvents
arrData(3) = Mid(arrData(3), 1, (InStrRev(arrData(3), Chr(44)) - 1)) & Mid(arrData(3), (InStrRev(arrData(3), Chr(44)) + 1))
Wend

arrData is an array of strings, you can use any string for it. Because of the number of records I had the replace function ended up taking too long so it was not an option. I hope this helps.

 

[FireStrike]

oh yea by the way, you probly won't need the doevents funciont, as I said I had ALOT of records to go through with this

 

[Ashfaque]

FireStrike,

I tried to use your code line.

Can you pls show exact use of it in my code?

With kind regards,

Ashfaque

 

[FireStrike]

try something like this.

dim strTemp as string

strTemp=combo7.value
While InStrRev(strTemp, Chr(44))
strTemp = Mid(strTemp, 1, (InStrRev(strTemp, Chr(44)) - 1)) & Mid(strTemp, (InStrRev(strTemp, Chr(44)) + 1))
Wend

Set rst2 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
"where Productname= '" & Replace(strTemp, Chr(39), Chr(34) & Chr(34)) & "'")

Let me know if you have any problems. I am running access 2003, and I believe this should work on access 97 and up, but am not sure.

 

[Ashfaque]

It produces Run Time-error as "The value you entered is not valid for the field"
on the following line of code:

Me.Text0 = rst2!ProductCode

Regards,
Ashfaque