Call Value from SubForm with On Click Event

[pwag]

Good Morning Pro's,

My first time trying to loop through a Subform to grab a value. I would like to call the ID value in the row I click. My first time trying this as I usually use the Listboxes click event to call a value. is this possible on a subform? to click a row and call it's ID value already present in the row?

What I have thusfar but it only loops through all the rows and returns all values for ID. Anyway to grab the ID for just the row the user has clicked?

Dim MyValue As String
Dim rs As Recordset
 
Set rs = Forms![ProjectForm]!Plan_Update.Form.RecordsetClone
 
If Not rs.EOF Then rs.MoveFirst
While Not rs.EOF
    MyValue = rs!ID
        MsgBox MyValue
    rs.MoveNext
Wend
 

Thanks,
Paul

The subform is datasheet view.

[jforbes]

I’m not 100% sure what you are attempting to do, but my understanding is that you are attempting to find the ID of the Selected Record of a Subform from your Main Form (ProjectForm). If so, you can get the value from the Subform like this:

Dim MyValue As String
MyValue = Me.Plan_Update.Form!ID
 

This assumes that the Control for your Subform is named Plan_Update.

I've also seen this helpful reference posted here and there on this forum that helps with situations like this one:
http://access.mvps.org/access/forms/frm0031.htm

[pwag]

Hi and Thanks jforbes. What I'm trying to do is mimic the behavior of a list box. When you click on a listbox it gives you the idea value based on the bound column OR Listbox.column(0). I'm trying to get the ID value. Thanks.

[zmbd]

Well depending on how you have things setup with the forms there can be twists on the following:

Usually, there is a Main_Form (most often in single form view) and a sub_form (most often in data table view) linked by the common field in the parent-child relationship. Despite the appearance, the subform is not a list_box control. It may contain such controls; however, it is not in and of itself a list box.

Now when the user clicks on the record, that becomes the bound form's current record. This holds true in the subform.
Once selected, there are a lot of methods to refer to this current record's field(s) value(s); however, I find the easiest is to refer to the bound control by name of the control and then the value property of that control.

For example: Main_Form and the SubForm_1

On SubForm_1 you have a record source and one of your controls is called "Example_Value" bound to a field in the recordsource

From within the SubForm_1 you can use the Me.ExampleValue.Value format to refer to the value of the field for the currently selected record in VBA associated with the subform or other controls on the subform. No need to provide the entire path. Keep in mind the SCOPE of form makes the "Me" shortcut refers to the subform at this point.

From the Main_Form you would need Me!SubForm_1!ExampleValue.Value (mind you the scope is the main_form here... thus the "me" in this case is the Main_form.)

Reference Sheet for Referring to Controls on Forms

[twinnyfo]

Paul,

With J above, I, too, am a bit confused as to what you are trying to do. In the code you posted, all you are really doing is looping through the records and assigning the value of ID to your variable. But since you are looping through all records, you are not really doing anything.

J's method above will work, but must be couched in some event that will execute it.

You mention the form is in datasheet view, which limits your possibilities to either an OnFocus or Before/After Update event. And these events would need to be coded for each visible field.

What is it that you want to do with your ID that you have identified? It may be easier to use a continuous form, displaying your desired fields, and include a Command Button to execute your code. It is sometimes a more straightforward approach. However, there may also be reasons for you maintaining the datasheet view--which is also understandable.

[pwag]

Thanks jforbes. that seems to have worked. i took out the loop and it works like i want it to now.

This is what worked.

Dim MyValue As String
MyValue = Nz(Forms![Project]!Plan_T_Update.Form.ID, 0)
MsgBox MyValue