Hi all,
I'm sure this is something simple, but I just can't pinpoint it. I've searched the boards, googled to death and I don't see anything that stands out causing my rst.addnew to overwrite a random record while putting in the new record.
Let me explain. I have a multiselect listbox that adds records into a table for those selected. It's adding the records, but not before replacing the LogID and blanking out a grantID for a random existing record in the table.
Here is my code:
Dim db As dao.Database
Dim rst As dao.Recordset
Dim var As Variant
Dim intLogID As Integer
Dim strInactive As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblgrantlog_join", dbOpenDynaset)
If Me.Inactive.Value = False Then
strInactive = False
Else
strInactive = True
End If
intLogID = Me.txtLogID.Value
'check that listbox is not null
If Me.lstGrants.ItemsSelected.Count = 0 Then
msgbox "Please select at least one grant to add to the log", vbOKOnly
Exit Sub
End If
For Each var In Me.lstGrants.ItemsSelected
rst.AddNew
rst!LogID = intLogID
rst!GrantID = Me.lstGrants.Column(0, var)
rst!Inactive = Me.Inactive.Value
rst!PAAD = Me.chkPAAD.Value
rst!AAAD = Me.chkAAAD.Value
rst!datEofchange = Date
rst.update
Next var
'close recordset
rst.Close
'requery main log form
Forms!frmaccess.lstGrants.Requery
'requery listbox
Me.lstGrants.Requery
Set rst = Nothing
Set db = Nothing
Me.chkAAAD.Value = False
Me.chkPAAD.Value = False
Me.Inactive.Value = False
Any assistance would be appreciated!
Sarah
I'm sure this is something simple, but I just can't pinpoint it. I've searched the boards, googled to death and I don't see anything that stands out causing my rst.addnew to overwrite a random record while putting in the new record.
Let me explain. I have a multiselect listbox that adds records into a table for those selected. It's adding the records, but not before replacing the LogID and blanking out a grantID for a random existing record in the table.
Here is my code:
Dim db As dao.Database
Dim rst As dao.Recordset
Dim var As Variant
Dim intLogID As Integer
Dim strInactive As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblgrantlog_join", dbOpenDynaset)
If Me.Inactive.Value = False Then
strInactive = False
Else
strInactive = True
End If
intLogID = Me.txtLogID.Value
'check that listbox is not null
If Me.lstGrants.ItemsSelected.Count = 0 Then
msgbox "Please select at least one grant to add to the log", vbOKOnly
Exit Sub
End If
For Each var In Me.lstGrants.ItemsSelected
rst.AddNew
rst!LogID = intLogID
rst!GrantID = Me.lstGrants.Column(0, var)
rst!Inactive = Me.Inactive.Value
rst!PAAD = Me.chkPAAD.Value
rst!AAAD = Me.chkAAAD.Value
rst!datEofchange = Date
rst.update
Next var
'close recordset
rst.Close
'requery main log form
Forms!frmaccess.lstGrants.Requery
'requery listbox
Me.lstGrants.Requery
Set rst = Nothing
Set db = Nothing
Me.chkAAAD.Value = False
Me.chkPAAD.Value = False
Me.Inactive.Value = False
Any assistance would be appreciated!
Sarah