Creating a Query using controls with some default values.

[Locopete99]

Hi Guys,

I have the following code I found online to help me create a query using check box controls so the user can add the values they require. I have slightly modded this code.

Option Compare Database

Private Sub Command0_Click()

On Error Resume Next
Dim ctl As Control
Dim strSQL As String
Dim strSQL_2 As String
Dim qdf As DAO.QueryDef
Dim qdfDemo As DAO.QueryDef
Const conQUERY_NAME As String = "S Monthly Fields"     'Physical Query Name
 
For Each ctl In Me.Controls                 'Query every Control on the Form
  If ctl.ControlType = acCheckBox Then      'Is it a Check Box?
    If ctl.Value Then                       'Is it selected?
      strSQL = strSQL & ctl.Tag & " ,"      'Build the Field Selection(s)
    End If
  End If
Next
 
If strSQL = "" Then Exit Sub        'No Fields selected, get out!
 

 
On Error GoTo Err_cmdTest_Click
 
'Build complete SQL Statement
strSQL_2 = "SELECT  Me.Part Number, Me.Make or Buy, Me.Account, Me.Holon, " & Left$(strSQL, Len(strSQL) - 2) & " FROM Shikomi Forecast;"
 
'Create the QueryDef Object
Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  Resume Exit_cmdTest_Click
End Sub

I'm having a problem because I want some default values to be added to the query to begin with.

I'm having trouble with this line as I'm not too sure on the syntax.

strSQL_2 = "SELECT  Me.Part Number, Me.Make or Buy, Me.Account, Me.Holon, " & Left$(strSQL, Len(strSQL) - 2) & " FROM Shikomi Forecast;"

Basically, I want the query to have the fields Part Number, Make Or Buy, Account and Holon to be added on the front of any query as default.

Using VBA and SQL, how would I correctly select these from the table "Shikomi Forecast"

 

[plog]

Just to summarize: You want a way for users to build their own simple queries. Right?

May I suggest you use Access? I mean, it has this great query building tool that is extremely simple to use--no VBA necessary. You simply go to the ribbon, select build a new query, use an easy interface to add tables to your query, drag and drop the fiields you want to appear in your query and hit a simple 'Run' icon and you have your data.

You are recreating a function of Access in Access. It makes no sense.

With that said, I see two errors with your code--specifically the SQL inside your VBA code:

1. Poor names. When you use non-alphanumeric characters in table and field names (including spaces, e.g. Part Number) you then need to surround the entire name with brackets ([Part Number]).

2. Improper table references. When you want to use a full reference in SQL (table and field name) you do it like so:

TableNameHere.FieldNameHere

When you put 'Me.Holon you are telling the SQL to return the Holon field in the table named Me. However, your FROM clause isn't using a table named Me--its using a table named Shikomi Forecast. So even with proper bracketing your SQL has no idea what you mean when you use Me.

Back to my original point--you have a tool in Access to build queries. Even you don't want to let end users use it, you can use it to get your SQL correct. I suggest you build a query using the query builder, then steal its SQL to use in your VBA.

 

[Locopete99]

Hi Plog,

I'm sorry, please allow me to explain.

1) The users that will be using this access database that I am creating will not know how to run a query.
2) I want to set up a query using some default values say X, Y and Z and then have them be able to select a further set of values to add to this query using control boxes. For example click A, B and C to add to the query to get that data, or A, B and D, depending on the requirement. The user will need to select a range of data and I have two options. 1) continually creating queries every month to fulfil the users requirement or 2) Allow them to add the fields they require using control boxes.

Whilst I am fully aware of the default query building wizard, I want to go a step further. In fact, I need to in this instance. I have come across multiple instances of people needing to do this, and actually the original code that I posted on this forum was taken from someones answer to another persons request on another forum i found whilst I was trying to find the answer.
3) With regards to the improper references I have tried the following:

a) Building the query in the design view and stealing the SQL names for them
b) Using Tablename.Fieldname, Tablename.[FieldName], [Tablename].[FieldName]
c) I tried using Me.[fieldname] as a last ditch effort, knowing full well that Me. in fact normally refers to the current form in which the code is present when using VBA instead of SQL.

 

[Minty]

If the default fields are already in the [Shikomi Forecast] Query / Table then simply refer to them by name

strSQL_2 =  "Select PartNumber , [Make or Buy], AccountNo, Holon, " & your extra fields go here & " FROM  [Shikomi Forecast] "

Debug.PRint strSQL_2

This assumes they are uniquely available in the underlying table / query.

Notice I've added a debug.print to allow you to see the SQL you have created. You almost certainly will need this to see what isn't correct.

 

[plog]

1. Then teach them how to run a query. You are spending a ton of time to make things marginally easier for them--with significant decrease in utility. What happens when they want to apply criteria in your system? What happens if they want to JOIN 2 tables together? What happens if they want to get totals?

2. No, each month they can make a copy of an existing query and then modify it to their new needs. With your system they will always have to use the tool and never be able to save their creations.

3. [TableName].[FieldName] will work as long as 1-- TableName is in the FROM clause (or a JOIN) and 2--FieldName is a field in TableName.

 

[Locopete99]

Thanks Minty