Change 'Show' Property with VBA Code

[tho28199]

Hello all,

I have tried searching through this forum for clues on whether this is possible, but I'm not even sure how to phrase the search criteria.

I would like to create a form that has a check box for each field in an underlying query. I can do this.

I would then like to change the 'Show' property of the corresponding fields in the query (turning it on if the check box is true, off if false).

When the user checks which fields they want to appear, then clicks a command button on the form, the query will be executed to output the results to an Excel file, which will only contain the selected fields.

Is this even possible, and if so, how do I change the 'Show' property with VBA code?

 

[DCrake]

Simple Software Solutions

Instead of using check boxes - as you may run out of room if your table contents increase, I would switch to a listbox that the user can multi select from. Your list box would contain two columns - only one visible. First being that ACTUAL fieldname in the table, the second being a more user friendly name that the user will understand. Once the user has selected all the fields he want to see in Excel you can then use VBA to iterate throught the selected items in the list box to generate the sql needed for your query.

Example:

For x = 0 To Me.LstfieldNames.ListCount -1
    If Me.LstFieldNames.Selected(x) = True Then
        strFields = strFields & Me.LstFieldNames.Column(0) & ", "
    End If
Next

Drop the last comma

strFields = left(strFields,Len(strFields)-1))

This will leave you with a string containing the selected fields seperated by commas which can then be used to generate the SQL for your query.

CodeMaster:

 

[tho28199]

I only wish the solutions were that simple

Thanks, DCrake

It appears the listbox is limited to 20 items, and my query is more than double that.

Would it be possible to contatenate the string results from 3 listboxes for the SQL? How would I code that?

I really don't know VBA code... I just try to pick up what I can learn and use from this awesome forum and it's very knowledgeable and helpful contributors like yourself.

Can you also assist with the syntax for the SQL?

 

[DCrake]

Simple Software Solutions

When using the wizard it limits you to 20 items, however, you can extend this manually.

To do this click on the properties of the list box and and select value list. Press Ctrl+F2 (Zoom) and add them manually here.

Don't forget to set the multi select property to True.

Once the user has highlighted all the fields they want to use they will will click a button to export to Excel. This is where you would put your code.

Sub CmdButton OnClick()

Dim strFields As String
Dim strSQL As String

For x = 0 To Me.LstfieldNames.ListCount -1
    If Me.LstFieldNames.Selected(x) = True Then
        strFields = strFields & Me.LstFieldNames.Column(0) & ", "
    End If
Next

strFields = Trim(strFields)

strFields = left(strFields,Len(strFields)-1))



strSql = "SELECT " & strFields & " " & _
       "FROM [B]YourTableName[/B] & _
       "WHERE " & [B]YourCriteria [/B]& ";"


Dim qdf As QueryDef
'This rewrites the sql content of the query to match the selections made by the user.
Set qdf = CurrentDb.QueryDefs("[B]YourQueryName[/B]")
     qdf.SQL = strSsql


End Sub

At this point go to queries and design the query and you will see that the fields are the ones chosen by the user.

You can now use this query to export to Excel.

David

 

[tho28199]

Almost there

Hi David,

Thank you for the assistance thus far. I am almost there. However, this piece of code is stringing together only the last list item selected, resulting in a circular reference when the query is executed.

For X = 0 To Me.lstFieldChoice.ListCount - 1
If Me.lstFieldChoice.Selected(X) = True Then
strFields = strFields & Me.lstFieldChoice.Column(0) & ", "
MsgBox strFields, vbOKOnly
End If
Next

I inserted the MsgBox so I could watch it execute.

Can you help me out with this code, please?

 

[DCrake]

Simple Software Solutions

Sorry,

Missed out a crutial bit of code

it should read

strFields = strFields & Me.lstFieldChoice.Column(0[COLOR="Red"],x[/COLOR])

Where x is the list index number.

David

 

[tho28199]

Amazing what a difference that makes, David.

Thank you so much for your assistance with this. It is working perfectly now.

Much appreciated.

 

[DCrake]

Simple Software Solutions

Glad to be of help

 

[tho28199]

Limit on number of items selected

Hi David,
I just discovered it is not working quite as perfectly as initially thought. The problem now is that, although I was able to create a list of 43 items, it appears the SQL limits me to a selection of only 30 items. Any more than that and I get the error message "Characters found after end of SQL statement".
Is this a limitation that I need to live with, or is there another solution?

 

[DCrake]

Simple Software Solutions

I think it lies with your field names I'm guessing you have field names with spaces in them. If this is the case then you will have to enclose each field in square brackets [Field Name 1], [Field Name 2], [Etc], [Etc]

If this is not the case run the code and put a top after the loop and debug print the strSql and post it back

David

 

[tho28199]

David, you were absolutely right about the field names, but not because of spaces. I missed a " mark after the 31st field in the list values, causing the remaining descriptions to become the column 0 values. Once I fixed that minor mistake, it works perfectly, returning results for all fields if all are selected.
Thanks again for your invaluable assistance.

 

[DCrake]

Simple Software Solutions

We got there in the end