What to do when there's no match?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a command button with a macro attached that opens a new form and
looks for a matching record. If there's no match, I want to have a pop up
box saying "There's no match" and some options. I've created a Pop-up Form,
but need to know the code for: "if there's no match, display this pop-up".

fldName = [tName.fldName], if no match....


Can anyone help?
 
Eleanor,

If you're using a macro, you can use a Condition in the macro design.
In the Condition of the OpenForm action, something like this...
DCount("*","YourTable","[fldName]=whatever")>0
In the Condition of the MsgBox action, something like this...
DCount("*","YourTable","[fldName]=whatever")=0

Sorry, can't be more specific... it is not clear what you want to
"match" to.
 
Thanks for the help. My macro looks like this:

Private Sub Go_to_Matching_Scoring_Click()
On Error GoTo Err_Go_to_Matching_Scoring_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail - All"
stLinkCriteria = "[PE ID]=" & Me![PE ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Go_to_Matching_Scoring_Click:
Exit Sub
Err_Go_to_Matching_Scoring_Click:
MsgBox Err.Description
Resume Exit_Go_to_Matching_Scoring_Click
End Sub

Right now, it works great, unles there's no matching record. When that
happens it comes up with the debug error and I want it to pop up a form
saying "There's no matching record"


Steve Schapel said:
Eleanor,

If you're using a macro, you can use a Condition in the macro design.
In the Condition of the OpenForm action, something like this...
DCount("*","YourTable","[fldName]=whatever")>0
In the Condition of the MsgBox action, something like this...
DCount("*","YourTable","[fldName]=whatever")=0

Sorry, can't be more specific... it is not clear what you want to
"match" to.

--
Steve Schapel, Microsoft Access MVP
I've created a command button with a macro attached that opens a new form and
looks for a matching record. If there's no match, I want to have a pop up
box saying "There's no match" and some options. I've created a Pop-up Form,
but need to know the code for: "if there's no match, display this pop-up".

fldName = [tName.fldName], if no match....


Can anyone help?
Click to expand...
Click to expand...
 
Eleanor,

This is not a macro. It is a VBA procedure.

Try it like this...

If DCount("*","YourQuery",stLinkCriteria)=0 Then
MsgBox "There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

(where YourQuery is the name of the query (or table) that is the record
source of the "Scoring Detail - All" form)

By the way, it is not a good idea to use a - as part of the name of a
database object... can you rename the form?

--
Steve Schapel, Microsoft Access MVP
Thanks for the help. My macro looks like this:

Private Sub Go_to_Matching_Scoring_Click()
On Error GoTo Err_Go_to_Matching_Scoring_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail - All"
stLinkCriteria = "[PE ID]=" & Me![PE ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Go_to_Matching_Scoring_Click:
Exit Sub
Err_Go_to_Matching_Scoring_Click:
MsgBox Err.Description
Resume Exit_Go_to_Matching_Scoring_Click
End Sub

Right now, it works great, unles there's no matching record. When that
happens it comes up with the debug error and I want it to pop up a form
saying "There's no matching record"


Steve Schapel said:
Eleanor,

If you're using a macro, you can use a Condition in the macro design.
In the Condition of the OpenForm action, something like this...
DCount("*","YourTable","[fldName]=whatever")>0
In the Condition of the MsgBox action, something like this...
DCount("*","YourTable","[fldName]=whatever")=0

Sorry, can't be more specific... it is not clear what you want to
"match" to.

--
Steve Schapel, Microsoft Access MVP
I've created a command button with a macro attached that opens a new form and
looks for a matching record. If there's no match, I want to have a pop up
box saying "There's no match" and some options. I've created a Pop-up Form,
but need to know the code for: "if there's no match, display this pop-up".

fldName = [tName.fldName], if no match....


Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Yes, you're right. I've got it as an Event procedure. I'm getting my
terminology mixed up I guess.

Thanks for the advice about the name of the form - you're right about that
too. I'll change it and I'll give this a try.

Steve Schapel said:
Eleanor,

This is not a macro. It is a VBA procedure.

Try it like this...

If DCount("*","YourQuery",stLinkCriteria)=0 Then
MsgBox "There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

(where YourQuery is the name of the query (or table) that is the record
source of the "Scoring Detail - All" form)

By the way, it is not a good idea to use a - as part of the name of a
database object... can you rename the form?

--
Steve Schapel, Microsoft Access MVP
Thanks for the help. My macro looks like this:

Private Sub Go_to_Matching_Scoring_Click()
On Error GoTo Err_Go_to_Matching_Scoring_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail - All"
stLinkCriteria = "[PE ID]=" & Me![PE ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Go_to_Matching_Scoring_Click:
Exit Sub
Err_Go_to_Matching_Scoring_Click:
MsgBox Err.Description
Resume Exit_Go_to_Matching_Scoring_Click
End Sub

Right now, it works great, unles there's no matching record. When that
happens it comes up with the debug error and I want it to pop up a form
saying "There's no matching record"


Steve Schapel said:
Eleanor,

If you're using a macro, you can use a Condition in the macro design.
In the Condition of the OpenForm action, something like this...
DCount("*","YourTable","[fldName]=whatever")>0
In the Condition of the MsgBox action, something like this...
DCount("*","YourTable","[fldName]=whatever")=0

Sorry, can't be more specific... it is not clear what you want to
"match" to.

--
Steve Schapel, Microsoft Access MVP

Eleanor of Aquitaine wrote:
I've created a command button with a macro attached that opens a new form and
looks for a matching record. If there's no match, I want to have a pop up
box saying "There's no match" and some options. I've created a Pop-up Form,
but need to know the code for: "if there's no match, display this pop-up".

fldName = [tName.fldName], if no match....


Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Aach! this is crazy making! So, I did it the way you suggested, but it
wouldn't match the records, so I changed it to do the match first and then
the msg box. This sort of works because I don't get a debug error anymore,
it goes to a blank record in the associated form, but it won't pop up the
message box.

What am I doing wrong?

Private Sub Go_to_Matching_Scoring_Click()
On Error GoTo Err_Go_to_Matching_Scoring_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail - All"
altDocName = "ScoringPopUp"
stLinkCriteria = "[PE ID]=" & Me![PE ID]
If stLinkCriteria = "[PE ID]=" & Me![PE ID] Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "no matching records"
End If

Exit_Go_to_Matching_Scoring_Click:
Exit Sub

Err_Go_to_Matching_Scoring_Click:
MsgBox Err.Description
Resume Exit_Go_to_Matching_Scoring_Click

End Sub

Eleanor of Aquitaine said:
Yes, you're right. I've got it as an Event procedure. I'm getting my
terminology mixed up I guess.

Thanks for the advice about the name of the form - you're right about that
too. I'll change it and I'll give this a try.

Steve Schapel said:
Eleanor,

This is not a macro. It is a VBA procedure.

Try it like this...

If DCount("*","YourQuery",stLinkCriteria)=0 Then
MsgBox "There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

(where YourQuery is the name of the query (or table) that is the record
source of the "Scoring Detail - All" form)

By the way, it is not a good idea to use a - as part of the name of a
database object... can you rename the form?

--
Steve Schapel, Microsoft Access MVP
Thanks for the help. My macro looks like this:

Private Sub Go_to_Matching_Scoring_Click()
On Error GoTo Err_Go_to_Matching_Scoring_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail - All"
stLinkCriteria = "[PE ID]=" & Me![PE ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Go_to_Matching_Scoring_Click:
Exit Sub
Err_Go_to_Matching_Scoring_Click:
MsgBox Err.Description
Resume Exit_Go_to_Matching_Scoring_Click
End Sub

Right now, it works great, unles there's no matching record. When that
happens it comes up with the debug error and I want it to pop up a form
saying "There's no matching record"


:

Eleanor,

If you're using a macro, you can use a Condition in the macro design.
In the Condition of the OpenForm action, something like this...
DCount("*","YourTable","[fldName]=whatever")>0
In the Condition of the MsgBox action, something like this...
DCount("*","YourTable","[fldName]=whatever")=0

Sorry, can't be more specific... it is not clear what you want to
"match" to.

--
Steve Schapel, Microsoft Access MVP

Eleanor of Aquitaine wrote:
I've created a command button with a macro attached that opens a new form and
looks for a matching record. If there's no match, I want to have a pop up
box saying "There's no match" and some options. I've created a Pop-up Form,
but need to know the code for: "if there's no match, display this pop-up".

fldName = [tName.fldName], if no match....


Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Eleanor,

Go back and read Steve's post again. You missed the most important
part, which is the call to DCount. That's what tells you if there are
any matching records. His code wasn't quite complete because the call
to DCount needs to know the record source for the form you are about to
open, which you hadn't posted because it isn't part of the event
procedure.

BTW, if you don't want the form to open when there are no records, then
you probably don't want to allow the user to add records either.
There's a form property -- look through the property sheet -- that you
can change to eliminate the blank space for adding a record.

Edward
 
The problem is when I do it like that, I get an error saying "Compile Error -
else without if"

Thanks for the info about eliminating the blank space. That's very useful.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail All"
stLinkCriteria = "[PE ID]=" & Me![PE ID]

If DCount("*", "Scoring Results", stLinkCriteria) = 0 Then MsgBox
"There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
 
Eleanor,

That's because your syntax is incorrect. You need to go to a new line
after the "Then", like this...

If DCount("*", "Scoring Results", stLinkCriteria) = 0 Then
MsgBox "There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

I'm pretty sure that's how I gave you before.
 
Okay. Thanks very much. I appreciate your help.

Steve Schapel said:
Eleanor,

That's because your syntax is incorrect. You need to go to a new line
after the "Then", like this...

If DCount("*", "Scoring Results", stLinkCriteria) = 0 Then
MsgBox "There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

I'm pretty sure that's how I gave you before.

--
Steve Schapel, Microsoft Access MVP
The problem is when I do it like that, I get an error saying "Compile Error -
else without if"

Thanks for the info about eliminating the blank space. That's very useful.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Scoring Detail All"
stLinkCriteria = "[PE ID]=" & Me![PE ID]

If DCount("*", "Scoring Results", stLinkCriteria) = 0 Then MsgBox
"There's no matching record"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Click to expand...
Click to expand...
 
Back
Top