Results 1 to 3 of 3
  1. #1
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35

    Variable within form control reference

    I have a large quantity of textboxes that need to be filled with the results of SQL statements. I would like to use a for loop to do this but I am unable to successfully integrate a variable in my form control reference. the code shown below errors out at the last line of the second for loop stating that there is no such form control named 'LetterStrategy_BalanceTo & (x)'.

    Code:
     
    Dim SQL_LetterStrategy_BalanceToArray(1 To 9)
    For x = 1 To 9
    SQL_LetterStrategy_BalanceToArray(x) = " SELECT Top 1 BalanceTo" & x & " FROM" _
        & " [Letter_Strategy] WHERE Client ='" & Client & "'" & " ORDER BY [EffectiveDate] DESC;"
    Next x
     
    For x = 1 To 9
        Set rs = db.OpenRecordset(SQL_LetterStrategy_BalanceToArray(x), dbOpenDynaset)
        Dim LetterStrategy_BalanceToArray(1 To 9)
        LetterStrategy_BalanceToArray(x) = rs.Fields("BalanceTo" & x)
        Forms![Strategy Binder]![LetterStrategy_BalanceTo & (x)] = LetterStrategy_BalanceToArray(x)
    Next x
    It may just be an issue of syntax. Any help would be appreciated.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Can you just bind the query to the form?
    if you have another query bound, create a subform.

    If you don't want to change you design, then try this:
    Code:
    For x = 1 To 9
        Set rs = db.OpenRecordset(SQL_LetterStrategy_BalanceToArray(x), dbOpenDynaset)
        Dim LetterStrategy_BalanceToArray(1 To 9)
        LetterStrategy_BalanceToArray(x) = rs.Fields("BalanceTo" & x)
        me.controls("LetterStrategy_BalanceTo" & x) = LetterStrategy_BalanceToArray(x)
    Next x

  3. #3
    Tyork is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    35
    Thanks, that worked perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-09-2010, 04:10 AM
  2. Control variable is Null
    By ngruson in forum Access
    Replies: 2
    Last Post: 04-21-2010, 10:27 AM
  3. Using a string variable to specify a control
    By Gerry in forum Programming
    Replies: 3
    Last Post: 04-14-2010, 02:28 PM
  4. using forms control as report variable
    By Seven in forum Reports
    Replies: 3
    Last Post: 12-19-2009, 04:04 PM
  5. Can't reference control in subform
    By evanscamman in forum Forms
    Replies: 0
    Last Post: 12-25-2007, 06:47 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums