Double click in subform to cause main form to go to selected record (1 Viewer)

[Laurad]

I have a simple form with a datasheet subform which could have more than one row of records. I want to be able to double-click on one of the records in the sub form to make the main form locate a record based on the row selected in the sub form.

The main form has people's names in it and the subform/datasheet has the same database of names, they cross-match as I'm working on Ancestry DNA matches.

What coding should I use?

 

[jdraw]

Here's a generic data model for genealogy set up that may be helpful.

You're asking about forms, but forms act as a "window/s" onto your tables.
Good luck.

 

[Laurad]

Thanks for your quick reply. I do have very good family tree software, but I have been writing my own database to compare Ancestry.com DNA matches. Each match has their own Main form, just a name with a sub form with all the matches for that one person. Each of the people in the subform are also members of the MainForm. So, if Anne matches with Peter and Paul and Fred, I want to be able to click on Fred to see who Fred also matches with as his matches may include some of the same people and other people.

Is there a way of double-clicking a row in the subform/datasheet to make the main form locate the person listed in the subform?

Thanks,
Laura

 

[jdraw]

Can you post a copy of your database with a few records to show your form and you compare logic? No need for the full data base of names etc.

I'm sure the functionality is possible, but details may temper a solution.
Look through the post in similar threads at bottom of the page for related posts.

 

[Laurad]

Hi jdraw, Thanks for your help.



I have about 350 records - people who DNA match with me and/or my brother. By comparing who matches with whom in my database I can begin to narrow down how we are related. In some cases my matches match with other people who I also match with - it's bringing all those matches together that helps me pinpoint. As I mentioned, I already have good genealogy software on my computer - this is specifically for comparing DNA matches on Ancestry.



I attach a small database with a form and sub form. I've removed most of the records and left 13. I've removed surnames. What I'm trying to achieve is a way of showing the name of my match at the top of the main form frmPersons along with a lot of other information and notes (which I have not included in this example), and in the sub form (datasheet) subfrmLnkDNAandPersons the list of people we both match to.


I want to be able to double-click on a name in the sub form to make the main form "findfirst" the record for the name I double-clicked on. I hope this is clear. It does mean the whole form will refresh, I guess. I don't know if this is a bad idea, but it's sort of how I want it to work. I could open a new form, but if I then double-click on a name in the new form - another form will open, and so on. That's not what I want.



Also, I know this is a bit circular in some ways, but the thing is that these are all the people who match to me one way or another.



tblDNAMatches is my main table and will contain more fileds with all the extra information about each individual
tblPersons is going to be my lookup table for all of my DNA matches.

tblLnkDNAandPersons is a link table with the ID of tblDNAMatches and tblPersons


I hope this is clear and thanks again.
Laura

 

[jdraw]

Try this. I'm not sure exactly what the details of requirement are.

 

[Laurad]

Thankd jdraw - it does just what I need. I was trying to overcomplicate, I think. It's a neat, simple solution and I can use it in other databases where I need to locate a record with a double-click from the datasheet record.



Just to clarify for those who don't look at the actual database which I uploaded:


Private Sub Persons_DblClick(Cancel As Integer)
Me.Parent.RecordSource = "Select * from tblDNAMatches where ID = " & Me.Persons
Me.Requery
End Sub


Thank you for helping, I can move on now and find my ancestors, or at least their origins.
Laura

 

[jdraw]

Happy to help.
Good luck with your project.

 

[Laurad]

I'm back, I've been populating my database and the double-click is working really well to locate records on the Main form from the sub form. Unfortunately, though, it seems to filter the main form to just the one record I have linked to and although I can keep double-clicking from the sub form I cannot then scroll through all the rest of the records on the main form as only one is visible.



There doesn't seem to be a "filter" although I guess the records are filtered to the one I clicked through to.



I've tried:-

Forms!frmPersons.Refresh
Forms!frmPersons.Requery
Me.Parent.Refresh
Me.Parent.Requery


Nothing causes the rest of the records to be available. I have to close the form and reopen it. This is not ideal.



I hope you can help.

Laura

 

[jdraw]

Laura,

Can you detail the requirement with some sample data?
When I saw your original post, it seemed you wanted to select (by double clicking in the subform) to put that record on the main form and show the mainform/subform relationships for that selected person.
You know the intricacies of your requirement and readers do not.

If you just want to go back to the mainform - as you do when the form first opens - then I suggest you record the original recordsource of the Form, then reuse that when you want to go back to the "general" data for the form.

You could have a separate button that when clicked - requeries the form with the original recordsource.

Good luck.

Update: I realized I still have a copy of your database.

Try this ( I added a UNION Select in the recordsource)

Private Sub Persons_DblClick(Cancel As Integer)
MsgBox ("I want to be able to double click this box and have the main form 'frmPersons' move to the person I double-clicked on " & Me.Persons.Column(0) & " named " & Me.Persons.Column(1))
Me.Parent.RecordSource = "Select * from tblDNAMatches where ID = " & Me.Persons & _
                " Union Select * from tblDNAMatches where ID <> " & Me.Persons
Me.Requery
End Sub

 

[Laurad]

Thanks, the button on the form was the simplest solution.



Sorry, I really did try to explain what I was trying to achieve, but realise it's not always clear to the reader.



Thanks again