Saving Unbound multi-select

[Airwolf via AccessMonster.com]

I am using an unbound multi-select list box as the interface where the user
can
select the zip codes they want. However, I want to be able to store this data
in a table so that each site has their zip codes highlighted when I open the
contact information form. Is this possible, and if so, how might I go about
doing this?

 

[John W. Vinson]

I am using an unbound multi-select list box as the interface where the user
can
select the zip codes they want. However, I want to be able to store this data
in a table so that each site has their zip codes highlighted when I open the
contact information form. Is this possible, and if so, how might I go about
doing this?

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' 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 AnimalCondition 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("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = 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.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MultiSelect.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Daniel]

Should be too hard.

you'll need a table that cross-references each user or group (depending on
setup) to the zip code they should be associated with by default. Then when
the form loads you'll need to use Dlookup to perform the check and adjust the
default value accordingly.

Another alternative is to simply make a couple of different versions (1 per
area) and adjust the default value manually).

 

[Airwolf via AccessMonster.com]

Thanks for all the suggestions, I think I'll need to make a table.
Daniel, what do you mean by making a couple of different versions?

 

[Airwolf via AccessMonster.com]

Roger... do you have a 2003 compatible version of the file? when I tried to
convert it, I ended up with a lot of problems.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MultiSelect.mdb" which illustrates how to do this.

I am using an unbound multi-select list box as the interface where the user
can

[quoted text clipped - 5 lines]

about
doing this?

 

[Roger Carlson]

I have uploaded a new sample that is still in Access 2000 format, but which
will convert to Access 2003 with no difficulty. I tried converting it, and
I had the same problems you did with the original, but I have been able to
convert this new copy. Sorry for the inconvenience.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger... do you have a 2003 compatible version of the file? when I tried
to
convert it, I ended up with a lot of problems.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MultiSelect.mdb" which illustrates how to do this.

I am using an unbound multi-select list box as the interface where the
user
can

[quoted text clipped - 5 lines]

about
doing this?

 

[Airwolf via AccessMonster.com]

Thanks!

I have uploaded a new sample that is still in Access 2000 format, but which
will convert to Access 2003 with no difficulty. I tried converting it, and
I had the same problems you did with the original, but I have been able to
convert this new copy. Sorry for the inconvenience.

Roger... do you have a 2003 compatible version of the file? when I tried
to

[quoted text clipped - 9 lines]