sub form

[ddtpmyra]

Hi,

I created a form that has a subform (child link) the next thing i wanted to do is...
after displaying the table inside the subform (datasheet view) In the event on_click I wanted to capture the data I click and assigned to a variable. How can I do that?

Thanks,
Myra

[puppydogbuddy]

You did not give much info to go on, but here is an example in which the subform recordID is assigned to a variable in the subform's before update event

Option Compare Database
Option Explicit
Dim lngrecordID As Long

 'code is behind the subform
Private Sub Form_BeforeUpdate(Cancel As Integer)
lngrecordID = Me.CurrentRecord
End Sub

[ddtpmyra]

puppydogbuddy,

to make it simple the query is in datasheet form and once I click a a record (ex. John) it will run a query for me with variable is equal to 'John'. Let me know if you need more information. Thanks for looking to this.

[puppydogbuddy]

puppydogbuddy,

to make it simple the query is in datasheet form and once I click a a record (ex. John) it will run a query for me with variable is equal to 'John'. Let me know if you need more information. Thanks for looking to this.

Myra,

Ok. Assuming the control you clicked was customer name and the current record is "John", the code would look like this:

Private Sub CustomerName_Click()
'global variable gCustomerName set up in a standard module
'so it will persist for use in other procedures and queries.
' If you don't need your variaible to persist beyond the current procedure or
' subform, then you don't need a global variable.

'assign name of current customer displayed  in a textbox control on the subform ( e.g. John) to a global variable

gCustomerName = Me!txtCustomerName.Value        '<<<<<<<<<<<<<<

End Sub

You did not fully explain what your intent was in doing this, but if what you really want to do is filter the the subform by different field selections, you should look into "filter by selection" . See this link for sample code that could be used behind a command button.

http://home.clara.net/tkwickenden/codeex/208.htm

[ddtpmyra]

This works perfectly fine, the intent is to get the current value and pass it on another query to pop another window that display another information.

You rock!

Myra