Solved Custom Built InputBox (1 Viewer)

[Momma]

I want to eliminate the 'OpenForm action was cancelled' Run-time error when the user press cancel on an InputBox.
I found a thread, https://www.access-programmers.co.u...-with-cancel-on-inputbox.298736/#post-1569434, where one of the moderators referred the OP to the following link regarding Extending the InputBox function for MS Access Forms.
https://www.databasejournal.com/fea...the-InputBox-function-for-MS-Access-Forms.htm
I was hoping to have a look at the sample database for they which they have a link on that page but unfortunately, the page doesn't exist anymore.
Does someone have a sample db that does sort of the same thing?

 

[The_Doc_Man]

That 'OpenForm action was cancelled' is error 2501, which is a trappable run-time error. If you are opening the form via VBA, then you can declare an error handler (On Error GoTo error-handler-label) in that subroutine to trap the error. Then from the error handler, you can check the error number. If it is 2501, you can do either a Resume Next or a more targeted Resume target-label to proceed from there. What you would do for other errors is up to you, of course. If you are opening the form via a macro, it gets trickier.

 

[theDBguy]

I usually just use something like:

If InputBox("Ask for user input") <> "" Then
    'do something
End If

 

[Momma]

I usually just use something like:

If InputBox("Ask for user input") <> "" Then
    'do something
End If

I've tried the following but it still gives the error.

Private Sub CmdPuppySearch_Click()
        
    DoCmd.OpenForm "frmPuppySearch"
        If InputBox("Ask for user input") <> "" Then
        Exit Sub
    End If

End Sub

 

[June7]

What is the input used for? Ask for the input then OpenForm or not depending on response.

 

[Momma]

What is the input used for? Ask for the input then OpenForm or not depending on response.

The form is bound to a query and it prompts for a date.
When the user chooses to cancel I want to eliminate the error message. If a date is typed in it works fine.

 

[June7]

Date input to InputBox?
I am not understanding how this InputBox has anything to do with form or query.

Does query have popup input prompt for dynamic parameter in its design? I never do this.

I tested your code. I do not get "cancelled" error when cancelling InputBox and form opens just fine.

 

[Momma]

My limited knowledge might cause confusion. Let me explain.
I have a form with command buttons that serves as a menu.
Some of the command buttons open a form with data selected through a query for a specific date, for example, the date of birth for a specific litter.
It does happen sometimes that the wrong command button was selected and when it prompts for a date and you cancel, that's when you get the error 2501

 

[theDBguy]

I've tried the following but it still gives the error.

Private Sub CmdPuppySearch_Click()
        
    DoCmd.OpenForm "frmPuppySearch"
        If InputBox("Ask for user input") <> "" Then
        Exit Sub
    End If

End Sub

That's actually the opposite of what I said. Exiting the Sub is not doing something.

Anyway, it does seem like you're talking about a parameter prompt, rather than an InputBox().

 

[June7]

I tested a form bound to dynamic parameterized query using popup prompt and error handler code and it works. Simple example:

Private Sub Command109_Click()
On Error GoTo Err:
DoCmd.OpenForm "Holidays"
Err:
Debug.Print Err.Number
End Sub

More info http://allenbrowne.com/ser-23a.html

 

[Momma]

Thank you so much for the help and advice, everyone. As always on this forum, I learned something valuable today!

 

[June7]

My advice is to not use dynamic parameterized query. But if you must, don't use popup input prompts. Cannot validate user entry. Reference controls on form for parameter. Validate entries before opening form or report.

 

[Momma]

My advice is to not use dynamic parameterized query. But if you must, don't use popup input prompts. Cannot validate user entry. Reference controls on form for parameter. Validate entries before opening form or report.

Sorry, I don't quite understand what you mean. Do you have an example of what you suggest?

 

[June7]

Example query:

SELECT * FROM tblEmployees WHERE EmpID = Forms!EmpSearch!cbxEmp;

Example code:

If Not IsNull(Me.cbxEmp) Then DoCmd.OpenReport "Employee"

But instead of a dynamic parameter in query, I would prefer:

If Not IsNull(Me.cbxEmp) Then DoCmd.OpenReport "Employee", , , "EmpID=" & Me.cbxEmp

Review http://allenbrowne.com/ser-62.html