Help writing multi select list box selections to table

[mystif via AccessMonster.com]

I am a novice who has built a database to track vineyard spray records. I
have put a multi-select list box on a data entry form, and cannot figure out
how to get the selections made in that list box to GO somewhere. I need them
to populate to a table. I was given some sample code to work with, pasted it
into the VB editor and changed the field and table names accordingly (I think)
Can anyone see any obvious mistakes I have made here? I receive an error
message when the code runs NO CURRENT RECORD. (Code below)

Thank you in advance!

Private Sub cmdProcess_Click()

' Comments : Update the tblOrderNumbBlocksSelected table based on
' the selections in
' the unbound multiselect listbox lstbxVineyardBlockSelect.
' Newly selected rows will be added to the table,
' newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the tblOrderNumbBlocksSelected table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("tblOrderNumbBlocksSelected", dbOpenDynaset)
With Me!lstbxVineyardBlockSelect
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this SprayOrderNumber-Block
' combination is currently
' in the table
rs.FindFirst "[SprayOrderNumber] = " & Me.SprayOrderNumber & " AND
" _
& "[Block] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!SprayOrderNumber = Me.SprayOrderNumber
rs!OrderBlockID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subtblOrderNumbBlocksSelected.Requery

PROC_EXIT:
Exit Sub

 

[Lorenda]

What is the variable Me.Dirty?

I am a novice who has built a database to track vineyard spray records. I
have put a multi-select list box on a data entry form, and cannot figure out
how to get the selections made in that list box to GO somewhere. I need them
to populate to a table. I was given some sample code to work with, pasted it
into the VB editor and changed the field and table names accordingly (I think)
. Can anyone see any obvious mistakes I have made here? I receive an error
message when the code runs NO CURRENT RECORD. (Code below)

Thank you in advance!

Private Sub cmdProcess_Click()

' Comments : Update the tblOrderNumbBlocksSelected table based on
' the selections in
' the unbound multiselect listbox lstbxVineyardBlockSelect.
' Newly selected rows will be added to the table,
' newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the tblOrderNumbBlocksSelected table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("tblOrderNumbBlocksSelected", dbOpenDynaset)
With Me!lstbxVineyardBlockSelect
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this SprayOrderNumber-Block
' combination is currently
' in the table
rs.FindFirst "[SprayOrderNumber] = " & Me.SprayOrderNumber & " AND
" _
& "[Block] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!SprayOrderNumber = Me.SprayOrderNumber
rs!OrderBlockID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subtblOrderNumbBlocksSelected.Requery

PROC_EXIT:
Exit Sub

 

[mystif via AccessMonster.com]

I am not sure. I was given this code and I am not familiar with the me.dirty.
I do see "on dirty" as a property option in list and combo boxes but do not
know what it means.

What is the variable Me.Dirty?

I am a novice who has built a database to track vineyard spray records. I
have put a multi-select list box on a data entry form, and cannot figure out

[quoted text clipped - 65 lines]

PROC_EXIT:
Exit Sub

 

[Jason Lepack]

Me.dirty

Me is the form that is currently referenced
dirty is a property of the form. A form is dirty if it has data that
has been entered into a field but has not been written to the tables
yet (ex you can still hit ESC to clear the data that has been entered)

What is the variable Me.Dirty?

I am a novice who has built a database to track vineyard spray records. I
have put a multi-select list box on a data entry form, and cannot figure out
how to get the selections made in that list box to GO somewhere. I need them
to populate to a table. I was given some sample code to work with, pasted it
into the VB editor and changed the field and table names accordingly (I think)
. Can anyone see any obvious mistakes I have made here? I receive an error
message when the code runs NO CURRENT RECORD. (Code below)

Thank you in advance!
' Comments : Update the tblOrderNumbBlocksSelected table based on
' the selections in
' the unbound multiselect listbox lstbxVineyardBlockSelect.
' Newly selected rows will be added to the table,
' newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the tblOrderNumbBlocksSelected table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("tblOrderNumbBlocksSelected", dbOpenDynaset)
With Me!lstbxVineyardBlockSelect
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this SprayOrderNumber-Block
' combination is currently
' in the table
rs.FindFirst "[SprayOrderNumber] = " & Me.SprayOrderNumber & " AND
" _
& "[Block] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!SprayOrderNumber = Me.SprayOrderNumber
rs!OrderBlockID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subtblOrderNumbBlocksSelected.Requery

PROC_EXIT:
Exit Sub

- Show quoted text -

 

[Rick Brandt]

I am not sure. I was given this code and I am not familiar with the
me.dirty. I do see "on dirty" as a property option in list and combo
boxes but do not know what it means.

OnDirty is an event that fires when a record is "dirtied" which means
"changed but not yet saved". That event is only available in the newest
versions (sorry I don't know specifically which version added it).

The Dirty *property* can be tested to determine if a record is dirty and it
can be set to False to force a record save. That has been around for quite
a while (at least as far back as Access 95).

 

[mystif via AccessMonster.com]

Thank you both for your notes on me.dirty. I have changed the me to my form
name and it seems to like that code, but my results still do not write to the
table. Any further ideas? I would be happy to post this file for review.

Me.dirty

Me is the form that is currently referenced
dirty is a property of the form. A form is dirty if it has data that
has been entered into a field but has not been written to the tables
yet (ex you can still hit ESC to clear the data that has been entered)

What is the variable Me.Dirty?

[quoted text clipped - 73 lines]

- Show quoted text -

 

[John W. Vinson]

Can anyone see any obvious mistakes I have made here? I receive an error
message when the code runs NO CURRENT RECORD. (Code below)

That would suggest that the recordset you're opening returns no records. Are
[SprayOrderNumber] and [Block] Number fields (as the code expects) or Text?

Try setting a "breakpoint" in the code. Open the VBA editor on the routine,
and click in the grey vertical bar to the left of the code window next to some
executable statement - the line

Set db = CurrentDb

for example. A brown/red circle will appear. When you click the button, the
execution will stop at this line; you can press F8 to step through the code
line by line; hover the mouse over a variable to see its value; or type (e.g.)
?lngCondition

in the Immediate window to see its value. Without knowing more about the
structure of your tables, how your listbox is set up, etc. it's hard to guess
what might be going wrong!

John W. Vinson [MVP]