I want to use two Combo Boxes on my

[Marcia2]

I want to use two Combo Boxes on my form.

I want the first Combo Box to list some general categories, such as HOSPITAL, CHURCH, and BUSINESS.

I want the values in the second Combo Box to list the specific names of these organizations, such as Burkemont Church, Duke Hospital, First Presbyterian, Morganton Hospital, Stanford Hospital, First Baptist Church, etc.

The catch: I want to be able to somehow assign each value in the second combo box to one of the values in the first combo box, so that when I choose "HOSPITAL" in the first box, the only options that I will see in the second combo box are hospitals (Duke, Morganton, and Stanford). Likewise, when I choose "CHURCH" in the first box, I only want to see First Baptist, First Presbyterian and Burkemont.

I created two additional tables for this. (tell me if I shouldn't have):
I created a new field called "CategoryList," using the Lookup Wizard. I selected option 2 (I will type
in the values that I want), assigned two columns for this field, and typed my list (simplified for our purposes):

Col. 1 Col. 2
CHURCH Burkemont
CHURCH First Baptist
CHURCH First Presbyterian
HOSPITAL Duke
HOSPITAL NC Baptist
HOSPITAL Grace

I clicked on "Next," chose Col. 2 for my field that uniquely identifies the row, clicked on "Next" and named it "CategoryList."

I then added this "CategoryList" field to my form. It has a drop-down box which shows the above rows, but when I choose the the one I want, only the first column (CHURCH) is visible on my form, and I do not see the name of
the second organization column (First Baptist).

I am completely lost. This was so easily done in Lotus Approach (linking one combo box to a selection in another), and I feel like I'm in over my head with Access.

Does anyone know how to do this? If this is a complicated matter involving VBA programming, please also include information on where to enter the programming references you're talking about.

Thanks so much!!!

--
Marcia

 

[LonnieJohnson]

Marcia,

I would only use one table listing all my organization names.

I would have a category column in my table that indicated if this was a church, hospital, etc.

I would make my first combo box using the wizard and opting to get my data from a table. I would pick the table above and select the catergory column. Once you get the combo box set up you will need to go to it's Row Source and change the syntax SELECT DISTINCTROW... to SELECT DISTINCT... . This will give you unique values in your box.

Put a second combo box on your form. When the wizard starts up just hit ESC.

Now in the first combo box's After Update event I would put the following code.

Combobox2.RowSource = "SELECT THENAME FROM THETABLE WHERE" _
& " THECATEGORY = '" & ComboBox1 & "'"

THENAME = The name of the field in your table that has the names of your institutions.
THETABLE = The name of your table that has the institutions and categories.
THECATEGORY = The name of the field in your table that has the categories in it.

Hope this helps. B-) ljprodev@yahoo.com
ProDev
MS Access Applications

 

[Marcia2]

Thanks so much for your reply. I need some more help with this... I may not have entered your code right.

I created two tables, one main table for my addresses (tblAddresses) with my name and address information, and a second table for my categories (tblCategory). In the tblCategory table, I created two fields (fldCategory1Name for my primary category, and fldCategory2Name for my organizations).

I then created a form called frmAddresses where I put my name and address fields. I also created the two combo boxes as you instructed.

In the first combo box (cboCategory1), I changed the RowSource syntax to:
SELECT DISTINCT [tblCategory].[fldCategory1Name] FROM [tblCategory];
Under the Event tab - After Update, I entered the following code:

Private Sub cboCategory1_AfterUpdate()

cboCategory2.RowSource = "SELECT FLDCATEGORY2NAME FROM TBLCATEGORY WHERE" _
& "FLDCATEGORY2NAME=" & cboCategory1 & ""

End Sub

I wasn't sure about entering the quotation marks at the end of your posted instructions... was that two double quotes and a single?? I tried it both ways.

When I open my frmAddresses form, my name and address information is there, and I am able to choose my categories when I click on cboCategory1.

When I try to choose my organizations, however, from cboCategory2, I get an error message: "Syntax error in FROM clause." (Error 3131)

What did I do wrong?

Thanks!!!

 

[LonnieJohnson]

Yes, you will need the single qoutes. Also a couple of spaces. Try this...

cboCategory2.RowSource = "SELECT FLDCATEGORY2NAME FROM TBLCATEGORY WHERE " _
& "FLDCATEGORY2NAME = '" & cboCategory1 & "'"

Good luck B-)
ljprodev@yahoo.com
ProDev
MS Access Applications