Help with subform controls

[George Too]

Hi, I have a Subform Control in a Tab Control which gets its RecordSource from code once certain conditions are met.

Folliwing this line "'Forms![frmMain]![childHigh].Form![txt056]", I can reference the controls in the form just fine but when I do the following: "For Each control In Forms![frmMain]![childHigh]!Form.controls", Access complaints that it can't find the referenced control. What is the correct syntax then?

Thanks for your help,

George

 

[Nouba]

Hi George,

try

Forms![frmMain]![childHigh].Form.Controls

from frmMain you can shortly write

Me![childHigh].Form.Controls

 

[George Too]

Thanks for your post Nouba.

Forms![frmMain]![childHigh].Form.Controls works. The next problem occurs when trying to get the Tag for the controls accessed in the "For Each" loop. I have tried many variations of the above line but it doesn't seem to work.

Here's what I have:

For Each control In Forms![frmMain]![childHigh].controls
.Range(Format$(Column) & Format$(Row)).Select
'This line does not work
.ActiveCell.FormulaR1C1 = Forms![frmMain]![childHigh].Form.control.Tag
Row = Row + 1
Column = Column + 1
Next control

Thanks,
George

 

[George Too]

So, how do I access the Tag property of each control thru this method. Any takers?

Thanks,
George

 

[Nouba]

try using

For Each control In Forms![frmMain]![childHigh].controls
  Debug.Print control.Tag
Next conrol

 

[George Too]

"Debug.Print control.Tag" does not work. I need to access the Tag property for each control so that each tag becomes the heading for an Excel spreadsheet that the code creates.

Any other ideas?

Thanks,
Geoge

 

[jjturner]

Have you tried:

.ActiveCell.FormulaR1C1 = .Tag

At a first mental glance, it would seem to me that a successful reference to your control set in the 'For Each control' line should pass those control references into the loop implicitly, therefore you wouldn't need to re-reference them explicitly in the above problematic line.

However, foreseeing that Access may be confused by my suggestion since your Excel property assignment is also implicitly referencing your Range/WorkSheet object, you could then try:

.ActiveCell.FormulaR1C1 = control.Tag

A shot in the dark, but hope this may help.

Regards,
John

PS - are the spreadsheet headings being represented by actual formulas (.FormulaR1C1)?

 

[George Too]

Hello jjturner,

You are absolutely correct. control.Tag works just fine. Now, regarding .FormulaR1C1, no, this are just names for my columns. I use it because it works but if you suggest a better approach I'm willing to try it.

Thanks for your reply.

George

 

[jjturner]

George,

Glad that worked - I just noticed that Nouba was basically suggesting the same thing as me with his 'debug.print' statement (your solution being just to substitute in the '.ActiveCell ... = control.Tag' for the 'debug.print').

Anyhow, with respect to '.ActiveCell.FormulaR1C1', why not just use '.ActiveCell = control.Tag' without 'FormulaR1C1'?

HTH,
John

 

[George Too]

jjturner, with respect to '.ActiveCell.FormulaR1C1', that's what I'm using. I removed all the '.ActiveCell.FormulaR1C1' from my code just in case that property would slow it down or something.
Again, thanks to all for your replies. Code is working perfectly fine. Another satisfied customer...

Regards,
George.