Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Decreasing Sum Need In Subform

    Hi,

    I have a main form with 2 subforms in it.

    The first sub form is based on query which produces calculated results based on From-To date selected on main form.

    The second sub form is based on table with one-to-many relationship with main form.

    For example if selects date range 01-01-09 to 30-04-09, the first form will produce the InvNum, QtyofCartons etc. which shows in each invoice total sold qty of cartons like below. There may be number of products in a single invoice but we are concern only with number of qty.

    InvNum QtyofCartons Date
    2514 10 12-01-09
    2854 50 19-02-09
    3029 15 09-03-09

    In footer of this sub form sum of qty will 75

    In the second sub form which is based on table, user enters gift items (selected thru a combo. The field displayed there in are, GiftName, Brand, CartonRange etc.

    The combo of gift displays few fields out of which one field displays CartonRange. It means if a customer take 60 cartons and paid within 90 days, he will be entitled to receive gift according to the table below.

    GiftName CartonRange
    Sterio(small) 5
    Mixer(medium) 10
    Sterio(big) 15
    Nokia Mobile 25
    TV Set 30
    Washing Machine 45

    So if the said customer purchased 60 cartons and paid in time then he will receive 1 TV set and so on. But incase customer don’t want TV Set and instead he want 1 Nokia Mobile phone and one small stereo etc. WE NEED TO GIVE HIM THESE 2 ITEMS EVEN THEY ARE NOT UPTO OUR POLICY.

    I did almost all work but now my difficulty is I need to calculate decreasing Sum of cartons in selected date range and display into a text box placed in second from with each record. This way I can see how much cartons are left in text box so accordingly I can enter the gift.

    Example.

    If customer purchases 30 cartons (generated by first sub form) and user started entering gift in second sub form as follows.

    GiftName GiftStock CartonRange InvNum CartonQty IssuingQty
    Nokia Mobile 41 25 2854 60 1
    Stereo (big) 3 15 2514 35 1
    TV Set 7 30 2854, 3029 30 1
    Stereo (small) 11 05 3029 05 1
    Here if user enters next gift should be allowed as the gifts are already issued and purchased carton qty is become (CartonQty) became ZERO.

    I tried the following way in my event procedure that I place behind combo of second sub form from where I select gift items to issue.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim TotCarton

    Set rst = CurrentDb.OpenRecordset("Select InvNum, Invdate, sum(SalesQty) As TotCarton " & _
    "from T_SalesInvFoot Where Invdate>= " & Forms!F_Gift_C!TxtFrom And InvDate <= Forms!F_Gift_C!TxtTo)

    TotCarton = rst!TotCarton

    Lines ….etc..

    The code line Set rst ….produces error….Type Mismatch...

    My questions..

    1. How can I calculate decreasing Sum of cartons in selected date range and display into a text box placed in second from (Green text box) with each record. Or do I need a permanant field to place in table?

    2. Is this right way? If not, what could be other solution.

    Please extend your help. ( I have attached pic of my form for ref. There might be diff field names..I just gave example field names...)
    Attached Thumbnails Attached Thumbnails Sample.JPG  
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Set rst = CurrentDb.OpenRecordset("Select InvNum, Invdate, sum(SalesQty) As TotCarton " & _
    "from T_SalesInvFoot Where Invdate>= " & Forms!F_Gift_C!TxtFrom And InvDate <= Forms!F_Gift_C!TxtTo)
    This is missing date delimiters...
    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
    Sep 2002
    Location
    South Wales
    Posts
    580
    Yes - the SELECT statement is mal-formed.

    Try this and post results:

    "Select InvNum, Invdate, sum(SalesQty) As TotCarton from T_SalesInvFoot Where Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And InvDate <= #" & Forms!F_Gift_C!TxtTo & "#;"
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks guys,

    Since it is necessary to have date from both header and footer tbl, I did a little more addition to the code...

    Set rst = CurrentDb.OpenRecordset("Select T_SalesInvHead.AccNewCode, T_SalesInvHead.InvNum, Sum(T_SalesInvFoot.SalesQty) As TotCarton " & _
    "T_SalesInvHead.SalesInvNum, T_SalesInvHead.InvDate, T_SalesInvFoot.InvNum " & _
    "FROM T_SalesInvHead LEFT JOIN T_SalesInvFoot ON T_SalesInvHead.InvNum = T_SalesInvFoot.InvNum " & _
    "Where T_SalesInvHead.Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And T_SalesInvHead.InvDate <= #" & Forms!F_Gift_C!TxtTo & "# " & _
    "GROUP BY Forms!F_Gift_C!AccNewCode;")

    but it produces Run-time error 3141
    The select statement include reserved word or an orgument name that is misspelled or missing, or the punctuation is incorrect.....
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Try this - note GROUP BY Clause has form!etc within quotes on your version...

    Set rst = CurrentDb.OpenRecordset("Select T_SalesInvHead.AccNewCode, T_SalesInvHead.InvNum, Sum(T_SalesInvFoot.SalesQty) As TotCarton " & _
    "T_SalesInvHead.SalesInvNum, T_SalesInvHead.InvDate, T_SalesInvFoot.InvNum " & _
    "FROM T_SalesInvHead LEFT JOIN T_SalesInvFoot ON T_SalesInvHead.InvNum = T_SalesInvFoot.InvNum " & _
    "Where T_SalesInvHead.Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And T_SalesInvHead.InvDate <= #" & Forms!F_Gift_C!TxtTo & "# " & _
    "GROUP BY " & Forms!F_Gift_C!AccNewCode &" ;")
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks again,

    But this modification also produces same error....
    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Er - There was also a comma missing after TotCarton

    Set rst = CurrentDb.OpenRecordset("Select T_SalesInvHead.AccNewCode, T_SalesInvHead.InvNum, Sum(T_SalesInvFoot.SalesQty) As TotCarton, " & _
    "T_SalesInvHead.SalesInvNum, T_SalesInvHead.InvDate, T_SalesInvFoot.InvNum " & _
    "FROM T_SalesInvHead LEFT JOIN T_SalesInvFoot ON T_SalesInvHead.InvNum = T_SalesInvFoot.InvNum " & _
    "Where T_SalesInvHead.Invdate>= #" & Forms!F_Gift_C!TxtFrom & "# And T_SalesInvHead.InvDate <= #" & Forms!F_Gift_C!TxtTo & "# " & _
    "GROUP BY " & Forms!F_Gift_C!AccNewCode &" ;")
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks again.

    I have placed , and then tried now it says.....You tried to execute a query that does not include the specified expression 'InvNum' as part of an aggregate function.

    What I am trying to do is like this....I want to have sum of SalesQty from table called T_SalesInvFoot. This footer table have a foriegn key InvNum which have relation with its header table called T_SalesInvHead. (One-To-Many)

    I am displaing From - To date and AccNewCode on header from based on which my above recordset code should generate sum of SalesQty (carton qty) for the selected period. Off course this will be triggered once the combo box is updated in footer form.

    One it is done then I could go further....
    With kind regards,
    Ashfaque

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    OK - write the query in design view based on known parameters rather than referencing forms.

    Once the query returns the data you want, post the SQL>
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks garethdart.

    I have attached my db with less records.

    Please enter date range 01-01-09 to 30-04-09 and select the customer Al Masa2.

    You will see the customer has taken 125 cartons within above periods.

    My conditions was if he pays within 90 days (from date of invoice) it will display in right top sub form.

    The bottom form to issue gift to the customer based on the demand.

    Example if customer purchased 125 carton in above period then he will be entitled to get the gift according to the tbl T_GiftMaster. But he may ask for diffrent gift items too.

    So for example we can give him codless phone for which he should take min 15 cartons and so. on...

    Now what I want in one text box (green color) of gift sub form that once Codless phone is issued to him means it should store 125 - 15 = 110 bcz codless phone will be issued on 15 carton purchase. So when user will enter another gift, the available purchased carton sum shoud be 110 likewize it should reduce....remaining control I can handle in the code....

    Hope I am clear at my end....
    Attached Files Attached Files
    With kind regards,
    Ashfaque

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Any news ?
    With kind regards,
    Ashfaque

  12. #12
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    I won't be able to have a look at this personally for about 48hrs -
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  13. #13
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397



    Hopefully 48 hrs finished....
    Last edited by Ashfaque; 05-17-09 at 08:19.
    With kind regards,
    Ashfaque

  14. #14
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If you are trying to check a customers entitlement, you will have to do a real-time calculation during the before_update event of the gift assignment subform.

    Maybe have an UNBOUND text box in the subform to DISPLAY their entitlement.

    And also calculate the remaining entitlement during the after_update event of the gift assignment.

    The bound / formula text box you have used will utlimately not work for these type of inline calculations.

    I am too busy to give a more detailed answer right away but hope this will assist - if you need a more immediate answer, I am afraid you will have to rely on other members.
    Last edited by garethdart; 05-17-09 at 11:05.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

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

    Any one else there who can help ?

    Thanks,
    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
  •