message for sbendbuckeye

[daveonion]

hi hope you get this,
i you replied to one of my qestions a couple of weeks ago
see answer below. Anyway so far i have made the list box and combo boxes however i'm a bit stumped on how to turn the selected items in the list box into a query. Any ideas would be appreciated,
in advance thanks .
Dave


Here's one thought about how you might do that.

1. Give your user a form with a multiselect listbox. Select FieldList as RowSourceType on the listbox Data tab. This will allow him to select the fields he wants on the report.

2. Create a couple comboboxes based on selected fields above which would allow him to select primary sort, secondary sort, etc.

3. Create a query from the listbox selected fields and set your Order By clause from the comboboxes, if specified.

4. Autocreate a report based on the query and you have a starting point.

Sorry this is so brief but I'll be late for work. Hope this helps and good luck!

PS. How would you characterize your coding comfort level? Some of the above will involve some code.

 

[daveonion]

alright, i've attempted it myself with the following code

Private Sub Command6_Click()
Dim varitem As Variant
Dim strcri As String
Dim strsql As String
Dim db As DAO.database
Dim qu As DAO.Querydef
Set db = CurrentDb()
Set qu = db.QueryDefs("reportclaire&quot
For Each varitem In Me!List0.ItemsSelected
strcri = strcri & " car." & Me!List0.ItemData(varitem) & ","
Next varitem

strsql = "SELECT" & strcri & "FROM Car" & ";"
qu.sql = strsql
End Sub

however as you can see the select has a , at the end but in the sql statement the last field selected doesn't have a , so i need code to state the last field doesn't have a ,

 

[kewo99]

This should drop the last character from strcri:

strsql = "SELECT" & left$(strcri,len(strcri)-1) & "FROM Car" & ";"

Ken

 

[daveonion]

thanks kewo99