Solved Error 2447 (1 Viewer)

[cheekybuddha]

I finally got a solution by using an OnClick event on a Command Button as follows that does what I need.

I agree with gasman - it doesn't seem like much of a solution.

Does this attached revision of your db work for you?

 

[mike60smart]

I agree with gasman - it doesn't seem like much of a solution.

Does this attached revision of your db work for you?

Hi David

That version works just great.

All I need to do now is on the after Update of the Status Combobox is add a Password as only Admin personnel can
change this value.

 

[cheekybuddha]

All I need to do now is on the after Update of the Status Combobox is add a Password as only Admin personnel can
change this value.

You probably want to do this in the BeforeUpdate event of the combo so that you can cancel the event if the password is incorrect.

 

[Gasman]

Could just set the Locked property of the subform container control. I always name control different from the object it holds, like ctrLoad. So code in main form Current event:

Do you see where you went wrong there Mike.? You were given good advise by June7 and did not even folow it?

 

[mike60smart]

Do you see where you went wrong there Mike.? You were given good advise by June7 and did not even folow it?

Hi Gasman
I did try with no luck

 

[Gasman]

Hi Gasman
I did try with no luck

Well the important part was to name the subform control differently to the subform which you did not do?
As soon as cheekybuddha made that change then it worked?

 

[mike60smart]

I agree with gasman - it doesn't seem like much of a solution.

Does this attached revision of your db work for you?

Hi David

On incorporating your solution into a Backup of the original database on the Open of the "Dispatch" Form
I am back to the original error.

When I click Debug it highlights the following Line of Code

.Parent.sfLoadsBills.Locked = Nz(txtStatus, vbNullString) = "Billed"

Any thoughts?

 

[cheekybuddha]

Hi David

On incorporating your solution into a Backup of the original database on the Open of the "Dispatch" Form
I am back to the original error.

When I click Debug it highlights the following Line of Code

.Parent.sfLoadsBills.Locked = Nz(txtStatus, vbNullString) = "Billed"

Any thoughts?

Looks like you may have missed a dot:

.Parent.sfLoadsBills.Locked = Nz(txtStatus, vbNullString) = "Billed"
' probably ought to be:
.Parent.sfLoadsBills.Locked = Nz(.txtStatus, vbNullString) = "Billed"

 

[mike60smart]

Looks like you may have missed a dot:

.Parent.sfLoadsBills.Locked = Nz(txtStatus, vbNullString) = "Billed"
' probably ought to be:
.Parent.sfLoadsBills.Locked = Nz(.txtStatus, vbNullString) = "Billed"

Hi David
Inserted the missed Dot and it did not resolve the issue.

 

[The_Doc_Man]

The only REAL way to figure this out is to put a breakpoint on that line and see what txtStatus contains.

 

[cheekybuddha]

Mike, try placing this code in the Current event of form frmLoadsBillingSubform:

Private Sub Form_Current()
10    On Error GoTo Err_Form_Current

        Dim sfCtlName As String, ctl As Control

20      With Me
30        On Error Resume Next
40        With .Parent
50          For Each ctl In .Controls
60            If ctl.ControlType = acSubform Then
70              If ctl.SourceObject = Me.Name Then
80                sfCtlName = ctl.Name
90                Exit For
100             End If
110           End If
120         Next ctl
130       End With
140       If Err Then Debug.Print "Not a subform"
150       On Error GoTo 0
160       Debug.Print "Subform control name:", sfCtlName
170       If Len(sfCtlName) = 0 Then
180         Debug.Print "Not a subform"
190         Exit Sub
200       End If
210       .Parent.Controls(sfCtlName).Locked = Nz(.txtStatus, vbNullString) = "Billed"
220       With .txtLocked
230         If .Parent.Parent.Controls(sfCtlName).Locked Then
240           .Value = "Form is locked"
250           .BackColor = 421882
260         Else
270           .Value = "Form is unlocked"
280           .BackColor = 5167783
290         End If
300       End With
310     End With

Exit_Form_Current:
320     Exit Sub

Err_Form_Current:
330     Select Case Err.Number
        Case Else
340       MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
                 "Description: " & Err.Description & vbNewLine & vbNewLine & _
                 "Sub: Form_Current" & vbNewLine & _
                 IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
                 "Module: " & Application.VBE.ActiveCodePane.CodeModule.Name, , _
                 "Error: " & Err.Number
350     End Select
360     Resume Exit_Form_Current
       
End Sub

Then post back the output from the Immediate Window

 

[mike60smart]

Mike, try placing this code in the Current event of form frmLoadsBillingSubform:

Private Sub Form_Current()
10    On Error GoTo Err_Form_Current

        Dim sfCtlName As String, ctl As Control

20      With Me
30        On Error Resume Next
40        With .Parent
50          For Each ctl In .Controls
60            If ctl.ControlType = acSubform Then
70              If ctl.SourceObject = Me.Name Then
80                sfCtlName = ctl.Name
90                Exit For
100             End If
110           End If
120         Next ctl
130       End With
140       If Err Then Debug.Print "Not a subform"
150       On Error GoTo 0
160       Debug.Print "Subform control name:", sfCtlName
170       If Len(sfCtlName) = 0 Then
180         Debug.Print "Not a subform"
190         Exit Sub
200       End If
210       .Parent.Controls(sfCtlName).Locked = Nz(.txtStatus, vbNullString) = "Billed"
220       With .txtLocked
230         If .Parent.Parent.Controls(sfCtlName).Locked Then
240           .Value = "Form is locked"
250           .BackColor = 421882
260         Else
270           .Value = "Form is unlocked"
280           .BackColor = 5167783
290         End If
300       End With
310     End With

Exit_Form_Current:
320     Exit Sub

Err_Form_Current:
330     Select Case Err.Number
        Case Else
340       MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
                 "Description: " & Err.Description & vbNewLine & vbNewLine & _
                 "Sub: Form_Current" & vbNewLine & _
                 IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
                 "Module: " & Application.VBE.ActiveCodePane.CodeModule.Name, , _
                 "Error: " & Err.Number
350     End Select
360     Resume Exit_Form_Current
      
End Sub

Then post back the output from the Immediate Window

Hi David
I first applied a Breakpoint as the_DocMan suggested and it displayed "Available"

Removed the Breakpoint and applied your Code

It the displays the following in the Immediate Window
Subform control name: sfLoadsBills

 

[cheekybuddha]

Did it error after that?

 

[mike60smart]

Did it error after that?

Hi David

Yes it did

 

[cheekybuddha]

Most strange!!!

Please post the error with the new error handling message

 

[mike60smart]

Most strange!!!

Please post the error with the new error handling message

Hi David

I am going to strip back the Form and rebuild as I am sure your code must be OK.
I will come back shortly with my results
Many thanks for hanging in there yet again

 

[mike60smart]

Most strange!!!

Please post the error with the new error handling message

Hi David
I have reconstructed the Forms and attached the new layout for the process involved

In the attached Db after I select a Status it does not automatically populate the txtUnlocked Control in the Subform.
It does when I click in the Combobox for selecting the Load ?

Can you tell me where it is wrong?

 

[cheekybuddha]

Hi Mike,

When you change the Load (frmCurrentLoadListsubform.cboLoadNumber) it changes the form's current record (via DoCmd.SearchForRecord), which in turn causes the subform frmLoadsBillingSubform to requery and therefore its Current event runs.

When you change frmCurrentLoadListsubform.cboStatus it just updates the field in the current record - it does not navigate to a new record, and therefore does not cause a requery in subform frmLoadsBillingSubform. You have to do this manually to cause the subform's Current event to fire:

' in module for frmCurrentLoadListsubform
Private Sub cboStatus_AfterUpdate()
  Me.sfLoadsBills.Form.Requery
End Sub

 

[mike60smart]

Hi David
Can you send Test2 example back so I con check your solution?

 

[cheekybuddha]

Here you go, Mike.