How to set recordset or count to determine existing record in table (1 Viewer)

[Tamang Aman]

Dear Experts
This is my first time ever in Forum and seeking of big help from all of you.
I am very very new to VBA and I have been self-learning VBA from two month and have been assigned to new project “Work Authorization & Lock Out Tag Out”
I have table called tblWA & tblLOTO
Basically most of the LOTO/s are associated with WA #, following example
WA # in tblWA
Associated LOTO/s in tblLOTOs
25258
123456
123457
123458
123459
25259
123410
123411
123412
123413


However, both tables are in relationship right now
Now, this is my goal to achieve
I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close)
Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close
In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board can’t close
Here is the code I have been playing with no success
Private Sub PermitStatus_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from qryLOTO where (WAAndPrefix)= " & Me.WAPrefix & " AND (LOTOStatusList) = "In Field Today, "On-Hold, "In Approval")

On Error Resume Next
DoCmd.SetWarnings False
''''''''''''''''''''''''''''''''
If rs.EOF Then
On Error Resume Next 'Exit Sub
DoCmd.SetWarnings False
If MsgBox("The selected LOTO " & WAPrefix.Value & " The WA # you trying close are associated with many LOTO/s, can't close this WA# unless associated LOTO/s are closed"),,"Warning !!! Please read carefully"

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
I am looking forward to receive any help from any of you that will safe my life
Thanks & best regards,
Tamang

 

[clarkcovey]

Tamang,
What are the results of your recordset when you run it? Are you receiving any type of error message?

You can set a breakpoint at your "Set rs = ..." and step through the code, checking the results as you progress.

One thing you may want to add after your "Set rs = ..." command is to check if there are any records. If there are no records from the record set, what do you want to happen? Trying to perform a function on non-existent data may prove to be a bit frustrating.

You can use something like this: "If rs.RecordCount <> 0 Then "

Clark

 

[smig]

No need for recordset.
You can use DCount() to check if you have records matching your criteria.

 

[Tamang Aman]

Dear Smig / larkcovey

There was no any error messages from the event however after 2 hours of hard work i got my result. This is how i achieved and did, may be not the best code writing but its working effectively. Here is code i used

Private Sub PermitStatus_GotFocus()
Forms!frmWASwitchBoard!WAHolder.Value = DCount("[WAID]", "[qryLOTO]", "WAID=" & WAID.Value)
& WAPrefix.Value)
If Forms!frmWASwitchBoard!WAHolder.Value > 0 Then
Else
Me.PermitStatus.Locked = False
End If


Private Sub PermitStatus_AfterUpdate()
If Forms!frmWASwitchBoard!WAHolder.Value > 0 Then
If MsgBox("The selected WA " & WAPrefix.Value & " can't be closed at this time due to there are " & Forms!frmWASwitchBoard!WAHolder.Value & " active LOTO/s " & vbCrLf & vbCrLf & "Do you want to view Refrenced LOTO / s to THIS WA?" _
& vbCrLf & vbCrLf & "Click Yes to View or No to Cancel", _
vbYesNo + vbExclamation, "Warning !!! This action can't be perform") = vbYes Then
Me.PermitStatus = Forms!frmWASwitchBoard!Statusholder.Value
Cancel = True
DoCmd.OpenForm "frmRefrencedLOTOOnlyWithWAForSearchA"
Exit Sub
Else
End If
End If
End Sub