Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47

    Unanswered: Check-box update

    I have a form with 2 subforms.

    First subform displays Purchase Orders, Second subform shows Purchase Order Items Information.

    I have a 'completed' check-box on the each Items record which is set to 'Yes' when the Quantity Received = Quantity Ordered.

    I want to set a 'completed' check-box on the Purchase Order record when ALL the Items records have been received.

    Any ideas on how this can be done? I have included a screen shot to help clarify.

  2. #2
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    oops
    Attached Thumbnails Attached Thumbnails dateprob.gif  

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Check-box update

    Originally posted by Anduril
    I have a form with 2 subforms.

    First subform displays Purchase Orders, Second subform shows Purchase Order Items Information.

    I have a 'completed' check-box on the each Items record which is set to 'Yes' when the Quantity Received = Quantity Ordered.

    I want to set a 'completed' check-box on the Purchase Order record when ALL the Items records have been received.

    Any ideas on how this can be done? I have included a screen shot to help clarify.
    Is not easy, this is an idea.
    First include in Table Purchase Order a field status (yes/no) format
    true/False.
    Second make a function, this function must read all the records of the detail order, for the order select in first subform, and check the field
    status completed if all are in true then set to true the field status order
    of the first subform.
    This function must run in the event exit of the second subform, because in that situation you finish with the detail order.
    The problem with this is if the order have many detail records...can take a time for do that.
    Saludos
    Norberto

  4. #4
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47

    Re: Check-box update

    Originally posted by Norberto
    Is not easy, this is an idea.
    First include in Table Purchase Order a field status (yes/no) format
    true/False.
    Second make a function, this function must read all the records of the detail order, for the order select in first subform, and check the field
    status completed if all are in true then set to true the field status order
    of the first subform.
    This function must run in the event exit of the second subform, because in that situation you finish with the detail order.
    The problem with this is if the order have many detail records...can take a time for do that.
    I already have a check box on the Purchase Order.

    I thought that I could possibly do this:

    For each record in the Items subfile I have to check to see if it has been fully received, I actually do this by

    Qty Ordered - Qty Received = Difference, if Difference = 0 then that Item is Completed.

    If I then =Sum(Difference) for the subfile, if =Sum(Difference) = 0 for the entire subform then all Items must of been received. I can then set the Completed check-box value on the Purchase Order = 'Yes'

  5. #5
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Check-box update

    Originally posted by Anduril
    I already have a check box on the Purchase Order.

    I thought that I could possibly do this:

    For each record in the Items subfile I have to check to see if it has been fully received, I actually do this by

    Qty Ordered - Qty Received = Difference, if Difference = 0 then that Item is Completed.

    If I then =Sum(Difference) for the subfile, if =Sum(Difference) = 0 for the entire subform then all Items must of been received. I can then set the Completed check-box value on the Purchase Order = 'Yes'
    I thing that is a good idea and fast.
    Saludos
    Norberto

  6. #6
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47

    Re: Check-box update

    Originally posted by Norberto
    I thing that is a good idea and fast.
    OK, I'm going with that idea, however, I'm getting a #ERROR on the =Sum(Difference)

    No idea why at the moment

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Let me point out a logic issue in your design.

    Each detail record has an order quantity and an order received and an order complete.

    What happens when a vendor partially ships on that quantity of 90 ? When, and if, the complete the order, your original parital quantity and date will be lost. Is that a problem ?

    As for the completion of the PO, on the after update of the checkbox, do a:

    DCount("[CheckBox]","tblPO_Details","[PO_Number]=me.[PO_Number]")

    If it is the same as the count of the records, then the PO is complete and you can set the PO as complete.

    I would not us the order versus received logic because then the checkbox is redundant. With the checkbox, you have the power to declare closed, a detail line that is not closed by virtue of the quantity logic.

  8. #8
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    Originally posted by PracticalProgram
    Let me point out a logic issue in your design.

    Each detail record has an order quantity and an order received and an order complete.

    What happens when a vendor partially ships on that quantity of 90 ? When, and if, the complete the order, your original parital quantity and date will be lost. Is that a problem ?

    As for the completion of the PO, on the after update of the checkbox, do a:

    DCount("[CheckBox]","tblPO_Details","[PO_Number]=me.[PO_Number]")

    If it is the same as the count of the records, then the PO is complete and you can set the PO as complete.

    I would not us the order versus received logic because then the checkbox is redundant. With the checkbox, you have the power to declare closed, a detail line that is not closed by virtue of the quantity logic.
    I like your idea alot but some of this is beyond my understanding at the moment:

    How do I count the records in the subform?

    and on the DCount, doesnt that have to also check if its 'Yes'

    Sorry if this seems very simple to you experts !!

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What is the recordsource of the subform ? That's what you will do a DCount on.

    A checkbox has a value of 0 (No) or -1 (Yes). So the sum of the checkboxes will actually be the opposite of the count.

  10. #10
    Join Date
    Feb 2003
    Location
    San Antonio, TX
    Posts
    31
    "A checkbox has a value of 0 (No) or -1 (Yes). So the sum of the checkboxes will actually be the opposite of the count."

    Or, you might just check for
    RecordCount + Sum(Checkboxes) = 0

    That will also allow you a quick check as to the number of incomplete entries.
    Ralph D. Wilson II
    email: rwilson@thewizardsguild.com
    URL: http://thewizardsguild.com

    "Any sufficiently advanced technology is indistinguishable from magic." A.C. Clark

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I like the simplicity of RDWilson2's test.

    So, Audril, on the After Update of the checkbox on the PO Details subform, you would have:

    If DCount("*","[PO Details]","[PO Number]=" & [me.[PO Number]) + DSum("[Completed]","[PO Details]"]","[PO Number]=" & [me.[PO Number]) = 0 Then

    Set the checkbox for the PO.

  12. #12
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47
    Originally posted by PracticalProgram
    I like the simplicity of RDWilson2's test.

    So, Audril, on the After Update of the checkbox on the PO Details subform, you would have:

    If DCount("*","[PO Details]","[PO Number]=" & [me.[PO Number]) + DSum("[Completed]","[PO Details]"]","[PO Number]=" & [me.[PO Number]) = 0 Then

    Set the checkbox for the PO.
    Hmmm, this would be an expresion in the After Update?

    The Complete check box on the PO is called POComplete, how do I set it to Yes in an expression?

    If DCount("*","[PO Details]","[PO Number]=" & [me.[PO Number]) + DSum("[Completed]","[PO Details]"]","[PO Number]=" & [me.[PO Number]) = 0 Then <what goes here??????????

    I tried this:

    If DCount("*","[PO Details]","[POrdID]=" & [Me![POrdID]) + DSum("[Completed]","[PO Details]","[POrdID]=" & [Me![POrdID]) = 0 Then
    Me![POComplete] = "Yes"

    Sorry very confused now
    Last edited by Anduril; 03-30-04 at 19:20.

  13. #13
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You are almost there . . .

    now, your expression: Me![POComplete] = "Yes"

    says, find the field [POComplete] in the current subform and set it to the string "Yes".

    I think what you really want to say is:

    find the field [POComplete] on another subform whose parent form is the same as the parent of my form and set it to ON, TRUE, or YES.

    To do that you would say:

    Parent![Purchase Orders].form![POComplete]=True

    where [Purchase Orders] is the name of the subform control you are using for the Purchase Order subform control.

    I prefer to use TRUE, or FALSE, not YES, or NO. And, BTW, notice how True does not have quotes around it. It is not the string, "YES", it is just YES, the literal.

  14. #14
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Check-box update

    Originally posted by Norberto
    Is not easy, this is an idea.
    First include in Table Purchase Order a field status (yes/no) format
    true/False.
    Second make a function, this function must read all the records of the detail order, for the order select in first subform, and check the field
    status completed if all are in true then set to true the field status order
    of the first subform.
    This function must run in the event exit of the second subform, because in that situation you finish with the detail order.
    The problem with this is if the order have many detail records...can take a time for do that.
    I send your Old Sample Base with the Update for the problem Order Completed.
    See : the form Goods Received 1, the subform Goods Received Details Subform 1, the table Purchase Orders and the table Purchase Order Details.
    I hope this can help you......
    Attached Files Attached Files
    Saludos
    Norberto

  15. #15
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    47

    Re: Check-box update

    Originally posted by Norberto
    I send your Old Sample Base with the Update for the problem Order Completed.
    See : the form Goods Received 1, the subform Goods Received Details Subform 1, the table Purchase Orders and the table Purchase Order Details.
    I hope this can help you......

    Well I have tried both methods and can't get either to work

    I have attached the most recent Dbase - Forms are Goods Received, Goods Recevied Subform, Goods Received Details Subform.

    This has me completely beaten atm

    Thanks for all the help so far
    Attached Files Attached Files

Posting Permissions

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