either bof or eof is true, or the current record has been deleted. reuested operation requires a current record

I have the code below and I am getting the error as  "either bof or eof is true , or the current record has been deleted" basically , The step is to enter the ANo and then click the save button, the commit button checks the status
If the status is Open or closed it just commits and lock the fields
And if it is null it checks the ANumber if it already exist, it also check if it belongs to xxx client
And finally if it belongs to xxxclient and it does not exist in the db then record will be saved.
 ... would greatly appreciate your help ! I am not getting what is wrong in the code ... please clarify

thanks

Private Sub commit_Click()
On Error GoTo Err_commit_Click

Select Case Me.Sta.Value
  Case "Open"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        Me.lSL.Enabled = False
        Me.lBh.Enabled = False
        Me.frmDet.Enabled = False
  Case "Closed"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        Me.lSL.Enabled = False
        Me.lhBh.Enabled = False
        Me.frmDet.Enabled = False
  Case Else
    Dim strCt As String
    strCt = retrCust(lName())
    Dim ANo As Integer
    ANo = Me.lSL.Value
    Dim strSQL As String
    Dim rstConn As ADODB.Recordset
    Dim strSQL1 As String
    Dim rstConn1 As ADODB.Recordset

    strSQL = _
    " SELECT tAL.sID, tAL.CtID " & _
    " FROM tAL " & _
    " WHERE ((tAL.CustID)= '" & sCust & "') " & _
    " AND (tAL.slD)= " & ANo
    'Open Connection
    Set rstConn = New ADODB.Recordset
    rstConn.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText

    strSQL1 = _
    " SELECT tHr.lhL , tHr.lsta" & _
    " FROM tHr " & _
    " WHERE (tHr.lhL)= " & ANo
    'Open Connection
    Set rstConn1 = New ADODB.Recordset
    rstConn1.Open strSQL1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
     If (rstConn!lD = ANo And ANo = rstConn1!lhL) Then
                MsgBox "The  Number entered exist in the database", vbOKOnly, "Exist!"
                DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
                DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
                Me.lhL.Enabled = False
                Me.lhh.Enabled = False
                Me.frmDet.Enabled = False
                DoCmd.CancelEvent
        ElseIf (ANo <> rstConn1!lhL And rstConn!lD <> ANo) Then
                MsgBox "The ASL Number entered does not belong to BSC", vbOKOnly, "Incorrect ASL!"
                DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
                DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
                Me.lhL.Enabled = False
                Me.lhh.Enabled = False
                Me.frmDet.Enabled = False
                DoCmd.CancelEvent
        ElseIf (rstConn!lD = ANo And ANo <> rstConn1!lhL) Then
                DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
                Me.lhL.Enabled = False
                Me.frmDet.Enabled = False
         Else
                MsgBox "Enter an ANumber", vbOKOnly, "Enter an A#!"
                DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
                Me.lhL.Enabled = False
                Me.lhh.Enabled = False
                Me.frmDetails.Enabled = False
                DoCmd.CancelEvent
        End If
        rstConn1.close
        rstConn.close
    End Select

Exit_commit_Click:
    Exit Sub

Err_commit_Click:
    MsgBox Err.Description
    Resume Exit_commit_Click
   
End Sub

Radhs74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Radhs74Author Commented:
i gave this Debug.Print tHr.lhL after the strSQL1 statement and whenever it comes to this line of debug it goes to msgbox err.description and shows the error as "object required" .... and it does not show anything in the immediate window
0
Radhs74Author Commented:
anyone can let me know what the issue is as i got to complete this assignment asap

thanks
0
käµfm³d 👽Commented:
Your recordset usually doesn't have any rows when you get this error message. Try adding a check like

    If Not rstConn.EOF Then

before you attempt to access columns in the recordset at the line

    If (rstConn!lD = ANo And ANo = rstConn1!lhL) Then

Make sure to end this new "If" by adding another "End If" after the one you have in this case statement. The full code would be as below.

Also, if you are concerned that your recordset should have rows, double-check your query to ensure that is logically correct.
Private Sub commit_Click()
On Error GoTo Err_commit_Click

Select Case Me.Sta.Value
  Case "Open"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        Me.lSL.Enabled = False
        Me.lBh.Enabled = False
        Me.frmDet.Enabled = False
  Case "Closed"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        Me.lSL.Enabled = False
        Me.lhBh.Enabled = False
        Me.frmDet.Enabled = False
  Case Else
    Dim strCt As String
    strCt = retrCust(lName())
    Dim ANo As Integer
    ANo = Me.lSL.Value
    Dim strSQL As String
    Dim rstConn As ADODB.Recordset
    Dim strSQL1 As String
    Dim rstConn1 As ADODB.Recordset

    strSQL = _
    " SELECT tAL.sID, tAL.CtID " & _
    " FROM tAL " & _
    " WHERE ((tAL.CustID)= '" & sCust & "') " & _
    " AND (tAL.slD)= " & ANo
    'Open Connection
    Set rstConn = New ADODB.Recordset
    rstConn.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText

    strSQL1 = _
    " SELECT tHr.lhL , tHr.lsta" & _
    " FROM tHr " & _
    " WHERE (tHr.lhL)= " & ANo
    'Open Connection
    Set rstConn1 = New ADODB.Recordset
    rstConn1.Open strSQL1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
    
    If Not rstConn.EOF Then
		If (rstConn!lD = ANo And ANo = rstConn1!lhL) Then
				MsgBox "The  Number entered exist in the database", vbOKOnly, "Exist!"
				DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
				DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
				Me.lhL.Enabled = False
				Me.lhh.Enabled = False
				Me.frmDet.Enabled = False
				DoCmd.CancelEvent
		ElseIf (ANo <> rstConn1!lhL And rstConn!lD <> ANo) Then
				MsgBox "The ASL Number entered does not belong to BSC", vbOKOnly, "Incorrect ASL!"
				DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
				DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
				Me.lhL.Enabled = False
				Me.lhh.Enabled = False
				Me.frmDet.Enabled = False
				DoCmd.CancelEvent
		ElseIf (rstConn!lD = ANo And ANo <> rstConn1!lhL) Then
				DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
				Me.lhL.Enabled = False
				Me.frmDet.Enabled = False
		 Else
				MsgBox "Enter an ANumber", vbOKOnly, "Enter an A#!"
				DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
				Me.lhL.Enabled = False
				Me.lhh.Enabled = False
				Me.frmDetails.Enabled = False
				DoCmd.CancelEvent
		End If
	End If
	
        rstConn1.close
        rstConn.close
    End Select

Exit_commit_Click:
    Exit Sub

Err_commit_Click:
    MsgBox Err.Description
    Resume Exit_commit_Click
   
End Sub

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Radhs74Author Commented:
there are 2 recordsets one is rstconn and rstconn1 as I am newbie to vba , do we have to declare a connection in order to use the recordset and where exactly do we need to close or set it to "nothing"

i declared 2 variables one for first sql as var_v1 and var_v2  for second sql .. one more question I have is as I did implement this part of the code for both the SQLs it is retrieving data from the first sql as I checked with debug.print to get the value .. but it is giving issue when i try to get the data from second sql this is the code i added for both the first sql and second sql after the rstconn1.open
If Not rstConn.EOF And Not rstConn.BOF Then
            Var_v2 = (rstConn.Fields(0).Value)
        End If
    rstConn.close

it gives me the error "object required" but i am not sure what i am missing

thanks
0
Radhs74Author Commented:
I am getting the error as "command or object isn't available now" on the line
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

please help out

thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Radhs74Author Commented:
thanks for your time i did resolve the problem by myself , thanks for your time

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.