Confirm a record exists before opening a form 2

[rilkyn]

Hi

I have a form which is used to update records on table 1. At the bottom of the form is a button to got to form 2 and display related records that exist. This works fine unless there are no related records on table 2. If this happens form 2 is just blank. What I would prefer is for a warning message to appear advising there are no records to amend.

I tried the following code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLink As Integer, Count as Integer

stDocName = "Delete Document"

stLinkCriteria = "[Link]=" & "'" & Me![Combo97] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

stLink = Count([Link])
If stLink < 0 then
msgbox "No records exist."

End if

but just get errors. Can someone please point me in the right direction.

 

[lars7]

You could have a look at DLookup.

http://support.microsoft.com/kb/q208786/#XSLTH3169121123120121120120

 

[rilkyn]

I've had a look at using Dlookup and other postings and have come up with the following:

Dim stDocName As String
Dim stLinkCriteria As String
Dim StLink As Variant
StLink = Nz(DLookup("[Link]","[Document List]" ,_
"'[Link]'" = "'" & Me![Combo97] & "'"), 0)
If StLink = 0 Then
stDocName = "Delete Document"
stLinkCriteria = "[Link]=" & "'" & Me![Combo97] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "Test"
End If

The problem is that it does not matter whether the value exists or not the result is the same, StLink is always a zero.

Can you show me where I am going wrong?

 

[JoeAtWork]

Try DCount instead

Dim recCount As Long

recCount = DCount("[Link]","[Document List]" ,_
 "[Link]" = "'" & Me![Combo97] & "'")

If recCount > 0 Then
   stDocName = "Delete Document"
   stLinkCriteria = "[Link]=" & "'" & Me![Combo97] & "'"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
   MsgBox "There are no records"
End If

 

[TheAceMan1]

How are ya rilkyn . . .

. . . and this:

[blue]   Dim Criteria As String
   
   Criteria = "[Link] = '" & Me![Combo97] & "'"
   
   If Not IsNull(DLookup("[Link]", "[Document List]", Criteria)) Then
      DoCmd.OpenForm "Delete Document", , , Criteria
   Else
      MsgBox "Link Doesn't Exist!"
   End If[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[rilkyn]

Once again, the experts come to my rescue. Thank you as this problem is now solved. I've gone with Aceman's solution.

Thanks, again

Rilkyn