Cascading Lists for a form

[kcdoell]

Hello:

I have been struggling with building a cascading list on a form that I created. My problem is that I am getting a "Datatype Mismatch in criteria expression" error that I can not seem to figure out. I hope someone can help...

The background is that I have two drop down boxes on a form called:

cboPolicyName
cboPolicyNumber

On the rowsource for the cboPolicyName I have it pointing to my table:

SELECT DISTINCT tblPolicyName.PolicyName, tblPolicyName.PolicyNameID FROM tblPolicyName ORDER BY tblPolicyName.PolicyName; 
 

This works fine and the dropdown box populates with my various "Policy Names"

On the After update event of that same control I have the following:

Private Sub cboPolicyName_AfterUpdate()
'When the Policy Name is selected, the appropriate Policy Number region list will
'display in the drop down list of CboPolicyNumber
 
On Error Resume Next
   cboPolicyNumber.RowSource = "Select tblPolicyNumber.PolicyNumber " & _
            "FROM tblPolicyNumber " & _
            "WHERE tblPolicyNumber.PolicyNameID = '" & cboPolicyName.Value & "' " & _
            "ORDER BY tblPolicyNumber.PolicyNumber;"
End Sub
 

I set my cboPolicyNumber control rowsource to be blank.

I am new at doing these types of cascading lists but I have looked at how they are created and I believe I did everything right. For some reason, I believe the After Update is where the error is occurring. I have looked at everything I can think of, making sure that everything is bound on column 1 and that that column is numeric.

Does anybody have any ideas what I can check or where I am going wrong???

Thanks,

Keith.

[kcdoell]

Hello:

I saw the "how to doc." that was written by Rabbit, and followed it to a tee...The end result worked perfectly though it was different approach than I had been reading about... I am not very familiar with the "Me." values.

Here was the solution:

 
Private Sub cboPolicyName_AfterUpdate()
'When the Policy Name is selected, the appropriate Policy Number list will
'display in the drop down list of CboPolicyNumber
 
    With Me![cboPolicyNumber]
      If IsNull(Me!cboPolicyName) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [PolicyNumber] " & _
                     "FROM TblPolicyNumber " & _
                     "WHERE [PolicyNameID]=" & Me!cboPolicyName
      End If
     Call .Requery
End With
 
End Sub
 
 

The only problem I am having is that now my query I created using the Access interface wizard now comes up blank. In the criteria of that query I am pointing to the form:

[forms].[DataEntry].[cboPolicyName]
[forms].[DataEntry].[cboPolicyNumber]

If I manually input the policy name and number into the query it works.

Below is the SQL of that query:

 
PARAMETERS [forms].[DataEntry].[cboPolyNumber] Short, [forms].[DataEntry].[cboPolyName] Short;
SELECT tblPolyNumber.PolicyNumber, tblPolyName.PolicyName, tblMainRes.ReIndexName, tblMainRes.Pages, tblMainRes.EffectiveDates, tblMainRes.ExpirationDate, tblMainRes.Volume, tblMainRes.Tab, tblMainRes.PolicyNumberID
FROM (tblPolyName INNER JOIN tblPolyNumber ON tblPolyName.PolicyNameID = tblPolyNumber.PolicyNameID) INNER JOIN tblMainRes ON tblPolyNumber.PolicyNumberID = tblMainRes.PolicyNumberID
WHERE (((tblPolyNumber.PolicyNumber)=[forms].[DataEntry].[cboPolyNumber]) AND ((tblPolyName.PolicyName)=[forms].[DataEntry].[cboPolyName]));
 
 

Does anybody know why it can not see the selections that have been chosen on my form?

Thanks,

Keith.

[Stewart Ross]

...WHERE (((tblPolyNumber.PolicyNumber)=[forms].[DataEntry].[cboPolyNumber]) AND ((tblPolyName.PolicyName)=[forms].[DataEntry].[cboPolyName]));
 

Does anybody know why it can not see the selections that have been chosen on my form?

Thanks,

Keith.

Hi Keith. If you are referring to two existing form fields you need to include their values in the WHERE clause as string literals (assuming both are strings):

WHERE (((tblPolyNumber.PolicyNumber) = '" & [forms].[DataEntry].[cboPolyNumber] & "') AND ((tblPolyName.PolicyName) = '" & [forms].[DataEntry].[cboPolyName] & "'"));

-Stewart

[kcdoell]

Stewart:

I was in a rush last night to get out the door but just before doing so I solved it! In the end, it was my bound column in cboPolicyName. My code (AfterUpdate) was written to look for the PolicyNameID. That is why I had a syntax error when I changed my cboPolicyName to bound on 2. To solve I dropped in my PolicyNameID into my query and put my criteria there instead of the PolicyName field. I did the later and it worked error free!

Thanks for all your help!