Managed to cascade my combo boxes on one form... cant on the other!

[aqueousdan]

Could someone take a look at my database please.



I have two forms. One for storing engineer data and the other for breakdowns (MBRs) in a factory. The engineer form is based on the engineers table. The breakdowns (MBR) form is based on a query (so I can work out total time of a breakdown based on a start and stop time)

In the engineer form I have made it so the Factory combo box populates based on what is chosen from the Site combo box. This is done using a query in the row source and then using a requery event procedure on the After Update of the Site combo box.

This all works nicely but when I try and do exactly the same thing on my breakdown (MBR) the Site combo box doesnt populate.

Can anyone take a look and see what im missing?

Thanks for your time,

Dan B

[ItsMe]

The first thing that jumps out at me is that you are using the following in your combo's RowSource

SELECT Factories.FactoryID, Factories.FactoryName, Factories.SiteName
FROM Factories WHERE (((Factories.SiteName)=[Forms]![MBRForm]![Site]));

This part of the SQL is where you need to reference the control name of your combo ...
[Forms]![MBRForm]![Site]
You do not have a combo named 'Site'. I see one named, SiteName, though.

[ssanfu]

Look at the differences between the two form:

Form = EngineerForm

combo box name = "Site"
Control source = "SiteName"   (text type field)
Row Source = "SELECT Sites.SiteName FROM Sites; "
Bound column = 1
Column count = 1
Column widths =


combo box name = "Combo33"  (FactoryName)
Control source = FactoryName   (text type field)
Row Source = SELECT Factories.FactoryName, Factories.SiteName FROM Factories WHERE (((Factories.SiteName)=[Forms]![EngineerForm]![Site])); 
Bound column = 1
Column count = 1
Column widths = 

------------------------------------
Form = MBRForm

combo box name = "Site"
Control source = SiteName   (text type field)
Row Source = SELECT [Sites].[SiteID], [Sites].[SiteName] FROM Sites;  
Bound column = 1
Column count = 2
Column widths = 0";1"


combo box "Combo186"  (FactoryName)
Control source = Unbound
Row Source = SELECT Factories.FactoryID, Factories.FactoryName, Factories.SiteName FROM Factories WHERE (((Factories.SiteName)=[Forms]![MBRForm]![Site])); 
Bound column = 1
Column count = 2
Column widths = 0";1"

For form MBRForm,
the combo box "Site" bound column is "SiteID", a Long Integer.
for "Combo186" (FactoryName), in the WHERE clause, you are comparing "Factories.SiteName" (a text field) to "[Forms]![MBRForm]![Site]", a Long Integer.
The recordset will always be 0 records.



In combo box "Site", your control source is a text type field (SiteName), but the bound column is "SiteID", a Long Integer....?????
In combo box "Combo186" (FactoryName), your control source is UNBOUND; if it was bound to text type field "FactoryName", you would be storing "FactoryID" ( a number) in a text type field.


Also, you should change the after update code in EngineerForm to

Private Sub Site_AfterUpdate()
    Me.Combo33.Requery
    Me.Combo33 = Empty
End Sub

and for MBRForm

Private Sub Site_AfterUpdate()
    Me.Combo186.Requery
    Me.Combo186 = Empty
End Sub

otherwise you could end up with invalid factory names for a Sitename.

[aqueousdan]

Thankyou very much. That is very helpful and has worked perfectly!