Form recordset question

[RonL]

A noob question. I have a form whose recordsource is a saved query. But not all fields in the query are on the form eg. FldID or fldSortby. In VBA , how do I reference the value of say fldSortby for the current record on the form?

[Bob Fitz]

Why not just put a text box on the form, bound to the appropriate field.
What do you want to do with the value of the field.

[RonL]

The value is for my programming purposes. Has no use for user. I know I could add a control and make it invisible, but would rather not redesign the form (used as a subform) at this stage.

[June7]

You can reference the field even if no textbox bound to it, as long as it is included the form RecordSource.

[RonL]

You can reference the field even if no textbox bound to it, as long as it is included the form RecordSource.

Good. But how? This, and variants of it, gives an error: varTest = frmMain.ctlsbfShotList.Form.RecordSource.[fldOrderBy]

FldOrderBy is in sbfShotList recordsource, but there's no corresponding control on the form. How to reference? Thx, Ron

EDIT - OK, got it. Looks like vartest = Me.ctlsbfShotList.Form.[fldOrderBy] will do it. Thank you!!

[June7]

Where is that code?

If code is behind the form that has field:

varTest = Me!fldOrderBy

If code is behind main form and field is in subform:

varTest = Me.ctlsbfShotList!fldOrderBy

[RonL]

.........
If code is behind main form and field is in subform:

varTest = Me.ctlsbfShotList!fldOrderBy

It's this latter case. The code is in the main form unload event, which means the subform unload has not yet fired. My testing shows you do need the .form in the syntax (as I showed above: varTest = Me.ctlsbfShotList!Form.fldOrderBy )

The important thing I've learned here is that each field (or calculated expression (?) if the source is a query) of a form's recordsource is available in the form's VBA, whether or not there's a corresponding control on the form. I'm still not clear on whether that means a field for which there's no control is in the form's actual recordset. Will work that out if necessary.

Thanks! -Ron

[June7]

Works for me without the .Form maybe because I used bang (!).

[RonL]

Works for me without the .Form maybe because I used bang (!).

You're right! Works here too with bang. (I hadn't actually tried that before.)

More stuff to read up on.... Thanks!