Count Records then have Message Box for Action

[ElizaD]

I am trying to figure out how to create the code that would open a popup
message box if the record count for a table is greater than 0 with a yes/no
choice to continue or not. If there are no records in the table, then no
message box would show. I do not need to know the actual record count.

Private Sub OpenReqUpdate_Click()
On Error GoTo Err_OpenBARUpdate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Request Update Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenReqUpdate_Click:
Exit Sub

Err_OpenReqUpdate_Click:
MsgBox Err.Description
Resume Exit_OpenReqUpdate_Click

End Sub

Main Form: Select command button “Open Request Update†(code above)

Insert the following in the above code:

If Record Count of tblReqDetails is < 0, then
continue on to open “Request Update Formâ€

Else If Record Count of tblReqDetails is >0, then
Message Box stating “Request already exists, continue?â€

If No, then do nothing
If Yes, continue on to open “Request Update Formâ€

I can find bits and pieces of what I want on the web and in my books, but I
am having a difficult time putting it all together. Any help would be
appreciated.

 

[GBA]

You probably want to go to the VB Help and get to know DCount.

Here is a snippet of code I used once that is similar to what you are trying
to do:
Dim NameCheck As Integer
NameCheck = DCount("[Customers]", "CustomersTable", "[Customers]= '" &
Me![Customer] & "'")
If NameCheck = 0 Then
MsgBox ("Stop! This customer is not found in the customer list")
End If

 

[ElizaD]

Thanks, this is very helpful. I have been reading on the DCount function but
there are a lot of arguments out there about it and I am still trying to make
sense of it. Most of it is written for people with more coding experience
than I have - I am a rank beginner! Your example is very clear and simple and
I appreciate it.

You probably want to go to the VB Help and get to know DCount.

Here is a snippet of code I used once that is similar to what you are trying
to do:
Dim NameCheck As Integer
NameCheck = DCount("[Customers]", "CustomersTable", "[Customers]= '" &
Me![Customer] & "'")
If NameCheck = 0 Then
MsgBox ("Stop! This customer is not found in the customer list")
End If

I am trying to figure out how to create the code that would open a popup
message box if the record count for a table is greater than 0 with a yes/no
choice to continue or not. If there are no records in the table, then no
message box would show. I do not need to know the actual record count.

Private Sub OpenReqUpdate_Click()
On Error GoTo Err_OpenBARUpdate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Request Update Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenReqUpdate_Click:
Exit Sub

Err_OpenReqUpdate_Click:
MsgBox Err.Description
Resume Exit_OpenReqUpdate_Click

End Sub

Main Form: Select command button “Open Request Update†(code above)

Insert the following in the above code:

If Record Count of tblReqDetails is < 0, then
continue on to open “Request Update Formâ€

Else If Record Count of tblReqDetails is >0, then
Message Box stating “Request already exists, continue?â€

If No, then do nothing
If Yes, continue on to open “Request Update Formâ€

I can find bits and pieces of what I want on the web and in my books, but I
am having a difficult time putting it all together. Any help would be
appreciated.