A parameter for a rowsource of a combobox

[odrap]

On one of my forms i have two subforms. The second subform is a subform of the first subform.
The purpose of the parentform is to display suppliers of products. A supplier can be choosen via a combobox on this form.
To make the link between the parentform and the first subform i use the control SupplID. The purpose of the first subform is displaying the OrderId and OrderDate of an order of products with the current supplier as destination.
The second subform is a subform of the first subform. Is serves as orderdetail ,and as link between this subform and the first subform serves the control OrderID.
On the second subform i have a combobox that makes it possible to choose the product that has to be ordered. Now i want to arrange the rowsource of this combobox in such a way that the list of products the combobox will display is in accordance with the current supplier displayed in the parent form. Therefore have a parameter in the query meant as rowsource of the combobox that reference the field SupplID in the parentform . e. g " = Forms!frmSupplier!SupplId".
Runing this query in the query designwindow, results in a demand for a SupplierlD, and after that it displays only the products this supplier delivers, what is just what i want. But when i set the rowsource for the combobox in the second subform equal this query and open the forms that doesn't work; I get a demand entering a value for this parameter, but even an correct input doesn't gives a good result. It seems that the query doesnt find the value SupplID in the forms mentioned in the parameter. What's wrong?

 

[Vigilante001]

Hi odrap...

Are you requering the combobox after you make a selection with your supplID? If you don't requery it, the control might not show the correct list of products.

I would have the query that is linked to the products combobox directly reference the SupplID combobox on the main form, and not a typed field SupplierID. It'll save you a step. Then, make sure the main form has the Has Module property set to "On", and on the AfterUpdate event for your SupplID combobox, put some code there that requeries the products combobox. For example:

Forms!frmSupplier!FirstSubform.Form!SecondSubform.Form!cmbProducts.ReQuery

That way, each time you change the SupplID, the products requeries itself automatically.

I attached an example if it helps, and it may help you figure out if I got your question right... in it, Form1 would be your parent form.

 

[odrap]

I like to thank you very much for the help.
Tomorrow i examine the sample you sent me, and if i still have questions about it, i let you know.
Have a good night!
greetings from Brugge in Belgium.

 

[Vigilante001]

Sounds good.. just let me know. Good luck from Cleveland, Ohio, USA!

 

[Vigilante001]

Hi odrap... did it work our for you?

 

[odrap]

It worked out fine. Thank you a lot !!
By the way;
The combobox i mentioned i my question is the control with tab number 0 in my subform. To make it impossible for the user to leave this control without selecting a item, I tried to put some code in the exit event of this control to check for a empty artnr,and if so to alert the user for this fact. But since this event takes also takes place every time the form opens for the first time, that seems not to be the good place to make the check. Where is the best place where i can put this code to eliminate a empty artnr or is there another and better way to check for a ampty artnr?

 

[Vigilante001]

I would set the .Visible property of the 2nd tab = No until the user makes a selection from the combobox. Based on your Mainform, subform1, subform2 setup, and assuming that page/tab 0 on subform2 is the first to see, and page/tab 1 is the one you want blocked until dataentry...In the subform, I would have some code that looks similar to this:

Private Sub Combo2_AfterUpdate()

    If Combo2 <> "" Then
        Forms!MainForm!SubForm1.Form!SubForm2.Form!tabcontrol.Pages(1).Visible = True
    Else
        Forms!MainForm!SubForm1.Form!SubForm2.Form!tabcontrol.Pages(1).Visible = False
    End If

End Sub

Private Sub Form_Load()
    Forms!MainForm!SubForm1.Form!SubForm2.Form!tabcontrol.Pages(1).Visible = False
End Sub

That way the page in question is hidden until a combobox value is established. Let me know...

 

[odrap]

Thank you very much for the help.

 

[Vigilante001]

Anytime...