Why doesn't this work? Recordset from query using form control

[pondlife]

On MyForm I have a group of controls that are populated programmatically from MyQuery which includes a criterion using one of the forms' controls:

... WHERE MyField = Forms![MyForm]![MyControl]

The recordset for the controls that are populated programmatically is loaded on the form's OnLoad event, thus:

Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cn
.Source = "SELECT * FROM MyQuery;"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open
End With

Why doesn't this work? The error is "No value given for one or more required parameters" on .Open. I guess this means that the rst is empty?

The form works when WHERE MyField = (a value), and MyQuery returns a non-empty recordset when run separately.

Possibly a problem of events sequence? Including the full query statement in the code makes no difference. Hmm...

 

[Guus2005]

Access doesn't recognize Forms![MyForm]![MyControl] as a valid fieldname and expects you to enter a parameter.

Try this in your immediate window:

?rst.parameters.count 
1

You will see that you need to enter a parameter.

Use [ParameterControl] instead of Forms![MyForm]![MyControl]
and use
rst.parameter("ParameterControl") = Forms![MyForm]![MyControl]

b4 u open the rst.

HTH

Enjoy!