asked on

Access VBA Query Parameter

Hi,

I have an form called frmMain and I have a subform which is as query. The query ask the user to enter a parameter and the form opens fine.

My question is how can I open this form via vba and pass the parameter value into the query so it loads up like it would as above? I'm using Access 2000 and I cannot use the OpenQueryDefs method.

Thanks,

INHOUSERES

myVariable = "Hello World"
docmd.OpenForm "testForm",,,,,,myVariable

In the form, you can reference that viariable via "Me.OpenArgs":

msgbox me.OpenArgs

mcs26

ASKER

Hi INHouseres,

Thanks for the reply. I have just tried the code below. The only problem is when the form opens up the query parameter still asks me to enter the variable?

Thanks

Sub PriceChecksUpdate()

Dim ISIN As String

ISIN = InputBox("Please enter bonds ISIN:", "ISIN")
DoCmd.OpenForm "frmPriceUpdate", acNormal, , , , , ISIN

End Sub

ASKER CERTIFIED SOLUTION

INHOUSERES

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcs26

ASKER

Thanks for that. I have added a text box however when the form loads up the query does not return any records.

In my query I have in the criteria box [Forms]![frmPriceUpdate]![txtISIN].

The code I have is below, thanks again!

Sub PriceChecksUpdate()

Dim ISIN As String

ISIN = InputBox("Please enter bonds ISIN:", "ISIN")
DoCmd.OpenForm "frmPriceUpdate", acNormal, , , , , ISIN

End Sub

In the form I have

Private Sub Form_Load()
Me.txtISIN = Me.OpenArgs
End Sub

mcs26

ASKER

I should also say the txtISIN is populates with the correct value

mcs26

ASKER

I just added me.recalc & its now works thanks very much

INHOUSERES

Ah yes, sorry for omitting that.

Glad I could have been of assistance.