Controls in Main Form & Subform (1 Viewer)

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
This is my first post in this Forum.

I am creating a Family Tree Database. tblPersons contains the fields, PersonID, FirstName, MiddleName, LastName , Gender & relevant Dates & data.

Persons are added in the Main Form which contains a Subform where Children are added related to the PersonID on the Main Form.

The Main Form contains two Combo Boxes where the Mother & Father can be selected. If not in the tblPersons, it can be added through Not In List Event.

What I need is that when the Mother Combo Box or Father Combo Box are double clicked, the Main form should show the PersonID of the mother or father, so that other data such as dates can be entered.

I also want when the PersonID in the Sub Form is double clicked, the main form should display that persons data.

I request experts to provide me the VBA code for both the actions.

Alex
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:09
Joined
Jul 9, 2003
Messages
13,553
Hi, welcome to the forum.

What is the record source of the main form?
 

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
Hi, welcome to the forum.

What is the record source of the main form?
Thank you,
tblPersons is the record source of the Main Form. In fact the entire Persons are in that table. I linked the Subform to the Main form through a Query.
 

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
The SQL of the Query on which the Subform is based:

SELECT tbl_Person.*
FROM tbl_Person
WHERE (((tbl_Person.MotherID)=[Forms]![Frm_Persons]![PersonID])) OR (((tbl_Person.FatherID)=[Forms]![Frm_Persons]![PersonID]));
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:09
Joined
May 7, 2009
Messages
14,607
my sample family tree.
 

Attachments

  • FTree.accdb
    692 KB · Views: 83

Cotswold

Member
Local time
Today, 23:09
Joined
Dec 31, 2020
Messages
57
You could take a look at the Microsoft Template. Do an internet search for :
Microsoft Access Family Tree Genealogy History Templates Database

If your database analysis is similar it may be helpful. However, it only records maybe ¼ of the data you will need, difficult to expand and is a bit noddy. It is worth a look though, if only to indicate exactly how not to name the Field names in tables* It also uses Macros for just about everything. Which I simply cannot understand in something designed as a new user training aid.
Except for the Autoexec I advise you to use VBA code and not Macros, which are difficult to maintain. Access can be described as a system of doors behind doors, but Macros are akin to the stuff that drops through the cracks in the floor, never to be seen again.

For your Family History I'd advise you to add a record in your tblPerson as "Unknown", then as more data becomes available you can change update. I'd also have an "Unknown" in any of the address tables you haver, as many of these will not be known in the early days. You can then change from Unknown as you come across the information.

* In one Microsoft Template example I have even seen a "/" used as part of a Field Name!
 

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
Mine is different. What I need is when the Combo Box on the Main Form is double clicked, the data should change in the main form, Also when the PersonID in the subform is double clicked, the main form data should change.
 

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
Main Form.jpg


This is the screen shot of the Main Form. What I want is when the Mother (Or Father) Combo Box is Double Clicked, the PersonID should change to that of the PersonID of the Combo Box and display the related data.
Since all data come from tblPersons, to link the Subform PersonID to that of the Main Form, the sub form data source is as per the SQL I have given above.
Same way when the PersonID in the Subform is double clicked, the main form PersonID should change to that of the Sub Form and display the related data.
I hope the screen shot clarifies what I need.

Thanks
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:09
Joined
Jul 9, 2003
Messages
13,553
Mine is different.
Yes, it certainly is!

I didn't come back earlier because I thought you might explore and use arnelGPs solution.

My first impression of your database is that you are taking the wrong approach, and I don't think it will work.

Having said that, some clever VBA code might well make it work, but that's not the point. You are going about it in the wrong way right from the start.

Even if you can make it work, it just won't work in the long run.

I think you need to take a step back, get some advice from the experts here on how to redesign your database to take advantage of the the inherent abilities of Microsoft Access.
 

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
I am glad to inform that I succeeded in the Double Click event of both the Combo Boxes in the Main Form:
Code:
Private Sub cmbFather_DblClick(Cancel As Integer)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = " & Str(Nz(Me![cmbFather], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
--------------------------------------------------------------------
Private Sub cmbMother_DblClick(Cancel As Integer)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = " & Str(Nz(Me![cmbMother], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
---------------------------------------------------------------------------
Still I have not succeeded in the Double Click Event of the PersonID control in the Subform.

I hope experts can help me.
Thanks

Alex
 

Alex Motilal

New member
Local time
Tomorrow, 04:39
Joined
Jul 7, 2021
Messages
15
Hi,
I succeeded by placing the following code.

Private Sub PersonID_DblClick(Cancel As Integer)
Dim strCriteria As String
strCriteria = "PersonID = " & Me.PersonID

Me.Parent.Form.Filter = strCriteria
Me.Parent.Form.FilterOn = True
End Sub

The problem is by clearing the filter, it goes to the first record. I want to remain in the same record. Please suggest the code to be added in the DblClick event.

Thanks,
Alex
 

Users who are viewing this thread

Top Bottom