Parameter Request - Loading main form with Subform Query

[Kevin Broecker]

Good morning all,



I am struggling to resolve an Form/Subform issue in Access 2003 where the subform has a combobox and criteria linked to two other controls. My end goal is to be able to load this Form/Subform and enter values in the two controls of the subform that will fill the value in the CboBox.

My MainForm has 1 SubForm with a Combo box that is intended to be automatically populated based on inputs in 2 previous controls within the subform. When loading the MainForm I am requested to enter a parameter value for each of the controls listed as criteria in the Combobox SQLQuery.

If I run the subform by itself the Combo box works as intended. (If the criteria in the syntax is listed as follows Forms![SubformName].[cboControlName]. I have tried changing the criteria syntax to Forms![MainFormName]![SubformName].[cboControlName] and loading the main form but I am prompted for the parameter value.

My end goal is to be able to load this Form/Subform and enter values in the two controls of the subform that will fill the value in the CboBox. I have found a lot of suggestions on the Net regarding AfterUpdate and OnLoad events but it always asks for the Parameter at MainForm load.

Thanks for any assistance you can provide.

Best regards,
Kevin

[Kevin Broecker]

Not sure if this will help or not but this SQL Statement that works when just running the subform. When I try to run this from the Parent Form it asks for the two parameters.

SELECT qryBulkCorrectionFactor.ID, qryBulkCorrectionFactor.Proof, qryBulkCorrectionFactor.Temp, qryBulkCorrectionFactor.[Correction Factor] FROM qryBulkCorrectionFactor WHERE (((qryBulkCorrectionFactor.Proof)=[Forms]![BulkReceiptSubform].[ProofRnd]) AND ((qryBulkCorrectionFactor.Temp)=[Forms]![BulkReceiptSubform].[TempRnd]));

[John_G]

When referring to a control on a subform, the syntax is this:

Forms![MainFormName]![SubformContainerName].form.[cboControlName]

where SubformContainerName is the name of the control on the main form that contains the subform - it is not the name of the subform itself.

[John_G]

Here is the reason it asks for the parameters:

When a form is used as a subform, it does not become a part of the Forms! collection, i.e. it is not an "Open" form, so the reference to
[Forms]![BulkReceiptSubform] is not recognized.

Try the syntax I showed you above.

[Kevin Broecker]

John_G,

Thanks for the proper syntax. That has everything working.

I appreciate your help.

Kevin

[ravindrababup]

Greetings John_G,

I am new Access User and struggling to resolve same Issue.

I have a individual form (Work Order) created from a Query_Work Order.
The form "Work Order" has two combo boxes. Combo1 , Combo2. I was able to create onload, on change events successfully.
I used the following:

Private Sub Combo1_Change()
Forms("Work Order").Requery
End Sub


Private Sub Combo2_change()
Forms("Work Order").Requery
End Sub


Private Sub Form_Load()
Forms("Work Order").Requery
End Sub


=============================================


Expression for Default values


combo1=DFirst("[Categories]","Categories")
combo2=DFirst("[ship]","ship")




However When I wanted to open the "WORK ORDER" form using Navigation, I am getting errors

-- Asking input Parameter values
-- Form does not load with records
-- Form does not requery with change events

Please help me in resolving this, I tried all possible syntaxes and my deadline is tomorrow.

Thanks,
Ravindra

[John_G]

You didn't provide a great deal of information about what you are doing, so I have quite a few questions.

- First, are you using the combo boxes to limit or select which records you want to see? If not, what are you using them for?
- What is the record source for the form?
- Do you have a filter set for the form?

Asking input Parameter values

- Have you been able to determine which query or form control is asking for parameters?

Form does not load with records

There could be a number of causes for that. Is the form's Data Entry property set to No? Is there an initial filter on the form?
Does the form's record source show any records if used outside the form, i.e. if you run the query by itself, does it work?

If you can give more information on at least some of these questions we'll be better able to help you.

[ravindrababup]

Dear John, Thanks for the reply... the Time since I posted I am eagerly waiting for your reply.

I am using the two comboxes combo1,Combo2 boxes to filter and display records on the form. The record source for this form is Query Work Order. The value from these forms is passed as Query Criteria to respective fields.

Forms![Work Order]!cboworkorderselectcombo1
Forms![Work Order]!cboworkorderselectcombo2

I have set default values for these two combo boxes on form load. This is perfectly working fine.


I created a Navigation form Name : Dashboard. On this tab I added Work Order, intended to open the Main Work Order Form.
However this is asking the parameter inputs for these two combo boxes. Also when opened it does not refresh with change events.

Please suggest

[ravindrababup]

1. The Query, which is the record source of Work Order form doesnot work on its own:
It ask for the parameter values that were entered as criteria :
Forms![Work Order]!cboworkorderselectcombo1
Forms![Work Order]!cboworkorderselectcombo2

2. The intial filter on form is controlled by default values of combo1,combo2. I used Dfirst function to set default values.
3. The form Data entry was set to NO

[John_G]

Try using the After Update event of the combo boxes instead of On Change
Try removing the Requery in the On Load event - I don't think you need it, and it might be causing the parameter prompt. The form will always run the query when it opens anyway.

Let's look at the combo boxes. What is the row source for them, how many columns do they show, and what is the bound column? (are there any hidden columns in the combo boxes?) I ask that to see if the form's source query is using the values you expect it to.

[ravindrababup]

Try using the After Update event of the combo boxes instead of On Change
Try removing the Requery in the On Load event - I don't think you need it, and it might be causing the parameter prompt. The form will always run the query when it opens anyway.

Let's look at the combo boxes. What is the row source for them, how many columns do they show, and what is the bound column? (are there any hidden columns in the combo boxes?) I ask that to see if the form's source query is using the values you expect it to.

The combo boxes row source is from a table Category, Ship respectively. I arranged the table such a way that Column1 is always on top.
This is the value submitted to combo box on form and then as criteria to Query.

I believe when opening from Navigation, the Query Criteria is not getting the parameter values. These combo box1, combobox2 are actually defined on the form Work Order. I think the syntax in the query is not able to identify that!!

[John_G]

Instead of putting the criteria in the query, you could put the criteria in the Filter property of the form. The syntax is the same as a SQL where clause but without the WHERE. Then, in the On Open event of the form, set Me.FilterOn = True to turn on the filtering.

I have not tested that, but it should get rid of the parameter prompts.