Accessing fields on Record Source without putting them on form/subform

[Lau via AccessMonster.com]

I have a database designed to maintain family membership information. I have
a mainform and subform. The subform draws data from tbl_family, which
contains fields - household_id, family_member_id, and relationship plus
others. Please note that Head of Household (coded AA) is one of the
selections for Relationship field.

On subform, each time the user selects a different person to be the Head of
Household, the relationships for all family members reset to blank, except
the one person just selected for head of household. Here is my working codes:


************
Private Sub cboRelationship_Change()
Dim strSQL_updhead As String
If Me.relationship_id = "AA" Then

If (MsgBox("Selecting new 'Head of Household' will reset relationship for
all family members. Are you sure you want to do this?", vbOKCancel) =
vbCancel) Then
Me.Undo
Else
strSQL_updhead = "UPDATE tbl_family " & _
"SET relationship_id = NULL " & _
"WHERE (household_id = " & me.household_id & ") and
(family_member_id <> " & Me.family_member_id & " );"

DoCmd.RunSQL strSQL_updhead

End If

End If
End Sub
************

I do not want household_id and family_member_id to appear on the subform.
Setting field properties Enabled =No and Visible=No won't help. How do I
access the 2 fields without putting them on the subform? Can somebody shade
some lights? Thanks.

 

[Albert D. Kallal]

In general, for code in that sub form, you should be able to use
Me.FieldName

However, if your code modifies/sets the recordsource at a runtime, then you
need to use ! (bang) notation:

try:
WHERE (household_id = " & me!household_id

Etc....

This is one reason why for when I refer to the underlying recordset in code,
I use ! (bang), and for actual text boxes, I use me. (dot).

So, you don't need to place the fields on the sub-form, and as a general
rule you should be able to use . (dot), but **IF** you set the sub-form's
data source at runtime, then you genreally have to use ! as per above...

 

[Jeff Boyce]

If you open the subform in design view, click on (i.e., highlight) the
control you wish not to see, then open the Properties window for that
control, you can set the Visible property to "No".

Is that what you're looking for?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Lau via AccessMonster.com]

That's a quick response!

Albert - I tried your solution and it worked!

Jeff - I did try your solution prior to posting it here. The fields were
only gray-out.


Thanks to both of you.

 

[Jeff Boyce]

Interesting!

When I use the .Visible property, objects disappear and appear.

When I use the .Enabled property, objects gray-out or not.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.