Results 1 to 10 of 10
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    VBA To Generate Table From Selected empID In Grid

    Good day all - I am attempting to generate a table that holds ONE row for the selected empID on the Subform. So for example, if you select empID abc123 - then I want table ELS to hold data like the below


    empID saleItem
    abc123 gr1, zz1, fla323


    I tried using Allen Brownes ConcatRelated() Function to do this, but I must be using it wrong as the saleItems are not combined, and the insert statement just duplicates the empID for the source table. I am attaching a sample database to illustrate what I mean. If someone here with more knowledge can assist me on why my table is not concatenating ONLY the selected userID I would appreciate it!

    Database3.accdb

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The SQL would look like:

    SELECT Table1.empID, ConcatRelated('saleItem','[Table1]',"empID='" & [empID] & "'") AS Item
    FROM Table1
    GROUP BY Table1.empID;

    Are there two of you working on this? There's a really similar thread out there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    If I copy and paste your syntax into the VBE I get a syntax error message.

    I should be the only one at my company working on this issue, since there is only myself and one other IT guy here

    EDIT ->
    The error I get is
    "Expected List Or Seperator" on the "empID='"

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I copy/paste that into a new query in your sample and get:

    empID Item
    abc123 gr1, zz1, fla323
    def456 kakal1, akcja


    You'd change it to a make table if that's what you need. This is the thread that seemed similar:

    https://www.accessforums.net/showthread.php?t=68280
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    AH - I see what it is. Iam using it for VBA - and you did it for a query, haha.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, you'd have to adjust the syntax for VBA. I was just giving you the SQL syntax to get the correct result.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by pbaldy View Post
    Yes, you'd have to adjust the syntax for VBA. I was just giving you the SQL syntax to get the correct result.
    I thought I had the syntax properly converted - but am getting an error of

    A RunSQL action requires an argument consisiting of an SQL statement

    And this is the VBA
    DoCmd.RunSQL ("SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID=''" & [empID] & "') AS Item FROM Table1 GROUP BY Table1.empID;")

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Look at that other thread. The double quotes in the Concat function close off the string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by pbaldy View Post
    Look at that other thread. The double quotes in the Concat function close off the string.
    if i try the syntax you suggested in that thread - I still get a syntax error
    Code:
    DoCmd.RunSQL ("SELECT Table1.empID, ConcatRelated('saleItem','[Table1]','empID= ' & Chr(34)  & [empID] & Chr(34) & ') AS Item Into Hopscotch FROM Table1 GROUP BY Table1.empID;")
    Last edited by jo15765; 09-27-2017 at 05:26 PM.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I see you started a new thread, so I'll close this one.

    https://www.accessforums.net/showthread.php?t=68301
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 12
    Last Post: 05-09-2017, 02:30 AM
  2. Replies: 1
    Last Post: 01-31-2013, 02:58 PM
  3. Replies: 7
    Last Post: 10-15-2012, 11:05 PM
  4. Replies: 5
    Last Post: 12-24-2011, 06:04 PM
  5. Grid lines vs Grid Dots
    By dharriet in forum Access
    Replies: 0
    Last Post: 10-14-2008, 09:17 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