Subform display based on Combo Box Selection

[Newbie456]

I was wondering if anyone has written code for this before, and could direct me to that. I would like to display a subform on my main form, based on a selection in a combo box on the main form.

I haven't taken the time to learn to write my own code yet, and thought I would see if it's already been done before I start (time constraints.)

I have a catalog of historic artifacts. I want to provide info on the artifacts, but depending on what type of artifact it is, diffent fields would be necessary. For example:

cboMaterial - options include glass and ceramic

If glass is selected, fields are:
Type, color, fragment, etc.

If ceramic is selected, fields are:
Paste, glaze, pattern, etc.

It's complicated, but they need to be part of a catalog for the site. Using subforms will be the best way, I think.

Thanks in advance!

 

[VicRauch]

You could have several different forms available, and based on what is selected in the combo box, change the SourceObject field to be the correct sub form for the selection in the combo box.

OR

You could hide some fields and unhide other fields within the sub form based on the selection in the combo box.

Either of these solutions would be quite easy to do within VBA.

 

[Newbie456]

That sounds exactly like I would like to do. Perhaps I can eliminate the need for sub forms altogether if I can make fields appear and disappear depending on the selection in cboMaterial.

How, exactly, could I go about doing that?

Thanks a bunch

 

[VicRauch]

The fields that will appear and disappear would be in the sub-form. So, I think you will need the sub-form.
To make a field appear:
[tt]me!subformname.form!fieldname.hidden = true[/tt]
To make it disappear, use false rather than true.

All of this code would be in the event handler for the AfterUpdate event of the combo box.

 

[Newbie456]

I simplified things a bit. I ditched the subform idea, since I was going to have to create those forms anyway. They aren't preexisting, and all my data gets linked to one big catalog.

Instead, I have series of combo and text boxes I want to appear or disappear depending on the selection within one combo box, cboMaterial.

Here is my code (that doesn't work)


Private Sub cboMaterial_AfterUpdate()
If cboMaterial_ = "Glass" Then
cboMatType.Requery
cboMatType.Visible = True
cboColor.Requery
cboColor.Visible = True
cboVessel.Requery
cboVessel.Visible = True
cboGlaze.Visible = False
cboPattern.Visible = False
cboFragment.Visible = True
txtObject.Visible = False
ElseIf cboMaterial_ = "Ceramic" Then
cboMatType.Requery
cboMatType.Visible = True
cboColor.Requery
cboColor.Visible = True
cboVessel.Requery
cboVessel.Visible = True
cboGlaze.Visible = True
cboPattern.Visible = True
cboFragment.Visible = False
txtObject.Visible = False
ElseIf cboMaterial_ = "Metal" Then
cboMatType.Requery
cboMatType.Visible = True
cboColor.Visible = False
cboVessel.Visible = False
cboGlaze.Visible = False
cboPattern.Visible = False
cboFragment.Visible = False
txtObject.Visible = True
Else
cboMatType.Visible = False
cboColor.Visible = False
cboVessel.Visible = False
cboGlaze.Visible = False
cboPattern.Visible = False
cboFragment.Visible = False
txtObject.Visible = True
End If
End Sub

The final group is "default" - an open text box for a description of the item if it doesn't fall into one of these three catagories (ie wood, plastic, rubber, etc.) It keeps going to default no matter what is input into cboMaterial. What's wrong?

 

[PHV]

I wonder:
If cboMaterial[!]_[/!] = "Glass" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VicRauch]

Assuming your cboMaterial field has one of these words in it, not an ID number, then you code should work if you take the "_" off the end of your field name. PHV hinted at that with cboMaterial[red]_[/red]

 

[Newbie456]

That worked! Wonderful!

 

[PHV]

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Newbie456]

What is option explicit instruction?

It's still buggy. For example, selections made in my cbo boxes aren't showing up when you navigate away from the record. They are saved in the table, but the field looks blank on the form when you go back to it (until you pull down the drop down menu.)

Also, a problem I noticed is that if you select "Ceramic" then change you mind (for example, to "bone") if you made any changes (for example, a glaze) then these are saved to the table, the cbo's disappear, and you can't change these. If it goes to bone, ceramic options shouldn't be available.

I know, I got myself in over my head, but I'm learning!

Thanks.

 

[lars7]

Above your code in Visual basic there are 2 combo boxes in the first you will find (General) and in the second (Declarations) then put this below:

Option Compare Database
Option Explicit