Cascading ComboBox Question

[Kevo]

I realize that there are many threads explaining this and I have tried to do it on my own!



I was wondering if someone could check out my code and help me.

It is working fine except....when I choose a "Department" it lists the "Instruments" as the DepartmentID number in the cboInstrument.

In other words, when I choose "Surgery" in the cboDepartment control and then go to cboInstrument....it only list multiple "1s" (1 is the DepartmentID)....If I choose one of the "1"s it changes to the InstrumentName (which is what I want it to display in the cbo to begin with?

Any ideas....Is this problem in the code? Or maybe something set wrong in cboInstrument?

Any help would be appreciated.



Private Sub cboDepartment_AfterUpdate()
Dim strSource As String
strSource = "SELECT [tblInstrument].[InstrumentID]," & _
" [tblInstrument].[DepartmentID]," & _
" [tblInstrument].[InstrumentName] " & _
"FROM [tblInstrument] " & _
"WHERE [DepartmentID] = " & Me.cboDepartment.Value
Me.cboInstrument.RowSource = strSource
Me.cboInstrument.Requery
End Sub

[June7]

Why don't you put the sql in the RowSource property of cboInstrument? Don't retrieve the DepartmentID, it is getting in the way.
SELECT InstrumentID, InstrumentName FROM tblInstrument WHERE DepartmentID=Forms!formname!cboDepartment;

Are these comboboxes in a Continuous or Datasheet View form? If so, you will see odd behavior.

[Kevo]

Well I did finally get it to work but my form is actually a form with a subform and the subform is in datasheet view....It is definitely not working.

Is there a way to have a cascading combo on a datasheet view?

Maybe I need to rethink the way I am approaching this.

It would just be nice if after choosing the department, it would narrow the list of instruments to only the ones that belong to that department. (since we have many, many instruments)

Maybe someone has a better idea for the way I am setting up my forms for data entry. I think the subform within a form view is best for what we are doing.

Attached is my db. Please note that the form I am concerned with at the moment is frmLoad.

Thanks for any advice.

[June7]

Cascading boxes in Continuous or Datasheet view require just a little more code. Here is one solution http://www.599cd.com/tips/access/two-combo-boxes/

[Kevo]

OK, well that kind of worked. It is giving me the correct choices for the Instrument now depending on the department I choose......and it is even behaving correctly as far as advancing to the next record in the datasheet view of the subform....

The problem is I choose cboDepartment....and the cboInstrument gives me the correct choices....but when I choose a instrument it populates each Instrument field in each record of the subform with the same instrument.

I am sure this has something to do with the control source maybe? I hope it is something simple.

Thanks,
Kevin

[June7]

I have never set up dependent comboboxes in a subform and I am finding it a challenge also. Could you replace the attachment in the earlier post with your revised project so I can see how you got it this far?

[Kevo]

Thanks for looking at it for me.

[June7]

I changed the DependentID combobox RowSource from a query reference to an SQL statement. The dependent comboboxes in the subform are working. The RowSource does requery to filter based on the DepartmentID. Data is properly saved to table. The reason some of the comboxes don't display data as you move row to row is because of the lookup alias. The comboboxes are set to show the alias names, not the ID which is the actual data stored in table. Because there really is only one InstrumentID combobox that the subform records use, the requery is reflected in all rows. This means not all alias names are available to all records displayed in the form, therefore some show blank. The only solution I can see is to have the subform in Continuous view and arrange the controls and subform size so only one record at a time is viewable or SingleForm view.

After seeing your project setup, I do remember helping someone else with this same situation and coming to the same conclusion.

[Kevo]

Ok I have searched all over and came up with nothing useful. I have found that this is a recurring question/problem for a lot of people. I really don't understand how you get past this.

I found one solution which involved placing a text box on top of the 2nd cbo and it supposedly would not disappear as the cbo does as you click to the next record, but this seems a little convoluted to me.

I had a vague idea but have no idea if you can do it......

Can you display a query or repot or whatever below your subform that would show the contents of your table?

I tried to create another subform but it just had the same controls as my first subform.....I need one that just displays the records.

Any ideas?

[June7]

Overlaying combobox and textbox has issues. Textbox on top means can't click into the combobox. Combobox on top means when you click into it the value in textbox is obscured.

What do you mean the additional subform just had same controls as the other subform? Each subform has its own controls, whatever you want. Were the forms in Continuous or Datasheet view? Showing another subform should be possible. Or can use a listbox but this requires code to refresh the listbox when moving record to record.

Can't show query directly on a form, has to be the RecordSource of a form. Reports don't show on forms (the reverse is possible).

[Kevo]

The listbox is what I had in mind...I will have to work out the code (may need help with that)

I guess sql in the rowsource to show the records with the current "LoadDetailsID" and then a requery in the afterupdate event of the last cbo of the record in the subform?

[June7]

Yes, the code in the event would simply be Me.listboxname.Requery.

[Kevo]

OK, I got the listbox to list the table results and requery as needed....but, I am not sure how to get the results to display the way I want.

If you look at "Load Log" in my Main Menu you will see the list box below the sub form...when you select data in the cbo in the subform and tab out of the "quantity" cbo the list updates perfectly! Yea!
What I don't know how to do is get it to display like the subform..... i.e. - I want InstrumentName instead of InstrumentID......In the subform this was accomplished through setting the columns to 2 and the widths to 0",1".

Any idea how to code to ask for the InstrumentName?

Can you say....[tblLoadDetails].[InstrumentID],[tblInstrument].[InstrumentName] Where [tblLoadDetails].[LoadID] = cboLoadID

I hope you understand what I am asking.....Can you ask for info from two different tables based on a value from just one table?

Thanks

[June7]

The listbox rowsource needs to be a join of tblLoadDetails to tblSet, tblInstrument, tblDepartment.

SELECT tblLoadDetails.LoadID, tblDepartment.DepartmentName, tblInstrument.InstrumentName, tblSet.SetName, tblLoadDetails.Quantity
FROM tblSet RIGHT JOIN (tblInstrument RIGHT JOIN (tblDepartment RIGHT JOIN tblLoadDetails ON tblDepartment.DepartmentID = tblLoadDetails.DepartmentID) ON tblInstrument.InstrumentID = tblLoadDetails.InstrumentID) ON tblSet.SetID = tblLoadDetails.SetID
WHERE (((tblLoadDetails.LoadID)=[Forms]![frmLoad]![LoadID]));

[Kevo]

June7.........You Are Da Bomb, My Friend!

I really appreciate all of your help. This worked perfectly. I have a long way to go until this db is the way I want it, but this was probably one of my biggest hurdles so far.

Thank You!