My MS Access form contains the following items:
· An unbound listbox with the Multi Select property set to “Extended” that queries unposted transactions (PostingStatus = “Open”) from a table named “tblData”
· A command button named “cmdPost”
I would like to select various records from the listbox , click the post button, and set the PostingStatus value for those records to “Post”. It seems like there should be vb code in the event procedure “cmdPost_Click()” to execute this function. I have tried the following code but there appears to be a problem with the “Rs.Edit” line. Any suggestions? Would it be easier to do this with a bound listbox?
Private Sub cmdPost_Click()
Dim CurDB As Database, Rs As Recordset, SQLStmt As String, Trx As Variant, ctl As Control
Set CurDB = CurrentDb()
Set ctl = Me![ID]
'For each record selected in listbox
For Each Trx In ctl.ItemsSelected
'Set PostingStatus to "Posted"
SQLStmt = "SELECT * FROM tblData WHERE [ID] = '" & ctl.ItemData(Trx) & "'"
Set Rs = CurDB.OpenRecordset(SQLStmt)
Rs.Edit
Rs!PostingStatus = "Posted"
Rs.Update
Rs.Close
End Sub
· An unbound listbox with the Multi Select property set to “Extended” that queries unposted transactions (PostingStatus = “Open”) from a table named “tblData”
· A command button named “cmdPost”
I would like to select various records from the listbox , click the post button, and set the PostingStatus value for those records to “Post”. It seems like there should be vb code in the event procedure “cmdPost_Click()” to execute this function. I have tried the following code but there appears to be a problem with the “Rs.Edit” line. Any suggestions? Would it be easier to do this with a bound listbox?
Private Sub cmdPost_Click()
Dim CurDB As Database, Rs As Recordset, SQLStmt As String, Trx As Variant, ctl As Control
Set CurDB = CurrentDb()
Set ctl = Me![ID]
'For each record selected in listbox
For Each Trx In ctl.ItemsSelected
'Set PostingStatus to "Posted"
SQLStmt = "SELECT * FROM tblData WHERE [ID] = '" & ctl.ItemData(Trx) & "'"
Set Rs = CurDB.OpenRecordset(SQLStmt)
Rs.Edit
Rs!PostingStatus = "Posted"
Rs.Update
Rs.Close
End Sub