Grab combo box selection and put in a query

[rikstick]

Hi, I have a simple Access database 2003 .mdb. It has one table and one form. I will need to grab a combo box selection and run a query based on the combo box selection.

For example let's say that the user selects the course number CIT3311 from the combo box, then when I click the command button, I would like to have the program populate a label with a field called description which is on the same row as the course number value in a table.

Any help will be greatly appreciated I program mostly in C++ so VBA is not my strong suit.

Thanks

 

[genomon]

Without knowing any of your schema, I would think DLookup would work. If you post back table structures with field names I can show you how to attach the code to the command button's OnClick() event. If DLookup() doesn't do it, we can write some SQL and work with a recordset.

Cogito eggo sum – I think, therefore I am a waffle.

 

[dhookom]

You should only need to display the description. Attempting to store the course number and course description in a related table would be a bad idea.

I'm not sure how you can have only one table when you have a combo box. The combo box Row Source is generally from a second table. I assume the Row Source is something like:

SELECT [CourseNumber], [CourseDescription]
FROM tblCourses
ORDER BY [CourseDescription]

If your combo shows only the number, you can use a text box with a control source like:

=cboCourseNumber.Column(1)

If you can't work this out, come back with these properties from your combo box:
Name:
Row Source:
Column Count:
Bound Column:
Column Widths:


Duane
Hook'D on Access
MS Access MVP

 

[rikstick]

Thanks a million!! I would prefer the code method.

My table name is: CourseDescription and it has 3 fields:
CourseNumber, CourseName and CourseDescription2. CourseNumber is the primary key.

The combo box object on the form has the name cmbCourseNumber and the labels to populate are called lblCourseDescription and lblCourseName.

The button name is cmdButton. The cmbo object will contain the field names from the field CourseNumber. And once the user clicks the command button the program will populate the the labels lblCourseDescription and lblCourseName with the appropriate values from the table based on the course number chose via the combo object.

Thanks again

 

[dhookom]

Why do you want to write code and do things the hard way? Why do you want to set captions of labels? You are using MS Access, not VB or some other environment where this functionality is more difficult.

What do you mean by "combo object"? We just call them combo boxes. Just set the Row Source of your combo box to:

SELECT CourseNumber, CourseName, CourseDescription2
FROM CourseDescription
ORDER BY CourseNumber;

Assuming the name of the combo box is cboCourseNum
Then use two text boxes with these properties:
Control Source: =cboCourseNum.Column(1)
Locked: Yes
Enabled: No

and
Control Source: =cboCourseNum.Column(2)
Locked: Yes
Enabled: No

You don't need a command button to update the text boxes. As soon as you make a selection in the combo box, the text boxes will display the related values. That's how Access works.

If you have other needs, it would help if you described the context of what you are attempting to accomplish.


Duane
Hook'D on Access
MS Access MVP

 

[rikstick]

Thanks for your input but I have one comment. Everything that you put on a form is an object. Textboxes are objects, labels, combo boxes, listboxes, etc. They all have an inherited structure that is derived from a class and thus they are indeed objects.

 

[dhookom]

I don't doubt that they are all objects. I understand their properties, methods, events, etc. It's just that when developing in the Access environment, we get used to referring to them as combo boxes, list boxes, text boxes, etc. There is no need to make these more complex.

Access "objects" expose some cool properties that make writing code unnecessary.

Duane
Hook'D on Access
MS Access MVP