Solved runtime error 3021 no current records (1 Viewer)

Ashisht76

Member
Local time
Tomorrow, 04:01
Joined
Jan 31, 2022
Messages
34
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:31
Joined
Feb 19, 2013
Messages
14,398
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
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:31
Joined
Jan 23, 2006
Messages
14,320
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:31
Joined
Oct 29, 2018
Messages
18,990
Try adding an error handler in your code.
 

SHANEMAC51

Active member
Local time
Tomorrow, 01:31
Joined
Jan 28, 2022
Messages
288
strQuery = "[FabricPieceID] between " & pcsid & " and " & pcsid1 & "And" & "
error
Code:
strQuery = "[FabricPieceID] between " & pcsid & " and " & pcsid1 & " And " & "
 

SHANEMAC51

Active member
Local time
Tomorrow, 01:31
Joined
Jan 28, 2022
Messages
288
I tried but I am unable to find solution, see if anyone can help
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!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:31
Joined
May 7, 2009
Messages
16,419
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 28, 2001
Messages
22,820
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.
 

SHANEMAC51

Active member
Local time
Tomorrow, 01:31
Joined
Jan 28, 2022
Messages
288
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 ..
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
 

Ashisht76

Member
Local time
Tomorrow, 04:01
Joined
Jan 31, 2022
Messages
34
I am trying to correction as per the suggestions made and will revert back meant while thanks everyone for replying me.
 

Ashisht76

Member
Local time
Tomorrow, 04:01
Joined
Jan 31, 2022
Messages
34
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
you absolutely nailed it! Its brilliant idea to have "If" statement with EOF to exit the code. Thanks a ton!
 

SHANEMAC51

Active member
Local time
Tomorrow, 01:31
Joined
Jan 28, 2022
Messages
288
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 ..
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
 

Ashisht76

Member
Local time
Tomorrow, 04:01
Joined
Jan 31, 2022
Messages
34
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
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.
 

Users who are viewing this thread

Top Bottom