Results 1 to 6 of 6
  1. #1
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56

    dynamic sql using control Name

    I have 5 form fields named cbVendo, cbVendor2 ... cbVendor5. I am trying to create a dynamic sql in which I use the name of the control to append the number at the end. Something like the below but I can't get it to work.



    I am putting the SQL as the rowsource of another combo box.


    "SELECT tblMarketData.Survey, tblMarketData.Vendor
    FROM tblmarketData
    WHERE tblMarketData.Vendor='"& [forms]![frmMarketHayEval]!cbVendor & Right(Me.ActiveControl.Name,1) & "'"

    I have highlighted the piece in red that is what I am trying to do.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't have to use code, just build a query that references the objects on the form.
    The query criteria can also use Right(forms!myForm!cboBox,1)

  3. #3
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56
    Normally I would do that however I have 12 combo boxes. I do not want to create separate queries for each one. The only other alternative is to redesign the database so that I have a 1:M relationship, instead of everything on one table row. That would reduce the combo boxes down to 3. But I rather not do that which is why I was trying to use the code so that I could have one query that is dynamic for all the combo boxes by appending the Right(Me.ActiveControl.Name,1)

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Can you have an ActiveControl event set a Tempvar to number 1 - 5? Then use the tempvar in the query.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it's screen.activecontol, not me.activecontrol and you need to finish with your closing single quote

  6. #6
    gammaman is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    56
    Thanks. It is working now.

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

Similar Threads

  1. Combine data/dynamic control
    By msAccessNoob in forum Reports
    Replies: 7
    Last Post: 07-22-2016, 05:02 PM
  2. Dynamic adjustment of a control
    By George in forum Modules
    Replies: 3
    Last Post: 05-20-2016, 08:09 AM
  3. Replies: 4
    Last Post: 02-18-2016, 12:06 PM
  4. Replies: 8
    Last Post: 07-30-2012, 04:43 PM
  5. Dynamic Form, Dynamic labels/ captions?
    By JFo in forum Programming
    Replies: 15
    Last Post: 10-12-2011, 08:33 PM

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