Changing a subform based on another subform

[EBGreen]

Here is my situation. I have a form that has two subforms (lets call them left and right). The left one is essentially a table view of several records. The right one is a record view of the detail information for the same query as the left subform. What I need to do is to make it so that if you click on a line in the left subform, then the right one automatically goes to the details for that record. Any ideas? I will try to post some of the code that I have tried, but its Monday and I'm putting out fires. I had hoped that this is something that someone has done before and has some sample code laying around because nothing I've tried works. Thanks in advance.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[M8KWR]

I have done something similar, but i cheated. Because I could not find a way to do this.

What i did was create a blank text box on the main form, everytime you click in the left subform update this blank field with the ID number that would link the two subforms.

Then once this text box has been updated, refresh the right subform. You would need to change the right subform query to have it look at this blank text box on the form to get its ID value from.

Hope this helps.

 

[EBGreen]

Thanks. I'll give that a try. Unfortunately, I suspect that the person who is having me do this will want to have his cake and eat it to. I think he will still want to be able to move from record to record on the right subform in addition to clicking a line in the left subform.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[M8KWR]

Just tell them, that there has to be a link, they should know what they are changing in the right subform related to the left subform, or is does not make sense.

You could write some code to display all the records.

Have a button saying "Display All", clear the blank text box, and put some code like,

if textbox is null(textbox) then

else

end if

etc etc etc

 

[RoyVidar]

What have you tried?

Ordinarily, one would use one (or more) of the events of the control(s) on the left form and use something like this:

[tt]dim rs as dao.recordset
set rs=me.parent!frmLeft.Form.RecordsetClone
rs.findfirst "PKfield=" & me!txtPkControl.value
if not rs.nomatch then
me.parent!frmLeft.Form.bookmark=rs.bookmark
end if[/tt]

Note however, that the subform reference (frmLeft here), should be the subform control name, which might differ from the subform name as viewed in the database window. See How to refer to a control on a subform or subreport in Access 2000 for more info.

Roy-Vidar