?
Solved

Act on Cancel Button in parameter query

Posted on 2008-02-05
12
Medium Priority
?
703 Views
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.
0
Comment
Question by:Frank Freese
  • 4
  • 4
  • 2
  • +2
12 Comments
 
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
       else
       docmd.close acform, me.name
end if
0
 
LVL 34

Expert Comment

by:jefftwilley
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.
J
0
 
LVL 7

Expert Comment

by:UniqueData
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...
             qdf.Execute
             qdf.Close
        endif
  else
      msgbox "You did not enter a value"
      docmd.close
   endif


0
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.

 
LVL 7

Expert Comment

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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20830105
fh_freese,

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
...ect

Here is a great link on this subject:
http://www.fontstuff.com/access/acctut08.htm

Hope this helps as well

JeffCoachman
0
 

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.  
0
 
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

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23137035.html?#a20820560
0
 

Author Comment

by:Frank Freese
ID: 20832420
capricorn1:
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
Else
(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])));
qd.sql=ssql
End If


0
 
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.

0
 

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?
0
 
LVL 120

Accepted Solution

by:
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
Else
    MsgBox "Deletion cancelled"
End If
End Sub
0
 

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!
0

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