• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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/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,
  • 4
  • 4
1 Solution
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)
End With
Me.PLI = str
darkdellAuthor Commented:
I get a "Comple error Invald use of property"
at the:   Me.PLI = str
darkdellAuthor Commented:
Also, no items are selected nor will they be. They are just shown to the user.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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?
darkdellAuthor Commented:
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.

I see now (it was in your previo┬┐s question). You can collect all fields from listbox with this code (add it to approve button, if you have it, PLI_txt should be bounded to your table's field PLI_Numbers):
Dim str As String, i As Integer
str = ""
With Me.List0         'Listbox name
    For i = 0 To .ListCount - 1
      If Len(str) > 0 Then str = str & ", "
      str = str & .ItemData(i)
    Next i
End With
Me.PLI_txt = str

Open in new window

You can also do it when you fill this listbox
darkdellAuthor Commented:
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:

 'Open Start Date Calendar
 Me.StartDate_txt.Enabled = True
 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.
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"

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now