Passing values from one form to another form based on a parameter query (1 Viewer)

[Eric the Viking]

Dear All
I have a main data entry form for my fishing club that is based on a parameter query using two parameters.
I have another continuous form that allows me to see at a glance who has paid up and who hasn't.
I would like to have a button on the continuous form that uses the two parameters to allow me to open the main data entry form based on the parameter query.
I have tried using various iterations with DoCmd and OpenArgs but I am clearly doing something wrong as I still get asked to enter the parameters rather than this happening through the VBA.
This is what I have tried in various formats:

Private Sub Command21_Click()
DoCmd.OpenForm "frmEnterMembersDebitsAndReceipts", OpenArgs:=Me.[Surname] & FirstName
End Sub

Hope someone can help.
Cheers

 

[pbaldy]

Is this what you're trying to do?

Open a second form to the record

www.baldyweb.com

 

[MajP]

docmd.OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

So try
DoCmd.OpenForm "frmEnterMembersDebitsAndReceipts",,, "SurName = '" & me.Surname & "' AND FirstName = '" & me.FirstName & "'"

Strings need to get wrapped in single quotes so should resolve to
Surname = 'Smith' AND FirstName = 'Bob'

 

[Eric the Viking]

docmd.OpenForm (FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

So try
DoCmd.OpenForm "frmEnterMembersDebitsAndReceipts",,, "SurName = '" & me.Surname & "' AND FirstName = '" & me.FirstName & "'"

Strings need to get wrapped in single quotes so should resolve to
Surname = 'Smith' AND FirstName = 'Bob'

Thank you for the suggestion and I see what you mean by the quotes resolving, but I have tried it out and I still get the prompts for the parameter query to enter Surname then FirstName...at a bit of a loss really.

 

[MajP]

Thank you for the suggestion and I see what you mean by the quotes resolving, but I have tried it out and I still get the prompts for the parameter query to enter Surname then FirstName...at a bit of a loss really.

Maybe I misunderstood. Does form "frmEnter...Receipts" also have parameters in the query? In this case it does not since, you are opening a filtered record by passing in the where clause.

 

[Eric the Viking]

Maybe I misunderstood. Does form "frmEnter...Receipts" also have parameters in the query? In this case it does not since, you are opening a filtered record by passing in the where clause.

"frmEnterD.....Receipts" is based on the parameter query which asks for Surname and FirstName. The continuous form has the fields Surname and FirstName in it but is based on a query not a parameter query. I am trying to open the form based on the parameter query using the values in the continuous form. Hope that makes sense?

 

[pbaldy]

You either want the criteria in the query or in the OpenForm, not both. Try taking them out of the query.

 

[Eric the Viking]

You either want the criteria in the query or in the OpenForm, not both. Try taking them out of the query.

Hi thanks for your reply, yes that works for the scenario above. But the "frmEnterD.....Receipts" is also open-end directly from a button on another form that depends on the parameters in the query to locate the record so I solve one problem but cause another. I know I could just duplicate the form and base it on a different query but that seems messy...

 

[pbaldy]

Why not open it with a wherecondition like this one? If you're using bracketed prompts, you can use InputBox to get the criteria before opening.

 

[Eric the Viking]

Why not open it with a wherecondition like this one? If you're using bracketed prompts, you can use InputBox to get the criteria before opening.

Hi not sure I follow that?

 

[pbaldy]

How is it opened from the other form now? Are the query criteria referring to that form or are they bracketed to prompt the user?

 

[Eric the Viking]

How is it opened from the other form now? Are the query criteria referring to that form or are they bracketed to prompt the user?

The form opens with the vba and I have duplicated the form and query so I can open it from the home form with the user prompt. The values are bracketed in the query to prompt the user, I was just after avoiding duplicating stuff and using vba to bypass the prompt and fill in the values, if you see what I mean?

 

[MajP]

Hi thanks for your reply, yes that works for the scenario above. But the "frmEnterD.....Receipts" is also open-end directly from a button on another form that depends on the parameters in the query to locate the record so I solve one problem but cause another.

Replace Me with the form that has the filtered record

DoCmd.OpenForm "frmEnterMembersDebitsAndReceipts",,, "SurName = '" & Forms("SomeForm").Surname & "' AND FirstName = '" & Forms("someForm").FirstName & "'"

Get rid of the parameters as already stated.

 

[pbaldy]

If the desired input values aren't on the form, you can gather them with InputBox then use the results in OpenForm.

 

[Pat Hartman]

FYI, when searching for names, you need to use double quotes to enclose the name string rather than single quotes. Otherwise, you'll get errors on names like O'Neill