Solved

Access 2007 Form - Combo Box rowsource error

Posted on 2010-09-14
9
304 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 - Access MVP) 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

786 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