Selection Box in Access

[mp3909]

I need to create an output file (i.e. an excel file) from a table in Access.


The table consists of many fields (i.e. about 20 fields).

I need to give the user the ability to choose which columns they would like to take from the table into the excel output file.

I am wondering how can I present to the user a box that shows all the available columns and they can choose which one they want to include.

I was thinking if it is possible to implement something like the below in Access? If not, what other options do I have?

[davegri]

That's way too complicated.
I would just have a label below the listboxes saying "To select/unselect multiple items, hold CTRL while clicking"

[Micron]

I think OP wants to know how to get a list of query or table fields into one side of the form.

Roughly speaking, you need to write code to support all the functionality; macros won't cut it. This code would gather a list of fields and populate the left listbox. However, you didn't say how the user knows what query or table is involved. Is that a combo box with a list of queries/tables involved? Whatever it is, you iterate over the fields collection and populate the left listbox. Then comes the decision on how to move the selections. Double click and move one at a time? Multi select then button click to use the moved values? The action then copies the selections to the other box and removes them from the source. Then another button transfers to Excel (maybe). But transfers what, exactly? Just field names into columns? That AND the data in them? Moving data will require more code to construct a query or table on the fly, using the selected fields. You pick another query/table from the combo and start all over again?

Not too difficult if you know how, but I'd say if one knew all that plus the details behind the scenes (names of tables/queries involved, etc.) it's maybe a couple of hours of work. If you have no idea on how to iterate over a fields collection and construct sql on the fly, then maybe double or triple that?

[Gicu]

Not too difficult to do, here is a sample you can adapt for your needs.

Cheers,
Vlad

[mp3909]

Not too difficult to do, here is a sample you can adapt for your needs.

Cheers,
Vlad

Hi Gicu,

Thank you so much for sharing this information, that's extremely helpful and I am very thankful!

My only problem is that when I select a different table in this form, the fields are not getting updated, it is still showing the fields from the old table.

I see there is an event macro that automatically executes after selecting a different table:

Private Sub cboMyTables_AfterUpdate()
Me.lstMyFields.Requery
End Sub

[Gicu]

Just change the code to add the line to update the row source:

Private Sub cboMyTables_AfterUpdate()
Me.lstMyFields.RowSource = Me.cboMyTables
Me.lstMyFields.Requery
End Sub

[mp3909]

Here is some snapshots to make it a little more clearer:



New table selected but still showing columns from previous table:

[Gicu]

Have you tried to add the rowsource line?

[mp3909]

it is working now after adding

the line to update the row source.
Thank you very much Gicu for your help.

[Gicu]

Glad to hear you got it working!
Vlad

[mp3909]

I am trying to access this form "frmCustomExcelExport" from within my main form "Form1".
In Form1 I have a command button called "cmdOutputLimitFile" and when the user clicks on this button, I want your form "frmCustomeExcelExport" to show up with preselected fields as per this image:



So far I have written this VBA code:

Private Sub cmdOutputLimitFile_Click()
    Forms![frmCustomExcelExport].SetFocus
End Sub

but I get an error saying "Microsoft Access cannot find the referenced form 'frmCustomExcelExport'

[mp3909]

Please ignore my last request, I managed to solve it.
I write the following code:

Private Sub cmdOutputLimitFile_Click()

DoCmd.OpenForm "frmCustomExcelExport", acNormal

Dim sSql As String
sSql = "SELECT [Column Header] FROM tblMandatoryColumns "
sSql = sSql & vbNewLine & "WHERE Report = 'Limit_Exposure_Report'"

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(sSql)

Dim i As Integer
Dim j As Integer

For i = 0 To Forms![frmCustomExcelExport].Controls("lstMyFields").ListCount - 1
     
    For j = 0 To rs.Fields.Count - 1
    
        Do While Not rs.EOF
        
            Debug.Print rs(j) & " " & j
            Debug.Print Forms![frmCustomExcelExport].Controls("lstMyFields").ItemData(i) & " " & i
            
            If Forms![frmCustomExcelExport].Controls("lstMyFields").ItemData(i) = rs(j) Then
                Forms![frmCustomExcelExport].Controls("lstMyFields").Selected(i) = True
                Exit For
            End If
            
            rs.MoveNext
        Loop
        
    Next j
    
Next i
End Sub

[mp3909]

Hi Gicu,

I set the "Pop Up" property to "No" and then switched it back to "Yes" but now I cannot see the form in form view.
I can only see it if I put the "Pop Up" property back "No" but I want it to Pop up