Reference fields in subform query (ms Access)

Question

I have a form, frmResults, which contains a subform control, frmResultsSub.

frmResultsSub is a subform control which contains a query instead of a form. In other words, its SourceObject property is set to the query, "Query.qrySearch"

Is there any way I can reference the fields of this query to e.g. to centre text or change field width?

I've tried these statements in the Open Event for frmResults, but neither was successful:

Me!frmResultsSub.fldA.Width = 600
Me.frmResultsSub.fldA.Width = 600

Benjamin W. · Accepted Answer · 2016-02-14 23:48:24Z

1

Reference the fldA field in the query the same as you would if it were the name of a textbox on a form contained in that frmResultsSub subform control.

Put this in On Load and verify it shows you the field name instead of triggering an error ...

MsgBox Me!frmResultsSub!fldA.name

Once you have that working correctly, you can work with the column's properties. During On Load, adjust its ColumnWidth property to change its width ...

Me!frmResultsSub!fldA.ColumnWidth = 600

That should allow you to set the width. But you also mentioned text alignment, and I don't see how to do that for a query column unless you create an actual form based on the query.

edited Feb 14, 2016 at 23:48

Benjamin W.

54k19 gold badges135 silver badges136 bronze badges

answered Feb 10, 2016 at 13:41

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jim Over a year ago

Many thanks for everyone's help here. As above, Me!SubFqrySearchHLA!DonorAx.ColumnWidth = 600, worked in the On Load event (forgot to adjust field/form names - thanks for seeing through the confusion!). All field widths can now be customised. What I should do is put the query output into a continuous form which will give much more format/customisation/presentation possibilities. The query, however, yields variable numbers of fields (the Wizard sets controls up according to the last run query). I could create a new continuous form for each display through VBA, or there might be an easier way.

Patrick Honorez · Accepted Answer · 2016-02-10 09:57:35Z

1

I don't think you can adjust column width on a query using VBA. You can however solve your problem using one of those methods:

create a form (AutoForm is ok) and make its default view as Datasheet. then use that form as the source of your subform control. that will be visually identical to your current solution.
use a listBox instead of a subform. It's quite versatile and you can easily set the listbox rowSource to any SQL statement. You will have to handle sync with main form data but it is very simple.

answered Feb 10, 2016 at 9:57

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Comments

Jim · Accepted Answer · 2016-02-14 20:52:39Z

1

Try this: Me.frmResultsSub.Form.fldA.Width = 600

edited Feb 14, 2016 at 20:52

Jim

4231 gold badge5 silver badges14 bronze badges

answered Feb 10, 2016 at 8:45

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Reference fields in subform query (ms Access)

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related