Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    21

    Unanswered: Conctenating/Populating Records Contents

    Hi there,

    I have a table [DailyUpdates] with thousands of record entries, usually the user searches this table through SearchForm resulting into some recordset. What I need to do on this recordset is to concatenate the contents of all the records for one field-[Description], and populate the resulting into textbox [Update] of a form [UpdateSummary]

    Anyone with solution?
    Last edited by Kassimu; 06-02-08 at 12:36. Reason: Clarification

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Set up a variable to hold the data.

    Open the recordset.

    Loop through the recordset and set the variable to itself & the field.

    Set the textbox = the variable.

  3. #3
    Join Date
    Apr 2008
    Posts
    21
    Partly, I have got it working when the recordset is opened based on the table "Daily Fault Update", I am using the following code.
    Code:
    Private Sub Form_Load()
    Dim db As Database
    Dim rstUpdates As Recordset
        Set db = CurrentDb
        Set rstUpdates = db.OpenRecordset("Daily Fault Update", dbOpenDynaset)
    If Not rstUpdates.EOF Then rstUpdates.MoveFirst
    Do While Not rstUpdates.EOF
        vntTempData = vntTempData & rstUpdates!Description
        rstUpdates.MoveNext
    Loop
    Me.FaultComms = vntTempData
    End Sub
    The problem I am having here is, this code gives data from all the records, I only need a filtered output. I tried to open the recordset based on query "Daily Fault Update Query" I end up with run time error '3061' -too few parameters expected 1.

    I tried to use filter property of recordset to limit the records and then open another recordset on it. The second sample of code!
    Code:
    Private Sub Form_Load()
    Dim db As Database
    Dim vntTempData as Variant
    Dim rstUpdates As Recordset
    Dim rstUpdates1 As Recordset
    Dim strOpen As String
    strOpen= "[TT_Number] =[Forms]![TXFaults].[TT_Number]"
        Set db = CurrentDb
        Set rstUpdates = db.OpenRecordset("Daily Fault Update", dbOpenDynaset)
        rstUpdates.Filter=strOpen
        Set rstUpdates1= rstUpdates.OpenRecordset
    If Not rstUpdates1.EOF Then rstUpdates1.MoveFirst
    Do While Not rstUpdates1.EOF
        vntTempData = vntTempData & rstUpdates1!Description
        rstUpdates1.MoveNext
    Loop
    Me.FaultComms = vntTempData
    End Sub
    I am getting the rutime error too few parameters. Expected 3 on this line
    Code:
    Set rstUpdates1= rstUpdates.OpenRecordset

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Do you have any parameters used in the query that you are trying to open, or in it's Parameters dialog box (Query - Parameters)?

    I'd be trying some code more like this:

    Code:
    Private Sub Form_Load()
       Dim vntTempData as Variant
       Dim rstUpdates As Recordset
       Set rstUpdates = CurrentDb.OpenRecordset("SELECT * FROM [Daily Fault Update] WHERE [TT_Number] = " & [Forms]![TXFaults].[TT_Number])
       While Not rstUpdate.EOF
          vntTempData = vntTempData & rstUpdates!Description
          rstUpdates.MoveNext
       Wend
       Me.FaultComms = vntTempData
    End Sub
    * Disclaimer: Code written on the fly, so 'scuze any bugz
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2008
    Posts
    21
    I dont have parameters and still the same error persits even after changing the code. I am using Access2007 and I have enabled DAO 3.6 liblary instead of ADO.

  6. #6
    Join Date
    Apr 2008
    Posts
    21
    Correction!!

    StarTrekker your code solved my problem! - it was I who misspelled? it when I was typing it, actually I skipped the double quotes and ampersand, see red texts in the code bellow
    Code:
    WHERE [TT_Number] = " & [Forms]![TXFaults].[TT_Number]
    Quote Originally Posted by StarTrekker
    Do you have any parameters used in the query that you are trying to open, or in it's Parameters dialog box (Query - Parameters)?

    I'd be trying some code more like this:

    Code:
    Private Sub Form_Load()
       Dim vntTempData as Variant
       Dim rstUpdates As Recordset
       Set rstUpdates = CurrentDb.OpenRecordset("SELECT * FROM [Daily Fault Update] WHERE [TT_Number] = " & [Forms]![TXFaults].[TT_Number])
       While Not rstUpdate.EOF
          vntTempData = vntTempData & rstUpdates!Description
          rstUpdates.MoveNext
       Wend
       Me.FaultComms = vntTempData
    End Sub
    * Disclaimer: Code written on the fly, so 'scuze any bugz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •