Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reviewing records in a subform (Access97)

    I have a form based on a table that has purchase order information in it. In a subform, I have the items that were purchased on that purchase order and a received date field. When each item is received I enter the date. When a date for all items has been entered, I want the status of the PO to be closed. If any of the items have not been received, I want the PO status to be open. How do I examine the received date field in the subform and if all have dates, mark the status field in the PO info table as closed.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reviewing records in a subform (Access97)

    If the parent form is based on the PO table, then put the code to set the status in the parent form. You could use the OnExit event of the subform or the OnCurrent event of the parent form (among others) to trigger a test for the count of items for that PO where the received date is null. You would only trigger the test if the current status was Open. If the count = 0, then set the status to Closed.

    Just keep in mind that, whatever method you use to set the status, you have to have some way to avoid setting it incorrectly when a user enters a date by mistake.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reviewing records in a subform (Access97)

    Thanks for your help. I'm not sure what the code would be. Can you help?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reviewing records in a subform (Access97)

    You could put this in the on exit of the subform control:
    <pre>Private Sub SubformItems_Exit(Cancel As Integer)
    Dim rst As Recordset
    Dim x As Integer
    Set rst = Me.SubformItems.Form.RecordsetClone
    rst.MoveFirst
    x = 0
    Do While Not rst.EOF
    If IsNull(rst!DateField) Then
    x = x + 1
    Enf If
    rst.MoveNext
    Loop
    Set rst = Nothing
    If x = 0 Then
    Me.POStatus = "Closed"
    else
    Me.POStatus = "Open"
    End If
    End Sub
    </pre>

    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reviewing records in a subform (Access97)

    Still another method, and a faster one, is something like this:

    Dim rst As DAO.Recordset

    Set rst = Me!SubformItems.form.RecordsetClone

    rst.Filter = "[DateField] Is Null"

    If rst.RecordCount =0 Then
    [tabl]Me.POStatus = "Closed"
    Else
    Me.POStatus = "Open"
    End If
    Set rst = Nothing

    You have to be aware though, that you will get a false "Closed" if there are no subform records.
    Charlotte

Posting Permissions

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