Refering to controls on subform issue

[jhob]

I have a subform called subfrm_Sites which is a child of a form called frm_ContractorsAndSites. subfrm_Sites is a datasheet view.

In subfrm_Sites I have the following code which populates some of the fields just before a new row is inserted. Now this code works perfectly when the subform is open directly (i.e. isn't a child of any form) but fails with a 'Runtime error 2450: Cannot find the form 'subfrm_Sites'.

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Dim SQL As String
    SQL = "SELECT MS_SysCode, MS_Cost FROM tbl_Materials"
    Set rs = db.OpenRecordset(SQL)
    While Not rs.EOF  ' Loop trough the table
        [Forms]![subfrm_Sites].Controls(rs("MS_SysCode")) = rs("MS_Cost")
        rs.MoveNext
    Wend
    rs.Close
    db.Close
End Sub

Now I have tried a number of permutations of referring to the controls on the subform, none of which have worked. I'm guessing there's a simple solution to this I'm just missing something obvious.

Could anyone help me out here and let me know where I'm going wrong?

Help muchly appreciated,
John

 

[RuralGuy]

Post back if this link is not helpful enough.

 

[jhob]

Cheers RuralGuy - that link was a great help!

The simple Me!ControlName worked.

Is it just me or is the access referencing system rather arcane?

 

[gray]

Well I'm baffled by it... I wanted to set a mainform textbox to the value of a subform box programatically... I've tried all sorts of ! [ and full stops (periods in americanese) plus .Value you name it (I worked on the basis of the story of monkeys on typewriters) but all I can generate is a #Name? error. I even lifted the syntax from the properties list of the textbox where it had worked happily. The last thing I tried was :-

Me.MyTextBox.ControlSource = Me.My_SubForm.Form.MySum_TextBox

Any chance of helping me out here... I tried the ref you gave for subforms but I don't think my case is on it.

Thanks

 

[RuralGuy]

Why do you need to set the ControlSource of YourTextBox programatically? Did you create the control programatically? Or did you really just want the value from the SubForm control placed in the MainForm control?
Me.MyTextBox = Me.My_SubForm.Form.MySum_TextBox

 

[gray]

Hi RG

The controlsource on the Main form textbox was set up to the subform textbox which was in fact a SUM (in footer of subform). I set this using the properties sheet and it worked perfectly in all cases except when there were no records to SUM in the subform. I just got the usual #Error.

After experimentation I decided to use the Current Event of the mainfrom to check to see if the were any records to SUM. If there were none, I was going to knock down the controlsource of the textbox and set its "value" to NULL (so I could do a test later) manually. If there were records, I was going to set the filter on the subform accordingly and re-set the controlsource to the subform SUM box. The reason I was headed this way was because when the user manipulates values in the subform (which is a datasheet) the SUM is automatically updated and so too then is my mainform textbox.

Phew! Sorry that was such a lenghty explanation... still if you are at 8300 ft I guess you are used to being light headed... lol

 

[RuralGuy]

This works as a fixed ControlSource for the MainForm control:
=IIF( IsError(My_SubForm.Form.MySum_TextBox),0,My_SubForm.Form.MySum_TextBox)

 

[gray]

Thanks RG ... works a treat... much appreciated (again)! Rgds

 

[RuralGuy]

Glad I could help.