A concatenate question in reverse

[lostNoob]

I have a query that pulls the right info but I am tring to display it in a form

The data looks like this

autonumber usernumber userfirstname userlastname checkbox
0000 0001 john smith false
0001 0001 john smith false
0002 0001 john smith true
0003 0002 sally jones true
0004 0002 sally jones true
0005 0002 sally jones false

I would like it displayed like this

usernumber userfirstname userlastname checkbox autonumber
0001 john smith false 0000, 0001
0001 john smith true 0002
0002 sally brown false 0005
0002 sally brown true 0003, 0004

I have all but the autonumber set up correctly. I am a bit new to the visual basic part of Access any help will be welcomed.

[dsatino]

you can't do this with a single query

[TheSmileyCoder]

Try to explain what you need, because it sounds like you might not have setup your tables properly. Depending on your needs, I would likely suggest using a subform,but I need to know more about what you need this for.

[dsatino]

I would agree, but this isn't my question. I'm new to this today so I probably responded in the wrong place somehow. Given your expert status, I'd love it if you could look at my question regarding how to use a custom function as both a filter and a non-filter. Thanks!

[lostNoob]

@TheSmileyOne
Ok, I have everything but the last part of this query setup and I am trying to list the series of autonumbers 0000,0001,0002,0003 That is attached to a specific user This is for a payroll form and report to show payout to the listed autonumbers

[TheSmileyCoder]

What is your table structure? Do you have a Table of users and a seperate table of payrolls? Are they related by the UserNumber?

If so, what you likely need to do, is first setup a form, tied to user table, then insert a subform (simplest approach is to drag the payroll table into the user form, while in design view, and then setup the Parent/child relation between the 2 forms).

[lostNoob]

@TheSmileyOne
this will not help list a single column from mydatabase to a comma delimited list in a textbox on my form

[TheSmileyCoder]

No it would not. It would however allow you a easy way to display such items in a subform, and you could even tie events to the subform, so that when a user clicks a payroll, that payroll gets opened.

Now if you need it comma delimited, you would need to write a function for that, as far as I know.

It could go something like this.

Again I would base the form on your user table, and then add a custom function in a textbox, this ensures that the function will not be called for all your users, but only for those you are actually displaying in the form.

Private Function fStrPayrolls(UserNumber as long) as string
  Dim rsPR as DAO.Recordset
  Dim strSQL as string
  strSQL="SELECT * FROM tbl_Payroll WHERE usernumber=" & UserNumber & " ORDER BY autonumber"
  set rsPR=CurrentDB.OpenRecordSet(strSQL,dbopendynaset,dbReadOnly)
  Dim strResult as string
  do while not rsPR.EOF
    strResult=strResult & "," & rsPR!Autonumber
    rsPR.MoveNext
  Loop
  fStrPayrolls=strResult
 
End Function