VBA Populates multiple Text Boxes from Table based on Combo Box Selection on Form

[gcoaster]

Hi
I have one combo box (cboMASTER)
What I would love to do is select a name from this combo box and I would like about 20 text boxes to populate based on the value of column 0 in the combo box, that is the record ID

cboMASTER = 1,FullName

What the form will show is a family tree. And will show relatives based on who I select in a combo box cboMASTER
It will show mother, father, grand father etc etc

I have 1 table
In the table there is MainID, FatherID, MotherID, ChildID, SpouseID etc

Example for Bart Simpson in tblMAIN
MainID FatherID MotherID ChildID SpouseID  FullName 
1	2	3	0	 0        Bart Simpson
2	3	4	1	 3        Homer Simpson
3	5	6	1	 2        Marg Simpson

So if I select lets say Bart Simpson who has an ID of 1, and motherid is 3 etc etc, the form will look like something like this and show FullName

                     / Homers Dad (txtGfather)
 / Homer Simpson (txtFather)
 |                    \ Homers Mom (txtGmother)
Bart Simpson (cboMASTER 1,FullName)
 |                    / marg's dads dad (txtFather3)
 |             / margs dad (txtGFather2)
  \ Marg Simpson (txtMother)
                \ margs mom (txtGmother2)

How would I code this in Access VBA?
It will requery all text boxes on cboMAIN change update
Would I do a SQL Statement like Select * from where for each text box based on the value of the of the combo box?
Hard to word this sorry! thanks in advanced!

[twinnyfo]

gcoaster,

Technically speaking, the way to solve this is very "simple." However, it is not very easy.

All you need to do is trigger a series of requeries on your other textboxes. The first text box (for example the father of the person selected in the combo box), you would have the VBA search for the FatherID of the person selected and return the value of that ID. Do the same for the mother. Etc., etc., etc., etc. However, you quickly come to realize that as "simple" as the concept is, it is not very easy, because each text box will have to have its own query assigned to it, and some queries will be incredibly complex, because it will be querying queries upon queries upon queries to find, for example, the great-great-great-great-grand parents.

What you are trying to do in a db "may" be possible, but I haven't sat down and thought about it long enough to say it can't, or to recommend a "better way."

On the surface, I would say that many times, re-inventing a tremendously complex wheel (when numerous off-the-shelf wheels are readily available) defeats the purpose of building the wheel in the first place, unless you are planning to build a wheel that is so much better than anyone could have imagined a wheel to be.

All this said, the over-complexificatio n that your proposed design entails is probably beyond what anyone here might be willing to wade into--but I could be wrong. I would recommend purchasing a family tree program and save yourself hours of headaches....

[gcoaster]

Hi twinnyfo
Apreaciate taking the time to respond.
not a problem for me to write queries for each txtbox
Its keeping my access sql skills fresh, every IT job I have applied for this skill is a plus, and its a win win when i go back to work in IT after this covid19 passess (not too confident it will anytime soon but anyways)

would it look similour to this? (this is a rough example)

Dim strtxtdad As String
Dim strtxtmom As String
Dim strtxtchild As String
Dim strtxtspouse As String
Dim strtxtGfather As String

strtxtsFather = "SELECT * FROM tblMAIN WHERE " & _
    "([MAINID] = cboMASTER)"
strtxtmom = "SELECT * FROM tblMAIN WHERE " & _
    "([FatherID] = txtwhatever1)"
strtxtchild = "SELECT * FROM tblMAIN WHERE " & _
    "([MotherID] = txtwhatever2)"

Me.strtxtsFather = strtxtdad 
Me.strtxtspouse = strtxtspouse 
Me.strtxtmom = strtxtGfather 

Me.txtFather.Requery
Me.txtMother.Requery

etc etc

[twinnyfo]

1. You've dim'ed all your variables wrong (I know you tried to fix it at least once). You need to dim Integers, so that you can find the other values you are looking for.

2. You need to remember to extract the actual value of your combo box, rather than make it text.

3. Remember that a SQL string is a string. So you will just end up with the actual string and not the results of the query in your text boxes. You need to use (dare I say it) Domain Aggregate queries.

4. Yes - to a certain degree that is the general approach. But, remember, for the grandfather, you need to find out first who the father is (or mother), and then, find out who the father is for that person.

So:

Dim intDad As Integer
Dim intMom As Integer
Dim intDadsDad As integer

intDad = _
    DLookup("FatherID", _
            "tblMain", _
            "MainID = " & Me.cboMaster)
Me.txtFather = _
    DLookup("FullName", _
            "tblMain", _
            "MainID = " & intDad)

'And so for intMom/txtMother

intDadsDad = 
    DLookup("FatherID", _
            "tblMain", _
            "MainID = " & [B][U]intDad[/U][/B])
Me.txtFathersFather = _
    DLookup("FullName", _
            "tblMain", _
            "MainID = " & [B][U]intDadsDad[/U][/B])


'Etc., etc., etc., etc., etc., etc., etc.

Hope this hepps!

[jimatqsi]

I'd suggest using a list box with 20 independent queries in a union for the rowsource. It beats messing with all those text boxes. Each query in the union would include where criteria to compare the particular ID needed against Forms!{formname }!cboMaster.

Jim

[gcoaster]

Hi Jimatqsi
that sounds awesome! can you give a sample? template? your the man!

[gcoaster]

Hi twinnyfo, can you give me an example this would look like using instead Select From Where? 😎

[jimatqsi]

Sure. I need a little time to get dinner and relax first. It's been a looong day.

[gcoaster]

Not a problem! relax.. are you done eating yet? so looking forward to it! refreashing the page.. lol

[NeoPa]

I've been too busy to respond properly today after I saw this earlier, but consider the concept of designing an underlying query that contains the required codes and then creating all the ComboBoxes as Bound Controls.

Unless I misunderstand the situation, the ControlSource needn't change. Only the value selected within the list needs to change based on the underlying data.

[Rabbit]

If this is intended for real world use, you may want to give a long hard look at the underlying table design first.

There are a few normalization issues that will cause you headaches if anything ever gets out of sync.

In addition to that, the way you have relationships set up will break when faced with real world scenarios such as adoptions, surrogate pregnancies, same sex couples, incest (especially if you're building a family tree far back in time).

[twinnyfo]

I also echo Rabbit's concerns, not to mention multiple children, which is going to be a much more common occurrence with a family tree.

[gcoaster]

HI
not worried about normalization, this will not be used in a production environment

adoptions are not a concern, surrogate pregnancies none, same sex couples not an issue (homosexuals do not procreate), incest (especially if you're building a family tree far back in time) yea there is some of that but its not a problem

[jimatqsi]

Gcaoster, I am sorry, I forgot all about this.Here you go ... Only things on my form are a combo box and a listbox. The combo box bound column is the MainID column. The list box has 2 columns. This code is the rowsource for the combo box

SELECT 'Father' AS Relation, Relative.FullName
FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.FatherID = Relative.MainID
WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))

union
SELECT 'Mother' AS Relation, Relative.FullName
FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.MotherID = Relative.MainID
WHERE (((tblMain.MainID)=[Forms]![Family_frm]!cboMaster))

union
SELECT 'Child' AS Relation, Relative.FullName
FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.ChildID = Relative.MainID
WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
union
SELECT 'Spouse' AS Relation, Relative.FullName
FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.SpouseID = Relative.MainID
WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))

Jim

[jimatqsi]

I also have the combo box default value = 1, so the form opens up to the person with MainID=1.