Act on Cancel Button in parameter query

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.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
is that coming from a  message box?

if (msgbox(ms....., vbokcancel))=vbOk then
      'code here
       docmd.close acform,
end if
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.

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"

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Jeffrey CoachmanMIS LiasonCommented:

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

Frank FreeseAuthor Commented:
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.  
Rey Obrero (Capricorn1)Commented:
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
Frank FreeseAuthor Commented:
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

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

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

Frank FreeseAuthor Commented:
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?
Rey Obrero (Capricorn1)Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
Perfect - as a matter of fact, outside of knowing about the CurrentDb.Excecute command I was thinking the same thing.
I do appreciate you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.