asked on

Storing ListBox values in a single field of a access database

I have a Listbox that is popluated from a function that produces a unique indexing number(PLI) system.
A/AAX
A/AAY
A/AAZ Thru Z/ZZZ etc.

The listbox is unbound.
The form is bound to a query.

How do I store my Listbox values into the table(tbl_Project) field named PLI for future use.
I am assuming the data must be delimited in some way.

Thank you,

als315

You can add this field (PLI) to your form (it may be hidden) and this code in afterupdate event of listbox (let listbox name will be List0) will save selected values:
Dim str As String
Dim item As Variant
str = ""
With Me.List0 'Your ListBox
For Each item In .ItemsSelected
If Len(str) > 0 Then str = str & ", "
str = str & .ItemData(item)
Next
End With
Me.PLI = str

darkdell

ASKER

I get a "Comple error Invald use of property"
at the: Me.PLI = str

darkdell

ASKER

Also, no items are selected nor will they be. They are just shown to the user.

als315

You should have text field PLI on your form. But it is not clear: why you need list box if you don't like to select items?

darkdell

ASKER

There is a text field named PLI_txt per your instructions. The listbox is used to show the user the number ID seqs generated by the function. What better way to show a user a list than using a listbox?

So, this occurs for the each "Project" that is created. Once the user approves the ID seqs for the specific project I need to store them in a project's database field for future use. So I will be selecting them all.

I would rather hold the ID Seqs in the "tbl_Project" in a field called "PLI_Numbers" rather than have a seprate related table (long story) ,so please save me the normlizing lecture. Lol
Fyi - PLI stands for: Project Lifecycle Indicator.

Thanks

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

darkdell

ASKER

That was great Thank you. One quick side question; do you know to make the datepicker open when a Form is first Opened? I am using:

OnOpen
'Open Start Date Calendar
Me.StartDate_txt.Enabled = True
Me.StartDate_txt.SetFocus

Dim ctlCurrentControl As Control
Set ctlCurrentControl = Screen.ActiveControl
If ctlCurrentControl.Name = "StartDate_txt" Then
'MsgBox "I'm Active"
Application.RunCommand (acCmdShowDatePicker)

End If

Can't figure why it won't work.

als315

Datepicker will work if field has type - Date/Time. So set its Format property to one of the Date formats, and set its [Show Date Picker] property to "For Dates"