Recordset query that references form control

[adam.vogg]

I am opening an ADODB recordset based on a query. However, i want
that query to reference a form control.

here is my code:

Dim myC As ADODB.Connection
Dim myR As New ADODB.Recordset
Set myC = CurrentProject.Connection
myR.ActiveConnection = myC
myR.Open "SELECT * FROM tblClaimant Where OrderNum= [Forms]![frmClaim].
[text39].[value]", , adOpenStatic, adLockOptimistic



This is not working. Do I need some sort of qualifiers around the
reference?


Thanks for the help,

ADAM

 

[John W. Vinson]

I am opening an ADODB recordset based on a query. However, i want
that query to reference a form control.

here is my code:

Dim myC As ADODB.Connection
Dim myR As New ADODB.Recordset
Set myC = CurrentProject.Connection
myR.ActiveConnection = myC
myR.Open "SELECT * FROM tblClaimant Where OrderNum= [Forms]![frmClaim].
[text39].[value]", , adOpenStatic, adLockOptimistic



This is not working. Do I need some sort of qualifiers around the
reference?


Thanks for the help,

ADAM

First try removing the .[value] - the value property is the default in any
case.

If that doesn't help, try concatenating the actual contents of the control
into the SQL string:

Dim strSQL As String
strSQL = "SELECT * FROM tblClaimant Where OrderNum= " _
& [Forms]![frmClaim].[text39]
MyR.Open strSQL

or, if OrderNum is of Text datatype,

strSQL = "SELECT * FROM tblClaimant Where OrderNum= '" _
& [Forms]![frmClaim].[text39] & "'"

For readability, that's

strSQL = "SELECT * FROM tblClaimant Where OrderNum= ' " _
& [Forms]![frmClaim].[text39] & " ' "

 

[adam.vogg]

Thanks John, concatenating the control into the string seemed to do
the trick.