Select Query for drop down depending on selection of previous field. 1

[sabrina30]

This is what I would like to do. If I can't do what I want can I please get some suggestion on alternatives. Ok..here is what I want: I have a combo field with selection of Nurse, Doctor, etc. I have another combo field with Template selections. Doctors have different selections from Nurse. I have 3 tables Loggin(choosing Nurse, dr, etc), Nurse templates and Doctor Templates. I would like to be able to select Nurse from Field one and have field two give me a selection of Nurse templates, or select Doctor and get a selection of Doctor Templates. Is this possible have tried creating IIF statements without any luck.

I appreciate any help with this.

Thank you,
Sabrina

 

[gnt]

Sabrina-
Maybe I'm not fully understanding you, but here's what I would do.
Create a union query combining your nurse & dr. templates, and then set the row source of your 2nd combo box to a sql statement selecting from this query using the 1st combo box as criteria. For example...

Sub combo1_afterupdate()
Dim strSQL as String
strSQL = "SELECT templates FROM qryTemplates WHERE_ loggin = '" & [Forms]![frmForm]![combo1] & "';"
Me!combo2.RowSource = strSQL
Me!combo2.Requery
End Sub

-gnt

 

[sabrina30]

I think I need more help. I understand what gnt is saying, just seem to be having trouble applying it. The Loggin table has Record_ID and Loggin_Type (Nurse, Doctor, etc) The NurseTemplate table has Record_ID and Template (variety of nurse template) and then a Template table with Record_ID and Templates (variety of Dr. Templates) These 3 tables are used with another table called Defect_Table and are all on one form. The Loggin, NurseTemplate and Template tables are used for the Combo boxes. I want to select Nurse from the Loggin field in the form and have the template field show the Nurse Templates from the NurseTemplate table or select Doctor from the loggin field on the form and have Dr. templates display in the template combo box from the Template table.

I know this has been answered and answered well, but I seem to be having trouble getting it to work. I may have set the union up wrong.

Any help is appreciated

Sabrina

 

[gnt]

Sabrina-
Your union query doesn't specify which templates are the doctor's and which are the nurse's. Try this...
Create a table with 2 fields: 1) Table, which contains the exact name of your template tables, and 2) LogginType, which will contain Nurse and Doctor. Then use this table as the rowsource for your first combo box - binding and hiding the first row. Now your 2nd combo's sql can SELECT templates FROM me!cbo1.value.

Does that make sense? In other words, the 2nd combo box will select from the table specified in the 1st.

Let me know if that still doesn't do what you need.
-gnt

 

[sabrina30]

Okay, I appreciate your help and see what you are saying. My problem is I am not any good at SQL statements. I will keep trying to figure the SQL using the example from your first post.

Thank you,
Sabrina

 

[gnt]

The first method I gave you will not work - I was under the impression that your template tables indicated dr./nurse, but it looks like they only give the name of the template.

I don't mind walking you through the sql - it's not too complex.

Design your lookup table as described above. Let's say it's named tblTemplateLookup. Now, be sure that your NurseTemplate table and your DoctorTemplate table have the exact same field names - I noticed one was 'Template' and one was 'Templates'. These have to be the same.

On your form, set the Loggin Combobox (I'll call it cboLoggin) RowSource to tblTemplateLookup. Column Count should equal 2, Column Widths should equal: 0";2" and Bound Column should equal 1. No go to the AfterUpdate property, click the code builder and copy and paste this code into your Sub:

Me!cboTemplates.RowSource = "SELECT Template FROM " & Me!cboLoggin & " ORDER BY Template;"
Me!ComboTemplates.Requery

You'll have to make these amendments:
--The 'cboTemplates' will be the name of your second combo box.
--The 'cboLoggin' will be the name of your 1st combo box.'
--Where 'Template' appears (twice), put in the name of your field in the doctor and nurse templates table - the one I said needed to be the same.

Hopefully this is bit more clear. Let me know-

 

[sabrina30]

You are Awesome...thank you soooo much