Help needed to cancel the opening of a form. 1

[Secretgeek]

Good morning everyone.

The title of this thread almost says it all.

What I need to add is that I only want the form to cancel opening if the filter value passed from form A does not exist and the user hasn't clicked the 'Add New Case' button on Form A.

What is happening at the minute is my 'Add New Case' works fine. But if you enter a number that doesn't exist in my 'Go to case number' button & textbox, Form B opens completely blank (and impossible to get out of without switching to design view) and I don't want that. My trouble is I can't work out how to get access to check either if the ID number exists already or if the Add new record button has been pressed.

I suspect it could be solved with a SQL query running on the contents of the 'Go to Case Number' textbox but I simply don't have the necessary knowledge where sql is concerned.

Any help, as always, much appreciated.

 

[MajP]

In your go to case, use a combobox not a textbox. Set it to limittolist = yes. The user can still type away, but can only enter a valid number. Also it is just a much more user friendly interface.

 

[Secretgeek]

It's funny how you can get so wrapped up you can't see the wood for the sql's!

Many thanks MajP. Works like a charm.

 

[randy700]

To determine if the case number exists in the table:

if(DCount("CaseNbr", "tblCases", "CaseNbr = '" & Me.txtCaseNbr & "'") > 0 Then
    MsgBox "Case number found"
Else
    MsgBox "Case number not found"
End If

Remove the single quotes if case number is not a text field.


Randy