473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VBA to select specific columns from database to table

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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub genQuery()
  2.  
  3. sqlStr = "SELECT"
  4. MsgBox sqlStr
  5.  
  6. If Me.[1] = True Then
  7.         sqlStr = sqlStr & " Table.ColumnA "
  8.     Else
  9.         sqlStr = sqlStr & " Table.ColumnA "
  10. End If
  11.  
  12. If Me.[2] = True Then
  13.         sqlStr = sqlStr & " Table.ColumnB "
  14.     Else
  15.         sqlStr = sqlStr & " Table.ColumnB "
  16. End If
  17.  
  18. If Me.[3] = True Then
  19.         sqlStr = sqlStr & " Table.ColumnC "
  20.     Else
  21.         sqlStr = sqlStr & " Table.ColumnC "
  22. End If
.
.
.

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

How may I solve it?

I am using Access 2007

Thank you!
Aug 22 '14 #1
4 5690
twinnyfo
3,653 Expert Mod 2GB
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.
Aug 22 '14 #2
twinnyfo
3,653 Expert Mod 2GB
Actually, I just checked the Visible Property on one of my datasheet forms, and this method will not work....
Aug 22 '14 #3
Jim Doherty
897 Expert 512MB
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
Attached Files
File Type: zip HideColumns.zip (64.6 KB, 136 views)
Aug 22 '14 #4
Rabbit
12,516 Expert Mod 8TB
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table.ColumnA Table.ColumnB Table.ColumnC
Aug 22 '14 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Martin | last post by:
Hi there, I've been looking far and wide on this one. I'm looking for a way to select from columns where the columns have a certain pattern! Example: Instead of doing: SELECT q1, q2,...
8
by: Arpan | last post by:
A Form has a select list which lists all the column names of a SQL Server database table. Users will select one or more than one column from this select list & after submitting the Form, the...
4
by: Tmuld | last post by:
Hello, I have a complete table within a dataset. I want a dataview to show particular filtered data - works great with the dv.rowfilter. But can I display only certain columns that are...
8
by: David | last post by:
Hi, Could someone please xplain how to add a field to an existing SQL table in VB.Net I have added the field in the Server Explorer and it shows up when I reload the program but I cannot...
3
by: Rich Shepard | last post by:
I need to learn how to process a byte stream from a form reader where each pair of bytes has meaning according to lookup dictionaries, then use the values to build an array of rows inserted into a...
4
by: Amar | last post by:
Hi All, I need to select data from a database table containing huge amount of data. Now I am storing data using one primary key and I am just using simple select statement, and this process...
5
by: =?Utf-8?B?Y2RiaWdncw==?= | last post by:
Hi, I've been tasked to write a windows app that allows people to enter transactions. For each transaction, there can be an unknown number of items, and as a person enters an item, the program...
2
omerbutt
by: omerbutt | last post by:
hi there i have to select 7 columns from table1, 1 column from table2, and 1 column from tables3 and show the result but i am not getting it right the main thing that i am trying to achieve is that i...
0
by: SEhtesham | last post by:
Hi, I have my application in VS2008 and coded Vb.net. My query is i want to save record from an excel sheet to my Database Table. But i dont want to insert the Excel sheet as a whole...
2
by: betty blue | last post by:
I am a newbie to C#. I want to select three columns from my text file i.e. Empl No, Start Date and Created Date. After selecting this, I want to insert the data into a database. I have attached a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.