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-complexification 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 = " & intDad)
Me.txtFathersFather = _
    DLookup("FullName", _
            "tblMain", _
            "MainID = " & intDadsDad)
 
 
'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.

[jimatqsi]

If you want relatives to come out in a selected order, you could do something like this. Add a column to the list box for the counter, make it visible or not, as you choose.

SELECT 1 as Nbr, '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 2 as Nbr,  '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 3 as Nbr,  '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 4 as Nbr,  'Spouse' AS Relation, Relative.FullName
FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.SpouseID = Relative.MainID
WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
 
 

[jimatqsi]

I have not included a sort instruction, so the query will sort by the first column. That's quick and dirty.

[gcoaster]

Hi,
this looks like a SQL behind a query
yea ok that gives me ideas, and thanks.
but this is not a full clear concise answer or soultion

When it comes to applying this to VBA in ACCESS behind a form on load or combo box after update its not going to work.

what I am looking for is VBA in MS Access formatted with all of the qoutes and " & _ "'" etc etc


Here let me spell it out in my limited knowledge

' IDIOT CODE
Select * FROM tblMAIN
sqlME: MainID = cboMASTER 
AND also give me rows that match
'using sqlME somehow below
sqlFATHER: FatherID = MainID
sqlMOTHER: MOtherID = MainID
then fill the text boxes on form with the FullName column
sqlFATHER = txtFather ( fullname )
sqlMOTHER = txtMother ( fullname )
 

I am looking for something like this below
I would use join or union somewhere.. not sure where! I am only dealing with one table tblMAIN, not sure how to match the combo box id with MAINID and then find another row in table matching the FatherID with MainID

Private Sub cboMASTER_AfterUpdate()
Dim sqlMOTHER As String
Dim db As Database
Dim rs As DAO.Recordset
 
sqlMOTHER = "SELECT * FROM tblMAIN WHERE MainID = " & Forms![MAIN]![cboMASTER] & ";"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlMOTHER)
    Me.txtMother.Value = rs!FullName
    Set rs = Nothing
    Set db = Nothing
End Sub
 

[twinnyfo]

gcoaster,

1. We typically don't work complete solutions for posters here, but guide you in the right direction.

2. Whether you aim to fill each text box individually or use a list box like Jim has suggested, you still must create individual queries to find each individual person. Even Jim's solution does that--a UNION QUERY is nothing more than the results of several queries.

As we mentioned at the outset, what you are asking for here is basically very simple: Create a query to find each individual and assign that value to a text box: EASY. But, it is difficult, because of the complexity of the project. All you have to do is go back three generations and you are now building queries based upon queries based upon a query: Technically speaking, this is simple, but keeping it all straight is more complicated.

The bottom line here is that no one will be able to give you "the answer" that you are apparently looking for. There is no, simple "four lines of code" that will fill all your text boxes with the people that you are trying to find. It just can't be done that easily.

We've also mentioned issues with normalization--this is an issue no matter what. We've mentioned issues with the complexities of family structure. All of these recommendations and cautions--thus far--have fallen on unreceptive ears.

We are trying to hepp, but you have to work with us and put forth some effort to work toward a solution yourself. We understand that VBA is not your first language, and we've tried to make allowances for that. But, we can't tutor you through the basics here. Most of us all have full-time jobs and Bytes is a side hobby.

Unless you put forth the effort to try what we have suggested and work toward a solution yourself, we are unable to assist you more than we already have.

[gcoaster]

Hi jimatqsi,
what if I where to use a nested Dlookup vs sql querys joined with unions?

[gcoaster]

twinnyfo, yea busy weekend sorry for not getting back to you.
had to read up on Dlookup
I notice there are different formats for different data types

DLookup(Expr,Domain,[Criteria])

DLookup("FieldName" , "TableName" , "Criteria = n")

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

DLookup("FieldName" , "TableName" , "Criteria= #date#")


trying this out now.

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 = " & intDad)
Me.txtFathersFather = _
    DLookup("FullName", _
            "tblMain", _
            "MainID = " & intDadsDad)
 

[gcoaster]

Ok, tried solution
It just might work!!!!!

get an error

when value is zero?

So now I nneed to figure out Nz, and how to add it
maybe? or an if then else?
IsNull?

maybe like this?

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

[twinnyfo]

gcoaster,

Your error is because you have assigned a value to the .ControlSource property of your text box. Once you do that in the form design, you are unable to assign a value to it through VBA. You need to remove any .ControlSource values you have for your controls. Then it should work--even if your code returns null values.

However, searching using Nulls can always be a problem.

When using Nz(), your code above is almost there. You need to designate what the returned value is if it is null:

intDad = _
    DLookup(Nz("FatherID", _
            "tblMain", _
            "MainID = " & Me.cboMaster), 0)
Me.txtFather = _
    DLookup(Nz("FullName", _
            "tblMain", _
            "MainID = " & intDad), "No Relative Found")

Notice how in the first example, we are looking for an integer--so, the returned value for Null is 0. In the second example, we are looking for a text value, so, we return a text string that will make sense to the user.

Hope this hepps!

[gcoaster]

This is error is causing headaches! any ideas to get this to work? I have a feeling this is incomplete.

[twinnyfo]

We (me included) arranged the arguments incorrectly:

intDad = _
    Nz(DLookup("FatherID", _
               "tblMain", _
               "MainID = " & Me.cboMaster), _
       0)
Me.txtFather = _
    Nz(DLookup("FullName", _
               "tblMain", _
               "MainID = " & intDad), _
       "No Relative Found")

I also realigned the text so you can see which argument go with which function.

Over time, you will learn to identify and correct these errors more quickly and by yourself.

Hope that hepps.

[gcoaster]

Dim intDad As Integer
   intDad = Nz(DLookup("MainID", "tblMain", "MainID= " & Me.cboMASTER), 0)
   Me.txtFather = Nz(DLookup("FullName", "tblMain", "MainID= " & intDad), "No Relative Found")
End Sub
 

Produces this error

Shouldnt it be something like

Me.txtFather.Value = 

[twinnyfo]

See Post #23 again...................

[gcoaster]

Ahh thank you!

You need to remove any .ControlSource values you have for your controls. Then it should work--even if your code returns null values.

Before

After

It Works! thanks twinnyfo!!!

[twinnyfo]

Glad I could hepp!

Let us now if you run across any other sticklers!

[gcoaster]

Oh don't you worry, I will be back soon, And I wouldn't go anywhere else! :-) thanks again.