Edit multiple records from a listbox 1

[mcfarland]

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

 

[kewo99]

Before rs.edit I would check to make sure that a record was returned by SQLstmt:

If rs.recordcount > 0 then

also, I would specifically declare the recordset as a dynaset:
Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)

Ken

 

[mcfarland]

Thanks Ken. I revised my code to look like the following but I’m still getting the same error. Could the Dim variables be incorrectly defined? When type “Rs.”, the auto drop-down list doesn’t contain “Edit”. Does that matter?

John

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, dbOpenDynaset)

If Rs.RecordCount > 0 Then
Rs.Edit
Rs!PostingStatus = "Posted"
Rs.Update
Rs.Close
End If
End Sub

 

[kewo99]

If you're using Access 2k or XP then it may be defaulting to ADO instead of DAO. I have no experience with ADO -- was Access 97 only until just recently - employer switched to XP.
Make sure you have a reference set to DAO 3.x
Change your dim to: rs as DAO.Recordset

Hope that helps.

Kenm

 

[mcfarland]

I’m using Access 2000. That seemed to work for the “Rs.Edit” problem. However, I’m seeing another error with the “Set ctl = Me![ID]” line. “ID” is the unique primary key in tblData. How should I define what has been selected in the listbox, which is named “lstPostTrx”? The following is the revised code.

Private Sub cmdPost_Click()

Dim CurDB As Database, Rs As DAO.Recordset, SQLStmt As String, Trx As Variant, ctl As Control

Set CurDB = CurrentDb()
Set ctl = [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, dbOpenDynaset)

If Rs.RecordCount > 0 Then
Rs.Edit
Rs!PostingStatus = "Posted"
Rs.Update
Rs.Close
End If

Next

End Sub

Thanks,
John

 

[kewo99]

The Set statement needs to use the control name, not the field name

Set ctl = lstPostTrx (may have to use Me!lstPostTrx or Froms!formname!lstPostTrx)

Ken

 

[mcfarland]

Thank you for you help so far, Ken. I figured that I could give you a star. I fixed the “ctl” statement and it seemed to work.

Now, the Debug is taking me to the “Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)” line. When I drag the cursor over the “SQLStmt” portion of the statement, it appears to be recognizing the first record ID that was selected in the listbox (SQLStmt = “SELECT * FROM tblData WHERE [ID] = ‘47’”). When I drag the cursor over the “dbOpenDynaset” portion of the statement, it indicates “2” (dbOpenDynaset=2). I’m not familiar with dynasets and I’m wondering if there is a problem with how this is working.

Private Sub cmdPost_Click()

Dim CurDB As Database, Rs As DAO.Recordset, SQLStmt As String, Trx As Variant, ctl As Control
Set CurDB = CurrentDb()
Set ctl = Forms!frmPost!lstbxPostTrx

'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, dbOpenDynaset)

If Rs.RecordCount > 0 Then
Rs.Edit
Rs!PostingStatus = "Posted"
Rs.Update
Rs.Close
End If

Next

End Sub

Thanks again,
John

 

[kewo99]

John,

Is the field ID a number or text field? The SQLStmt is treating it as text (surrounded by the apostrophe character). If it is a number you need to remove the aposthophes.

SQLStmt = "SELECT * FROM tblData WHERE [ID] = " & ctl.ItemData(Trx)

Ken

 

[mcfarland]

Good work! Ken, thanks for your patients.

John