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

Rads R
Rads R used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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

Author

Commented:
anyone can let me know what the issue is as i got to complete this assignment asap

thanks
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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
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

Author

Commented:
thanks for your time i did resolve the problem by myself , thanks for your time

thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial