Solved runtime error 3021 no current records

A

Ashisht76

Member
Local time
Today, 12:09
Joined
Jan 31, 2022
Messages
44
I am getting said error in case when my loop is hitting situation having no data in table feild, i have searched on net and found that i need to insert if statement to check the set value is null. I tried but I am unable to find solution, see if anyone can help .. Thx in advance

Private Sub BtnTotal13To24_Click()

Dim db As DAO.Database ' declaring dao databse
Dim rst As DAO.Recordset ' declaring dao recordset
Dim strSQL As String ' string for select statement to opend table data
Dim pcsid, pcsid1, y, x, z As Integer
Dim strQuery As String ' for dsum condition, conditon of first and 12th value of field and matching fabirc received id of table and form

Set db = CurrentDb ' set db as current db
strSQL = "SELECT * FROM TblFabricPieceEntry WHERE FabricReceiveID = " & Me!FabricReceiveID ' select statement for opending databse witch condition
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) ' opening table
x = 0 'for finding 1st value in table
z = 0 ' for segrigating dsum in correct textbox

For y = 0 To 2 Step 1

rst.Move (x) 'finding 1st value and then 13th , 23rd...
pcsid = rst!FabricPieceID ' assigning value to 1st variable
'Debug.Print pcsid
rst.Move (11) 'finding 12th value and then 24th , 36rd...
pcsid1 = rst!FabricPieceID ' assigning value to 2nd variable
'Debug.Print pcsid1

strQuery = "[FabricPieceID] between " & pcsid & " and " & pcsid1 & "And" & "[FabricReceiveID] = " & Me!FabricReceiveID
'placing variable in string with second condition
Select Case z
Case Is = 0
Me.TxtTotal1To12.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
Me.TxtTotal13To24.Value = 0
Me.TxtTotal25To36.Value = 0
'for filling up 1st text box of 1st loop
Case Is = 1
Me.TxtTotal13To24.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
Me.TxtTotal25To36.Value = 0
'for filling up 2nd text box of 2nd loop
Case Is = 2
Me.TxtTotal25To36.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
'for filling up 3rd text box of 3rd loop
Case Else
MsgBox "error"
End Select
z = z + 1
x = 1

Next y

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
 
please use code tags around your code to preserve indentation (highlight code and click the </> button). Otherwise difficult to read and understand what the code is supposed to be doing
 
There is a free utility Smart Indenter which will quickly indent vba code.

Access also has an Auto indent feature that works with the tab button.
 
Try adding an error handler in your code.
 
Ashisht76 said:
I tried but I am unable to find solution, see if anyone can help
Click to expand...
the only thing I realized is that there can be up to 36 entries per line of the report, but unfortunately not always

at the same time, every 12 records are summed up

EXOTIC!!
 
Code: 
Private Sub BtnTotal13To24_Click()

    Dim db As DAO.Database ' declaring dao databse
    Dim rst As DAO.Recordset ' declaring dao recordset
    Dim strSQL As String ' string for select statement to opend table data
    Dim pcsid, pcsid1, y, x, z As Integer
    Dim strQuery As String ' for dsum condition, conditon of first and 12th value of field and matching fabirc received id of table and form
    
    'arnelgp
    On Error GoTo err_handler
    
    Set db = CurrentDb ' set db as current db
    strSQL = "SELECT * FROM TblFabricPieceEntry WHERE FabricReceiveID = " & Me!FabricReceiveID ' select statement for opending databse witch condition
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) ' opening table
    x = 0 'for finding 1st value in table
    z = 0 ' for segrigating dsum in correct textbox
    
    For y = 0 To 2 Step 1
        
        rst.Move (x) 'finding 1st value and then 13th , 23rd...
        
        'arnelgp
        If rst.EOF Then
            Exit For
        End If
        
        pcsid = rst!FabricPieceID ' assigning value to 1st variable
        'Debug.Print pcsid
        rst.Move (11) 'finding 12th value and then 24th , 36rd...
        
        'arnelgp
        If rst.EOF Then
            Exit For
        End If
        
        pcsid1 = rst!FabricPieceID ' assigning value to 2nd variable
        'Debug.Print pcsid1
        
        strQuery = "[FabricPieceID] between " & pcsid & " and " & pcsid1 & "And" & "[FabricReceiveID] = " & Me!FabricReceiveID
        'placing variable in string with second condition
        Select Case z
            Case Is = 0
                Me.TxtTotal1To12.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
                Me.TxtTotal13To24.Value = 0
                Me.TxtTotal25To36.Value = 0
                'for filling up 1st text box of 1st loop
            Case Is = 1
                Me.TxtTotal13To24.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
                Me.TxtTotal25To36.Value = 0
                'for filling up 2nd text box of 2nd loop
            Case Is = 2
                Me.TxtTotal25To36.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
                'for filling up 3rd text box of 3rd loop
            Case Else
                MsgBox "error"
        End Select
        z = z + 1
        x = 1
        
    Next y
err_handler:
    If Not rst Is Nothing Then
        rst.Close
    End If
    Set rst = Nothing
    Set db = Nothing
End Sub
 
To answer the basic question: The easiest way to do this is when you loop the recordset, you have this line...

Code: 
strQuery = "[FabricPieceID] between " & pcsid & " and " & pcsid1 & "And" & "[FabricReceiveID] = " & Me!FabricReceiveID

You could do a DCOUNT() of strQuery immediately after you build the SQL string (before you attempt the other operations). That would tell you immediately whether you had an empty set. If you do, you can take some other action. What other action is, of course, your choice - but this is how you discover whether you need to perform that other action.
 
Ashisht76 said:
I am getting said error in case when my loop is hitting situation having no data in table feild, i have searched on net and found that i need to insert if statement to check the set value is null. I tried but I am unable to find solution, see if anyone can help ..
Click to expand...
the most unpleasant thing with this approach is which series of 12 records is missing a line

based on the figure 12, we can assume that this is an export from excel of some totals by month, for example, data for 3 years

or 12 lines each
1 -quantity
2- cost of work
3 - something else
 
I am trying to correction as per the suggestions made and will revert back meant while thanks everyone for replying me.
 
arnelgp said:
Code: 
Private Sub BtnTotal13To24_Click()

    Dim db As DAO.Database ' declaring dao databse
    Dim rst As DAO.Recordset ' declaring dao recordset
    Dim strSQL As String ' string for select statement to opend table data
    Dim pcsid, pcsid1, y, x, z As Integer
    Dim strQuery As String ' for dsum condition, conditon of first and 12th value of field and matching fabirc received id of table and form
   
    'arnelgp
    On Error GoTo err_handler
   
    Set db = CurrentDb ' set db as current db
    strSQL = "SELECT * FROM TblFabricPieceEntry WHERE FabricReceiveID = " & Me!FabricReceiveID ' select statement for opending databse witch condition
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) ' opening table
    x = 0 'for finding 1st value in table
    z = 0 ' for segrigating dsum in correct textbox
   
    For y = 0 To 2 Step 1
       
        rst.Move (x) 'finding 1st value and then 13th , 23rd...
       
        'arnelgp
        If rst.EOF Then
            Exit For
        End If
       
        pcsid = rst!FabricPieceID ' assigning value to 1st variable
        'Debug.Print pcsid
        rst.Move (11) 'finding 12th value and then 24th , 36rd...
       
        'arnelgp
        If rst.EOF Then
            Exit For
        End If
       
        pcsid1 = rst!FabricPieceID ' assigning value to 2nd variable
        'Debug.Print pcsid1
       
        strQuery = "[FabricPieceID] between " & pcsid & " and " & pcsid1 & "And" & "[FabricReceiveID] = " & Me!FabricReceiveID
        'placing variable in string with second condition
        Select Case z
            Case Is = 0
                Me.TxtTotal1To12.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
                Me.TxtTotal13To24.Value = 0
                Me.TxtTotal25To36.Value = 0
                'for filling up 1st text box of 1st loop
            Case Is = 1
                Me.TxtTotal13To24.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
                Me.TxtTotal25To36.Value = 0
                'for filling up 2nd text box of 2nd loop
            Case Is = 2
                Me.TxtTotal25To36.Value = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", strQuery)
                'for filling up 3rd text box of 3rd loop
            Case Else
                MsgBox "error"
        End Select
        z = z + 1
        x = 1
       
    Next y
err_handler:
    If Not rst Is Nothing Then
        rst.Close
    End If
    Set rst = Nothing
    Set db = Nothing
End Sub
Click to expand...
you absolutely nailed it! Its brilliant idea to have "If" statement with EOF to exit the code. Thanks a ton!
 
Ashisht76 said:
I am getting said error in case when my loop is hitting situation having no data in table feild, i have searched on net and found that i need to insert if statement to check the set value is null. I tried but I am unable to find solution, see if anyone can help ..
Click to expand...
of course, maybe I don't understand something, but apparently I would write the following code or if ...elseif ...elseif...end if
Code: 
Private Sub BtnTotal13To24_Click()

Dim db As DAO.Database ' declaring dao databse
Dim rst As DAO.Recordset ' declaring dao recordset
Dim strSQL As String ' string for select statement to opend table data
Dim x As Integer
Dim strQuery As String ' for dsum condition, conditon of first and 12th value of field and matching fabirc received id of table and form
Dim sum0 As Currency, sum12 As Currency, sum24 As Currency, sum36 As Currency
Dim n1z As Long
sum12 = 0
sum24 = 0
sum36 = 0
Set db = CurrentDb ' set db as current db
n1z = Me!FabricReceiveID
strSQL = "SELECT FabricReceiveID,ReceiveGreyFabricMeter FROM TblFabricPieceEntry " _
& " WHERE FabricReceiveID = " & n1z ' select statement for opending databse witch condition
Set rst = db.OpenRecordset(strSQL) ' opening table
x = 0 'for count of record
Do While rst.EOF = False
x = x + 1
sum0 = Nz(rst![ReceiveGreyFabricMeter], 0)
Select Case x
Case x = 1 To 12
sum12 = sum12 + sum0
Case x = 13 To 24
sum24 = sum24 + sum0
Case x = 25 To 36
sum36 = sum36 + sum0
Case Else
MsgBox "error"
End Select
rst.MoveNext
Loop
Debug.Print sum12, sum24, sum36
Me.TxtTotal1To12.Value = sum12
Me.TxtTotal13To24.Value = sum24
Me.TxtTotal25To36.Value = sum36


rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
 
SHANEMAC51 said:
of course, maybe I don't understand something, but apparently I would write the following code or if ...elseif ...elseif...end if
Code: 
Private Sub BtnTotal13To24_Click()

Dim db As DAO.Database ' declaring dao databse
Dim rst As DAO.Recordset ' declaring dao recordset
Dim strSQL As String ' string for select statement to opend table data
Dim x As Integer
Dim strQuery As String ' for dsum condition, conditon of first and 12th value of field and matching fabirc received id of table and form
Dim sum0 As Currency, sum12 As Currency, sum24 As Currency, sum36 As Currency
Dim n1z As Long
sum12 = 0
sum24 = 0
sum36 = 0
Set db = CurrentDb ' set db as current db
n1z = Me!FabricReceiveID
strSQL = "SELECT FabricReceiveID,ReceiveGreyFabricMeter FROM TblFabricPieceEntry " _
& " WHERE FabricReceiveID = " & n1z ' select statement for opending databse witch condition
Set rst = db.OpenRecordset(strSQL) ' opening table
x = 0 'for count of record
Do While rst.EOF = False
x = x + 1
sum0 = Nz(rst![ReceiveGreyFabricMeter], 0)
Select Case x
Case x = 1 To 12
sum12 = sum12 + sum0
Case x = 13 To 24
sum24 = sum24 + sum0
Case x = 25 To 36
sum36 = sum36 + sum0
Case Else
MsgBox "error"
End Select
rst.MoveNext
Loop
Debug.Print sum12, sum24, sum36
Me.TxtTotal1To12.Value = sum12
Me.TxtTotal13To24.Value = sum24
Me.TxtTotal25To36.Value = sum36


rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
Click to expand...
i understand you have made codes more efficient by putting do while loop and sum12, sum24, sum 36 that has allowed many lines to right off. Brilliant and thanks again.
 
You must log in or register to reply here.

Similar threads

zelarra821
Solved Loop through records in a table that gives an error
Replies
4
Views
145
zelarra821
zelarra821
L
Error 3061 - Too Few Parameters - Expected 1
Replies
7
Views
841
cheekybuddha
cheekybuddha
A
Solved checkbox placed in frame by a query, not responding
Replies
2
Views
425
Pat Hartman
P
F
Solved Add result from one function to another export function
Replies
18
Views
1,078
FoolzRailer
F
Sam Summers
Solved Runtime error 3021 - No current record. only on selecting a new image file
Replies
5
Views
897
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom