By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

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

gcoaster
100+
P: 117
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

Expand|Select|Wrap|Line Numbers
  1. Example for Bart Simpson in tblMAIN
  2. MainID FatherID MotherID ChildID SpouseID  FullName 
  3. 1    2    3    0     0        Bart Simpson
  4. 2    3    4    1     3        Homer Simpson
  5. 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

Expand|Select|Wrap|Line Numbers
  1.                      / Homers Dad (txtGfather)
  2.  / Homer Simpson (txtFather)
  3.  |                    \ Homers Mom (txtGmother)
  4. Bart Simpson (cboMASTER 1,FullName)
  5.  |                    / marg's dads dad (txtFather3)
  6.  |             / margs dad (txtGFather2)
  7.   \ Marg Simpson (txtMother)
  8.                 \ 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!
2 Weeks Ago #1

✓ answered by twinnyfo

We (me included) arranged the arguments incorrectly:

Expand|Select|Wrap|Line Numbers
  1. intDad = _
  2.     Nz(DLookup("FatherID", _
  3.                "tblMain", _
  4.                "MainID = " & Me.cboMaster), _
  5.        0)
  6. Me.txtFather = _
  7.     Nz(DLookup("FullName", _
  8.                "tblMain", _
  9.                "MainID = " & intDad), _
  10.        "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.

Share this Question
Share on Google+
29 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
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....
2 Weeks Ago #2

gcoaster
100+
P: 117
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)

Expand|Select|Wrap|Line Numbers
  1. Dim strtxtdad As String
  2. Dim strtxtmom As String
  3. Dim strtxtchild As String
  4. Dim strtxtspouse As String
  5. Dim strtxtGfather As String
  6.  
  7. strtxtsFather = "SELECT * FROM tblMAIN WHERE " & _
  8.     "([MAINID] = cboMASTER)"
  9. strtxtmom = "SELECT * FROM tblMAIN WHERE " & _
  10.     "([FatherID] = txtwhatever1)"
  11. strtxtchild = "SELECT * FROM tblMAIN WHERE " & _
  12.     "([MotherID] = txtwhatever2)"
  13.  
  14. Me.strtxtsFather = strtxtdad 
  15. Me.strtxtspouse = strtxtspouse 
  16. Me.strtxtmom = strtxtGfather 
  17.  
  18. Me.txtFather.Requery
  19. Me.txtMother.Requery
etc etc
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,482
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:

Expand|Select|Wrap|Line Numbers
  1. Dim intDad As Integer
  2. Dim intMom As Integer
  3. Dim intDadsDad As integer
  4.  
  5. intDad = _
  6.     DLookup("FatherID", _
  7.             "tblMain", _
  8.             "MainID = " & Me.cboMaster)
  9. Me.txtFather = _
  10.     DLookup("FullName", _
  11.             "tblMain", _
  12.             "MainID = " & intDad)
  13.  
  14. 'And so for intMom/txtMother
  15.  
  16. intDadsDad = 
  17.     DLookup("FatherID", _
  18.             "tblMain", _
  19.             "MainID = " & intDad)
  20. Me.txtFathersFather = _
  21.     DLookup("FullName", _
  22.             "tblMain", _
  23.             "MainID = " & intDadsDad)
  24.  
  25.  
  26. 'Etc., etc., etc., etc., etc., etc., etc.
Hope this hepps!
2 Weeks Ago #4

Expert 100+
P: 1,236
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
2 Weeks Ago #5

gcoaster
100+
P: 117
Hi Jimatqsi
that sounds awesome! can you give a sample? template? your the man!
2 Weeks Ago #6

gcoaster
100+
P: 117
Hi twinnyfo, can you give me an example this would look like using instead Select From Where? 😎
2 Weeks Ago #7

Expert 100+
P: 1,236
Sure. I need a little time to get dinner and relax first. It's been a looong day.
2 Weeks Ago #8

gcoaster
100+
P: 117
Not a problem! relax.. are you done eating yet? so looking forward to it! refreashing the page.. lol
2 Weeks Ago #9

NeoPa
Expert Mod 15k+
P: 31,761
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.
2 Weeks Ago #10

Rabbit
Expert Mod 10K+
P: 12,427
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).
2 Weeks Ago #11

twinnyfo
Expert Mod 2.5K+
P: 3,482
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.
2 Weeks Ago #12

gcoaster
100+
P: 117
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
2 Weeks Ago #13

Expert 100+
P: 1,236
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
Expand|Select|Wrap|Line Numbers
  1. SELECT 'Father' AS Relation, Relative.FullName
  2. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.FatherID = Relative.MainID
  3. WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
  4.  
  5. union
  6. SELECT 'Mother' AS Relation, Relative.FullName
  7. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.MotherID = Relative.MainID
  8. WHERE (((tblMain.MainID)=[Forms]![Family_frm]!cboMaster))
  9.  
  10. union
  11. SELECT 'Child' AS Relation, Relative.FullName
  12. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.ChildID = Relative.MainID
  13. WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
  14. union
  15. SELECT 'Spouse' AS Relation, Relative.FullName
  16. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.SpouseID = Relative.MainID
  17. WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
  18.  
  19.  
Jim
2 Weeks Ago #14

Expert 100+
P: 1,236
I also have the combo box default value = 1, so the form opens up to the person with MainID=1.
2 Weeks Ago #15

Expert 100+
P: 1,236
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT 1 as Nbr, 'Father' AS Relation, Relative.FullName
  2. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.FatherID = Relative.MainID
  3. WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
  4.  
  5. union
  6. SELECT 2 as Nbr,  'Mother' AS Relation, Relative.FullName
  7. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.MotherID = Relative.MainID
  8. WHERE (((tblMain.MainID)=[Forms]![Family_frm]!cboMaster))
  9.  
  10. union
  11. SELECT 3 as Nbr,  'Child' AS Relation, Relative.FullName
  12. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.ChildID = Relative.MainID
  13. WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
  14. union
  15. SELECT 4 as Nbr,  'Spouse' AS Relation, Relative.FullName
  16. FROM tblMain LEFT JOIN tblMain AS [Relative] ON tblMain.SpouseID = Relative.MainID
  17. WHERE (((tblMain.MainID)=[Forms]![Family_frm]![cboMaster]))
  18.  
  19.  
2 Weeks Ago #16

Expert 100+
P: 1,236
I have not included a sort instruction, so the query will sort by the first column. That's quick and dirty.
2 Weeks Ago #17

gcoaster
100+
P: 117
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

Expand|Select|Wrap|Line Numbers
  1. ' IDIOT CODE
  2. Select * FROM tblMAIN
  3. sqlME: MainID = cboMASTER 
  4. AND also give me rows that match
  5. 'using sqlME somehow below
  6. sqlFATHER: FatherID = MainID
  7. sqlMOTHER: MOtherID = MainID
  8. then fill the text boxes on form with the FullName column
  9. sqlFATHER = txtFather ( fullname )
  10. sqlMOTHER = txtMother ( fullname )
  11.  
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMASTER_AfterUpdate()
  2. Dim sqlMOTHER As String
  3. Dim db As Database
  4. Dim rs As DAO.Recordset
  5.  
  6. sqlMOTHER = "SELECT * FROM tblMAIN WHERE MainID = " & Forms![MAIN]![cboMASTER] & ";"
  7.     Set db = CurrentDb
  8.     Set rs = db.OpenRecordset(sqlMOTHER)
  9.     Me.txtMother.Value = rs!FullName
  10.     Set rs = Nothing
  11.     Set db = Nothing
  12. End Sub
  13.  
2 Weeks Ago #18

twinnyfo
Expert Mod 2.5K+
P: 3,482
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.
2 Weeks Ago #19

gcoaster
100+
P: 117
Hi jimatqsi,
what if I where to use a nested Dlookup vs sql querys joined with unions?
2 Weeks Ago #20

gcoaster
100+
P: 117
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.


Expand|Select|Wrap|Line Numbers
  1. Dim intDad As Integer
  2. Dim intMom As Integer
  3. Dim intDadsDad As integer
  4.  
  5. intDad = _
  6.     DLookup("FatherID", _
  7.             "tblMain", _
  8.             "MainID = " & Me.cboMaster)
  9. Me.txtFather = _
  10.     DLookup("FullName", _
  11.             "tblMain", _
  12.             "MainID = " & intDad)
  13.  
  14. 'And so for intMom/txtMother
  15.  
  16. intDadsDad = 
  17.     DLookup("FatherID", _
  18.             "tblMain", _
  19.             "MainID = " & intDad)
  20. Me.txtFathersFather = _
  21.     DLookup("FullName", _
  22.             "tblMain", _
  23.             "MainID = " & intDadsDad)
  24.  
2 Weeks Ago #21

gcoaster
100+
P: 117
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?

Expand|Select|Wrap|Line Numbers
  1. intDad = _
  2.     DLookup(Nz("FatherID", _
  3.             "tblMain", _
  4.             "MainID = " & Me.cboMaster))
  5. Me.txtFather = _
  6.     DLookup(Nz("FullName", _
  7.             "tblMain", _
  8.             "MainID = " & intDad))
2 Weeks Ago #22

twinnyfo
Expert Mod 2.5K+
P: 3,482
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:

Expand|Select|Wrap|Line Numbers
  1. intDad = _
  2.     DLookup(Nz("FatherID", _
  3.             "tblMain", _
  4.             "MainID = " & Me.cboMaster), 0)
  5. Me.txtFather = _
  6.     DLookup(Nz("FullName", _
  7.             "tblMain", _
  8.             "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!
2 Weeks Ago #23

gcoaster
100+
P: 117
This is error is causing headaches! any ideas to get this to work? I have a feeling this is incomplete.

1 Week Ago #24

twinnyfo
Expert Mod 2.5K+
P: 3,482
We (me included) arranged the arguments incorrectly:

Expand|Select|Wrap|Line Numbers
  1. intDad = _
  2.     Nz(DLookup("FatherID", _
  3.                "tblMain", _
  4.                "MainID = " & Me.cboMaster), _
  5.        0)
  6. Me.txtFather = _
  7.     Nz(DLookup("FullName", _
  8.                "tblMain", _
  9.                "MainID = " & intDad), _
  10.        "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.
1 Week Ago #25

gcoaster
100+
P: 117
Expand|Select|Wrap|Line Numbers
  1. Dim intDad As Integer
  2.    intDad = Nz(DLookup("MainID", "tblMain", "MainID= " & Me.cboMASTER), 0)
  3.    Me.txtFather = Nz(DLookup("FullName", "tblMain", "MainID= " & intDad), "No Relative Found")
  4. End Sub
  5.  

Produces this error



Shouldnt it be something like
Expand|Select|Wrap|Line Numbers
  1. Me.txtFather.Value = 
1 Week Ago #26

twinnyfo
Expert Mod 2.5K+
P: 3,482
See Post #23 again...................
1 Week Ago #27

gcoaster
100+
P: 117
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!!!

1 Week Ago #28

twinnyfo
Expert Mod 2.5K+
P: 3,482
Glad I could hepp!

Let us now if you run across any other sticklers!
1 Week Ago #29

gcoaster
100+
P: 117
Oh don't you worry, I will be back soon, And I wouldn't go anywhere else! :-) thanks again.
1 Week Ago #30

Post your reply

Sign in to post your reply or Sign up for a free account.