Populating a combo box from a Function

[CES]

All,
I have to combo boxes on my form the first Combo Box is named "Category" and the second Combo Box is named "subCategory" based on the value chosen in the "Category" Combo Box I want to filter the values of the "subCategory" combo box.
I've been able to find numerous examples of populating a combo box via the Filter by Form method,

Row Source = SELECT aField1 FROM aTable WHERE (((aTable.aField1)=[Forms]![F_Combo]![Advisor_Combo]));

But I can't seem to find any that show an example of how to accomplish the same task via VBA. If anyone knows of simple example I would appreciate you providing me with a link. Thanks in advance. - CES

 

[Douglas J. Steele]

In the AfterUpdate event of Category, use:

Private Sub Category_AfterUpdate()

Me.subCategory.RowSource = "...."

End Sub

replace "..." with the appropriate SQL

 

[CES]

In the AfterUpdate event of Category, use:

Private Sub Category_AfterUpdate()

Me.subCategory.RowSource = "...."

End Sub

replace "..." with the appropriate SQL

Douglas,
1 - I was wondering if that held true for all of the other properties in the property sheet of the control?

By that, I mean, if I wanted to change;

Background Color --- Me.SubCategory.BackgroundColor = "Some Color"
Text Align --- Me.SubCategory.TextAlign = "Left"

2 - If the answer to 1 is yes, can you also do the same thing for events?

Me.SubCategory.OnChange = "I'm not sure what would go here if you are attempting to call a function()"
but I'm assuming that a macro could be called by just using the macro name
Me.SubCategory.OnChange = SomeMacroName

3 - Is their a way of converting a Combo Box to a Text Box via code.

Anyway as always... Thank you, I've been looking for that solution most of the afternoon... - CES

 

[CES]

In the AfterUpdate event of Category, use:

Private Sub Category_AfterUpdate()

Me.subCategory.RowSource = "...."

End Sub

replace "..." with the appropriate SQL

Douglas,
1 - I was wondering if that held true for all of the other properties in the property sheet of the control?

By that, I mean, if I wanted to change;

Background Color --- Me.SubCategory.BackgroundColor = "Some Color"
Text Align --- Me.SubCategory.TextAlign = "Left"

2 - If the answer to 1 is yes, can you also do the same thing for events?

Me.SubCategory.OnChange = "I'm not sure what would go here if you are attempting to call a function()"
but I'm assuming that a macro could be called by just using the macro name
Me.SubCategory.OnChange = SomeMacroName

3 - Is their a way of converting a Combo Box to a Text Box via code.

Anyway as always... Thank you, I've been looking for that solution most of the afternoon... - CES

 

[Marshall Barton]

I have to combo boxes on my form the first Combo Box is named "Category" and the second Combo Box is named "subCategory" based on the value chosen in the "Category" Combo Box I want to filter the values of the "subCategory" combo box.
I've been able to find numerous examples of populating a combo box via the Filter by Form method,

Row Source = SELECT aField1 FROM aTable WHERE (((aTable.aField1)=[Forms]![F_Combo]![Advisor_Combo]));

But I can't seem to find any that show an example of how to accomplish the same task via VBA. If anyone knows of simple example I would appreciate you providing me with a link. Thanks in advance. - CES

If you set the RowSource to that kind of query, then all you
need to do is use:
Me.cboSubCategory.Requery
in cboCategory's AfterUpdate event (and most likely in the
form's Current event).

 

[Douglas J. Steele]

Douglas,
1 - I was wondering if that held true for all of the other properties in
the property sheet of the control?

By that, I mean, if I wanted to change;

Background Color --- Me.SubCategory.BackgroundColor = "Some Color"
Text Align --- Me.SubCategory.TextAlign = "Left"

Yes.

However, it's BackColor (not BackgroundColor), and it's a Long Integer, not
a string. You can use the RGB function to convert a known colour from RGB
notation to the appropriate Long, or there are some intrinsic constants
(vbBlack, vbBlue, vbCyan, vbGreen, vbMagenta, vbRed, vbWhite and vbYellow)
or you can learn what the colour is by setting a particular colour and then
looking at the value that was set. There are also some predefined constants
that will translate to whatever System Colors have been defined by the user.

Similarly, the TextAlign property is a numeric one: 0 for General, 1 for
Left, 2 for Center and 3 for Right.

In general, whenever your cursor is in a particular property in the
Properties dialog, F1 will take you to help for that property, which will
give you details about what's required.

2 - If the answer to 1 is yes, can you also do the same thing for events?

Me.SubCategory.OnChange = "I'm not sure what would go here if you are
attempting to call a function()"
but I'm assuming that a macro could be called by just using the macro
name
Me.SubCategory.OnChange = SomeMacroName

If you're trying to call a function (not a sub), you can put

Me.SubCategory.OnChange = "=MyFunction"

(include the equal sign)

For more sophistication, take a look at the CreateEventProc method of the
Module object.

However, recognize that this is an unusual requirement.

3 - Is their a way of converting a Combo Box to a Text Box via code.

Not that I'm aware of, but again, why would you need to? That would usually
be a one-time thing: no reason not to do it manually through the GUI.

 

[Ken]

The way I did this was using VBA.

I had the first combo box change the value of an invisible text box to its value.

Based on the value of the the text box i did a select case statement to full the second combo box based off of the first combo boxes selection.

Ken

10-Jan-07 04:28:39

All,
I have to combo boxes on my form the first Combo Box is named "Category" and the second Combo Box is named "subCategory" based on the value chosen in the "Category" Combo Box I want to filter the values of the "subCategory" combo box.
I've been able to find numerous examples of populating a combo box via the Filter by Form method,

Row Source = SELECT aField1 FROM aTable WHERE (((aTable.aField1)=[Forms]![F_Combo]![Advisor_Combo]));

But I can't seem to find any that show an example of how to accomplish the same task via VBA. If anyone knows of simple example I would appreciate you providing me with a link. Thanks in advance. - CES


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com