Ad-Hoc Query Builder in Access

[rangudu_2008]

Jimmy, you're GREAT...

The things that need further fine tuning are as below:
1. We cannot view all the joins that are saved (one each at a time);
2. Only the join part of the query is displayed in the SQL Query textbox (output query that is built) instead of the whole SQL statement; This query needs to be saved back to DB.

Also, the end user should be able to select fields from diff tables (using the first tab) which can be re-ordered and the SQL query should be auto-built in this case as well. This needs modification of my old code. The criteria tab needs to be coded simultaneously when this is done. I need ur help and guidance for accomplishing it.

Only when these are done, the query results can be exported as excel sheets.

Ranga
__________________________________________________________
Aaron,
Please comment on the things that have been accomplished so far...

__________________________________________________________
Aaron,
Please comment on the things that have been accomplished so far... And also share your ideas on the things that need to be done. Guide us.

Ranga

[Oorang]

Hi Ranga,
Here is a very simple example of what I had in mind (see attachment below), obviously you will need to expand upon it considerably to make it production worthy.

Edit: JimmyTheHand PM'd this to me and I thought it needed explanation:

Aaron,

Studying the code you posted in Rangudu_2008's thread (post #44) I noticed an interesting part:

[vba]#If Not m_blnErrorHandlersOff_c Then
On Error GoTo Err_Hnd
#End If[/vba]

First of all, I couldn't find m_blnErrorHandlersOff_c defined, not even dimensioned, anywhere. What is it? I thought it was a constant, but then its value is known, so why the If..Then.

Secondly, I'm about the # character. I don't remember seeing code lines started this way. What does it mean? Can you explain this to me is a few words, or give a link mayhaps?

Thanks,

Jimmy

First let me explain the meaning of the mysterious "#". "#" as a suffix (ex: Dim x#) is a type declaration character and is the same as "Dim x As Double". (They are bad practice, don't use them) As a prefix (ex: #If foo Then) it is a "conditional compilation directive". To understand whatthat is, you need to know a little about the guts of vba.
Unlike some languages, VBA does not compile in Machine Language for execution. VBA "compiles" into PCode for execution by a virtual machine at runtime. It is the P-Code that gets executed not your nice friendly VBA. So when a procedure is called, your code is compiled into p-code and the p-code is what runs.
A conditional compilation directive is evaluated at compile time and if the condition is True, the code inside the #If block is compiled. If it is False, the code inside the code block is NOT evaluated and not put in the P-Code block.

So why would you do this? Well, speed for one. Say you have a whole bunch of debug.print statements that you only want to run if you have a constant set to True. If you use a normal If block the if condition is evaluated during runtime everytime it is encoutered, thereby slowing your code. If you use an #If block instead, the code that get compiled into P-Code is just the Debug.Print line (not the #If itself, that has already been resolved) or if the condition is false, the entire block dissappears from the PCode.

Ok so now you know all of that. Let me explain what that was doing in my code. I use a code productivity tool called "MZ Tools" it's free, it's awesome and I can't say enough good things about it One of the things it does is allow you to do is set up a standardized error handler that it will add to your code at the click of a button. On of the other things it does is to allow you to a standard module header. My standard Module Header looks like this:

'-------------------------------------------------------------------------------
' Module : {MODULE_NAME}
' Author : {AUTHOR}
' Date : {DATE}
' Purpose :
' References :
' Dependencies :
'-------------------------------------------------------------------------------

Option Explicit
Option Private Module
Option Compare Binary
Option Base 0

'Setting this to True will turn off all error handling:
#Const m_blnErrorHandlersOff_c = False

My Standard Error Handler looks like this:

'Conditionally Invoke Error Handler:
#If Not m_blnErrorHandlersOff_c Then
On Error GoTo Err_Hnd
#End If

{PROCEDURE_BODY}

'******* Exit Procedure *******
Exit_Proc:
'Supress Error Handling to Prevent Error-Loops:
On Error Resume Next
'Release Objects:
'Set Return Value:

Exit {PROCEDURE_TYPE}

'******* Error Handler *******
Err_Hnd:
'$PROBHIDE RETVAL_DISCARDED
MsgBox Err.Description, vbSystemModal, "Error: " & Err.Number
'Return to Exit Procedure:
Resume Exit_Proc

In the code I created, I used the standard error handler, but not the standard module header. Mystery solved.

Edit 2:
So why did the code still use the error handler? Because if the condition is not found, it is assumed to be false and will therefore use the error handler. The only way to turn them off is to define a compiler constant (as in the standard header) and then set it to true. As a safety precaution, I made the condition that way in case I ever forgot to put in the header, or someone @ work copied it, and forget the header. As you can see it was a pretty good move (Always plan for your own fallibility )