Cascading Drop Down CB

[diofree]

Hi guys,
I have read a very good guide here http://www.fontstuff.com/access/acctut10.htm

I think I fall into scenario 2.

I have tried putting this code into the AFTER UPDATE event of the "parent" combo box:

Private Sub Position_AfterUpdate()
   On Error Resume Next
   Slot1.RowSource = "Select CandidateIdentified.CandidateIdentifiedCandidate " & _
            "FROM CandidateIdentified " & _
            "WHERE CandidateIdentified.CandidateIdentifiedPosition = '" & Slot1.Value & "' " & _
            "ORDER BY CandidateIdentified.CandidateIdentifiedCandidate;"
End Sub

I am getting error "Data type mismatch in criteria expression". Is there anything wrong with my code or is this an issue stemming from my lookups being generated in SharePoint (this has caused data type problems before)? Thanks!


UPDATE:
I must be doing something wrong, can't even get it to work in mock database using:

Private Sub Position_AfterUpdate()
   On Error Resume Next
   Candidate.RowSource = "Select tblCandidates.Candidate " & _
            "FROM tblCandidates " & _
            "WHERE tblCandidates.IdentifiedPosition = '" & Position.Value & "' " & _
            "ORDER BY tblCandidates.Candidate;"
End Sub

 

[sneuberg]

If CandidateIdentified.CandidateIdentifiedPosition is numeric you don't want the single quotes. If would be:

Private Sub Position_AfterUpdate()
   On Error Resume Next
   Slot1.RowSource = "Select CandidateIdentified.CandidateIdentifiedCandidate " & _
            "FROM CandidateIdentified " & _
            "WHERE CandidateIdentified.CandidateIdentifiedPosition = " & Slot1.Value & " " & _
            "ORDER BY CandidateIdentified.CandidateIdentifiedCandidate;"
End Sub

 

[CJ_London]

error indicates that CandidateIdentifiedPosition is not a string since '" & Position.Value & "' is clearly a string. Perhaps you are using lookups in your table field definitions - often causes this sort of confusion.

but more to the point in your first bit of code you are changing the slot1 rowsource with a value from slot1 in the position afterupdate event - would have thought you wanted to update with a value from the position control

if you are not doing this in a continuous form or datasheet, all you need to do in form design is set the candidate rowsource (using your second example) to

Select Candidate FROM tblCandidates WHERE IdentifiedPosition = [Position] ORDER BY Candidate

then in your postion afterupdate event you just need

Private Sub Position_AfterUpdate()
Candidate.requery
End Sub

Note that it is not necessary to use .value - it is the default property returned

 

[diofree]

Thank you to you both for the replies!

CJ,
After my post last night I got further along, first drop down works (mostly) but the second one is always blank. I tried your simpler approach, but got the same result. Here is my practice file if anyone would be willing to take a look.

I am doing my Combo Boxes in a form called tblReport

PS. I did realize my mistake after posting the first example. Thanks for that.

 

[sneuberg]

I find your organization confusing. What is tblReport suppose to be; a junction table between positions and candidates? If so I suggest a name that reflects that. On the other hand tblCandidates appears to be the candidates but I see David Francouer in their twice, once as a Janitor and once as a CEO.

If a candidate can be in more than once position at once, you need a table for the candidates, a table for the positions, and a junction table connecting the two. I suggest you clean this up and establish the relationships before doing anything else.

If you are still having problems with the combo boxes after the reorganization, please upload the new database and I'll give you some suggestions.

 

[sneuberg]

Another suggestion. Some people use surrogate keys (autonumbers) on every table whether it makes sense or not. When you have a table that has a field that makes a good primary key, like for example position name, I suggest making that the primary key and don't have an autonumber. It will make combo boxes easier and will prevent duplicate entries where they don't belong.

 

[diofree]

Sorry about that, I got lazy on my test DB and didn't quite mirror the table structure of my actual database. This update is much closer.

I tried two different SQL on the second drop down, both seem to do the same thing. The number of lines available in the second combo is correct, but they are still blank (no text).

Attempt1
SELECT tblEligibility.Candidate
FROM tblCandidates INNER JOIN tblEligibility ON tblCandidates.ID = tblEligibility.Candidate
WHERE ((([tblEligibility]![Position])=[Forms]![formPositionSlotting]![Position]));

Attempt2
SELECT tblEligibility.Candidate
FROM tblEligibility
WHERE ((([tblEligibility]![Position])=[Forms]![formPositionSlotting]![Position]))
ORDER BY tblEligibility.Candidate;

 

[sneuberg]

You only have one value in the select part of those queries and that row has zero width. I think the following is closer to what you want:

SELECT tblCandidates.ID, tblCandidates.LastName, tblCandidates.FirstName
FROM tblCandidates INNER JOIN tblEligibility ON tblCandidates.ID = tblEligibility.Candidate
WHERE (((tblEligibility.Position)=[forms]![formPositionSlotting]![Position]));

But do you want the second slot to depend on what's in the first or do both of these combos work the same?

 

[sneuberg]

Oh and just my opinion but I find those table lookups block ones ability to see what's really going on. That's one reason why my previous response took so long. I'd get rid of them and restrict the input to tables through forms.

 

[diofree]

You only have one value in the select part of those queries and that row has zero width. I think the following is closer to what you want:

SELECT tblCandidates.ID, tblCandidates.LastName, tblCandidates.FirstName
FROM tblCandidates INNER JOIN tblEligibility ON tblCandidates.ID = tblEligibility.Candidate
WHERE (((tblEligibility.Position)=[forms]![formPositionSlotting]![Position]));

But do you want the second slot to depend on what's in the first or do both of these combos work the same?

Yay!! This worked! You are awesome!


Yes other slots would work the same way. Only thing I would change if it was easy would be to ensure the same individual is not in more than one slot.

 

[diofree]

Moving back to my real DB, code is adjusted but getting the blank box in combo box again?

SELECT Candidates.ID, Candidates.CandidateDisplayName
FROM Candidates INNER JOIN CandidateIdentified ON Candidates.ID = CandidateIdentified.CandidateIdentifiedCandidate
WHERE (((CandidateIdentified.CandidateIdentifiedPosition)=[forms]![Landscape]![LandscapePool1]));

In the select I opted for DisplayName which joins LastName, FirstName into one field. Is that the issue?

 

[diofree]

Actually must be a different issue. Drop down only ever shows one row even if there should be multiple.

 

[sneuberg]

First I suggest create queries for the row sources of combo boxes and assigning them to the row source. It make it easier to debug. You can get the SQL of the combo box, and create a query from it, give a descriptive name like Row Source of ... and assign it to the the row source. Then with the form open you can see what this query is doing by itself.

With that the things to check are:

1. The number of columns in the combo box needs to match the number of fields selected in the query
2. The column widths, for example, 0";1";1" should also match the number of fields and be zero width only for fields needed in the query (Primary key for example) that you don't want to see

You should be able to make sure a slot is not filled with the same person twice and it should be easy, but it's not and I believe the reason is that your data structure is still off. I'll give you an alternate structure soon.

 

[sneuberg]

I'll give you an alternate structure soon.

Maybe not so soon. I'm a little stuck on this one.

 

[diofree]

Thanks!
Don't worry about it, I got it working. One of my variables was wrong in the SQL statement!