RecordSet Source using Unbound Control

[LouiseJ]

I am trying to create a Recordset from a query. The query has a reference to [Forms]![FrmAvalibleAccountNumbersNext]![txtAccountNumber]

“txtAccountNumber” is an unbound control on the Form.

If the Query is run from design view it works no problem (“FrmAvalibleAccountNumbersNext” is open when the query runs).

When trying to create the Recordset the code fails. All error messages are indicating that the problem is with the reference to the unbound control “txtAccountNumber”

I have tried various ways to create the Recordset, the latest in ADO, code as follows;

Dim LocalConnection As ADODB.Connection
Dim rst As ADODB.Recordset
Set LocalConnection = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "QryCheckForAccountNumber", LocalConnection, adOpenStatic, adLockOptimistic, adCmdTable

SQL for query “QryCheckForAccountNumber” cut from design view

SELECT [QrySalesEnquiries&CustomerSorted].TblSalesEnquiries.[Customer Account Number]
FROM [QrySalesEnquiries&CustomerSorted]
GROUP BY [QrySalesEnquiries&CustomerSorted].TblSalesEnquiries.[Customer Account Number]
HAVING ((([QrySalesEnquiries&CustomerSorted].TblSalesEnquiries.[Customer Account Number]) Like [Forms]![FrmAvalibleAccountNumbersNext]![txtAccountNumber]));

What’s the answer? Can anyone help please?
Thanks
LouiseJ

 

[PHV]

You may try this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset    
    
Set db = CurrentDb
Set qdf = db.QueryDefs("QryCheckForAccountNumber")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

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

 

[LouiseJ]

PVH

Thankyou. It works just great.

LouiseJ