SubForm Help Sorely Needed

[mfaj5]

SubForm and Combo Box

Hi There-

I am new to the forum. I am hoping someone can help me.

I built a contact database.

The first field in the form is a combo box with only 5 possible values (5 different types of contacts my company does business with).

Each of the five values has a completely unique set of fields to be filled out by the user to accumulate data. Rather than build a giant ugly form with 5 subforms, I would rather have the corresponding subform appear when the user selects the value in the combo box.

Does anyone know how to do this? Is ther a better way to accomplish this same idea? My guess is that it is not too hard, but I can't find the answer anywhere.

Your help would be greatly appreciated!

Thanks!
mfaj5

 

[Tay]

Firstly, build all of the 5 forms you want to use as your specific subforms (eg, frmOne, frmTwo etc). Then place a subform control in your main form, calling it for example, frmContactsSubform, and link it to your main form. ContactType is the combo on your main form you want to choose from.
Then in the On Current event of the main form, add what is below, changing the text to match whatever your field/subform names are.

Private Sub Form_Current()

Select Case Me!ContactType

Case "ContactTypeOne"
Me.frmContactsSubform.SourceObject = "frmOne"

Case "ContactTypeTwo"
Me.frmcontactsSubform.SourceObject = "frmTwo"

End Select

Me!frmContactsSubform.Requery

End Sub

this should then check to see what value is in your combo and display the relevant form. Hope I haven't confused you too much.

 

[mfaj5]

Thanks.

I will give it a try and let you know if I need more help.

 

[Neilamc]

Tay

First of all, apologies, I am very new to this but I have been attempting to do something very similar to mfaj5. I'd also appreciate a helping hand.

I tried your method but it failed in a few different ways.

The last attempt didn't produce error messages but selection with the combo didn't produce any objects in the subform.

I don't think I linked the main form and frmContactsSubform correctly. Could you elaborate further?

Is an object required within frmContactsSubform to begin with?

Is there special formatting required for frmOne, frmTwo etc.?

Many thanks

NM

 

[Tay]

So when you chose a contact type from your combo, the subform was completely blank?
I presume you have an 'index' field such as ContactID which is both in your main form and in your subforms. This should be the field that you use to link the main form and subform. Maybe you could explain a little further what you did, and what tables you have etc. You shouldn't need any specal formatting, so I don't think that's the problem.

 

[Neilamc]

OK

My aim is to produce a library system - something akin to directory filing - in which the choices availible in combo (B) are dependent on those made first in combo (A).

Eg. Choose the building: 1 or 2

Choosing building 1 in combo (A) will produce combo (B) with the further choice of colour (pink or Blue)

Where choosing building 2 in combo (A) will produce combo (B) with the choice of colour (green or red)

I wanted each of these choices to be recorded as a single record in the long run.

Does that make sense? Is there an easier way of doing this?

Could you suggest tables that would be required?

As I mentioned, I am VERY new to this so please feel free to simply suggest a good book!

Sorry mfaj5, I should have started a new string.

NM

 

[Tay]

It's probably worth you searching on the Forums for 'cascading combo boxes' as this will give you plenty to refer to.
I would have a table for Building Type (tblBuildingType) and have a numerical index field (BuildingTypeID) so that 'building1' = 1 and 'building2' = 2.
Then have another table for the colours (tblBuildingColour) with 'BuildingColour' as one field and 'BuildingColourID' as the other. so Pink = 1, Blue = 2 green = 3 red = 4.
Then make another table to link these two together with the fields BuildingTypeID and BuildingColourID.
So,
(BuildingType1)1---1(Pink)
(BuildingType1)1---2(Blue)
(BuildingType2)2---3(Green)
(BuildingType2)2---4(Red)

Then create a query to link all of the tables by the ID numbers
Base the combos on the query.
In the first combo's rowsource, type

SELECT DISTINCT query.BuildingType, query.BuildingTypeID FROM query;

then in the second combo's rowsource, type

SELECT query.BuildingColour, query.BuildingType, query.BuildingTypeID, query.BuildingColourID FROM query WHERE (((query. BuildingType)=[forms]![form]![cmboBuildingColour])) ORDER BY query.BuildingColour;

and change 'query' to whatever you name your query. HTH

 

[Neilamc]

I think i understand. I'll try that and the search you suggested but it'll probably take me a while to get round to it. Will start a new thread if I get stuck again.

thanks for you help Tay (and speed of response!)

NM