Display Multiple Values in Text Boxes based on a Combo Box Value

Question

I have one table - tblMAIN
I have 4 ID Fields - mainID, FatherID, MotherID and FullName
1 Form - frmMAIN
1 Combo Box - cboMAIN
2 Text Boxes - txtFATHER, txtMOTHER

I am trying to write a SQL statement in VBA that will select a record in the combo box cboMAIN and by doing that selection, two text boxes are populated.

Robert is selected in the cboMAIN, Robert has a MainID of 20
Robert mother is Ruth, she has a MainID of 30
Robert's father's MainID is 40

So in txtFather it will display record 40 / FullName and in txtMother it will display FullName for record MainID30.

I would like to add text fields and show Ruth's mother and Robert's father's father.

Here is an idea I have, but not sure what to do next.

Dim sqlME As String
Dim sqlFATHER As String
Dim db As Database
Dim rs As DAO.Recordset

sqlFATHER = "SELECT * FROM tblMAIN WHERE MainID = " & Forms![MAIN]![cboMAIN] & ";"

'AND NOT SURE WHAT I NEED TO DO HERE!

 Set db = CurrentDb
 Set rs = db.OpenRecordset(sqlFATHER)
 Me.txtFather.Value = rs!FullName
 Set rs = Nothing
 Set db = Nothing

June7 June7 · Accepted Answer · 2020-06-27T23:38:57

VBA setting value of UNBOUND textbox will show same value for every record.

This data is recursive in nature and Access SQL doesn't easily manipulate data of recursive nature. Other database platforms have utility to better deal with.

Build a self-join query object named qryAncestors:

SELECT tblMain.mainID, tblMain.FullName, tblMain_1.FullName AS Father, 
tblMain_2.FullName AS Mother, tblMain_3.FullName AS PGFather, 
tblMain_4.FullName AS PGMother, tblMain_5.FullName AS MGFather, tblMain_6.FullName AS MGMother
FROM tblMain AS tblMain_6 
RIGHT JOIN (tblMain AS tblMain_5 
RIGHT JOIN (tblMain AS tblMain_4 
RIGHT JOIN (tblMain AS tblMain_3 
RIGHT JOIN (tblMain AS tblMain_2 
RIGHT JOIN (tblMain AS tblMain_1 
RIGHT JOIN tblMain 
ON tblMain_1.mainID = tblMain.FatherID) 
ON tblMain_2.mainID = tblMain.MotherID) 
ON tblMain_3.mainID = tblMain_1.FatherID) 
ON tblMain_4.mainID = tblMain_1.MotherID) 
ON tblMain_5.mainID = tblMain_2.FatherID) 
ON tblMain_6.mainID = tblMain_2.MotherID;

Then options to use that query:

reference query as combobobox RowSource then textbox ControlSource references combobox columns by index =[cboMain].Column(2)
textbox ControlSource uses DLookup() expression, such as:
=DLookUp("Father", "qryAncestors", "mainID=" & mainID)
textbox ControlSource calls VBA custom function to return a value, like:
=GetAncestor(mainID, "Father")

Function GetAncestor(intID As Integer, strAnc As String)
GetAncestor = DLookUp(strAnc, "qryAncestors", "mainID=" & intID)
End Function

If you want to go beyond grandparents to any level, approach would have to be quite different. Recursive procedure is tricky. A function to return FullName of ancestor could be like:

Function GetAncestor(intID As Integer, intGen As Integer, strParent As String)
Dim x As Integer
GetAncestor = intID
For x = 1 To intGen
    GetAncestor = DLookup(strParent, "tblMain", "mainID=" & Nz(GetAncestor,0))
Next
GetAncestor = DLookup("FullName", "tblMain", "mainID=" & Nz(GetAncestor,0))
End Function

To get ancestor of specific generation, call function: GetAncestor(mainID, 1, "MotherID")

Display Multiple Values in Text Boxes based on a Combo Box Value

1 Answers