Results 1 to 4 of 4

Thread: Help please

  1. #1
    Join Date
    Sep 2003
    Posts
    18

    Unanswered: Creating Database Logic

    I have one main form which has a subform attached.

    The main form (Issues) has a status field (open, closed etc...).
    The subform (Actions) can have more than one actions per issue.
    This subform also has a status field for each actions.

    What I need to do is build some logic which will only allow the main form status to be closed if all the actions statuses have areclosed.

    Any help would be appreciated - I don't even know where to start on this.
    Last edited by mark_hall; 06-08-04 at 08:35. Reason: Title not very descriptive

  2. #2
    Join Date
    May 2004
    Posts
    17
    Mark,

    You need to open the recordset for the subform and loop through each record seeing if it is closed or not. If all are closed, then allow the status field on the main form to be set else cancel. Something like below may work.

    Code:
    Private Sub Status_BeforeUpdate(Cancel As Boolean)
        Dim rec As Recordset
        Dim CanClose As Boolean
        
        CanClose = True
        
        Set rec = Forms.Issues.Actions.Form.RecordsetClone
        Do While Not rec.EOF
            If rec("Status") <> "Closed" Then
                CanClose = False
                Exit Loop
            End If
            rec.MoveNext
        Loop
        rec.Close
        
        If CanClose <> True Then
            Cancel = True
            MsgBox "Please close all actions and try again"
        End If
    End Sub
    If you have any problems, just post again to this thread.

    Regards,
    Stephen.

  3. #3
    Join Date
    Sep 2003
    Posts
    18
    The Exit Loop is not allowed when I type this in ie. was expecting something else.

    Don't want to be a pain but could you breakdown the code with comments. I'm a newbie and would appreciate the runthrough of the code. Hopefully over time I wont need to keep pestering people on this forum!!!

  4. #4
    Join Date
    May 2004
    Posts
    17
    Mark,

    Oops, my mistake. That should be "Exit Do" not "Exit Loop"! The revised code (with comments this time) is below:

    Code:
    Private Sub Status_BeforeUpdate(Cancel As Boolean)
        Dim rec As Recordset
        Dim CanClose As Boolean
        
        'Lets assume at the start that user can close the issue
        CanClose = True
        
        'Make a copy of the recordset behind the actions subform
        Set rec = Forms.Issues.Actions.Form.RecordsetClone
        
        'Loop through the recordset and check if any actions are not
        'closed...
        Do While Not rec.EOF
            If rec("Status") <> "Closed" Then
                'One of the actions are not closed, therefore change
                'our CanClose variable to no and exit the loop (we can
                'exit without checking the rest as we only need one action
                'to be open to disallow closing the issue).
                CanClose = False
                Exit Do
            End If
            
            'Move to the next record in the recordset.  Very important
            'otherwise we will have an endless loop!
            rec.MoveNext
        Loop
        'Close the recordset to free up memory
        rec.Close
        
        'When we get to this point, we have either gone through every action
        'and all were closed (in which case our CanClose variable will still
        'equal "True"), or an open item was found and the CanClose variable
        'will now equal "False".  Therefore, ...
        
        If CanClose <> True Then
            'Set cancel = true to prevent access from accepting the status
            'change.
            Cancel = True
            
            'inform the user why the change has been cancelled
            MsgBox "Please close all actions and try again"
        End If
    End Sub
    Just set the "BeforeUpdate" event of the status combo box to "[Event Procedure]" and copy the above code into the procedure. You'll obviously need to change names, and I have assumed that you store the status as text rather than a Yes/No field.

    If you have any problems getting it working. Upload a copy of the database (only the form and subform and any tables necessary to make it work) and i'll adapt the code for you.

    Hope this helps,
    Stephen.

Posting Permissions

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