How to refer to a field (not a control) in a subform

[fluid]

Just wondering how, from a criteria in a query, I refer to a field that is present in an open subform record but has no control associated with it.

I've tried

[forms]![MyMainForm].[SubformControlName].form![MyField]

that doesnt work.

As far as I've been able to conclude, that only works to refer to controls, not fields..

 

[Ranman256]

Forms don't refer to fields. Tables have fields.
Forms have controls. Text boxes, combos,etc.
But sometimes they have the same name.

 

[fluid]

Ok, wrong question. Let me try again...

I have and open form, with a subform that is set to continuous, in each row of the subform i have an unbound combobox that I want to populate with a value( from a seperate union query) based on the value of a field in that same subform record (row). That way I can have that combobox , in each row, show a single, but different value for each row in the subform.

Any suggestons?

 

[CJ_London]

if the combobox is unbound, you cannot show different values on different rows.

Perhaps provide a more detailed description of what you are trying to do - it may be you need to calculate the value in your form recordsource - but then you won't be able to change it in the combo

 

[Galaxiom]

with a subform that is set to continuous, in each row of the subform i have an unbound combobox that I want to populate with a value

That way I can have that combobox , in each row, show a single, but different value for each row in the subform.

No you can't. Unbound controls in a continuous form have the same value for every record. Only bound controls can have different values.

 

[fluid]

Ok, I guess I was given some bad info.

All the info I want to show on each row would be available from a query, except, part of the info comes from a union query, which are read only. I've been told that any query that references a union query is also read-only. The fields that come from the union query only need to be read-only, but the other fields on the subform rows need to be read-write.

overview...I want 5 fields per row of the subform

Quantity, PartNumber, PartDescription, RFPNumber, OrderStatus.

PartNumber and PartDescription are the fields coming from the union query. (from 2 other separate DB's)

My underlying Table for the subform contains:

Quantity, Part_FK, RFPNumber, OrderStatus, WONumber_FK, Dept

I want to be able to use the Part_FK/dept to lookup the PartNumber and PartDescription.

 

[fluid]

this is what the subform looks like.

 

[fluid]

I should also add....the combobox's aren't 100% necessary, it's just the way I was trying to reach my end goal of having them on the same row as the other data.

 

[CJ_London]

From your attachment it looks like part# and description will be the same for each row? Or is this the problem?

why can't you simply have the partFK on the subform as a combo box to show the part # and then use a calculated control for description with a controlsource something like =[Part_FK].column(2)

 

[missinglinq]

To be honest I can't make heads or tails out of your scenario (...probably my fault) but a couple of points on the general subject of 'How to refer to a field (not a control) in a subform and vis-à-vis Unbound Controls on Continuous View Forms:'

If the Field is part of the RecordSet of the Form/Subform, but doesn't reside on the Form/Subform, itself, you can, in VBA code, still reference it. If the Field name is, for instance, FIUT (Field In Underlying Table), you'd simply use

Me.FIUT

like you would for a Control.

Actually, an Unbound Control (one not Bound to a Field in the underlying Table/Query) can be Record-specific, in a Continuous Form...but you have to use an Expression in its Control Source. In the Control Source, you'd use this:

=[FIUT]

This Unbound Control will be Read-Only, as all Controls whose Control Sources are based on Expressions, but they will be Record-specific!

The usual way of displaying data from a Table/Query not part of the Form's RecordSet is to use the DLookup function, once again in an Expression in its Control Source.

Not sure if that will work for you (as I said, I'm fuzzy on your goal, here) but you could use, in this case/example, FIUT in the Where Clause of the DLookUp.

Linq ;0)>

 

[Galaxiom]

If the Field is part of the RecordSet of the Form/Subform, but doesn't reside on the Form/Subform, itself, you can, in VBA code, still reference it. If the Field name is, for instance, FIUT (Field In Underlying Table), you'd simply use

Me.FIUT

like you would for a Control.

If there is a control by that name you will get the control because Controls is the default Collection of the Form. If you want a field in the recordset when there is a control with the same name, use:

Me.Recordset!fieldname

Actually, an Unbound Control (one not Bound to a Field in the underlying Table/Query) can be Record-specific, in a Continuous Form...but you have to use an Expression in its Control Source.

That depends on one's definition of "bound". In this case the control is bound to an expression. After all, the design interface only shows "unbound" when the control has an empty ControlSource.

 

[missinglinq]

...If there is a control by that name you will get the control...

Of course, you will...but the whole point here is that the Field isn't Bound to a Control on the Form...so why would you have a Control with that name?

...That depends on one's definition of "bound"...

That's the reason I included the qualifier

an Unbound Control (one not Bound to a Field in the underlying Table/Query)

Linq ;0)>

 

[Galaxiom]

an Unbound Control (one not Bound to a Field in the underlying Table/Query)

The issue is your incorrect use of the term "unbound".

Unbound Control A control that doesn't have a source of data (such as a field or expression) is called an unbound control

What you are referring to is called a "Calculated Control".

Calculated control A control whose source of data is an expression, rather than a field, is called a calculated control.

https://support.office.com/en-us/article/Introduction-to-controls-4a8cf5f2-d739-4ae9-b1e0-510c3f4d6975