I'm using the following code to get related data from Access table when the Product code is written in column E.
The product code in access table is formatted as \P0000.
However, the code doesnt work if I write the product code as P0043 in column E. I get the error message "No value given for one or more required parameters".
But the code works If I write 43 in column E. How could I make this code work if I write the Product Code as P0043.
Note: My original code is designed so that the product code gets copied from another table with the format P0043.
Code: Select all
Sub GetDetails()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim ce As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each ce In Range("E18:E53")
If ce.Value <> "" Then
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=E:\DATA\Database.accdb;"
Set rst = New ADODB.Recordset
rst.Open Source:= "SELECT [Price] from PList WHERE [Product Code]= " & ce.Value, _
ActiveConnection:=cnn, CursorType:=adOpenStatic, Options:=adCmdText
If Not rst.EOF Then
ce.Offset(0, 5).Value = rst.Fields("Price")
End If
rst.Close
End If
Next ce
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub