Help suppressing query from view

kevinh2320

Board Regular
Joined
May 13, 2016
Messages
61
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

1585333456263.png
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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? :rolleyes:
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Here's another way that might work, but would still require you to create a parameter in the query.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top