how to use subform in my combox 1

[longmatch]

Dear Friends:
I just started to use Access to create a form for my database input form. I have a question which still is unsolved for several weeks. Hoping I can clear this question by your help.
I was trying to use a combox to let my customers to input their data. But there is still more choices in each combox selection items. How can I design this form so that when my customer click combox items, it will popup the related choices.
In my database, I need the residents to input the diseases their patients have. The diseases are first divided into several category such as surgery, pediatrics, Psychological disorder and so on. In each category, there still have lot of diseases.
I am not sure whether I explain my question clearly. Thank you for your help.

Haijun

 

[MangroBongacello]

Hi, Haijun

I'm not sure if this is what you are after. If I understand your post correctly you want, when the user selects a category of disease, in the second combo box displayed only those diseases, that are of the selected category. I will try to suggest how to solve this, but if this is not the problem you have, well...

I guess you have 2 tables like this among others:

Table DiseaseCategories

Field: CategoryID
Field: Description

Table Diseases

Field: DiseaseID
Field: Description
Field: CategoryID (foreign key to relate to categories)

Now, if I'm on the right track, the first combo box on the form is where categories are shown.

The Record Source property for this combo box should be

select * from DiseaseCategories;

The Bound Column property must be bound to field CategoryID.

For the second combobox, where diseases are displayed, the record source property should be

select * from Diseases where CategoryID = Forms!YourFormName!Combobox1Name;

In the Combobox2 OnEnter event write this line of code which will requery the combobox to show only the required diseases

Combobox2Name.Requery

Be sure to use your names of fields, controls, tables and forms.

I hope this will be of use to you. If I totally missed the point here, I apologize. Let me know anyway.

Mangro

 

[longmatch]

Dear Mangro:
I thought you understand me right. You definitely know what I am meaning. Unfortunately I can not follow you. My questions are:
(1) how can I put two combox together so that it looks like one. When I selected disease category, the list of detailed disease name in this category will popup.
(2)The macro named can not be found Combobox2Name can not be found in my database.
I am just starting to use ACCESS one month ago. Right now I am learning VBA programing, and at the same time I am reading some ACCESS2000 books. Thank you for your help. Hoping to hear you soon

Big thanks

Haijun

 

[MangroBongacello]

Haijun,

I used YourFormName, Combobox1Name, Combobox2Name to refer to objects. You use the names that you gave to those objects. Replace YourFormName with the name you gave to the form, Combobox1Name with name of combo box that displays categories and Combobox2Name with name that displays diseases.

I suggest that we create a test form, where we will solve your problem. Then, you can repeat the same procedure in your "real" form. I use Access97 but I think this will work in Access2K too. Just follow these steps.

1. Create new blank form and save it as TestForm.
2. Put two comboboxes on it. Name the first combobox cmbCategories and the second cmbDiseases.
3. Set the Row Source property for cmbCategories (here I will use the names of table and fields as in my first post, you replace them with your own names. If you have difficulties with that, supply me with your table and field names for relevant tables and I will try to create SQL-s exactly as they should be). So, the Row Source property:

select * from DiseaseCategories;

5. Set Bound Column property to the field CategoryID (in my case that would be 1, since the CategoryID is the first field in DiseaseCategories table).

4. Set the Row Source property for cmbDiseases

select * from Diseases where CategoryID = Forms!TestForm!cmbCategories;

5. In the On Enter event of cmbDiseases write:

Private Sub cmbDiseases_Enter()
cmbDiseases.Requery
End Sub

This will requery the combo box every time it receives the focus, so if the user changes category the proper diseases will be shown)

6. In the After update event of cmbCategories write:

Private Sub cmbCategories_AfterUpdate()
cmbDiseases.SetFocus
cmbDiseases.Dropdown
End Sub

This will set the focus to cmbDiseases after the user selects category and show the list of available diseases. Note that we used Requery on Enter event of cmbDiseases so every time the user changes the category, the proper diseases will be shown.

That should do it. If it works ok, you can repeat the same procedure in your "real" form.

One more thing. You can use the ColumnWidths property of combo boxes to display only the columns you want. For example, if you have two fields that are displayed and you want to display only the second one, set it this way:

0 cm; 5 cm

So if you set the width of the column to 0, it will not be shown.

If you have some aditional questions, don't hasitate to ask. I visit the forum every few hours.

Good luck
Mangro

 

[longmatch]

Dear Mr. Mangro:
Thank you for your kind help. I followed your instruction step by step and right now the detailed diseases can be shown when I select the disease Category. So it works right now. But I still have question for you.
Could we use one combo box to finish this task instead of two. My goal is to use one combo box to do that. When my customers choose the category, the detailed diseases will be shown in the same combo box. Do you thing that is doable?
How can we do it.

Your helps were highly appreciated.

Haijun

 

[MangroBongacello]

Oh... so that is what you want to do. I really can't imagine why you would want to do it like that, but well, I guess you know what you need.

Yes, it is doable.

Again, I will write the steps you need to make to achieve this. I will use the same tables as before.

1. Put a combobox on the form and name it cmbDiseases.
2. In the row source property write sql:

select * from DiseaseCategories;

3. In the module of the form right under the Option Explicit statement declare a variable:

Dim varCategory

4. In the After update event of the combo box write:

Private Sub cmbDiseases_AfterUpdate()

If cmbDiseases.RowSource = "select * from DiseaseCategories;" Then
    varCategory = cmbDiseases
    cmbDiseases.RowSource = "select * from Diseases where CategoryID = " & varCategory & ";"
Else
    cmbDiseases.RowSource = "select * from DiseaseCategories;"
End If
cmbDiseases.Requery
End Sub

That's it.

It will work like this: When the user selects a category, combo box will be requeried to show the correspondent diseases.

If the user selects by mistake the wrong category, he must choose one disease from combobox and the combo box will again be requeried to show categories. Then he can again choose a category. In other words, every time the user selects an item from combobox, the query for combo box will change. Once it will display categories, then corresponding diseases. If you don't want that "round trip", drop out the Else part of the If statement.

I hope this is what you need. Let me know if it is ok.

Mangro

 

[longmatch]

Dear Mangro:
Thank you for your help in the past for me. I tried your direction last time using one combo box to finish the whole work. It does not work, not sure what went wrong. Did you try this?

Big thanks

Haijun

 

[MangroBongacello]

Dear Haijun,

yes, I tested it before I sent it and it worked. There may be a reason or two, why it doesn't work for you, but I'm just guessing here, there may be something else.

1. I used the names of the tables and fields as in my first reply. If you use different names, use them in SQLs.

2. Maybe you used different data types for your field. I assumed the following types:

Table DiseaseCategories

Field: CategoryID (Number - Long)
Field: Description (Text)

Table Diseases

Field: DiseaseID (Number - Long)
Field: Description (Text)
Field: CategoryID (foreign key to relate to categories) (Number - Long)

If, for example you used Text data type for CategoryID, then the code I posted in the last reply would look slightly different:

If cmbDiseases.RowSource = "select * from DiseaseCategories;" Then
varCategory = cmbDiseases
cmbDiseases.RowSource = "select * from Diseases where CategoryID = '" & varCategory & "';"
Else
cmbDiseases.RowSource = "select * from DiseaseCategories;"
End If
cmbDiseases.Requery
End Sub

Note the red single quotation marks ('" & varCategory & "';&quot

3. Did you use the semicolons ( where I used them?

These are the problems I can think of. Does access display any error messages when running the code?

I hope this will be of some help. Let me know.

Good luck
Mangro

 

[longmatch]

I finally got there. Thank you for your help. If I need to master the programming and all questions about ACCESS, which book I need to read and is worth to read.

Big thanks, and good wishes for your holiday season.

Haijun

 

[MangroBongacello]

Dear Haijun,

I learned almost everything I know about access from it's help. I think it's great. Add some common sense to it and you'll learn fast.

Best wishes to you too
Mangro.