asked on

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

Rads R

ASKER

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

Rads R

ASKER

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

thanks

kaufmed

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

Rads R

ASKER

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

ASKER CERTIFIED SOLUTION

Rads R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rads R

ASKER

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

thanks