Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: VBA problem with Single editable/non-editable form and subforms

    Afternoon chaperoonies!

    Can someone tell me if I am on the right lines in respect of VBA association when working with a Parent form and associated sub-forms as a single editable and non-editable object? Trial and error has so far led me to the following:

    1. Field backcolours

    sub-form field colours are controlled within the sub-form due to the parent form being unable to reference the sub-form fieldnames when there is no sub-form record to display. The sub-form is not displayed when non-editable therefore cannot be referenced.

    These field back colours change as expected except where the sub-form fields were not displayed previously- under these circumstances the sub-form field colours do not seem to want to repaint unless I click on the sub-form.

    Is associating the VBA with the sub-form for backcolour control correct?
    Why won’t the forms repaint?

    Code:
    Private Sub Form_Current()
    
    'Set field colours depending on FormState field on Parent form.
    
    If [Forms]![frmPatient]![FormState] = "Read only" Then
        Me.[DatabaseEmployee#].BackColor = RGB(236, 233, 216)
        Me.[CareManagerStartDate].BackColor = RGB(236, 233, 216)
        Me.[CareManagerEndDate].BackColor = RGB(236, 233, 216)
    ElseIf [Forms]![frmPatient]![FormState] = "Edit" Or [Forms]![frmPatient]![FormState] = "Add" Then
        Me.[DatabaseEmployee#].BackColor = RGB(255, 255, 128)
        Me.[CareManagerStartDate].BackColor = RGB(255, 255, 128)
        Me.[CareManagerEndDate].BackColor = RGB(255, 255, 255)
        
    End If
    
    Me.Repaint
    
    End Sub
    2. AllowEdits and AllowAdditions

    I’ve tried controlling the AllowEdits and AllowAdditions from both aspects independently; the VBA on the Parent form and then from VBA within the sub-forms.

    a) Controlling from the Parent form works perfectly (except for AllowDataEntry giving a separate issue which I’m yet to look into).

    Is this the correct way to do it?

    b) Planting the code on the sub-form works until there is no existing record on the sub-form to be displayed from the non-editable form status- the subform fails to display at all.

    Could the failure of the sub-form to display be as a result of a failed repaint?



    Code:
    Public Sub modfrmPatientProperties()
    
    '*****************************************************************
    'If record exists then set form header depending on PatientStatus.
    '*****************************************************************
    
    If [Forms]![frmPatient]![PatientStatus] = "Archived- Died" Then
        [Forms]![frmPatient].FormHeader.BackColor = RGB(226, 52, 83)
    ElseIf [Forms]![frmPatient]![PatientStatus] = "Archived- moved from area" _
        Or [Forms]![frmPatient]![PatientStatus] = "Archived- not eligible" Or _
        [Forms]![frmPatient]![PatientStatus] = "Archived- returned home" Then
        [Forms]![frmPatient].FormHeader.BackColor = RGB(255, 165, 74)
    ElseIf [Forms]![frmPatient]![PatientStatus] = "Suspended or awaiting placement" Then
        [Forms]![frmPatient].FormHeader.BackColor = RGB(255, 255, 153)
    ElseIf [Forms]![frmPatient]![PatientStatus] = "Current" Then
        [Forms]![frmPatient].FormHeader.BackColor = RGB(91, 170, 103)
    ElseIf [Forms]![frmPatient]![PatientStatus] = "" Then
        [Forms]![frmPatient].FormHeader.BackColor = vbWhite
    End If
            
    '***********************************************
    'Set form properties for edit, add.
    '***********************************************
    
    If [Forms]![frmPatient]![FormState] = "Edit" Or _
    [Forms]![frmPatient].FormState = "Add" Then
    
    'Disable buttons
    
        [Forms]![frmPatient]![btnCloseForm].SetFocus
        
        [Forms]![frmPatient]![btnPersonNameSearch].Enabled = False
        [Forms]![frmPatient]![btnRemoveFilter].Enabled = False
        [Forms]![frmPatient]![btnFindRecord].Enabled = False
        [Forms]![frmPatient]![btnEditRecord].Enabled = False
        [Forms]![frmPatient]![btnAddRecord].Enabled = False
    
    'Enable editing of form and sub-forms.
    
        [Forms]![frmPatient].AllowEdits = True
        [Forms]![frmPatient].AllowAdditions = True
        [Forms]![frmPatient].AllowDeletions = True
        
        [Forms]![frmPatient]![sfrmDOD].[Form].AllowEdits = True
        [Forms]![frmPatient]![sfrmDOD].[Form].AllowAdditions = True
        [Forms]![frmPatient]![sfrmDOD].[Form].AllowDeletions = True
           
        [Forms]![frmPatient]![sfrmCareManager].[Form].AllowEdits = True
        [Forms]![frmPatient]![sfrmCareManager].[Form].AllowAdditions = True
        [Forms]![frmPatient]![sfrmCareManager].[Form].AllowDeletions = True
           
        [Forms]![frmPatient]![sfrmNote].[Form].AllowEdits = True
        [Forms]![frmPatient]![sfrmNote].[Form].AllowAdditions = True
        [Forms]![frmPatient]![sfrmNote].[Form].AllowDeletions = True
                 
    'Set field backcolours.
        
        [Forms]![frmPatient]![PatientStatus].BackColor = RGB(255, 255, 128)
        [Forms]![frmPatient]![PatientNHS#].BackColor = RGB(255, 255, 255)
        [Forms]![frmPatient]![PatientTitle].BackColor = RGB(255, 255, 255)
        [Forms]![frmPatient]![PatientForename].BackColor = RGB(255, 255, 128)
        [Forms]![frmPatient]![PatientMiddleName(s)].BackColor = RGB(255, 255, 255)
        [Forms]![frmPatient]![PatientSurname].BackColor = RGB(255, 255, 128)
        [Forms]![frmPatient]![PatientDOB].BackColor = RGB(255, 255, 128)
        [Forms]![frmPatient]![PatientGender].BackColor = RGB(255, 255, 255)
        [Forms]![frmPatient]![DatabaseGeneralPractitioner#].BackColor = RGB(255, 255, 128)
        [Forms]![frmPatient]![DatabaseGeneralPractice#].BackColor = RGB(255, 255, 128)
        [Forms]![frmPatient]![DateEligibleForFunding].BackColor = RGB(255, 255, 255)
         
    'Remove record navigation buttons to avoid record change.
    
        [Forms]![frmPatient].NavigationButtons = False
        
    '***********************************************
    'Set form properties for Read only
    '***********************************************
        
    ElseIf [Forms]![frmPatient]![FormState] = "Read only" Then
    
    'Save records.
        
        If [Forms]![frmPatient].Dirty Then
        [Forms]![frmPatient].Dirty = False
        End If
        
        If [Forms]![frmPatient]![sfrmDOD].[Form].Dirty Then
        [Forms]![frmPatient]![sfrmDOD].[Form].Dirty = False
        End If
        
        If [Forms]![frmPatient]![sfrmCareManager].[Form].Dirty Then
        [Forms]![frmPatient]![sfrmCareManager].[Form].Dirty = False
        End If
            
        If [Forms]![frmPatient]![sfrmNote].[Form].Dirty Then
        [Forms]![frmPatient]![sfrmNote].[Form].Dirty = False
        End If
            
    'Enable buttons
    
        [Forms]![frmPatient]![btnCloseForm].SetFocus
    
        [Forms]![frmPatient]![btnPersonNameSearch].Enabled = True
        [Forms]![frmPatient]![btnRemoveFilter].Enabled = True
        [Forms]![frmPatient]![btnFindRecord].Enabled = True
        [Forms]![frmPatient]![btnEditRecord].Enabled = True
        [Forms]![frmPatient]![btnAddRecord].Enabled = True
    
    'Disable editing of form and sub-forms.
    
        [Forms]![frmPatient].AllowEdits = False
        [Forms]![frmPatient].AllowAdditions = False
        [Forms]![frmPatient].AllowDeletions = False
        
        [Forms]![frmPatient]![sfrmDOD].[Form].AllowEdits = False
        [Forms]![frmPatient]![sfrmDOD].[Form].AllowAdditions = False
        [Forms]![frmPatient]![sfrmDOD].[Form].AllowDeletions = False
           
        [Forms]![frmPatient]![sfrmCareManager].[Form].AllowEdits = False
        [Forms]![frmPatient]![sfrmCareManager].[Form].AllowAdditions = False
        [Forms]![frmPatient]![sfrmCareManager].[Form].AllowDeletions = False
           
        [Forms]![frmPatient]![sfrmNote].[Form].AllowEdits = False
        [Forms]![frmPatient]![sfrmNote].[Form].AllowAdditions = False
        [Forms]![frmPatient]![sfrmNote].[Form].AllowDeletions = False
                 
    'Set field backcolours.
    
        [Forms]![frmPatient]![PatientStatus].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientNHS#].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientTitle].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientForename].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientMiddleName(s)].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientSurname].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientDOB].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![PatientGender].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![DatabaseGeneralPractitioner#].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![DatabaseGeneralPractice#].BackColor = RGB(236, 233, 216)
        [Forms]![frmPatient]![DateEligibleForFunding].BackColor = RGB(236, 233, 216)
      
    'Add record navigation buttons to allow record change and refresh record.
    
     [Forms]![frmPatient].NavigationButtons = True
    
    End If
    
    '***********************************************
    'Set additional form properties for add.
    '***********************************************
        
        If [Forms]![frmPatient]![FormState] = "Add" Then
        [Forms]![frmPatient].AllowEdits = True
        [Forms]![frmPatient].AllowAdditions = True
        [Forms]![frmPatient].AllowDeletions = True
        [Forms]![frmPatient].DataEntry = True
        End If
        
        
    'Repaint subforms.
        
        [Forms]![frmPatient]![sfrmDOD].[Form].Repaint
        [Forms]![frmPatient]![sfrmCareManager].[Form].Repaint
        [Forms]![frmPatient]![sfrmNote].[Form].Repaint
        
    End Sub
    Thanks for any time anyone can spare as always!!
    Last edited by jonesyfella; 04-03-07 at 09:06.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    1. Try Me.Form.Repaint ? *shrug*

    Not sure about 2....

    Just a quick coding tip: Ever heard of "With / End With" statements?
    It'd really tidy your code up.
    Code:
    With [Forms]![frmPatient]
        .AllowEdits = True
        .AllowAdditions = True
        .AllowDeletions = True
        End With
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Thanks George. I'd made a mental note to remember to use With/End with and I'm still only a third the way through my VBA book before throwing it into practice! Nice reminder.

    I'm still puzzled over the repaint problem if anyone can assist? Incidentally the AllowDataEntry seems to be throwing the form into a temporary code loop which is apparent by the form flicker and the CreateDateTime incrementing through approx 15 seconds. Anyone any ideas on that?!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try a further Me.Refresh after the repaint...

    When in the VBA editor put breakpoints on and around your AllowDataEntry and step through the code as it runs. It might not be a loop, it might simply be taking that long to apply the change...
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Incidentally the refresh worked. The wizard pointed me in the right direction in respect of opening the form for a new record rather than opening it in data entry mode. Thanks for that George.

    Code:
    '***********************************************
    'Set additional form properties for add.
    '***********************************************
    
    If [Forms]![frmPatient]![FormState] = "Add" Then
        DoCmd.GoToRecord , , acNewRec
    End If
        
    'Repaint subforms.
    
    [Forms]![frmPatient]![sfrmDOD].[Form].Repaint
    [Forms]![frmPatient]![sfrmCareManager].[Form].Repaint
    [Forms]![frmPatient]![sfrmNote].[Form].Repaint
    [Forms]![frmPatient]![lsfrmPatient].[Form].Repaint
    [Forms]![frmPatient].Refresh
     
    End Sub

Posting Permissions

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