Passing subform keyfield value to VBA behind a button.

[harveya382]

As an Access newbie, I am short of knowledge being able to accomplish a programming task. and cannot find the right question to ask google for my question.
I am trying to get a button to in a subform to use the value of the key Field in the current data row of that subform in a SQL Query. My plan is to then use the value in an update query that updates a cross reference table. My situation is I have memberships in a swim club. Members schedule swim lessons.
The memberships have a table with a Membership# key. There is also a member table with a member# key and a membership# foreign key. There is a Lesson table with and Lesson# key, Session#, Level#, TimeSlot#.


I have a cross reference table (memberlesson) to facilitate a many to many join between members and lessons with and autokey and the member# and Lesson # fields.
I have a master form Membership with a Members subform displaying all the member records for a membership (mom, dad, child 1, child2, etc). I want to place a button on the row that has a member who is signing up for a lesson that updates the memberlesson table with the member# from the row that the button is on and a lesson# value determined by a query against the lesson table selecting the correct Session#, Level#, TimeSlot#. I know how to do the query to select the lesson# and to update the memberlesson able. I do not know how to get the value of the selected Member# record from the Members subform to pass into the update query.
Can you help me? Or direct me to a resource that can help me?
Thanks -

[ssanfu]

Have you tried putting the button in the subform? In design view, add the button and, in the click event, add this line:

MsgBox "Member Number is " & Me.Member_Num

Change to blue text to your field name.
In normal view, click the button. Is the correct ID returned? (I hope you are not using # in the field name)

If it is (and it should be according to my tests), add the rest of the code.

[harveya382]

This Me. function seems to work great thanks for your quick reply. When I figure out how to mark this answered, I will.