Access 2007 forms with parameterized RecordSource

Question

I'm sure there's a simple solution to this, but it seems to elude me.

So I have a form with a RecordSource of SELECT * FROM Table1 WHERE id=[@id]. (Yes, I know SELECT * is evil; it's just an example). Obviously the default behavior of this form is that upon opening, an input dialog will prompt for the "[@id]" value. But what I would like to do is call this form from VBA and instead of having the input dialog pop up, go ahead and tell the form what [@id] is, so the dialog never pops up, and the form opens just as if [@id] where replaced by a value in the query. I've tried the following so far...

Form_MyForm.RecordSource = String.Replace(Form_Form1.RecordSource, "[@id]", 1)
DoCmd.OpenForm "MyForm"

...and...

Form_MyForm.txtId.Value = 1
DoCmd.OpenForm "MyForm"

...and...

DoCmd.OpenForm "MyForm", acNormal, , "[@id]=" & 1

...but none of them seem to have the desired effect. How do i do this?

Thanks ahead of time.

Answer 1

See if it's easier to drop the WHERE clause from your query:

SELECT * FROM Table1;

Then use the optional WhereCondition parameter with Openform to indicate which id value you want:

DoCmd.OpenForm "MyForm", WhereCondition:="id = 1"