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