List Box: Populating table from form

brentos2004

Registered User.
Local time
Today, 06:26
Joined
Nov 30, 2005
Messages
11
Hi,

I have a form with various list boxes that display options based on a query. When options are selected the text relating to them is populated in a seperate text box at the side.

The problem im having is when I select options from the list box I want it to store the selected options in the table that holds each record when the form has been filled out and submitted. At the minute it populates everything into the table apart from the options selected from the list boxes.

Does anyone know how to do this?

Thanks in advance

Edit: I have got the control souurce of the list boxes set to the correct field in the table.
 
Last edited:
Does anyone have any ideas?

All I need it to do is store the options selected in the specified field in the table. So if the user selects Flamable and explosive from the list they are added to the field specified in the control source property of the list box.

Just cant seem to get it to store the selected values Help!!!!!!
 
There are a few ways to do this:

The dirty way
The fields in your main table you want to fill in are textfields. Simply place the fieldname in the Controlsource-property of your combo.

In your main table, the option the user selected will now be stored as plain text.

The clean way
In the table(s) that contain the various options for your combo's, each value has a unique numeric record-identifier. For example:

RecordID - Value
--------------------------
1 - Flammable
2 - Toxic
3 - Hazardous
etc...

Instead of storing the entire text-value in each record of your main table, you'll want only to store the unique RecordID. This will take alot less space.

So, how to do this?

In the query that lists out your values for a combo, add the RecordID as the FIRST column, followed by the value in the second one.

Change the columnwidths like this : 0cm;Xcm - where X is a value you may choose as you want. Also change the number of columns to 2 where this would have been 1 before you changed your query.

If you do this, the user will select a value from the list, and access will only store the RecordID in the main table instead of the entire text-value.

Also, don't forget to set the controlsource to the field from your table.

I hope this helps you out!

Seth
 
thanks for the reply seth.

I have got the field name in the control source property of the list box but when selections are made it is not storing anything in that field in the table.

The list boxes are set for multi select - simple mode so the user could select 3 options for instance and they would all be stored in the relevant field but thats not whats happening.

Im attaching my database hopefully somebody can help.
 

Attachments

Last edited:
Does anyone have any ideas???

Is what I want to do actually possible because its driving me crazy now!! HELP!!!
 
I'm working on it. Close to sollution, hang on for 10 minutes! ;)
 
:eek: That was more difficult then I thought! ;)

I attached your database to this post, I modified all the combo's to work.

So, how did I do it?

First off: For this to work, the name of your Control needs to be different from the name in your table. So to some control-names I added a "_". Don't worry, I modified the underlying code to match.

I created three custom functions:

Code:
'############################################################################################
'## Store the values selected by a multiple-select combo                                   ##
'## Code supplied by : Seth_Belgium (seth_belgium@hotmail.com)                             ##
'############################################################################################
Function StoreMultipleValues(ctl As Control, tablefield)
On Error GoTo Err_StoreMultipleValues

    Dim varItm As Variant
    Dim tempValue As String

    tempValue = "" 'Reset temporary value
    
    'Gather all selected values and store them as a string into the variable tempValue
    For Each varItm In ctl.ItemsSelected
        If tempValue = "" Then
            tempValue = ctl.ItemData(varItm)
        Else
            tempValue = tempValue & ";" & ctl.ItemData(varItm)
        End If
    Next varItm
    
    'Store the temporary value in the table-field
    tablefield.Value = tempValue & ";"

Exit_StoreMultipleValues:
    Exit Function

Err_StoreMultipleValues:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " - " & Err.description 'Display error number & description
    End Select
End Function

The above code saves the selected values to your table (a memo field) in the form of: value1;value2;value3;

Code:
'############################################################################################
'## Retreive the values stored from a multiple-select combo                                ##
'## Code supplied by : Seth_Belgium (seth_belgium@hotmail.com)                             ##
'############################################################################################
Function RetrieveMultipleValues(ctl As Control, tablefield)
Dim n, m As Integer
Dim item, tempItem

'Reset previous selections
m = -1
Do
    m = m + 1
    ctl.Selected(m) = False
Loop Until m = ctl.ListCount

'Start retrieving values and select them
n = -1
tempItem = tablefield

    Do
        n = n + 1
        item = Mid(tempItem, 1, InStr(1, tempItem, ";") - 1)
        
        If ctl.ItemData(n) = item Then
            ctl.Selected(n) = True
            tempItem = Mid(tempItem, InStr(1, tempItem, ";") + 1, Len(tempItem) - InStr(1, tempItem, ";") + 1)
        End If
    Loop Until n = ctl.ListCount Or tempItem = ""
End Function

This one retrieves the stored values from your table and selects them in the combo's.

Code:
Function RetrieveAllCombos()
'Retreive values for multiple-select combo's
RetrieveMultipleValues Me.Health_Hazrard, Me.[Health Hazard]
RetrieveMultipleValues Me.Precautions_, Me.Precautions
RetrieveMultipleValues Me.FireExplosion, Me.[Fire / Explosion]
RetrieveMultipleValues Me.RisktoHealth, Me.[Risk to Health]
RetrieveMultipleValues Me.FirstAid, Me.[First Aid]
RetrieveMultipleValues Me.Emergency_, Me.Emergency
RetrieveMultipleValues Me.Environmental_, Me.Environmental
End Function

That's the last function I wrote. It's simply the one that retrieves all the values for all combo's when you load the form, or navigate through your records.

That also means that you need to have custom navigation-buttons on your form (also added them... ;)).

The last thing I did was to add this line at the end of your existing AfterUpdate()-code for each combo:

Code:
StoreMultipleValues Me.ControlName, Me.FieldnameInTable

There it is... If anyone has any suggestions to perfect the above code, or any questions about it, just reply or send me PM.

Greetz,

Seth
 

Attachments

Seth, I dont know quite what to say apart from you are amazing!!

Thanks so much for all the help it really is appreciated!!

Just gota try and make the reports now so il probably be back tomorrow with more problems!

Thanks again
Brent
 

Users who are viewing this thread

Back
Top Bottom