If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Accessing fields on Record Source without putting them on form/subform
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
#2
|
|||
|
|||
Accessing fields on Record Source without putting them on form/subform
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... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada |
#3
|
|||
|
|||
Accessing fields on Record Source without putting them on form/subform
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" u46349@uwe wrote in message news:a065a9e188b06@uwe... 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200912/1 |
#4
|
|||
|
|||
Accessing fields on Record Source without putting them on form/subform
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. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Accessing fields on Record Source without putting them on form/subform
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. "Lau via AccessMonster.com" u46349@uwe wrote in message news:a06611a1b918e@uwe... 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. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|