Run Time Error When Canceling a Query

[StephenB]

Hello;

• I have a table that contains members (tbl_members). In this table I have a field for member ID (memid).
• I have a query (qry_members) based on the table. The query has parameters on the memid field so I can query for members by member ID.
• On the switchboard I reference the below function on a module (mod_common).

Function Edit_Members()
DoCmd.OpenQuery "qry_members", acNormal, acEdit
End Function

• When I click on Edit Members on the switchboard and enter a member ID, or even leave the parameter dialogue blank, and click "OK", the qeuery runs fine, giving me the appropriate member records, or none when I leave the paramters blank.
• However once the parameters dialogue box appears; if I click "Cancel", I get Microsoft Visual Basis message "Run Time Error '3270'; property not found": "Continue" (disabled), "End" (enabled), "Debug" (Enabled, default), "Help" (Enabled).

I realize the above is quite rudimentary, but I apprecaite any suggestions to resolve the error when clicking "Cancel".
TIA.

 

[vbaInet]

Can you enter edit data if you opened the query on its own without going via the switchboard?

Forms are used for any form of data manipulation and not queries.

 

[StephenB]

Yes. I have no problem when I cancel the query when I've directly opened the qeury. However, I use the switchboard so that users not familiar with access don't have to go to the database window. In fact, I have the db window hidden on start up.

 

[vbaInet]

Revert to using a form and set it to Datasheet view. You wouldn't have the problem then.

 

[StephenB]

I've been trying that as well...

Function Edit_Members2()
DoCmd.OpenForm "frm_members", acFormDS
End Function

...but then I get Run Time Error 2501: The OpenForm action was canceled.

I get the sense I should be using error handling to resolve.

 

[vbaInet]

Set the Default View property of the form to Datasheet View and just call the OpenForm method with one argument, the form name.

DoCmd.OpenForm "frm_members"

Also ensure that Datasheet View is Allowed on the form propery.

 

[gemma-the-husky]

most likely the query itself has a problem,

try opening the query directly. see if it fails to open.


maybe a type mismatch, a function failing, or a non-existent field - something like that.

 

[vbaInet]

most likely the query itself has a problem,

try opening the query directly. see if it fails to open.


maybe a type mismatch, a function failing, or a non-existent field - something like that.

That was what I was thinking and advised, but apparently it works OK when opened directly. See post #3.

 

[gemma-the-husky]

i would trace the program with a breakpoint

i dont use parameter queries like that, so i am not sure what is happening - but i suspect the error may be in the calling routine AFTER the cancel, not in the query itself

it may just be an error you need to intercept and dispose of - like a 2501 error.

 

[StephenB]

Set the Default View property of the form to Datasheet View and just call the OpenForm method with one argument, the form name.

DoCmd.OpenForm "frm_members"

Also ensure that Datasheet View is Allowed on the form propery.

I tried the above, but the form still opened in form view. Not only is the default view on the form datasheet, but I set all the other views to no.

I'm leaning towards Dave's suggestion on handeling the error. So I've bypassed the issue by manually creating a form menu (instead of using the switchboard wizard) and using toolbox to create a command button that opens the query, and then revised the error handling to a more user friendly message:

Private Sub Command39_Click()
On Error GoTo Err_Command39_Click

Dim stDocName As String

stDocName = "qry_FEP_Snapshot_Data Entry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox "You canceled your request", vbOKOnly, "Canceled"
Resume Exit_Command39_Click

End Sub

However, now I'm concerned that if another error appears, I'll miss it because the above handles all errors. Any thoughts?

 

[vbaInet]

In my opinion you're really not doing alot in that procedure so if you miss anything it will just be related to the opening of your query.

However, you can trap the error number in an IF statement. Something like this:

[I]Err_Command39_Click:
    If Err.Number = 2501 Then
        MsgBox "You canceled your request", vbOKOnly, "Canceled"
    Else
        [/I][I]MsgBox Err.Description
    End If[/I][I]
Resume Exit_Command39_Click[/I]

 

[StephenB]

Thanks vbaInet; that's exactly what I was hoping for.

 

[vbaInet]

You're welcome. Here's a good tutorial on error handling. You will find some ways of error trapping useful.

http://www.cpearson.com/excel/ErrorHandling.htm