VBA to select specific columns from database to table

[cppd123]

I have a database with 20 fields/columns.

Normally when I generate the data with a form, all the 20 fields will appear on the table.

Now i want to use checkbox to allow user to choose which column to appear.

I have got this:

Private Sub genQuery()

sqlStr = "SELECT"
MsgBox sqlStr

If Me.[1] = True Then
        sqlStr = sqlStr & " Table.ColumnA "
    Else
        sqlStr = sqlStr & " Table.ColumnA "
End If

If Me.[2] = True Then
        sqlStr = sqlStr & " Table.ColumnB "
    Else
        sqlStr = sqlStr & " Table.ColumnB "
End If

If Me.[3] = True Then
        sqlStr = sqlStr & " Table.ColumnC "
    Else
        sqlStr = sqlStr & " Table.ColumnC "
End If

.
.
.

However, it says
"Syntax Error (missing operator) in query expression"

How may I solve it?

I am using Access 2007

Thank you!

[twinnyfo]

cppd123,

First, please use the Code tags when posting code to this site, so it is easier for us to read and analyze.

Second, you said:

Normally when I generate the data with a form, all the 20 fields will appear on the table.

I don't understand, as all the fieldds are in the table regardless of whether you put them on a form. Do you mean when you create a Form in Datasheet view? Or regular Text Boxes? Either way, you need to designate which fields to place on the form.

In your Sub, there are several problems, but I am not sure how deep the problems are, as you did not post all of the code--such as, how are you using your variable sqlStr? Are you setting the query as the record source for your Form? There is much left unsaid.

Finally, if you have a datasheet view on your form and have selected all the fields to be available on your form, I believe (I'm not sure about it, though), if you use your If...Then statements, you can set the Visible Property of the text boxes holding your data to False, and they will no longer show up in Datasheet view.

Any other experts have experience with htis? I think I remember using this method once, but cannot recall if it worked or not.

[twinnyfo]

Actually, I just checked the Visible Property on one of my datasheet forms, and this method will not work....

[Jim Doherty]

Field Selection

Hello cppd,

There are a number of ways you can do this and obviously it largely depends on techniques you employ and the relevant skill level in order to get it done.

One such method you might imagine is that of a list box of fields derived from your table and presented in a listbox on a screen dialog form. Your user then selects the fields they wish at which point you then define the SQL servicing a 'generic' query ie: 'one' persisted query that can be used repeatedly (as opposed to creating a miriad of separate queries or parametised queries that get their parameter values from a form or elsewhere.

I suspect this might be of interest to whosoever is reading this thread and . I have therefore quickly knocked up a demo to illustrate what it is I am eluding too. (it's as quick to simply download and take a look at the file as it would be for me to write a lengthy description of the principles involved here, forgive the lack of complete error handling routines at this stage. It will either work! or it will not)

The attached compressed zip file is an accdb desktop file created in Access 2013 office 365 (yes it can be downgraded to an mdb if needs be for earlier access versions however this demo creation is for the posting op essentially (Acc2007 format) in order that he/she can unbuckle it and digest at least one technique and method. You could import the form and generic query object into your database and it should work as is.

I may well be off the mark with your references to a datasheet form and fields on a form hiding and unhiding and so on but if all you need is a flexible changeable tabular output that presents itself as such on screen and which can then be outputted in the usual manner then I perceive there to be no gain per se by involving a form as such in this context, as this would involve code commensurate with the need to 'hide and unhide' controls and columns it seems to me. It is as broad as it is long,so to speak.

That having been said it is only my opinion, so in relation to the form element I have revised the accdb file I uploaded originally and augmented it with a secondary concept that is to say one involving a form that uses the COLUMNHIDDEN property to hide and unhide various columns consistent with field selection

(Hi twinnyfo I think this is what you maybe referring to?)

I have not posted on the forum for a long while so I trust everything attachs smoothly

Kind regards

Jim Doherty

[Rabbit]

Please use code tags when posting code or formatted data.

The reason you're getting a syntax error is because the select column list needs to be comma separated. You may have other syntax errors in your dynamic SQL but that's all I can say from the little code you posted.

What you should do is print out the SQL that it builds so you can see if it's valid SQL. Currently it is not valid SQL because your SQL string is basically:

SELECT Table.ColumnA Table.ColumnB Table.ColumnC