Dynamic Label Captions

[posterman]

I have two tables “tblCategories” and “tblParts” that I am using with a series of forms as listed below.

1. “frmSelectCategory” – This form has a combo box with a list of part types, and a command button. A part category is chosen from the combo box and when the command button is clicked it runs “qryClassification” and opens a new form “frmAddNewPart”.

2. The “frmAddNewPart” has two combo boxes whose contents are determined by qryClassification. It also has 15 combo boxes for choosing different part attributes whose contents are also determined by “qryClassification”.

There are two things that I am trying to accomplish with the 15 combo boxes that I am having trouble determining.

a. Not all part categories have 15 attributes; I would like to only have those attribute combo boxes that have caption data in “tblCategories” show up on the form.

The values to go into the combo boxes come from “qryAddNewPart” based on “tblParts”. “qryAddNewPart” also queries “tblCategories” to determine what the combo box label captions should be.

b. I would like to have the 15 combo box label captions change depending on the results of “qryAddNewPart” and as noted above only show those attributes that have caption data in them
​

To try to clarify a little, “tblCategories” is queried by “qryAddNewPart” if an attribute ISNotNull it should be shown on “frmAddNewPart” and the label caption should be the value in the attribute field.

“tblParts is queried by “qryAddNewPart” to supply the attribute values to go into the combo boxes determined above.

I know this sounds confusing, let me know if you need more info, any help would be greatly appreciated.

Thanks in advance.

Paul

 

[John Big Booty]

I have two tables “tblCategories” and “tblParts” that I am using with a series of forms as listed below.

1. “frmSelectCategory” – This form has a combo box with a list of part types, and a command button. A part category is chosen from the combo box and when the command button is clicked it runs “qryClassification” and opens a new form “frmAddNewPart”.

2. The “frmAddNewPart” has two combo boxes whose contents are determined by qryClassification. It also has 15 combo boxes for choosing different part attributes whose contents are also determined by “qryClassification”.

...........

This part sounds like a Cascading Combo Box set up, There are a number of examples available here

..........

There are two things that I am trying to accomplish with the 15 combo boxes that I am having trouble determining.

a. Not all part categories have 15 attributes; I would like to only have those attribute combo boxes that have caption data in “tblCategories” show up on the form.

.....​

You will need to work out a logical test to determine which Combo boxes to show and put that in the on load event of your form. You can then show or hide each Combo as required using;

If Condition1 Then [COLOR="Green"]'Replace Condtion1 with your logical test[/COLOR]
     Me.ComboName.Visible = True
     Me.ComboName2.Visible = False
Else
     Me.ComboName.Visible = False
     Me.ComboName2.Visible = True
End IF

To set the Combo Box Captions you will once again need a logical test, and then use;

Me.Combo_Label.Caption = "Your Custom Caption"

Of course you can probably combine this with the test that decides which Combo Boxes to show.​

 

[posterman]

John thanks for your input, I’ve used your suggestion for making the fields visible or not and have that working. Your suggestion for the caption works well if I had a set number of captions for each field, however, that’s not the case here.

I have a form “frmSelectCategory” with a combo box listing all the available part categories. The user picks a category from the combo box and then clicks the “btnAddNewPart”. When “btnAddNewPart “is clicked it runs two queries “qryClassification” and “qryAttributes” and opens a new form “frmAddNewPart”.

When “frmAddNewPart” loads it has 21 fields split 6 in the top half of the form and 15 in the bottom half of the form. The 6 in the top half consist of 2 combo boxes whose choices are determined based on the category chosen and “qryClassification”, 3 are fields for direct user input, and the last one is a Yes/No combo box. These are all working fine.

The issue is with the 15 in the bottom half of the form. There are two things going on with these combo boxes. First the choices in the drop down lists are based on the choice of category and "qryClassification" this works correctly.

The issue is with the captions for the combo boxes. The default caption for them is Attribute 1 to Attribute 15. Depending on the category chosen in the first form “frmSelectCategory” there could be anywhere from 1 to 15 attributes listed. "qryAttributes" has 17 columns, CAT_ID, CATEGORY, ATTRIB_1 to ATTRIB_15. The values for ATTRIB_1 to ATTRIB_15 in “qryAttributes” change depending on the category chosen. What I would like to do is have the captions for Attribute 1 to Attribute 15 in “frmAddNewPart” change depending on the values for ATTRIB_1 to ATTRIB_15 of “qryAttributes”.

I’ve tried several ideas none of which worked, is there a way to accomplish this or am I beating a dead horse?

Thanks in advance for your help.

Paul

 

[John Big Booty]

Are you able to link the Caption to the selection in the Combo Box? You could possibly collect it using the query that populates the combo and then hide that column of the combo and use it to populate the combo caption.

Me.Combo_Label.Caption = Me.ComboName.Column(#)

Replace # with the appropriate column number that holds the data you want as the label, Remember that the column numbers start at 0 (zero),