Automatic Parameter Prompts Between Forms (1 Viewer)

[JoeyB_99]

I am using Access 2007.

Form A is based on a query with a parameter prompt for Formatted Cost Code. Later on I developed Form B based on a query that also has a parameter prompt for Formatted Cost Code.

I have Form A in the Main Menu. I then set up a button for the user to open Form B.

As I went through the wizard to create this button I checked "open the form and find the specific data to display", and for matching data I clicked on the Formatted Cost Code field for Form A and Form B.

I want the user to be able to open Form B based on the value they used when opening Form A and not have to enter the Formatted Cost Code again!

This is not happening. When the user clicks on the button to open Form B they are being prompted for the Formatted Cost Code.

When I checked the macro created under the On Click property of this new button it shows the following under the Where Condition,

="[Formatted Cost Code]=" & "'"&[Formatted Cost Code]&"'"

Is this correct? Do I need to change something here?

 

[AccessMSSQL]

Try changing it to this:
="[Formatted Cost Code]=" & "'"&[Forms]![NameOfFormA]![Formatted Cost Code]&"'"

make sure you change the form name to the name of your FORM A

 

[JoeyB_99]

Thanks AccessMSSQL but it did not work.

I checked and double-checked my typing. I am still getting prompted for the parameter.

Can you think of anything else to try?

 

[AccessMSSQL]

Check Form B, when you open this form by itself from the navigation pane do you get prompted for the parameter? If not, then make sure Form B actually has a field in it called [Formatted Cost Code].

 

[JoeyB_99]

I have double-checked Form B. Yes, when I open it separately I am prompted for the formatted cost code value/field.

Yes it does have this particular field and it is a text field.

Does it matter if the record source for this form is a query built inside the form?

 

[AccessMSSQL]

No, but does your query behind Form B have a parameter in it? You should remove it if it does. Also, check Form B filter properties. Make sure there is nothing in the filter property in Form B

 

[JoeyB_99]

Okay, this sounds good but a little confusing. I want to be able to run the Form B button by itself sometimes.

also, yes there is [Formatted Cost Code] in the filter property of Form B. Should I remove this?

 

[AccessMSSQL]

Yes. remove that from the filter. This is what is causing your prompt for the parameter value.

 

[JoeyB_99]

Okay. Sorry, I was wrong. The filter property has [Formatted cost Code] = 'xxxx'. It seems to be storing that most recent prompted value that the user inputs.

Is there another property that I need to turn off or set to false?

Also, how can I keep the prompt when I want to run this form by itself?

 

[AccessMSSQL]

Well, honestly, I tend to stay away from parameterized queries or prompts. I control any kind of input or search in a separate form. So you could create an interim form to open one or either form by itself. Sorry to send you in a different direction here but I would create a new unbound dialog form that you open up from a menu. On that form just have two buttons - one to open Form A and one to open Form B. Also on this form, create an undbound textbox or drop down list where the user can either enter the formatted cost code or select one. In your On Click event of either button open the form = to the value the user input in the textbox. You can use VBA for this if you want - I prefer not to use Access automated macro builder - I actually try to stay away from using macros.

If you choose to use vba it would be simply this code in the OnClick event of button A or Button B

Private Sub btnFormA_OnClick()
if txtFormattedCode <> "" then 
   DoCmd.openForm "frmA", , , "[FORMATTED COST CODE] = '" & me.txtFormattedCostCode & "'"
end if

IF they click button B just use the same code as above for the Button B onclick event except change the form name argument.

 

[JoeyB_99]

Thank you AccessMSSQL, I appreciate all the effort and input you have provided.

I have been thinking about abandoning the parameter prompt approach and doing something similar to your suggestion.