Stop Form Opening if No Records Match

[dots]

Hi all,

I'm pretty sure this has been answered before, but I can't seem to come up with the right "keywords" to find it.

I have a button on my form that when pressed, asks the user to input the Work Order number they are looking for. The form (based on a query) then opens to that specific Work Order.

What I want to do is if the user inputs a Work Order number that does not exist, they will get an error message.

Right now, my code is this:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WODeleteForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If DCount("[Work Order]", "qryWODelete&quot = 0 Then
MsgBox ("That Work Order Number Does Not Exist&quot
Exit Sub
End If

When I run it, I get the error message "You Canceled the Previous Operation".

Does anybody know what I'm doing wrong?

Thank you in advance.

 

[mgolla]

Change to:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "WODeleteForm"

If DCount("[Work Order]", "qryWODelete&quot = 0 Then
MsgBox ("That Work Order Number Does Not Exist&quot
Exit Sub
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If



HTH
Mike

 

[RoyVidar]

This Dcount doesn't have any criteria, so it would probably open the form anytime.

You'll need to pass the workorder as criteria to the DCount function, for instance something like this as the last argument of the DCount:

[tt]..., "[Work Order] = " & Me!WorkOrderControl.Value)[/tt]

(use the name of the control holding the inputted workorder, use [brackets] if it contains spaces)

Roy-Vidar

 

[dots]

Thank you for the input.

Mike: When I tried your suggestion I got the same error message "You Canceled the Previous Operation."

Roy: When I tried your suggestion I got the message, "Microsoft Access can't find the field 'Enter Work Order Number' referred to in your expression.

I changed the beginning of the If statement as follows:

If DCount("[Work Order]", "qryWODelete", "[Work Order] = " & Me![Enter Work Order].Value) = 0 Then

Enter Work Order is the variable in the query.

 

[RoyVidar]

That ususally means there's a typo. Do you have a control (textbox) on your form called [Enter Work Order], or are you using a parameter in the query?

If the latter there's no way you can test for number of records (as I know of). In addition, there's no way to do any datavalidation on it at all. Try entering the parameter in a text control on the form, and refer to that in the query, that way mgolla's suggestion might work without criteria.

Here's another version, more like how I open forms. Using DCount for testing, having a text control with the value on the form and a query without criteria, it might look something like this:

[tt]stLinkCriteria="[Work Order] = " & Me!WorkOrderControl.Value
If DCount("[Work Order]", "querywithoutcriteria", _
stLinkCriteria) = 0 Then
MsgBox ("That Work Order Number Does Not Exist&quot
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If[/tt]

Else, for easy validation, you could have the user select from a combobox, which ensures a valid selection...

Roy-Vidar

 

[dots]

Thank you Roy. I wasn't quite sure what you meant, but you pointed me in the right direction.

I added a variable field to my query called MyCount, which Counted the Work Order field. I added a field to my form with the control source of MyCount, and put the following code in the On Open event of the form:

Dim MyCt As Variant
MyCt = [MyCount]

If IsNull(MyCt) Then
MsgBox ("Work Order Number Does Not Exist&quot
DoCmd.Close
End If

It is working perfectly now.

Thank you.