Help suppressing query from view

kevinh2320

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

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
325
Office Version
365, 2016
Platform
Windows
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:
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
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
 

kevinh2320

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

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,170
Messages
5,442,799
Members
405,198
Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top