Help suppressing query from view

[kevinh2320]

I have the code below that works fine but there is one aesthetic operational change I need assistance with. The line highlighted below runs an append query with a parameter that requires the user to enter a date [Enter date letters will be mailed for this letter run - Ex: 3/2/2020]. While the Enter Parameter value box is on screen the query is displayed behind it. Is there a way to just display the Entry Parameter Value box prompting for the date but hide the query? Thanks for any help.

Private Sub process_data_for_review_Click()

If MsgBox("This action will process loaded data and setup for review. Do you want to continue?", vbYesNo + vbQuestion) = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "q-archive-last-run-agingRpt-data-for-comparison"
DoCmd.OpenQuery "q-delete-t-last-run-data-for-comparison-temp"
DoCmd.OpenQuery "q-append-last-run-agingRpt-data-TO-t-last-run-comparison-temp"
DoCmd.OpenQuery "q-delete-t-current-run-all-records-needing-review"
DoCmd.OpenQuery "q-append-current-run-all-records-TO-t-current-needing-review"
DoCmd.OpenQuery "q-update-review-Shorefish-AND-PUCPs-accts"
DoCmd.Close acQuery, "q-archive-last-run-agingRpt-data-for-comparison"

MsgBox ("Process Complete!")

Else

Exit Sub

End If

End Sub

 

[JonXL]

What is the process for running these queries? If there's a form you kick them off from, you could get the value from a control on the form and reference the form control in the query like [Forms]![formName]![controlName].

Also... is the data in that screenshot sanitized?

 

[kevinh2320]

Thx

 

[Micron]

You are asking for trouble with that naming convention, especially where it looks like you're trying to subtract something from "Date" which is a reserved word when used by itself. When used in what looks like a mathematical expression, you're lucky if it works. Suggest you check out

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

General: Commonly used naming conventions

 

[kevinh2320]

I tried the [Forms]![formName]![controlName] option but this is an append query so I get an invalid criteria error. Any other suggestions on how to have just the parameter dialog box appear without the query in the background?

 

[Micron]

I think you will have to
a) have this date in an unbound control on the form rather than letting the query raise a prompt and
b) create a parameter in query design that refers to that control

To set a query parameter without entering directly in sql view, right click on the query background in design view and choose parameters from the list. Add the control reference and specify the data type. Save the query and try again. You button click should validate that there is a date in the form control lest it be null, which will probably result in no query records.

I suggest you consider the Execute method for running action queries. As you have it, Set Warnings without an error trap can leave warnings turned off for the entire db session; i.e. they get turned off, code fails, system error is raised (or worse, is not), code stops running, warnings are still turned off.

 

[Micron]

Here's another way that might work, but would still require you to create a parameter in the query.

Private Sub process_data_for_review_Click()
Dim db As DAO.Database

Set db = CurrentDb

If MsgBox("This action will process loaded data and setup for review. Do you want to continue?", vbYesNo + vbQuestion) = vbYes Then
  With db
    .Execute "q-archive-last-run-agingRpt-data-for-comparison", dbFailOnError
    .Execute "q-delete-t-last-run-data-for-comparison-temp", dbFailOnError
    .Execute "q-append-last-run-agingRpt-data-TO-t-last-run-comparison-temp", dbFailOnError
    .Execute "q-delete-t-current-run-all-records-needing-review", dbFailOnError
    .Execute "q-append-current-run-all-records-TO-t-current-needing-review", dbFailOnError
    .Execute "q-update-review-Shorefish-AND-PUCPs-accts", dbFailOnError
  End With
  MsgBox ("Process Complete!")
End If
Set db = Nothing
End Sub

There are other ways such as defining the parameter in code. If the query parameter doesn't work with either of these suggestions (the code might raise a parameters error) then post back and will try one of the other methods.