Display details in two subform

[Nishikawa]

Hi,

I am trying to create a form with two subform in it. When users click on a row in subform A (datasheet), the row will change colour and the details will pop up in subform B (datasheet). I know this is possible as I have seen it in this forum a year back.

I have search in this forum but to no avail. Can anyone help me please?

Thanks in advance.

 

[John Big Booty]

You could populate the second form with a query that uses the clicked row in the data sheet as it's criteria. Which would look something like this;

[forms]![frm_name]![Control_Name]

 

[Nishikawa]

Both data in subform A and B are queries and are totally not related. When user click on a row, Subform A takes a value from the row, place it into a textbox and requery subform B. Subform B uses the value in the textbox as a filter.

My problem is how to make VBA pickup a specify value within a row. i tried this and failed.
Main form = companydetails
textbox in main form = txtbox
subform A = companyname
column in datasheet for subform A = company, reg number, activity
subform B = companydata

Private Sub company_GotFocus()
If IsNull(Me.activity) Then
Forms!companydetails.Form!txtbox = ""

Else
Forms!companydetails.Form!txtbox = Me.activity

End If
If Me.Parent.Name = "companydetails" Then
Forms!companydetails!companydata.Form.RecordSource = "queryname"
Forms!companydetails!companydata.Form.Requery
End If

end sub

 

[maxmangion]

Hi

check this this how you should reference between forms/subforms

Also note that in this line you have double exclamation mark

Else
Forms!companydetailsForm!!txtbox = Me.activity

 

[Nishikawa]

maxmangion,

Thanks for your link. Very good reference source. My subform 1 and 2 are both on the main form. A bit different from what the example was teaching.

 

[John Big Booty]

Yikes not quiet the question I thought I was answering

 

[Nishikawa]

i understand, but someone did a sample database that was so beautifully made and I am still unable to find it in this forum.

 

[John Big Booty]

If you are using a value form Subform A to determine what is being displayed in Subform B.

You should be able to base The query that is populating Subform B on the row selected in Subform A.

If your example EmplyeeID is the link then in the criteria, For EmployeeID, in the Query for SubForm B use

[forms]![Main_Form_Name]![SubformB]![employeeid]

This way you don't need to worry about transferring a value to your textbox. All you need to do is trigger a requery of SubformB whenever the user click on a new record in SubformA

 

[John Big Booty]

You don't mean this search tool do you?

 

[Nishikawa]

Nope, not the cool search tool. I think my problem is me unable to make subform B to requery from subform A

"forms!mainform!subformB.form.requery" does not work from subform A

 

[John Big Booty]

In the on Current event of Subform A put the following;

Forms!FRM_MainFrmName!SubformB.Requery

 

[John Big Booty]

Here's a Sample to play with

 

[Nishikawa]

This is just what I need! You are the man!!!