Act on Cancel Button in parameter query

Posted on 2008-02-05
Medium Priority
Last Modified: 2012-08-13
When I execute a query that has a parameter there are two choices - OK or Cancel. If the user selects Cancel I would like to take some action such as closing down the form. How do you capture this to act on it.
Question by:Frank Freese
  • 4
  • 4
  • 2
  • +2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20827516
is that coming from a  message box?

if (msgbox(ms....., vbokcancel))=vbOk then
      'code here
       docmd.close acform, me.name
end if
LVL 34

Expert Comment

ID: 20829113
IF this is your standard pop-up parameter input box that a query spits out, you can't capture that as an event. You can switch to a VBA with a Dynamic SQL olution where you prompt your user as Cap has illustrated, that way you can control the flow of events, but otherwise...not going to happen.

Expert Comment

ID: 20829742

Dim qdf as QueryDef
dim strAnswer  as string
   set qdf = Currentdb.QueryDefs("qryNameHere")
   strAnswer = inputbox("Enter Date to Query")
   if InputBox <> "" then
        if isnumeric(strAnswer) then  'or if you need a date then if isdate(strAnswer)
             qdf.Parameters(0) = clng(strAnswer)  'or cdbl, cint, etc...
      msgbox "You did not enter a value"

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Expert Comment

ID: 20829747
pardon me...
I typed...
     If InputBox <> "" then
should have said...
    If strAnswer <> "" then

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20830105

Yes, what everyboby here is trying to say is that instead of trying to "Capture" the screen, why not avoid seeing it in the first place?

<you can switch to a VBA with a Dynamic SQL olution where you prompt your user >
Like Jeffw stated, you could create a form where the user enters the parameter(s) into a combobox (or textbox).
This way you have more control over what they enter.
You can do things like:
-Cancel if nothing is selected
-Include an "All" choice

Here is a great link on this subject:

Hope this helps as well


Author Comment

by:Frank Freese
ID: 20832114
Thanks for the great tips. The query does not criteria back to a form.  I am not using the msgbox function. However, if I elected to use the message box function I could then evaluate the repsonse. If the response was Yes  I would accept the date as entered. I suspect I could then incorporate the date entered as part of the sql? If I'm correct here's is where I need help.
I know how to evaluate the msgbox response but I do not know how to capture the date that was entered and then incorporate it into the sql? I've never seen the code and I am new to switching to VBA with a Dynamic SQL.  
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20832185
you will use the querydef to alter the query sql dynamically

dim qd as dao.querydef, ssql as string

set qd=currentdb.querydefs("NameOfsavedQuery")

'build your ssql here

qd.sql=ssql   ' assign the new SQL statement to your existing query

post the SQL of your query and the names of the controls in the forms you are using as parameters to your query

see this similar post


Author Comment

by:Frank Freese
ID: 20832420
The query type is a delete query and does not reference back to a control on the form.
Here's what I have built so far:

Dim qd as dao.querydef, ssql as string
Dim UResponse

UResponse=MsgBox("Enter in the previous days date",1,"Enter in a date")
If UResponse = 2 Tthen
   Exit Sub
(Here's where I would like to take the date entered and make that the criteria for the query. I'm not sure if I can do that except with an Input box)
Set qd=currentbd.querydefs(qryDeleteByDate)
Ssql = DELETE * FROM tblAudit_Label_Records WHERE
(((Audit_Label_Records.AuditDate)=[Enter Date])));
End If

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20832632
where are you calling this codes? in the click event of a command button?

for a delete query, you don't need the querydef.


Author Comment

by:Frank Freese
ID: 20832749
The code  is being called in the click event. The user is asked if they want to delete non-current data. If so, then the query is executed and the date parameter is inputed. It is at that point where they can click Ok or Cancel. If they select cancel I want exit. It might look something like this:

DoCmd.OpenQuery "qryDeleteByDate"
If Cancel = True Then
Exit Sub
End If

I was considering my sub looking like this:
Private Sub cmdCollectData_Click(Cancel as Interger)

and exectuing Cancel=True but I do not know?
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 20832782
try this

Private Sub cmdDelete_click()
Dim dDate As Date

dDate = InputBox("Enter in the previous days date")
If (MsgBox("Do you want to delete records with date " & dDate & " ?", vbOKCancel)) = vbOK Then
CurrentDb.Execute "delete * from tblAudit_Label_Records where AuditDate=#" & dDate & "#", dbFailOnError
    MsgBox "Deletion cancelled"
End If
End Sub

Author Closing Comment

by:Frank Freese
ID: 31428297
Perfect - as a matter of fact, outside of knowing about the CurrentDb.Excecute command I was thinking the same thing.
I do appreciate you!

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question