Problems creating my own SQL "expression builder" 3

[Tadynn]

Hi all,

Pull up a chair, this will take a while to explain......

I'm currently developing a form in my database that will operate in a similar way to the expression builder available when writing a query, this form will allow me to write SQL statements on the fly.
What I have is a form with 1 text box, which I call SQLScript, 2 list boxes, which I call tables and fields respectively. The first list called "tables", contains a listing of the tables in my database using the MsysObjects table.

The second list called "fields" contains a dynamic listing of the fields within the tables. What this means, is that after clicking on the requested table in the "tables" listbox an afterupdate event runs which lists the fields within that table in the "fields"

Attached to both tables are doubleclick routines that insert the values into the SQLScript textbox. When you doubleclick on a field in the "tables" list box the table name will be inserted and when you doubleclick on a field in the "fields" listbox. The table name, a period, and the field name are inserted (tablename.fieldname).


With me so far? Well, what I've also created are few little buttons that insert reserved words like and not = and so on and also ones that insert the rough guts of different SQL statements. For instance you click the MAKE button and the following 'base statement'is inserted automatically into the SQLScript textbox:



Leaving the user to basically fill in the blanks

Because I've got this button that inserts the values into the SQLScript text box. My first problem was to overcome overwriting the statement everytime I click on one of the buttons. To get around this, I created 2 hidden text boxes. When a button or table/field is clicked, the data keyed into 'SQLScript' is inserted into 1 text box. The value behind the button/value clicked is inserted into text box 2 then both values are concatenated back into 'SQLScript' (text1 & " " & text2).

Can anyone see the problem yet?

The problem, is that when I click on the button and insert a 'base statement' and then click on the button or table/field to insert one of those values. The value clicked is always inserted after whatever text is already in there. So I get:

SELECT
INTO tbl_SQLResults
FROM ; tbl_Employees.EmpNo

Instead of:

SELECT tbl_Employees.EmpNo
INTO tbl_SQLResults
FROM ;

What I really want is a way to have the insert based on where the cursor was positioned in that field (I say was because I click out of the field in order to click on a button, value or field.

Another question that I wanted to ask was; the SQL statements being generated modify a table called tbl_SQLResults. What I wanted to know was, how can I have a table created automatically for inserting the results of the query and deleted when the form is closed?

Well I've certainly said enough. Hope your still awake.....
Oh, and the verions of access that I'm using is access 2k


Thanks, tadynn

 

[BSman]

Building a form that provides a more user friendly query builder (and keeps users out of the command mode of Access) isn't easy. I've done it, first using Access with an Access back end, and then created a version that works with a SQL Server back end (because the syntax is a bit different of the SQL code).

Here are a couple of things I did and had to hassle with.

Basic concept of form is a selection area with basically four controls filled in by the user:

1. Select column (I use a table that includes a list of the tables and queries I'm making available for my query builder, every column in each table including the internal column name and a more meaningful name that the users see, plus other information like the type of data, whether or not a combo box will be used and if so the data source for the query, number of columns, display widths, etc., and if the column is used for selection and if it is used for sorting.)
2. Select the operator. This comes from a table of operators (=, >, <, <=, >=, Between) I include in the application.
3. Enter or select the data. This is tricky. I actually have multiple overlapping controls (combo box, text box, etc.) and the one made visible depends upon what is needed as indicated by the associated data for the column selected in the first step.
4. Then select from a drop down box &quot;Done&quot;, &quot;And&quot;, or &quot;Or&quot;.

You can also include parenthesis to provide more flexibility. I also have a text box that displays a &quot;quasi-English&quot; version of the selection criteria being set up by the user. The actual SQL code is concurrently build &quot;behind the scenes&quot;.

Once the use selects &quot;Done&quot; the sort criteria must be selected. The user selects the column to sort by from a drop down box and then is asked to sort ascending or descending. A text box displays the results in &quot;quasi-English&quot;, while the SQL code is built behind the scenes.

When all of this is done, the user has three buttons to click on at the bottom of the form. One closes the ad hoc query builder form and passes the where clause of the SQL string to the next form (or report) so it can be used as a filter for the form/report.

The other two buttons open queries which were also being built as the user selects the criteria and sorting. One query returns all records. The second shows subtotals because it is a grouping query grouped by the sort columns. The only other columns displayed are ones that the table listing the columns also indicates are ones that should be used for calculations such as Sum or Count. Both of these queries are opened in the default spreadsheet type of display (which can very nicely be copied into Excel).

You have to include some addition functions to handle things like the times a user enters selection criteria that includes, for example, an asterisk ('), such as in the name O'Donnell. The function for that would change O'Donnell to O''Donnell so the asterisk would not be interpreted incorrectly.

I know this is a lot, but I hope it helps you.

 

[redapples]

I similary have built a query by form tool which is based upon concepts which I received from Duane Hookum on the MSDN message boards. If you send me an email to stephen_connell@hotmail.(NoSpam&quot;lol&quotcom I can zip it up and let you have a look. It basically uses a continuous form and takes these records to build array of the fields and conditions. It is maybe overkill for your needs but will be of some use to you I think. It is well annotated and as such you should be able to take from it what you need.

Redapples

I would post it but there are over 700 lines of code here.

Want the best answers? See FAQ181-2886

 

[Tadynn]

bsman,

Thank you for your valuable insight into this. You've certainly given me a lot to think about, and I'm thinking that the design idea that I had in my mind where the user writes the statement themselves is too dangerous, so I'm thinking of creating it as a kind of wizard. A couple of questions regarding how you output the results. One of the more noticable flaws with my design is the user has to create a table in order to view the results. I wouldn't mind them using select statements that write to a query as you've done. That way nothings stored permanently in the database. Would you mind sharing with me how you've achieved this?


redapples,

Thanks for offering to send me a sample of program. For me it's always good to see other people's creativity to get more ideas.
I tried sending an email to your email address stephen_connell@hotmail.(NoSpam&quot;lol&quotcom It's not stephen_connell@hotmail.com is it?
Anyway, here is my email address tadynn@bigpond.net.au if you want to send it.

 

[faeryfyrre]

Hi,
Even though you have decided not to persue your original design I thought i'd post a solution to one of the problems you had (only being able to concatenate the two textboxes rather than inserting the second textbox's contents anywhere within the first TextBox)

In my solution (which is only a rough 10min draft) there are two (2) textboxes - Text0 and Text2 - and 1 command button - Command4.


Private Sub Command4_Click()
'This is where the insertion is performed

Dim strLen As Long
Dim str1 As String
Dim str2 As String

'get the length on the initial string contained in Text0
strLen = Len(Me.Text0.Value)

'now we can split the contents based on the position
'of the cursor when Text0 lost focus - which was stored in
'the Tag property of Text0 during Text0's Exit event.
str1 = Mid(Me.Text0.Value, 1, Me.Text0.Tag)
str2 = Mid(Me.Text0.Value, Me.Text0.Tag + 1, strLen)

Me.Text0.Value = str1 & Me.Text2.Value & str2

Me.Text2.Value = &quot;&quot;

'Note that there is no validation in here at all - IE this would itsert
'a NULL value into Text0 if that is what Text2 contained

End Sub

Private Sub Text0_Exit(Cancel As Integer)
'This stores the location of the cursor within the field
'Note that you cannot use the SelStart property unless the
'Text0 control has the focus as it resets to 0 when the
'control loses focus.
Me.Text0.Tag = Me.Text0.SelStart
End Sub




Alec Doughty
Doughty Consulting P/L

&quot;Life's a competition. Play hard, but play fair&quot;

 

[Tadynn]

redapples

I got your email, thanks very much for that. I'm currently looking through your db at the moment and it already has already given me ideas on how I plan to redesign it.




faeryfyrre

I inserted your script and modified it to work with the doubleclick event on a listbox. It inserts the contents of 2 listboxes (table.field) in my text box based on where the cursor is, and it works fine.

Private Sub Fields_DblClick(Cancel As Integer)


Dim strLen As Long
Dim str1 As String
Dim str2 As String

strLen = Len(Me.SqlScript.Value)

str1 = Mid(Me.SqlScript.Value, 1, Me.SqlScript.Tag)
str2 = Mid(Me.SqlScript.Value, Me.SqlScript.Tag + 1, strLen)

Me.SqlScript.Value = str1 & Me.Tables.Value & &quot;.&quot; & Me.Fields.Value & str2

End Sub

I'm currently trying to figure out how to trap the null value in the text box field I've tried:

If SQLScript.value = Null then
SQLScript.value = Me.Tables.Value & &quot;.&quot; & Me.Fields.Value

Else

strLen = Len(Me.SqlScript.Value)

str1 = Mid(Me.SqlScript.Value, 1, Me.SqlScript.Tag)
str2 = Mid(Me.SqlScript.Value, Me.SqlScript.Tag + 1, strLen)

Me.SqlScript.Value = str1 & Me.Tables.Value & &quot;.&quot; & Me.Fields.Value & str2

End If


But it didn't do anything. I'm still working on it though and I don't expect it to be that hard to figure out. As for my design, well I still want to keep my initial design alive, I actually want to have 2 different front ends in terms of 'writing' SQL. One which will be dead simple to use, where the user selects the tables, fields, and doens't even know that there is SQL generating in the back ground. This is also very restricted and won't allow the user to write back to the database, and which allows SQL statements to be written, with full sql functions (delete, insert etc) available to the (very) selected few.

Anyway, all 3 of you have helped me out heaps with this so. Here's a couple of stars.

By the way, can anyone tell me how to actually build a query (example maybe like Dim MyQuery As QueryDef) I'm just unsure on how to do it. that's all.


Rgrds, Tadynn

 

[faeryfyrre]

you would need to use the IsNull() function rather than [Something] = NULL.

You could also use the Nz() function which performs a replacement on NULL values with any value you wish.

Look them both up in the Access help system to learn about these functions.

Glad i could help you out a little



Alec Doughty
Doughty Consulting P/L

&quot;Life's a competition. Play hard, but play fair&quot;