Results 1 to 8 of 8
  1. #1
    spitere
    Guest

    Could someone take a look at this?

    Private Sub Quantity_received_Click()
    Dim deliveredDate As Date
    Dim deliveredValue As Integer

    deliveredDate = Forms!Payment![Materials description].Column(1)
    MsgBox "European Date " & deliveredDate
    deliveredDate = Format(deliveredDate, "MM/DD/YYYY")
    MsgBox "American Date " & deliveredDate

    deliveredValue = DLookup("[Quanities received]", "[Delivery Correct Items]", "[Purchase order number] = '" & Forms![Payment]![Purchase order number] & "'" & "AND [Material description] = '" & Forms![Payment]![Materials description] & "'" & " AND [Date Received] = #" & deliveredDate & "#")
    MsgBox deliveredValue

    [Quantity received].SetFocus
    [Quantity received].Text = deliveredValue
    Form.refresh

    End Sub

    I just don't understand why the same quantity is appearing each time. This code seems to only return the quantity value for the first record.
    I'm using Eurpoean time but I was told that Access only works internally with US dating so maybe I'm wrong to convert.
    does anybody have any ideas?
    See you around
    Rob <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Could someone take a look at this?

    Can you make a query that returns the results you expect?

  3. #3
    spitere
    Guest

    Re: Could someone take a look at this?

    I'll explain a bit more - as it stands a Purchase Order is made up of a number of items - each item unique.
    Unfortunately when the shipment comes in there may be a shortage/oversupply of what we asked for.
    It is this delivery that has to be paid for, since the parts of the original order does not get delivered does not get paid for......all that's the easy bit.

    So when we go to the Payment form, the only thing that can differenciate between subsequent orders would be Item description, date recieved and purchase order number.

    eg car, 12-12-1212, PO-12345
    since part of the same order can come in later this can be the next record
    car, 13-13-1313, PO-12345

    So, to choose the right order to pay for I use this to populate a combo box

    SELECT [Delivery correct Items].[Material description], [Delivery correct Items].[Date Received], [Delivery correct Items].[Quanities received]
    FROM [Delivery correct Items]
    WHERE ((([Delivery correct Items].[Purchase order number])=[Forms]![Payment]![Purchase order number]) AND (([Delivery correct Items].Archived)=No));

    This returns everything hunky dory. The user can see which distinct delivery ther're paying for.

    What seems to be happening is that I value I select in the combo box isn't working properly.
    If I have all distinct values - it works perfectly.
    However, if I have the situation with two of a kind, part of the same order, even though with different date - only the first record is being read later in the form.

    I'm totally confused about this....It should work!?!

    Thanks for any and all heeellpppppp.
    Rob

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Could someone take a look at this?

    I still think you should make a query, and see if it returns the results you expect.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Could someone take a look at this?

    Hi Rob,
    I may be missing something as I confess I've just skimmed through your code (which looks OK at first glance to me) but haven't you already got the value you want in your combobox? It contains Item description, Date received and Quantity. Could you not just use Forms!Payment![Materials Description].Column(2) to retrieve the quantity?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    spitere
    Guest

    Re: Could someone take a look at this?

    Yeah, that's actually what I was trying to do originally.
    Here's the code that gets called when the user clicks on the
    quantity control:
    (the test cases are temporary)

    Private Sub Quantity_received_Click()
    Dim deliveredDate As Date
    Dim deliveredValue As Integer
    Dim Check As Integer

    [Materials description].SetFocus

    deliveredDate = Forms!Payment![Materials description].Column(1)

    Check = Forms!Payment![Materials description].Column(2)
    MsgBox "Is it right?" & Check


    MsgBox "European Date " & deliveredDate
    deliveredDate = Format(deliveredDate, "MM/DD/YYYY")
    MsgBox "American Date " & deliveredDate

    deliveredValue = DLookup("[Quanities received]", "[Delivery Correct Items]", "[Purchase order number] = '" & Forms![Payment]![Purchase order number] & "'" & "AND [Material description] = '" & Forms![Payment]![Materials description] & "'" & " AND [Date Received] = #" & deliveredDate & "#")
    MsgBox deliveredValue

    Check = Forms!Payment![Materials description].Column(2)
    MsgBox "Is it right?" & Check

    [Quantity received].SetFocus
    [Quantity received].Text = deliveredValue
    Form.refresh

    End Sub



    Using this example -> my combo box ends up like this:
    (des) (date) (quantity)

    car 12-12-1212 6
    car 13-13-1313 4

    Later in the form, when I've already selected the 2nd option on the combo box - the result of

    deliveredDate = Forms!Payment![Materials description].Column(1)

    Check = Forms!Payment![Materials description].Column(2)

    are the values for the 1st record, so date would be 12-12-1212 and quantity 6 even though I picked the 2nd option
    and the values should be 13-13-1331 and 4I don't where it's going wrong )-:

    ?!? Thanks for any help guys.
    Rob.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Could someone take a look at this?

    Rob,
    Is your combobox's bound column the item description? If you have the bound column set to a non-unique one (eg car appears more than once) then you'll find that the combobox seems to update itself to the first instance of that item that it finds. Try changing it to a list box and see if that cures the problem.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    spitere
    Guest

    Re: Could someone take a look at this?

    Wow, that was it - thanks everyone(Especially Rory), it turned out to be a one line fixer.
    I had the bound column to the description - car eg,
    so putting in the autonumber field and binding that to it instead did the trick.
    Thanks again-really appreciated everyones' help (o:
    Rob

Posting Permissions

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