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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

  
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:

Expand|Select|Wrap|Line Numbers

  
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 Inverness

Quote:

Originally Posted by kcdoell

Expand|Select|Wrap|Line Numbers

 
...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):

Expand|Select|Wrap|Line Numbers

 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!

Cascading Lists for a form

re: Cascading Lists for a form

re: Cascading Lists for a form

re: Cascading Lists for a form

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about