Enter Parameter Dialog Box- Macro-Cancel Button

[jandrade15]

Hi Everyone,

I Have an annoying problem. I have a macro thats running a query in which it prompts for a "enter parameter dialog box." Lets say I hit the macro by mistake and I hit the cancel button on the dialog box I get the "Action Failed" dialog box for the macro. Ive tried the onerror, stopallmacros, stopmacros trap to no avail. can anyone tell me what i'm doing wrong? I'd like to just hit the cancel button and stop the macro...
Also when I run the query without the macro it does cancel correctly.

Thanks for any help on this. I know the solution is right in front of me and should be clear but i'm racking my brain on this one....

 

[boblarson]

I would use VBA instead of a macro so you can do error handling and capture that error. Try using the built in tool for converting a macro to VBA and then in the Error handler that results, put this around the part of the error handler that shows the message box

If Err.Number <> 2501 Then
   ... the existing error handler stuff here
End If

 

[jandrade15]

Awesome! Again Boblarson Thanks a million for your help it worked perfectly! I also have another post in the queries side of the forum and here is another problem im having:

Ok im trying to make this work but im a little stuck.... here are some more specifics:
I hava a macro running two queries one to delete a temp table and one to append to that temp table. I have converted the macro to vb and im trying to insert the dcount function but im not sure how to make it work??? Below is the code I have:
Code:
Function Mail_Merge_EXP()
On Error GoTo Mail_Merge_EXP_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete_Exp_Table", acViewNormal, acEdit
DoCmd.OpenQuery "Expired_Data_Transfer", acViewNormal, acEdit

DoCmd.SetWarnings True


Mail_Merge_EXP_Exit:
Exit Function

Mail_Merge_EXP_Err:
MsgBox Error$
Resume Mail_Merge_EXP_Exit

End Function
How could I get the following to happen: Right after the "expired-data-transfer" query give me a record count msgbox for the records in the temp table where the data is being appended to?

I was trying to use the dcount function (below, not modified version)

If DCount("*", "[MasterTable]", "[Property Address] = " & Chr(34) & Me![Property Address].Text & Chr(34)) > 0 Then
   MsgBox "This address is already entered.", vbExclamation + vbOKOnly, "Duplicate Entry"
   cancel = True
   Me.Undo
End If

that you helped me with on a previous issue, but I couldn't get it right??? can you point out what I could be doing wrong? Thanks again Boblarson!

 

[boblarson]

Put the message box in after the query runs and put:

MsgBox DCount("*","YourTempTableName")

 

[jandrade15]

Again, it worked beautifully, Thank You Thank You!!!

Now to go a bit further... how can I modify the msgbox so that I can have the following statement:
If results are less than 0 then msgbox "your date gave no results:"
else msgbox "You have _ records"

again I was trying to use the previous code a and modify it but no luck. Here is what I currently have

Function Mail_Merge_EXP()
On Error GoTo Mail_Merge_EXP_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Delete_Exp_Table", acViewNormal, acEdit
    DoCmd.OpenQuery "Expired_Data_Transfer", acViewNormal, acEdit
    MsgBox DCount("*", "expired_temp")
    DoCmd.SetWarnings True

Mail_Merge_EXP_Exit:
    Exit Function

Mail_Merge_EXP_Err:
    MsgBox Error$
    Resume Mail_Merge_EXP_Exit

End Function

Thanks a million!!!

 

[boblarson]

Function Mail_Merge_EXP()
[COLOR="Red"]Dim lngCount As Long[/COLOR]
On Error GoTo Mail_Merge_EXP_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Delete_Exp_Table", acViewNormal, acEdit
    DoCmd.OpenQuery "Expired_Data_Transfer", acViewNormal, acEdit
    
[COLOR="Red"]    lngCount = DCount("*", "expired_temp")
    If lngCount = 0 Then    
        MsgBox "Your date gave no results", vbInformation, "Record Count"
    Else
        MsgBox "You have " & lngCount & " records", vbInformation, "Record Count"
    End If[/COLOR]
    DoCmd.SetWarnings True

Mail_Merge_EXP_Exit:
    Exit Function

Mail_Merge_EXP_Err:
    MsgBox Error$
    Resume Mail_Merge_EXP_Exit

End Function

 

[jandrade15]

Boblarson Thanks so much for all your help! everything you've helped me with has worked beautifully!!! Im learning alot from you and hopefully I can pick up a whole lot more! Again Thanks and I hope you have a great day! I hope its not as hot today!!

 

[boblarson]

You're welcome. Glad we could assist. And, it supposed to only be in the low 90's today instead of the 106 the other day.