Problem using a combo box value in a query

[ppullag1]

Hello Group,

In access 2003/VBA environment, I am trying to take a value from a combo box and use in the WHERE clause in a query and open the query as a recordset. But it throws an error "No value given for one or more required parameters" . It prints out the value from the combo correctly. The problem starts when it tries to open the recordset. The query looks simple and correct to me, may be I am passing wrong arguments to the recordset object.

Here is my code:

Dim strsql As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
MsgBox Forms![Test Form]![Area]
strsql = "Select Aid from Area where [Area] = Forms![Test Form]![area]"
rst.Open strsql, CurrentProject.Connection, adOpenStatic
MsgBox rst.RecordCount
rst.Close
Set rst = Nothing

Appreciate any help in this regard.

Thanks,
Pradeep

 

[PHV]

Replace this:
strsql = "Select Aid from Area where [Area] = Forms![Test Form]![area]"
By this:
strsql = "Select Aid from Area where [Area] = [tt]'"[/tt] & Forms![Test Form]![area] & [tt]"'"[/tt]

If the Area field is defined as numeric in the Area table then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Right now your code is putting a string into the query, not a value. Try

strsql = "Select Aid from Area where [Area] = " & Forms![Test Form]![area]

if your variable is numeric or

strsql = "Select Aid from Area where [Area] = '" & Forms![Test Form]![area] & "'"

if it is string data

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Slowest typer in the (mid) west

 

[ppullag1]

Thank You guys......I works great....Appreciate your time