Amending data in subforms

[Cads]

I've recently been upgraded here at work from Access 97 to Access 2002. Since that happened, I've hit a problem with changing values in a subform, which in 97 worked fine.

The following code relates to a checkbox which displays the subform to allow data entry. The code handles the situation that if the checkbox is unchecked, all fields are cleared when the subform is made invisible. Unfortunately, in 2002 I get the message "Run-time error 2448: You can't assign a value to this object" when it gets to the row "frm.Form![FullT Occp] = False". This field is another checkbox.

If I manually remove entries from the fields in the subform all is fine. It's just in VBA it don't like it!

Any idease, please?


Private Sub Check69_Click()

Set frm = Me![PA Variable Parts B1 and B2]
If Check69.Value = True Then
frm.Visible = True
Else
If (frm.Form![FullT Occp] = False Or IsNull(frm.Form![FullT Occp])) And _
IsNull(frm.Form![Other Occp]) And _
(frm.Form![Semi Retd] = False Or IsNull(frm.Form![Semi Retd])) Then
frm.Visible = False
Else
If MsgBox("Unsetting Sole Practitioner flag will clear the Sole Practitioner record", _
vbExclamation + vbDefaultButton1 + vbOKCancel, _
"Warning - Sole Practitioner delete") = vbOK Then

frm.Form![FullT Occp] = False
frm.Form![Semi Retd] = False
frm.Form![Other Occp] = Null
frm.Visible = False

Else
Check69.Value = True
End If
End If
End If
End Sub


Steve House
shouse@icaew.co.uk

http://www.icaew.co.uk

 

[SarahG]

Try the alternative way of referencing the check-box:

frm.form.[FullT Occp].value = false

HTH

 

[Cads]

Unfortunately this doesn't seem to change things - still getting the same error message. however, thanks for responding.

Perhaps I should have said that the 'frm' is the subform control in the main form and I'm basing my approach on Knowledgebase article Q209099 which says to refer to a control on a subform, use the following syntax:

Forms![main form name]![subform control name].Form![control name]

which I believe is what I'm doing.

 

[TheAceMan1]

How are ya Cads . . . . .

First, when performing upgrading this way, check [blue]that all the Libraries used in 97 appear in 2002[/blue]. I don't remember for 97, but for 2002, in a code window, click [blue]Tools - References[/blue]. The checked items at the top should match that used in 97. If any are missing they'll have to be installed.

As for your code, subform referencing is not quite correct. So I've modified the code. The modifications are in [purple]purple[/purple].

[blue]Private Sub Check69_Click()
   [purple][b]Dim sfrm As Form[/b][/purple]
   
   Set [purple][b]sfrm[/b][/purple] = Me![PA Variable Parts B1 and B2].Form
   
   If [purple][b]Me[/b][/purple]!Check69 = True Then
      frm.Visible = True
   Else
      If ([purple][b]sfrm[/b][/purple]![FullT Occp] = False Or _
         IsNull([purple][b]sfrm[/b][/purple]![FullT Occp])) And _
         IsNull([purple][b]sfrm[/b][/purple]![Other Occp]) And _
         ([purple][b]sfrm[/b][/purple]![Semi Retd] = False Or _
         IsNull([purple][b]sfrm[/b][/purple]![Semi Retd])) Then
         frm.Visible = False
       Else
         If MsgBox("Unsetting Sole Practitioner flag will " & _
                   "clear the Sole Practitioner record", _
                   vbExclamation + vbDefaultButton1 + vbOKCancel, _
                   "Warning - Sole Practitioner delete") = vbOK Then
         
            [purple][b]sfrm[/b][/purple]![FullT Occp] = False
            [purple][b]sfrm[/b][/purple]![Semi Retd] = False
            [purple][b]sfrm[/b][/purple]![Other Occp] = Null
            frm.Visible = False
          
         Else
            [purple][b]Me[/b][/purple]!Check69 = True
         End If
       End If
   End If
End Sub[/blue]

See Ya! . . . . . .