Problem firing subform events 1

[LakotaMan]

Hi all,

Using ver. 2007, I am having some trouble with a subform. This is probably simple to solve, but my noggin won’t wrap ‘round it.

I have locked controls on a main form, and it works fine. I would also like to lock controls on a subform (which is on another tab page). I’ve tried putting the code behind the Open and Load events of the subform –but they don’t even fire.

I know there must be something different in the events for a subform that I’m missing here, so I apologize for my ignorance.

Could somebody help me put my code in the right place?

Thanks,
LM

 

[GKChesterton]

Subform events are triggered BEFORE most events in the main form. Load and Open have to fire (try putting "Stop" in those events to see it happen).

Since the tab is a control of the parent form, can you not lock from the parent-form module?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[LakotaMan]

GKChesterton,

I did put a stop in. Nothing happened, that's how I knew the events weren't firing.

How would I lock the subform controls from the parent form?

 

[GKChesterton]

Opening a form triggers the Load and Open events. Something's not adding up ... as you know! I wonder if you allowed the form module make its own events, or did you type them up yourself?

As for locking the subform controls, you can't do that from the main form (well, you can through code, let's not go there). But you can lock the subform itself -- it is a control on the main form just like a text box.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[LakotaMan]

No, didn't make my own events, went stright to them from the design view of the form, via the properties window.

I've done some work with subforms before and know things can get tricky, but I've never seen common form events not fire before.

I will try locking the subform object itself, and see if I can make that work.

Thanks for your input.
LM

 

[GKChesterton]

You could post the totality of your VBA for the two forms. Maybe would yield something upon examination.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[LakotaMan]

Well. . . here it is:


Main Form:

Option Compare Database
Private Sub cboAreaID_AfterUpdate()

    If Me.cboRespPartyID = 1 Or Me.cboRespPartyID = 8 Or Me.cboRespPartyID = 12 Then
        PlantofOrigin = DLookup("Area", "tblArea", "AreaID=" & Forms!frmComplaints.cboAreaID)
    Else
        PlantofOrigin = "N/A"
    End If
End Sub
Private Sub cboAreaID_Change()
    Me.cboLocationID.Requery
End Sub
Private Sub cboComplaintCatID_Change()
    Me.cboComplaintTypeID.Requery
End Sub
Private Sub cboProdCatID_Change()
    Me.cboProdCodeID.Requery
End Sub
Private Sub cboProdCatID_Dirty(Cancel As Integer)
    Me.cboProdCodeID.Requery
End Sub
Private Sub cboProductID_Change()
    Me.cboRespPartyID.Requery
    Me.cboAreaID.Requery
    Me.cboLocationID.Requery
    Me.cboProdCatID.Requery
    Me.cboProdCodeID.Requery
End Sub
Private Sub cboRespPartyID_Change()
    Me.cboAreaID.Requery
    Me.cboLocationID.Requery
End Sub
Private Sub cmdCancel_Click()

        'Set counter table back one if this is a new entry
        If strUsertype = "New" Then
            'Save record first, so if new, the RptNum will be captured for deletion & resetting counter table
            DoCmd.RunCommand acCmdSaveRecord
            
            'Run query to set counter back
            DoCmd.SetWarnings False
            DoCmd.OpenQuery ("qryUPCounterforCancel")

            'Now, delete the record
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
            
        'If user is not new, do nothing, just undo changes
        Else
            If Me.Dirty = True Then
                Me.Undo
            End If
        End If
        
        DoCmd.Close

End Sub
Private Sub cmdMADMOrder_Click()
    Me.ADMOrder.SetFocus
    Me.ADMOrder.Text = "Multiple"
End Sub
Private Sub cmdMCarLotTr_Click()
    Me.CarLotTrailerNum.SetFocus
    Me.CarLotTrailerNum.Text = "Multiple"
End Sub
Private Sub cmdMCarrier_Click()
    Me.Carrier.SetFocus
    Me.Carrier.Text = "Multiple"
End Sub
Private Sub cmdMCarrier_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.lblButtonHelp.Visible = True
End Sub
Private Sub Form_Current()
    'Refesh all comboboxes
    Me.cboProductID.Requery
    Me.cboRespPartyID.Requery
    Me.cboAreaID.Requery
    Me.cboLocationID.Requery
    Me.cboComplaintCatID.Requery
    Me.cboComplaintTypeID.Requery
    Me.cboProdCatID.Requery
    Me.cboProdCodeID.Requery
End Sub
Private Sub Form_Load()
    If strUsertype = "New" Then
    
        'Take form out of Data Cntry mode
        Me.DataEntry = True
        Me.AllowAdditions = True
        
        Dim CurrentYear
        Dim CounterYear
        Dim LastCounterNum
        Dim NewComplaintNum As String
          
        CurrentYear = Right(Date, 2)
        CounterYear = DLookup("Year", "ComplaintsCounterTable")
        CounterYear = Right(CounterYear, 2)
        
        LastCounterNum = DLookup("LastCounterNum", "ComplaintsCounterTable")
       
       DoCmd.SetWarnings False
        If CurrentYear - CounterYear = 1 Then
            'Reset the table for the new year and first complaint  --run an update query to reset the value
            DoCmd.OpenQuery ("qryUPResetCounterforNewYear")
        Else
            'add one to last counter and use year for CurrrentYear variable  --Use update query for first part
            DoCmd.OpenQuery ("qryUPCountertoNextComplaintNum")
        End If

        DoCmd.SetWarnings True
       
        NewComplaintNum = LastCounterNum + 1 & "-" & CurrentYear
        Me.ComplaintNum = NewComplaintNum
        
        Me.AllowAdditions = False
        Me.EntryDateTimeStamp = Now()
        Me.CustomerName.SetFocus
    
    
    ElseIf strUsertype = "Response" Then
        Me.DataEntry = False
        Me.AllowAdditions = False
        Me.lblButtonHelp.Visible = False
        Me.cmdClose.SetFocus
        LockForm
    End If


End Sub
Public Sub LockForm()
    
    Dim ctl As Control
    Dim strName As String
    Dim strTag As String
    
    For Each ctl In Me.Controls

        If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acOptionButton) And ctl.Tag = "lock" Then
                strName = ctl.Name
                ctl.Enabled = False
                ctl.Locked = True
        ElseIf ctl.ControlType = acCommandButton And ctl.Tag = "lock" Then
                ctl.Enabled = False
        End If

    Next ctl
    

End Sub

SubForm:

Option Compare Database
Private Sub cmdAddResponse_Click()
    lngCompID = Forms!frmComplaints.ComplaintID
    strCompNumber = Forms!frmComplaints.ComplaintNum
        
    DoCmd.OpenForm "sfrmAddResponse", , , , acFormAdd

End Sub
Private Sub Form_Load()

    If struser = "Response" Then
        Dim ctl As Control
        Dim strName As String
        Dim strTag As String

        For Each ctl In Me.Controls

            If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acOptionButton) And ctl.Tag = "lock" Then

                strName = ctl.Name
                ctl.Enabled = False
                ctl.Locked = True

            ElseIf ctl.ControlType = acCommandButton And ctl.Tag = "lock" Then
                ctl.Enabled = False
            End If
            
        Next ctl

    End If

End Sub

Good luck!

 

[GKChesterton]

Observation 1: Avoid Form_Current. It trips much more often than you want and tends to go out of control (like Activate).

Observation 2, About how to lock a form: Sometimes you need control at the field level, but usually it's much easier to use something like form.RecordsetType (which you set to 0 or 2, dynaset and snapshot respectively). There are other form-level settings that are even simpler I believe. Sometimes the 'gotcha' is that you can't filter or do anything, but in general you can adjust the settings to what you need with much less code than you've shown us.

So, you say you've tried this?

Private Sub Form_Load()
    Stop
    If struser = "Response" Then ...

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[LakotaMan]

Would love to avoid using the Current event, but don't know another way to refresh cbos for each record . . .

That's a great idea on the recordset solution, I'll work with that.

Yes, I put a stop on both the load and open events.

 

[LakotaMan]

. . .not at the same time!

 

[GKChesterton]

. . .not at the same time!

"Stop" merely throws the code into break mode. Wouldn't matter if you had it in both events .. could be helpful in fact.

Back to making it read-only: In order of ease and brutality,

1. While .mdb is closed, make doc property "read-only". You can also set this from within Access (File -> Properties).

2. For any query, go to properties in design view and set Recordset Type to Snapshot. Now a form based on that query is effectively read-only. (You'll see Record Locks nearby -- that's about multi-user protections, does not apply.)

3. For any form, ditto #2 above.

4. Also in form data properties, Allow Edits and similar items.

5. "Enabled" isn't a form property, but it does apply to a subform as a control on the parent form. Setting this to "no" locks the records and gives the end-user a sensation of powerlessness.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[missinglinq]

I'm sorry but suggesting

Observation 1: Avoid Form_Current. It trips much more often than you want and tends to go out of control (like Activate).

is just plain silly! Like any event, it needs to be used appropriately, such as (in this case) when you need to have something occur each time you move from one record to another!

The Dim statements in your subform's Form_Load event should appear at the beginning of the sub. I have seen a number of reports of stating that having them elsewhere, such as

[c0Private Sub Form_Load()

    If struser = "Response" Then
        Dim ctl As Control
        Dim strName As String
        Dim strTag As String

        For Each ctl In Me.Controls

rather than

[c0Private Sub Form_Load()
        Dim ctl As Control
        Dim strName As String
        Dim strTag As String

    If struser = "Response" Then

        For Each ctl In Me.Controls

can cause problems.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[GKChesterton]

The Missinglinq -- sure, you're correct. If moving from record to record on continuous form, that can call for Current. My Wrox text says it is overused by novices and can be "hard to control," and advises to use it sparingly. It's snared me in the past.

If I can retreat to a more valid position: An alternative might be advisable, if available.

So what do you think of the problem where "Stop" isn't executed? I'm very curious about that.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[TheAceMan1]

How are ya LakotaMan . . .

[ol][li]Resolving the issue of the [blue]Load[/blue] enent not firing is prime here. As such, you may not understand what the [blue]Stop[/blue] statement is doing. To circumvent this and give you a more active type of triggered indicator, replace the stop statement with the following MsgBox (where you see it in [purple]purple[/purple]):

[blue]Private Sub Form_Load()
   [purple][b]MsgBox "LOAD EVENT TRIGGERED! ..."[/b][/purple]
   
   If struser = "Response" Then[/blue]

If you don't get the messagebox then I'd say there's some kind of corruption going on.
If the messagebox shows itself then continue ...[/li]
[li]Wether your code gets executed or not depends on the following mystery line:

[blue]   If struser = "Response" Then[/blue]

Where is the variable [blue]struser[/blue] and how does it get initialized? ... unless its a field on the subform whose proper syntax should be [blue]Me!struser[/blue] ...[/li][/ol]
If you change the [blue]Tag[/blue] property of the [blue]command buttons[/blue] from [blue]Lock[/blue] to [purple]Loc[/purple], your code condenses to:

[blue]   Dim ctl As Control, strName As String, strTag As String

   If struser = "Response" Then
      For Each ctl In Me.Controls
         If ctl.Tag = "[purple]lock[/purple]" Then
            strName = ctl.Name
            ctl.Enabled = False [green]'Not really needed. Locked suffices[/green]
            ctl.Locked = True
         ElseIf ctl.Tag = "[purple]loc[/purple]" Then
            ctl.Enabled = False
         End If
      Next
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[LakotaMan]

Hey AceMan,
Glad to hear from you . . .
strUser is a global variable set by a command button on a menu-form which loads first.
Using your suggestion, I put the message box code in, and added a check to see if the strUser variable was being set properly (which was why I had put the stop in when the trouble first started):

    Dim UserType
    UserType = struser
    MsgBox "The user is " & UserType

All of this executed. So I put a stop at the last “End If” and it did stop there.
So. . . obviously none of the controls fulfilled my conditions . . . so I checked the properties of the controls and it turns out I forgot to tag them.
Now, yes, everybody can be mad at me for forgetting something stupid, but I’m giving you a star for making me see it. . .
PLUS . . . thanks for optimizing my routine, that was just what I was looking for, didn’t even think about doing only the lock!
I got rid of a couple of the other variables that I was using for tracking and now everything is working fine.
You the AceMan for sure, wish I could I give you 2 stars?
Thanks,
LM