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.

this may be acieved by Binding a DataReport To an ADO Recordset at Run Time, create a query based on user selection and set DataSource to query result.
For details refer http://support.microsoft.com/kb/190411

'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
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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.