?
Solved

Access 2007 Form - Combo Box rowsource error

Posted on 2010-09-14
9
Medium Priority
?
308 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses

801 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