Solved

Access 2007 Form - Combo Box rowsource error

Posted on 2010-09-14
9
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…

730 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