Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10092

Re: Combining several row values into one row using VBA

Show all headers | View raw

Am 10.11.2014 um 16:55 schrieb Patrick Finucane:
> Public Function WhatLine(strWho As String) As String
>      Dim rst As Recordset
>      Set rst = CurrentDb.OpenRecordset("Select YourTableName.* From YourTableName Order By Who", dbOpenDynaset)
>      
>      rst.FindFirst "Who = """ & strWho & """"
>      Do While rst!Who = strWho
>          WhatLine = WhatLine & rst!What & ", "
>          rst.MoveNext
>      Loop
>
> End Function
Hi Patrick,

a modified version to prevent a full table scan in this rapidly used 
function:

Public Function WhatLine(strWho As String) As Variant
     Dim rst As DAO.Recordset

     Set rst = CurrentDb.OpenRecordset( _
         "Select YourTableName.* From YourTableName Where who='" & 
strWho & "'", dbOpenForwardOnly)

     if rst.RecordCount > 0 then
         rst.movefirst

         Do until rst.eof
             WhatLine = WhatLine & rst!What & ", "
             rst.MoveNext
         Loop
     end if

     rst.close
WhatLine= left(WhatLine, len(WhatLine)-2)
End Function

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Combining several row values into one row using VBA rpboll <RPBOLL@gmail.com> - 2014-11-10 07:00 -0800
  Re: Combining several row values into one row using VBA Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-11-10 07:55 -0800
    Re: Combining several row values into one row using VBA rpboll <RPBOLL@gmail.com> - 2014-11-10 09:32 -0800
      Re: Combining several row values into one row using VBA Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-11-10 10:38 -0800
        Re: Combining several row values into one row using VBA rpboll <RPBOLL@gmail.com> - 2014-11-10 12:04 -0800
          Re: Combining several row values into one row using VBA Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-11-10 12:13 -0800
    Re: Combining several row values into one row using VBA Ulrich Möller <knobbi38@arcor.de> - 2014-11-11 01:36 +0100
      Re: Combining several row values into one row using VBA Patrick Finucane <patrickfinucanetx@gmail.com> - 2014-11-11 08:01 -0800
  Re: Combining several row values into one row using VBA "Ron Paii" <None@none.com> - 2014-11-10 14:11 -0600
  Re: Combining several row values into one row using VBA Bob Quintal <rquintal@sPAmpatico.ca> - 2014-11-10 22:09 +0000
  Re: Combining several row values into one row using VBA Ulrich Möller <knobbi38@arcor.de> - 2014-11-10 23:10 +0100
    Re: Combining several row values into one row using VBA Ulrich Möller <knobbi38@arcor.de> - 2014-11-11 00:59 +0100
  Re: Combining several row values into one row using VBA Dave W <davewi11@yahoo.co.uk> - 2014-11-11 20:57 +0000

csiph-web