Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Run-time error 3021. No current record

    Hello,

    Below are 2 VBA statements I placed after upted event of PurQty field in my footer form that will generate sum of the PurQty of respective part number in the same T_PurInvFoot table and at the same time it will sum the SalesQty from other table called T_SalesInvFoot for further calculation purposes.

    The first one which rst1 statement works fine as I am entering PurQty in it for desired any partnumber. It sums the PurQty from table. But when it comes to execute second statement which is rst2, the error apprears

    "Run-time error 3021"
    No current record

    I know that there is really no record for the entered matching partnumber in other table. But I want VBA to return me 0 (zero) if there is no simillar record available in T_SalesInvFoot.

    Set rst1 = CurrentDb.OpenRecordset("Select partnum, SUM(PurQty)AS NEWQTY from T_PurInvFoot Where PartNum='" & (PartNum) & "' GROUP BY partnum")

    Set rst2 = CurrentDb.OpenRecordset("Select partnum, SUM(SalesQty)AS SOLDQTY from T_SalesInvFoot Where PartNum='" & (PartNum) & "' GROUP BY partnum")

    How can I get 0 value if there is no record in other table?

    The above 2nd statement works fine when there is simillar partnum record in T_SalesInvFoot table. But it is not necessary that what I am purchasing currently should have sold earlier. This means there may or may not be simillar record in T_SalesInvFoot table.

    So my question is how can I get 0 value if there is no simillar record available using Select statement?

    Any idea?
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    how can I get 0 value if there is no simillar record available using Select statement?
    I'd just trap the error.

    You could also verify there are records to gather before executing the rst2 statement.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thats what I want to know....

    I tried with If err.number = 3021 then....bla bla...but no use.
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can you be more clear please.

    Bla bla's don't help me understand your situation, nor does "no use".

    If error trapping didn't help (I find that almost impossible to believe) then try the other idea I suggested.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Bla bla stands for etc....and no use mean it did not help out.

    Anyhow, as I explained that it is not necessary that similar records are available in table T_SalesInvFoot.
    Example if I make in entry for part A205241-J which is first time entry and client never sold it before. Meaning there will not be any record in T_SalesInvFoot table.

    And when user entering this part in T_PurInvFoot means he is purchasing and this will be sum thru the rst1 statement.

    I hope I clear the question.
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change your SQL:
    Code:
    Set rst2 = CurrentDb.OpenRecordset("Select SUM(SalesQty) AS SOLDQTY from T_SalesInvFoot Where PartNum='" & (PartNum) & "'")
    You don't need to return the partnum (because you already know it!)

    This will return a 0 count instead of a NULL recordset.
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks George,

    I implemented your idea on all rest 3 select statements. 2nd selected (as you said) worked but the same error apprears when executes next Select statement.

    All my VBA code is as follows:

    Set rst1 = CurrentDb.OpenRecordset("Select partnum, SUM(PurQty)AS NEWQTY from T_PurInvFoot Where PartNum='" & (PartNum) & "' GROUP BY partnum")

    Set rst2 = CurrentDb.OpenRecordset("Select SUM(SalesQty)AS SOLDQTY from T_SalesInvFoot Where PartNum='" & (PartNum) & "'")

    Set rst3 = CurrentDb.OpenRecordset("Select SUM(PurRetQty)AS PRETQTY from T_PurInvFoot Where PartNum='" & (PartNum) & "'")

    Set rst4 = CurrentDb.OpenRecordset("Select SUM(SalesRetQty)AS SRETQTY from T_SalesInvFoot Where PartNum='" & (PartNum) & "'")

    NEWQTY = rst1!NEWQTY -----this is returning the entered value (example qty 10)
    SOLDQTY = rst2!SOLDQTY ---this is producing NULL value while I need it to return 0.
    PRETQTY = rst3!PRETQTY ----this is producting 0 value
    SRETQTY = rst4!SRETQTY ---- this alos prouceing Null value while I need it to return 0.

    Finally I am storing stock like below:
    NEWQTY = (rst1!NEWQTY + rst4!SRETQTY) - (rst2!SOLDQTY + rst3!PRETQTY)

    Value of this NEWQTY will be offcourse stored in table in proper way further

    Please extend your help.
    With kind regards,
    Ashfaque

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I confused Sum with Count, apologies (Count will return 0, where Sum will return NULL).

    Expanding on the changes above, use the Nz() function
    Code:
    SOLDQTY = Nz(rst2!SOLDQTY, 0)
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    To avoide length, I have shorten the codelines as below

    Set rst1 = CurrentDb.OpenRecordset("Select partnum, SUM(PurQty)AS NEWQTY, SUM(PurRetQty)AS PRETQTY from T_PurInvFoot Where PartNum='" & (PartNum) & "' GROUP BY partnum")

    Set rst2 = CurrentDb.OpenRecordset("Select SUM(SalesQty)AS SOLDQTY, SUM(SalesRetQty)AS SRETQTY from T_SalesInvFoot Where PartNum='" & (PartNum) & "'")

    NEWQTY = rst1!NEWQTY -----this is returning the entered value (example qty 10)

    PRETQTY = rst1!PRETQTY -----this is producing NULL value while I need it to return 0.

    SOLDQTY = rst2!SOLDQTY ----- this is correct as it is returning 0 value

    SRETQTY = rst2!SRETQTY ----- this also producting Null value while I need it to return 0.

    NEWQTY = (rst1!NEWQTY + rst2!SRETQTY) - (rst2!SOLDQTY + rst1!PRETQTY)

    As a result the NEWQTY is producing NULL.

    Please advise how can I get it return as 0.
    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks to all gents.

    I finally got it done as below

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset

    Me.Recalc

    Dim NEWQTY: NEWQTY = 0
    Dim SOLDQTY: SOLDQTY = 0
    Dim PRETQTY: PRETQTY = 0
    Dim SRETQTY: SRETQTY = 0
    Dim X

    Set rst1 = CurrentDb.OpenRecordset("Select partnum, SUM(PurQty)AS NEWQTY, SUM(PurRetQty)AS PRETQTY from T_PurInvFoot Where PartNum='" & (PartNum) & "' GROUP BY partnum")

    Set rst2 = CurrentDb.OpenRecordset("Select partnum, SUM(SalesQty)AS SOLDQTY, SUM(SalesRetQty)AS SRETQTY from T_SalesInvFoot Where PartNum='" & (PartNum) & "' GROUP BY partnum")


    NEWQTY = rst1!NEWQTY
    PRETQTY = rst1!PRETQTY

    If rst2.RecordCount < 1 Then
    SOLDQTY = 0
    SRETQTY = 0
    Else
    SOLDQTY = rst2!SOLDQTY
    SRETQTY = rst2!SRETQTY
    End If

    For X = 1 To rst1.RecordCount
    Set rst = CurrentDb.OpenRecordset("Select * From Product_master where PartNum='" & (rst1!PartNum) & "'")
    If rst.RecordCount >= 1 Then
    NEWQTY = (rst1!NEWQTY + SRETQTY) - (SOLDQTY + PRETQTY)
    rst.Edit
    rst!Stock = NEWQTY
    NEWQTY = 0: SOLDQTY = 0: PRETQTY = 0: SRETQTY = 0

    rst.Update
    End If
    rst.Close
    rst1.MoveNext
    Next
    'End If
    rst1.Close
    With kind regards,
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •