Custom Query Builder on Form

[SierraJuliet]

Any suggestions on adding a large text box that allows custom query entries on form, which is parsed and displayed in a subform datasheet?

[June7]

Not sure what you mean by 'custom query entries'. The subform datasheet would have to an object - table, query, form.

[SierraJuliet]

Custom query entry like a textbox that someone types a SQL statement into and the results display in a subform datasheet. I am merely looking for some suggestions on the textbox portion. The textbox is just in case someone is interested in something particular but there is no query already built for it. I know it is possible, just not sure what route to take on it.

[isladogs]

Perhaps this utility that I posted at AWF will help
https://www.access-programmers.co.uk...d.php?t=293372

[SierraJuliet]

That utility is nifty.

The idea behind original post is to enter a SQL statement based on whatever the person is interested into the textbox (i.e. proper SQL syntax). The execution of this textbox SQL statement is a query that launches when a button is clicked, which is then outputted to a subform datasheet located on the same form as the textbox used to generate the query. This is the intent. I am almost there but getting annoying errors, so more troubleshooting is required.

[SierraJuliet]

So I got it to work. The following is the code in the "Run Query" click [Event Procedure]. "Me!txtQueryCustom" is the text box on the form where user enters their custom SQL statement. "subfrmQueryCustom" is the subform datasheet. "qryCustom" is a saved query that changes each time a custom query is executed by clicking "Run Query".

Private Sub btnQueryRunCustom_Click()

On Error GoTo btnQueryRunCustom_Click_Err

Dim strSQL As String

strSQL = Me!txtQueryCustom
If strSQL <> "" Then

CurrentDb.QueryDefs("qryCustom").SQL = strSQL

Me.subfrmQueryCustom.SourceObject = "query.qryCustom"

Else
End If

btnQueryRunCustom_Click_Exit:

Exit Sub

btnQueryRunCustom_Click_Err:

MsgBox Err.Description, vbOKOnly, ""
Resume btnQueryRunCustom_Click_Exit

End Sub

The following is the text box (Me!txtQueryCustom) and "Run Query" button. Below this is the subform datasheet (subfrmQueryCustom) that is populated with results of whatever the query is, which is ultimately determined by the newly saved "qryCustom" each time the button "Run Query" is clicked.



The following is the result of setting "Enter Key Behavior" to "New Line in Field" under Property Sheet > Other for the text box. Just thought it might come in handy to mention, especially for complex queries.

[isladogs]

Glad you got it to work. Just out of interest though, why do you want to do this?

[SierraJuliet]

Just for Murphy... or a custom view of data not already parsed via another query.

[SierraJuliet]

Oh and I should have clarified. When I said for a "user" to run custom queries, I meant admin folks, not "users" themselves. That would invite disaster.

[isladogs]

Oh and I should have clarified. When I said for a "user" to run custom queries, I meant admin folks, not "users" themselves. That would invite disaster.

Yes it would but why can't admin users just run a query in the usual way from the nav pane?

[SierraJuliet]

Because the only thing they can see is the frontend view (no nav pane, VBA code, etc.) while the backend is elsewhere. The main view form is a modal popup after login. The remainder of frontend is hidden and locked down. There is only one way around this, which I know but not giving that out. Of course they have other query means but the idea behind a custom query builder on form was to incorporate that functionality into the frontend for simplicity. Admin accounts also have the ability to directly edit table content from the form while all others cannot. Now the developers (i.e. me) can access the nav pane and so forth for updating things, which are then pushed out to the networked users but even admin should not be allowed to just start hand jamming in new code because it is designed a particular way for a reason. If admin had the ability to "customize" the frontend interface then the potential for issues later is greater, not to mention troubleshooting whatever good idea they came up with. Just my two cents.

[June7]

Seems to me 'admin' is just a category of 'user'. Why shouldn't anyone be able to use this utility if it doesn't actually modify db?

[SierraJuliet]

Admin has the ability to see things that typical users do not need to see or have access to because of least privilege and the potential for the average person to destroy information by accident. The aforementioned custom query builder in deed has the ability to modify the database tables. I tested it and confirmed I can run DELETE, INSERT, UPDATE, and SELECT statements. The standard user does not need to customize a view for analyzing data, they just need to input the data while managers can see the metrics at work but the users cannot. Meanwhile admin can see everything managers can but also have the ability to modify data or customize parsing for a particular circumstance all from the frontend. For example, admin can see all information, managers can only see information pertaining to their department/group, and users can only see information pertaining to them individually or as part of a group assignment.

[June7]

Still, to you (developer) they are all just users with different levels of permissions, none of which can modify design (that's your job). If bottom level users should not be able to edit existing records, then code could prevent them from building action SQL, however, if they really have no need to query data then certainly, no need for them to even be aware of the tool.

[SierraJuliet]

You are surely misunderstanding the logic behind it. Admin can run the SQL builder, Managers and Users do not because they do not need it and they do not see the SQL builder either because it is hidden from them. In fact there are MANY things hidden from users, less hidden from managers, and nothing hidden from admin except the interface nuances itself. This allows admin to check anything they want with the DATA itself but not mess anything up when it comes to interfacing to that data. I have met plenty of admin with good ideas that just caused problems down the road.