Results 1 to 1 of 1
  1. #1
    SALPBE is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2011
    Posts
    4

    Arrow Current Record Update Issue

    The code below concatenates a table of information and then inserts this string into ECR_Table.FinishedGoodAffected



    Problem is that I want to modify the current record being viewed in the form. It only seems to modify the second record.

    How can I modify to make it update the current record on the form?
    (I think the issues is withing the RED code text)

    Code:
    Private Sub lstFinishedGoods_LostFocus()
    'Code modified from http://www.mvps.org/access/modules/mdl0008.htm
       Dim lodb As DAO.Database, lors As DAO.Recordset
       Dim rs1 As DAO.Recordset
       Dim lovConcat As String, loCriteria As String
       Dim loSQL As String
       Dim booFirst As Boolean
       Dim fConcatFld As String
       On Error GoTo Err_fConcatFld
       lovConcat = ""
       Set lodb = CurrentDb
       loSQL = "SELECT BillNumber FROM Table1"
       Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
       booFirst = True
       With lors
          If .RecordCount <> 0 Then
             'start concatenating records
             Do While Not .EOF
                If booFirst = True Then
                   lovConcat = lovConcat
                   booFirst = False
                End If
                lovConcat = lovConcat & lors![BillNumber] & vbCr & vbLf
                .MoveNext
             Loop
          Else
             GoTo Exit_fConcatFld
          End If
       End With
       Set rs1 = lodb.OpenRecordset("Select * from ECR_Table", DB_OPEN_DYNASET)
       If rs1!FinishedGoodAffected = "" Then
          rs1.GetRows
          rs1!FinishedGoodAffected = lovConcat
       Else
          rs1.GetRows
          rs1.Edit
          rs1!FinishedGoodAffected = rs1!FinishedGoodAffected & " & " & vbCr & vbLf & lovConcat
       End If
       rs1.Update
    Exit_fConcatFld:
       Set lors = Nothing
       Set lodb = Nothing
       Set rs1 = Nothing
       DoCmd.SetWarnings False
       DoCmd.RunSQL ("Delete *.* from Table1")
       DoCmd.SetWarnings True
       Exit Sub
    Err_fConcatFld:
       MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
       Resume Exit_fConcatFld
    End Sub
    Last edited by RuralGuy; 02-24-2011 at 11:17 AM. Reason: Added Code Tags

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

Similar Threads

  1. Current record number
    By turvoll in forum Programming
    Replies: 1
    Last Post: 11-04-2010, 10:09 AM
  2. Run Update Query on Current Record in Form
    By c3pse in forum Queries
    Replies: 3
    Last Post: 08-14-2010, 05:40 AM
  3. copy current record to new record
    By er_manojbisht in forum Forms
    Replies: 1
    Last Post: 02-27-2010, 05:31 PM
  4. Replies: 3
    Last Post: 12-08-2009, 01:02 AM
  5. Mailmerge Current Record...
    By oakleyp2 in forum Programming
    Replies: 0
    Last Post: 02-22-2006, 08:08 AM

Tags for this Thread

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