Hi Ladies and Gents.
I have written an application that reads data from a MS Access database. What I am trying to achieve is that on my data report connected to only 1 data environment, I would like the user to select what fields they want displayed whether it be by listbox or drop down box. My command query selects all fields. I would greatly appreciate it if anyone can help me.

'Create Blank form
'Add a List control and change the style property to "checkbox"
'Add Command Button
'Place you mdbFile in the apps path
'then set the values for YourMdbFile and YourTableName


'Run and click command1
'The function GetUserSelectStr will create an SQL Select String you can then pass
'to a procedure to display the users selections. Hope this helps, good luck!

**** Start of Form Code **************
Dim YourMdbFile As String
Dim YourTableName As String

Private Sub Command1_Click()
MsgBox GetUserSelectStr(YourTableName) ' Enter Your Table Name Here
End Sub

Public Sub LoadDbFieldList(dbFilepath As String, dbTableName As String)
Dim db As Database
Dim rs As Recordset
Dim Td As TableDef
Dim Fld As Field
Set db = OpenDatabase(dbFilepath)
For Each Td In db.TableDefs
If Td.Name = dbTableName Then
For Each Fld In Td.Fields
List1.AddItem Fld.Name
Next Fld
End If
Next Td
db.Close
End Sub

Public Function GetUserSelectStr(dbTableName As String) As String
Dim i
Dim StartBuildSelectStr, MidBuildSelectStr, TailSelectStr, CompleteSelectStr As String
StartBuildSelectStr = "Select "
TailSelectStr = " from " & dbTableName & ";"
For i = 0 To List1.ListCount - 1
If List1.Selected(i) Then
MidBuildSelectStr = MidBuildSelectStr + "[" + List1.List(i) + "], "
End If
Next i
MidBuildSelectStr = Mid(MidBuildSelectStr, 1, Len(MidBuildSelectStr) - 2)
CompleteSelectStr = StartBuildSelectStr + MidBuildSelectStr + TailSelectStr
GetUserSelectStr = CompleteSelectStr
End Function

Private Sub Form_Load()
YourMdbFile = "tscs.mdb"
YourTableName = "Customers"
LoadDbFieldList App.path & "\" & YourMdbFile, YourTableName
End Sub

'**** End of Form Code

This article has been dead for over six months. Start a new discussion instead.