Solved

Access 2007 Form - Combo Box rowsource error

Posted on 2010-09-14
9
305 Views
Last Modified: 2012-05-10
My form has a combo box which gets set with the following in the rowsource property

SELECT [ICNTable].[ICNNumber] FROM ICNTable WHERE (Left(ICNNumber,8)=CRNNumber) ORDER BY ICNNumber;

It works fine but now the CRNNumber is changing to a variable length.  So in the load event of the form I put in the following code

   If Len(CRNNumber) <> 8 Then
      targetLen = Len(CRNNumber)
      ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
         "(Left (ICNNumber, 'targetLen') = CRNNumber) ORDER BY ICNNumber;"
   End If

and now I'm getting the following error message.

This expression is typed incorrectly, or it is too complex to be evaluated.  For example, a numberic expression may contain too many complicated elements.  Try simplifying the expression by assigning parts of the expression to variables.

I'm not sure where to go from here.

Thanks!
Helen
0
Comment
Question by:greenprg
  • 5
  • 4
9 Comments
 
LVL 75
ID: 33675309
Remove ' from around targetLen

   If Len(CRNNumber) <> 8 Then
      targetLen = Len(CRNNumber)
      ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
         "(Left (ICNNumber, targetLen) = CRNNumber) ORDER BY ICNNumber;"
   End If

mx
0
 
LVL 75
ID: 33675313
Also, where is this defined:

targetLen

?

mx
0
 

Author Comment

by:greenprg
ID: 33675360
Without the single quotes around targetLen, when I click on the combo box, it asks me for the value of targetLen like it was a parameter that it does not have the value for.

targetLen is defined right after the If statement

      If Len(CRNNumber) <> 8 Then
-->      targetLen = Len(CRNNumber)
           ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
              "(Left (ICNNumber, targetLen) = CRNNumber) ORDER BY ICNNumber;"
        End If

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 75
ID: 33675403
I meant as in

Dim targenLen As ?

0
 

Author Comment

by:greenprg
ID: 33675454
Oh....

Dim targetLen as Integer
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 33675460
Well, you can't really reference a variable like that in a SQL stmt.

Create a hidden text box on the form    txtTargetLen

Then this:

Dim targetLen As Long

      If Len(CRNNumber) <> 8 Then
-->      Me.txtTargetLen = Len(CRNNumber)
           ICNNum.RowSource = "SELECT ICNTable.ICNNumber FROM ICNTable WHERE " & _
              "(Left (ICNNumber, Forms!YourFormName.txtTargetLen) = CRNNumber) ORDER BY ICNNumber;"
        End If
0
 

Author Comment

by:greenprg
ID: 33675627
Perfect.  Works great!

Thanks!
Helen
0
 

Author Closing Comment

by:greenprg
ID: 33675635
Thanks for the quick answers.
0
 
LVL 75
ID: 33675733
You are welcome.
Thank you for using Experts Exchange.

mx
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question