tomlebold@msn.com wrote:[color=blue]
> The function or code below creates a saved query used as the record
> source for forms and reports. The funtions is called each time a user
> clicks on a button to display a form or report.
> This works just after security has been set for the user by cheching
> all options, which include
> delete, modify, append and administer. Security is being set on the
> tables, queries and forms.
> The second time the user clicks on the button that runs the function
> the security check boxes become unchecked. The problems seems to be
> that the security needs to be checked just before the code creates the
> saved query: Set qryCriteria =
> db.CreateQueryDef("qryCriteriaSelected", SQL). How do set security in
> VBA code for query on object.
>[color=green]
>>[/color]
> Dim SQL As String
> Dim db As DAO.database
> Dim qryCriteria As DAO.QueryDef
> Dim ClearEndDate As String
>
> On Error GoTo BuildSql_Err
>
> 'Select and From clause
> SQL = "SELECT [Tbl-ContractInformation].*, WHERE "
>
> 'Build Reference Number Clause
> If Not IsNull(Forms![frmSelectCriteria]![cboReferenceNumb]) Then
> SQL = SQL & "([Tbl-ContractInformation].[ReferenceNumb] = "
> SQL = SQL & "Forms![frmSelectCriteria]![cboReferenceNumb]) And
> "
> End If
>
> 'Build Reference Number Prefix Clause
> If Not IsNull(Forms![frmSelectCriteria]![cboReferencePrefixNumb])
> Then
> SQL = SQL & "(Left([ReferenceNumb],5) = "
> SQL = SQL &
> "Forms![frmSelectCriteria]![cboReferencePrefixNumb]) And "
> End If
>
> SQL = SQL & " -1 = -1; "
>
> Set db = DBEngine.Workspaces(0).Databases(0)
>
> 'Deletes the stored query def: qryCriteriaSelected
> DoCmd.DeleteObject acQuery, "qryCriteriaSelected"
>
> 'Turn off Access system warning to create, delete or updates tables
> DoCmd.SetWarnings False
>
> 'Create stored query def with return value deal criteria
> Set qryCriteria = db.CreateQueryDef("qryCriteriaSelected", SQL)[/color]
The second time you run it the query is created again, therefore any
security settings will be lost.??
--
regards,
Br@dley
