panchitocarioca
Registered User.
- Local time
- Today, 02:21
- Joined
- Aug 11, 2008
- Messages
- 11
Hola,
I have a small question. I have a form which is based on a query that is built up through VBA code as an SQL text and the query is then saved. In case the query already exists, then the existing version of the query is first deleted (db.QueryDefs.Delete "QueryForReport") after which the new query under the new name is saved.
Access to the database is restricted through a workgroup information file. In case the database administrator is logged onto the database, there is no problem in opening the form that is based on this query. However, in case any other user opens the database, an error occurs: the user is not authorised to carry out this operation (delete the query). It does not matter which authorisations I specify for all users, even if I give all users administrator rights on the "QueryForReport", that only allows a user to delete the query once. Thereafter, a new query is created of which the current user is the owner and any other user (except for the administrator) is not authorised to delete that query. So, if thereafter another user logs into the database, then the query can again not be deleted.
A potential solution seemed to be: not to base the form on a query, but through VBA set the recordsource of the form (in the on-open event the following code is included: “me.recordsource = …… “. Now, this works fine with reports (that have subreports) but it does not seem to work with a form that has subforms. Upon opening the form, Access asks for the field through which the main form/table and the subform/table are linked. However, that field is definitely included in the sql code that is used to set the recordsource of the form (if i take the SQL text, put it in a query, save the query, and base the form on that query, then it works without any error, so the SQL code is correct). If you try to alter the field through which the main form and subforms are linked through the properties window then the message is given that the mainform and subforms are not linked. Strangely enough, if you just try to open the form and click “ok” when you are prompted for the link field, then the form opens and the correct link between main and subform seems to be in place. Even if you create a new record and fill in data in the subform, then in the linked field of the subform, automatically the correct code (of the main form) is filled in. So, some sort of link does seem to have been estaliblised.
Does anyone know how to properly set the recordsource in the open event, for a form with a subform without getting above problem?
Or maybe the solution is the following. Is it possible to change the owner (through VBA code) of the query "QueryForReport" to the CurrentUser before that query is to be deleted? That might be another solution.
Many thanks in advance for the help.
Best regards,
I have a small question. I have a form which is based on a query that is built up through VBA code as an SQL text and the query is then saved. In case the query already exists, then the existing version of the query is first deleted (db.QueryDefs.Delete "QueryForReport") after which the new query under the new name is saved.
Access to the database is restricted through a workgroup information file. In case the database administrator is logged onto the database, there is no problem in opening the form that is based on this query. However, in case any other user opens the database, an error occurs: the user is not authorised to carry out this operation (delete the query). It does not matter which authorisations I specify for all users, even if I give all users administrator rights on the "QueryForReport", that only allows a user to delete the query once. Thereafter, a new query is created of which the current user is the owner and any other user (except for the administrator) is not authorised to delete that query. So, if thereafter another user logs into the database, then the query can again not be deleted.
A potential solution seemed to be: not to base the form on a query, but through VBA set the recordsource of the form (in the on-open event the following code is included: “me.recordsource = …… “. Now, this works fine with reports (that have subreports) but it does not seem to work with a form that has subforms. Upon opening the form, Access asks for the field through which the main form/table and the subform/table are linked. However, that field is definitely included in the sql code that is used to set the recordsource of the form (if i take the SQL text, put it in a query, save the query, and base the form on that query, then it works without any error, so the SQL code is correct). If you try to alter the field through which the main form and subforms are linked through the properties window then the message is given that the mainform and subforms are not linked. Strangely enough, if you just try to open the form and click “ok” when you are prompted for the link field, then the form opens and the correct link between main and subform seems to be in place. Even if you create a new record and fill in data in the subform, then in the linked field of the subform, automatically the correct code (of the main form) is filled in. So, some sort of link does seem to have been estaliblised.
Does anyone know how to properly set the recordsource in the open event, for a form with a subform without getting above problem?
Or maybe the solution is the following. Is it possible to change the owner (through VBA code) of the query "QueryForReport" to the CurrentUser before that query is to be deleted? That might be another solution.
Many thanks in advance for the help.
Best regards,
