Recordset

[Guest]

i need some help to correct the below code. Thanks

Public Sub Check_Records()

Dim dbs As DAO.Database, qdfCurr As DAO.QueryDef, rst As DAO.Recordset,
strSQL As String

Set dbs = CurrentDb

If QueryDefs("NAMEOFMYQRY").Recordset.RecordCount = 0 Then
MsgBox "There are no records to wiew.", vbQuestion, "Error"
End If

End Sub

 

[Douglas J. Steele]

QueryDef objects have no Recordset or RecordCount property. You could open a
recordset (using the OpenRecordset method) and work with it, or simply use:

Public Sub Check_Records()

If DCount("*", "NAMEOFMYQRY") = 0 Then
MsgBox "There are no records to wiew.", vbQuestion, "Error"
End If

End Sub

 

[Guest]

the message box comes up, but the code continue to run. I want when the
person hit ok to stop

 

[Guest]

Private Sub Command11_Click()

Dim LBx As ListBox, criName As String, criStatus As String, Cri As String,
DQ As String, itm
DQ = """"

Call Which_Month

Set LBx = Me!List2
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criName <> "" Then
criName = criName & ", " & DQ & LBx.Column(1, itm) & DQ
Else
criName = DQ & LBx.Column(1, itm) & DQ
End If
Next
criName = "[Assigned Team Member] In(" & criName & ")"
Debug.Print criName

Else '=0 nothing in listbox
MsgBox "Please select an Analyst", vbCritical
Exit Sub

End If


Set LBx = Me!List4
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criStatus <> "" Then
criStatus = criStatus & ", " & DQ & LBx.Column(0, itm) & DQ
Else
criStatus = DQ & LBx.Column(0, itm) & DQ
End If
Next
criStatus = "[Status] In(" & criStatus & ")"
Debug.Print criStatus

Else '=0 nothing in listbox
MsgBox "Please select one or more Status", vbCritical
Exit Sub
End If
Call Check_Records
Cri = criName & IIf(criName > "", " and ", "") & criStatus
DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing

End Sub

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click
DoCmd.Close
DoCmd.OpenForm "Reports"
Exit_Command12_Click:
Exit Sub
Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub
Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of month you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Month in MM format: Enter '0' for All",
"Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where [Submit Date] LIKE '" & strPrompt & "*' " & ""
Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If

End Sub


Public Sub Check_Records()

If DCount("*", "Step1_Member_Status") = 0 Then
MsgBox "There are no records to wiew.", vbQuestion, "Error"
Else

End If

End Sub

 

[Guest]

Please help
Public Sub Check_Records()
Dim Cancel
Dim m
If DCount("*", "Step1_Member_Status") = 0 Then
m = MsgBox("There are no records to wiew, Would you like to
continue!", vbQuestion + vbYesNo, "Error")
If m = vbYes Then
Cancel = False
Else
STOP ALL Codes because this function is called from a Sub, and it keep
runing(last 4 lines keep runing if a person select NO) I want to end
End If
End If
End Sub

Call Check_Records
Cri = criName & IIf(criName > "", " and ", "") & criStatus
DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing

 

[Douglas J. Steele]

Setting Cancel = False doesn't do anything.

You could try something like:

Public Sub Check_Records()
Dim Cancel As Boolean
Dim m As Long

Cancel = False
If DCount("*", "Step1_Member_Status") = 0 Then
m = MsgBox("There are no records to wiew, Would you like " & _
"to continue!", vbQuestion + vbYesNo, "Error")
If m <> vbYes Then
Cancel = True
End If
End If

If Cancel = False Then
Call Check_Records
End If

End Sub