Loading
Sequential Record Update

  • Donald QuigleyPosted May 15, 2020, 9:13 AM

    I'm sorry, Jefferson, but where would that code be inserted? 
    .. and shouldn't there be a period between rs and [IDER]?
    0
  • Jefferson S. Motta

    Jefferson S. MottaPosted May 15, 2020, 7:47 AM

    Hi,
     
    the error is a runtime error from VBA.
     
    you need convert to string your var "SQL", like " & CStr(rs[IDER])) & "
    0
  • Salman Beg

    Salman BegPosted May 15, 2020, 7:38 AM

    Type mismatch error means I think the problem with datatype. Check datatype. Thanks
    0

  • Donald QuigleyPosted May 15, 2020, 7:20 AM

    Jefferson - That did not work.
     
    I get a Run-time error '13': Type mismatch.
    0
  • Jefferson S. Motta

    Jefferson S. MottaPosted May 14, 2020, 6:06 PM

    Hello, you are missing the clausula where:
     
     WHERE " + rs[IDER] + " = tblCensus4.[IDER];"
    1. Public Sub Recordset()    
    2. Dim db    
    3. Dim rs    
    4. Dim strvalue As String    
    5. Dim SQL As String    
    6. Set db = CurrentDb    
    7. Set rs = db.OpenRecordSet("tblCensus1Sorted")    
    8. If Not (rs.EOF) Then    
    9. Do While Not rs.EOF    
    10. strvalue = rs.GivenName.Value & " " & rs.Surname.Value    
    11. 'View strvalue in Immediate Window    
    12. Debug.Print strvalue    
    13. SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & strvalue  
    14.   
    15. WHERE " + rs[IDER] + " = tblCensus4.[IDER];"   
    16.   
    17. DoCmd.RunSQL SQL    
    18. rs.MoveNext    
    19. Loop    
    20. rs.Close    
    21. Set rs = Nothing    
    22. Set db = Nothing    
    23. End If   
     
    0

  • Donald QuigleyPosted May 14, 2020, 5:56 PM

    IDER refers to a common field name in the two tables.  The problem is that the first table has multiple records with the same IDER value and the second table has only one record for that IDER value.  I'm trying to concatenate fields from the first table for each IDER value into the second one for that IDER value.
    The attached code will get all the right values from the first table, but it throws them all into every record in the second table; ie, with no regard to the IDER values.
    0
  • Jefferson S. Motta

    Jefferson S. MottaPosted May 14, 2020, 1:30 PM

    You can use too:
     
    SELECT CAST(column1 as nvarchar) +  ',' + CAST(column2 as nvarchar)+ ',' + CAST(column3 as nvarchar)
    AS YOUR_NAME_COLUMN
     
     
    What you means for: IDER? 
    0

  • Donald QuigleyPosted May 13, 2020, 10:46 PM

    Here is my problem. I need to concatenate two fields from multiple records into a single field, separated by CR&LF, and copy them into one field of one record in a related table (common "IDER"). I thought this would do it, but I keep getting this text box:
     
    Enter Parameter Value?
    strvalue
     
    Code
    1. Public Sub Recordset()  
    2. Dim db  
    3. Dim rs  
    4. Dim strvalue As String  
    5. Dim SQL As String  
    6. Set db = CurrentDb  
    7. Set rs = db.OpenRecordSet("tblCensus1Sorted")  
    8. If Not (rs.EOF) Then  
    9. Do While Not rs.EOF  
    10. strvalue = rs.GivenName.Value & " " & rs.Surname.Value  
    11. 'View strvalue in Immediate Window  
    12. Debug.Print strvalue  
    13. SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & strvalue;"  
    14. DoCmd.RunSQL SQL  
    15. rs.MoveNext  
    16. Loop  
    17. rs.Close  
    18. Set rs = Nothing  
    19. Set db = Nothing  
    20. End If  
    As you can probably tell, I know only enough about VBA to get myself in trouble. Any help would be appreciated.
    0

  • Donald QuigleyPosted May 13, 2020, 6:37 AM

    Th CONCAT part is understood.  But how to get the 3 records into a one record field (based on the IDER) is the issue.
    0
  • Jefferson S. Motta

    Jefferson S. MottaPosted May 13, 2020, 4:51 AM

    select CONCAT(GivenName, ' ' + SurName) as myTextData
    From MyTABLE WHERE [YOUR_CRITERIA]
    ORDER BY [YOUR_fieldId]
    0

  • Donald QuigleyPosted May 12, 2020, 8:17 AM

    Field X
    June Barbara Quigley   |
    Francis Albert Quigley  | Row Y
    Ida Louise Wagner       |
                 :
    GivenName Surname a  |
    GivenName Surname b  | Row Z
    GivenName Surname c  |
    GivenName Surname d  |
    0
  • Suraj Kumar

    Suraj KumarPosted May 12, 2020, 1:35 AM

    What is your expected output? Please provide that also.
    0

  • Donald QuigleyPosted May 9, 2020, 5:08 PM

    I want to concatenate text items like these into one field in another table.
     
    0
  • Suraj Kumar

    Suraj KumarPosted May 9, 2020, 1:30 PM

    You can provide more details like the table structure/schema, sample data, and expected output for an answer.
    0