Creating query with VBA

garywood84

Registered User.
Local time
Today, 16:45
Joined
Apr 12, 2006
Messages
168
I have a query being created by VBA code behind a button, using parameters specified on a form. I want to set the resultant query, when it's displayed, to allow modifications to the data, but not any additions (i.e. not the creation of new records).

Can anyone advise me how I can do this?

Thanks,

Gary
 
If you set the query to be viewed in a form, you can set the form's properties to
Allow Additions = False
Allow Edits = True
 
Bob,

For ease (i.e. easily allowing sorting, etc), the result just opens up in datasheet view. Is it possible to set/restrict edit/modification permissions for this in a similar way to a form?

Gary
 
The problem is that using a form would be very complicated! The form on which users choose query parameters also lets them specify which fields they want to include in the output. So, to use a form, rather than just a standard datasheet view, would require me to dynamically create the form to display the results, so that it included the appropriate fields, using VBA.

Can anyone think of any alternative to having to do this?

Gary
 
Hi Gary,

Not sure what you're asking.

Here’s a sample for changing the form’s properties. Put it into a module or new module. Not into the form’s module.

Code:
Function ChangeAllowEdits(strFormName As String, booAllowEdits as Boolean)

Dim frm As Form, prop As Property

On Error Resume Next
For Each frm In Forms
If frm.Name = strFormName Then
    For Each prop In frm.Properties
    	If prop.Name = "AllowEdits" Then
    	prop.Value = booAllowEdits
    	End If
    Next prop
End If
Next frm
'your error handler here
End Function

To call the function,

Call ChangeAllowEdits(“YourFormName”, False)

In the form’s on open event or whatever event. You can add more arguments if you wish to change additional properties.

Not all form’s properties can be change in run time. Some properties require you to open the form in design mode than close and open again. Some can’t at all.

PS. I believe this similar code is avialable on this site.

garywood84 said:
The problem is that using a form would be very complicated! The form on which users choose query parameters also lets them specify which fields they want to include in the output. So, to use a form, rather than just a standard datasheet view, would require me to dynamically create the form to display the results, so that it included the appropriate fields, using VBA.

Can anyone think of any alternative to having to do this?

Gary
 
Last edited:
Hi unclejoe,

Thank you for your post. I'm sorry if my original post was not clear - let me try and clarify.

I have a form with a button on it that runs a query. Parameters and options for that query, of 3 types are set from listboxes/comboboxes on the form:

  1. Critieria for each field (=list box where user selects the values they want to include, or selects nothing to include all values)
  2. Fields to include in output (=list box of all fields which are available for display in the query output. The user selects which fields they want, or makes no selections to include them all)
  3. Options to set the sort order for the records in the query result (=3 combo boxes which the user can use to set up to three things to sort by).
One these selections have been made, the user presses "Run Query" and an SQL statement is generated based on their selections. This is saved as a query and the result opens as a datasheet view.

Now, my problem is that this datasheet view, in many cases, is editable, with changes updating the tables behind the query. I do not want this to be the case: rather, I want the query result datasheet to be read only.

I understand from posts in this thread that I could potentially achieve this by displaying the results not as a datasheet but as a continuous form. However, this would require very complex VBA because the fields that are displayed on the form would be different every time the query is run: depending on which fields the user has selected to include on the original form where they clicked "Run Query". So, the form which displayed the results would have to be created "on-the-fly" since it's fields could not be specified in advance.

I hope that this clarifies and will not allow you to see my problem. Basically, I need to do the kind of thing you propose to prevent a form being editable, but to protect a datasheet rather than a form.

Thanks in advance if you can help,

Cheers,

Gary
 
Hi Gary,

I see, what actually is a query, not a form’s datasheet view. You got me confused on that.

I don’t know how you design your database. So I can only suggest you try the Security options. Go to Tools – Security – User and Group Permissions

List Groups – uncheck the permissions of your choice.

Good Luck.

garywood84 said:
Hi unclejoe,

Thank you for your post. I'm sorry if my original post was not clear - let me try and clarify. 'cut to reduce space

I hope that this clarifies and will not allow you to see my problem. Basically, I need to do the kind of thing you propose to prevent a form being editable, but to protect a datasheet rather than a form.

Thanks in advance if you can help,

Cheers,

Gary
 
Hi Gary,

(Lights just came on)
Actually, you can create an unbound form with a subform. Adjust the width of the subform to fit the width and height of your form.

I believe the query is name and since it is name it will be easier.

1. Create a new form
2. Click the subform/subreport icon
3. Subform wizard appears
4. Click cancel. Do not create the subform record source!
5. A empty box appears.
6. Go to the subform properties
7. Go to Data tab
8. Go to Source Object
9. Click the combo like button.
10. Select the name Query – like “Query.YourQueryName”
11. Still on the Data Tab
12. Go to Locked – select “Yes” to locked data.

Hope it works for you.

garywood84 said:
Hi unclejoe,

Thank you for your post. I'm sorry if my original post was not clear - let me try and clarify. Gary
 
Hi Unclejoe,

Thanks for both your replies. What you propose makes sense, however, I have one further query.

As I've said, the user selects options on a form (call this "Form 1") which feed into a query ("Query") and currently the results of Query are displayed as a datasheet.

What you are suggesting would see these query results appear on a second form ("Form 2") as a subdatasheet, so to all intents and purposes they would look like a datasheet.

However, the name of "Query" is not constant. Because the database has to support multiple users, "Query" is actually a temporarily saved query called "[Username]".

This means that I can't set an absolute record source for the subform you propose - the record source would have to be set by the VBA when it opens the main form after running the query.

Is this possible?

I hope this makes sense - please let me know if anything isn't clear, I know this is getting quite complicated now!!

Thanks,

Gary
 
Attached is an example of a method I use for allowing users to select tables/fields from my databases (Creates tables/queries/sub forms on the fly). The output is in a CSV file format, which the user can then import into another piece of software for further data manipulation.
 

Attachments

Last edited:
Hi Gary,

garywood84 said:
This means that I can't set an absolute record source for the subform you propose - the record source would have to be set by the VBA when it opens the main form after running the query.

Not the Record Source of the subform, it’s the Source Object. To Change it

Me.Child0.SourceObject = “Query.YourQueryNameHere”

Child0 is the default name of the datasheet subform. It does not have any bound fields so it doe not have any problems when you change the query or even change the query to view another table.

You can use a command button to refresh the source object or some other events, e.g. thru VBA than open the form to view the query that was changed.

garywood84 said:
However, the name of "Query" is not constant. Because the database has to support multiple users, "Query" is actually a temporarily saved query called "[Username]".

It would never be a problem if the query were save in the Front End of the user PC. Since the SourceObject is fixed (the name is fixed), I don’t see any problem at all.

In fact, if you design your database to determine whom the user is, you programme something like below to change the SourceObject.

E.g. If LogOnUserName = “Gary” then SourceObject = “Query.QryUserGary”.

Hope you understood me.

garywood84 said:
What you are suggesting would see these query results appear on a second form ("Form 2") as a subdatasheet, so to all intents and purposes they would look like a datasheet.

However, the name of "Query" is not constant. Because the database has to support multiple users, "Query" is actually a temporarily saved query called "[Username]".

This means that I can't set an absolute record source for the subform you propose - the record source would have to be set by the VBA when it opens the main form after running the query.

Is this possible?

I hope this makes sense - please let me know if anything isn't clear, I know this is getting quite complicated now!!

Thanks,

Gary
 

Users who are viewing this thread

Back
Top Bottom