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

    Unanswered: Code help to get last sold price.

    Hi,

    My sales form contains a sub-form to enter the material. Both forms are based on One-To-Many relationship. They are called F_SalesInvHead & F_SalesInvFoot

    The header form holds many fields out of which one is called CustomerCode

    The footer form also holds many field out of which one is called CboBarcode (a combo) thru which user selects barcode number and gets data into other fields like Item Description, Stock, SalesPrice bla bla.. The SalesQty is entered by user.

    Now my user would like to display somewhere on sub form what was the LAST price (last record) for that particular item he sold to current customer. This he need to just have an idea. If there is no record of sale before for this customer for that item, the unbound text box will display (Zero) 0 or Null.

    To do this, I placed one unbound text box (TxtDispLastSoldPrice ) on sub-form and wrote below code on AfterUpdate of CboBarcode. So once the CboBarcode is updated, the placed textbox to display last sold price of that particular item to that particular customer. I know I did not wrote to get last price but want to know how to it.

    This is the code that produces error.

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

    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, SalesPrice From T_SalesInvFoot " & _
    "where CustomerCode= '" & Forms!F_SalesInvHead!CustomerCode & "'")

    Me.TxtDispLastSoldPrice = rst2!SalesPrice
    rst2.Close
    Set rst2 = Nothing

    Run-time error "3061"

    To few parameters. Expected 1.


    I want to know how to get last record's sales price.

    I know there is DLast function but I need to use it thru select statement.

    Can somebody help me please?
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    Me.TxtDispLastSoldPrice = rst2!SalesPrice
    think this should be :

    Me.TxtDispLastSoldPrice.TEXT = rst2!SalesPrice

    To go tolast record

    Code:
    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, SalesPrice From T_SalesInvFoot " & _
    "where CustomerCode= '" & Forms!F_SalesInvHead!CustomerCode & "'")
    
    if rst2.recodrcount >0 then rst2.movelast
    
    Me.TxtDispLastSoldPrice = rst2!SalesPrice
    rst2.Close
    Set rst2 = Nothing
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't think that's it tbh.

    Is T_SalesInvFoot a query that has a parameter defined?
    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

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

    T_SalesInvFoot is a table

    The code should be triggered while control is on sub-form which will save data into the same table (T_SalesInvFoot). So once the cbo is updated, it shoud bring last sold price of that item.
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I think it should be something like:

    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, BarcodeNumber, SalesPrice From T_SalesInvFoot " & _
    "where BarcodeNumber= '" & CboBarcode & "'" And CustomerCode = Forms!F_SalesInvHead!CustomerCode)

    Bcz I should comapre the desired result putting Cbobarcode and customer code criteria to get last sold price.

    But it also produces runtime error 13 - Type mismatch.
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Code:
    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, BarcodeNumber, SalesPrice From T_SalesInvFoot " & _
    "where BarcodeNumber= '" & CboBarcode & "'" And CustomerCode = Forms!F_SalesInvHead!CustomerCode)
    This has a syntax error...

    It should be more like

    Code:
    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, BarcodeNumber, SalesPrice From T_SalesInvFoot " & _
    "where BarcodeNumber= '" & CboBarcode & "' And CustomerCode = " & Forms!F_SalesInvHead!CustomerCode)
    The type mismatch is hard to determine though since there's no information on what data types you have.
    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

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

    The criteria is ok but but still producing same error 3061 - 'Too few parameters. Expected 1.
    With kind regards,
    Ashfaque

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you tried proving this query is right...
    in say the query browser.

    what does the intellisense help tell you are the parameters for the openrecordset keyword?

    what does the help system itself tell you are the parameters?

    personally I find your naming convention confusing.. to me something that ends in code infers its text/string so I'd expect the value to be encased in single or double quote marks, id expect a number to end in id or no.

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

    The barcode is of Text type and customercode is number.

    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, BarcodeNumber, SalesPrice From T_SalesInvFoot " & _
    "where BarcodeNumber= '" & CboBarcode & "' And CustomerCode = " & Forms!F_SalesInvHead!CustomerCode)

    When excecuted above code pressing F8 function key, it read customercode and Barcode properly bcz I checked keeping mouse on it to see the generated value.

    But still produces said error. I don't know why.
    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you looked at the help system ti find out what parameters are required?

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    OpenRecordset only needs the recordset to open...

    Can you upload the database?
    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

  12. #12
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Hi

    Q: what value do you have in the combobox (Int , string )

    Can you otherwise set a break point on this line (F9)
    Quote Originally Posted by Ashfaque
    Set rst2 = CurrentDb.OpenRecordset("Select CustomerCode, BarcodeNumber, SalesPrice From T_SalesInvFoot " & _
    "where BarcodeNumber= '" & CboBarcode & "' And CustomerCode = " & Forms!F_SalesInvHead!CustomerCode)
    And paste the "query" in your debug window (ctrl+G)
    put a questionmark in front it and hit enter-key
    And see what it gives you as result
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    [QUOTE=StarTrekker]OpenRecordset only needs the recordset to open.../QUOTE]

    I remain to be convinced..
    if its a table recordset then yes that works, no further parameters are required

    but not if you are using a SQL query. On my copy of Access the help file makes that very very clear, hence my previous comment.

    post #7 states:The criteria is ok but but still producing same error 3061 - 'Too few parameters. Expected 1.
    so my guess is that the version the OP is using is similar to the version of Access I use

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

    Here I attached my db. I tried best to reduce its size.

    Please note when ADD new invoice, user password is 123.

    Please check the last 2-3 lines of code (Where I put comment “'CHECK MY CODE HERE PLEASE”) in AfterUpdate event of CboBarcode combo on sales invoice footer form. The result to be displayed in RED color unbound text box I placed on same sub form.

    I need previous sold price (if any) to the current customer for the entered barcode item.

    Please select barcode item from Combo in footer form.

    You may try selecting “ELE.CABLE AME.TYPE” or “ZF KIT-K120” both are entered as barcode in product_master table with their current stock.
    Attached Files Attached Files
    With kind regards,
    Ashfaque

  15. #15
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    In the attached db in post # 14, it produced no error but also result any value for sales price.
    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
  •