473,570 Members | 3,027 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

gcoaster
117 New Member
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!
Jun 24 '20 #1
29 3318
twinnyfo
3,653 Recognized Expert Moderator Specialist
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....
Jun 24 '20 #2
gcoaster
117 New Member
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
Jun 24 '20 #3
twinnyfo
3,653 Recognized Expert Moderator Specialist
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!
Jun 24 '20 #4
jimatqsi
1,275 Recognized Expert Top Contributor
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
Jun 24 '20 #5
gcoaster
117 New Member
Hi Jimatqsi
that sounds awesome! can you give a sample? template? your the man!
Jun 24 '20 #6
gcoaster
117 New Member
Hi twinnyfo, can you give me an example this would look like using instead Select From Where? 😎
Jun 24 '20 #7
jimatqsi
1,275 Recognized Expert Top Contributor
Sure. I need a little time to get dinner and relax first. It's been a looong day.
Jun 24 '20 #8
gcoaster
117 New Member
Not a problem! relax.. are you done eating yet? so looking forward to it! refreashing the page.. lol
Jun 25 '20 #9
NeoPa
32,564 Recognized Expert Moderator MVP
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.
Jun 25 '20 #10

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

Similar topics

0
1798
by: James | last post by:
Hi everyone, I wanna put serveral multiple text boxes on page by logic:iterate in struts, and then get values of these multiple text boxes. But that makes me headache, Could anyone give me a hand?
2
1976
by: Randy | last post by:
I am new to Access. I see how to use Format Painter and can select a field to format, but how do you format multiple text boxes after you have selected the box you want to copy format. I have to keep going back to the original text box and selecting boxes one by one. Thanks for any help.
2
2851
by: jonvan20 | last post by:
I have been having trouble with a simple Dlookup command that was Reccommended to me by a nice fellow named Vic, On the other hand I have statements like this that wont run they give me a run time error Dim QuoteNumber As String QuoteNumber = Me.txtQuoteNumber Me.txtDealerNumber = DLookup("Dealer_Number", "Quote Table",...
6
8260
by: zuchowra | last post by:
Hi everyone. I need help. I have a combo box in my form that i want to populate multiple text boxes after you select your selection in the combo box. Here is my set up. The Fields that need to be populated are lsited below but must be recorded to a new table. Gets info from Course occurance Table. Combo Box - Gets Information from Course...
1
1237
by: djsmiler | last post by:
Hi All I am new to all this. I have a form which is pulling data from a table into a combo box. I am trying to get text boxes to show me the fields from the combo box. But not sure how to do it. From my table I am displaying branch code in the combo box and need to see Branch Name, Telephone Number,Brand,Area Code and Home Feeder. ...
10
2682
by: Dracenfels | last post by:
I need to write a form that asks the usser to define the number of problems (this is simply from a dropdown menu), depending on the number they select they will be asked for a definition of each problem in a seperate text box (one text box for each problem). So if they pick 2 as the number of problem they will be given two text boxes, 5 boxes for...
2
1518
by: cynderborg | last post by:
I have three text boxes that are horizantally next to each other. I would like to create the look of a table on the report. The borders of all three text boxes will be visible in the report. What I need to do is ensure that if one of the boxes grows vertically, all three become the same height. That way the section keeps the "table" look to...
7
5794
by: jotr | last post by:
I need help writing a Java Script for Adobe Acrobat Prof 7.0. What I am trying to do is have a list of Cities in a drop down box. When a specific city is selected there are 4 other text boxes that are populated with emergency contact numbers. So to be specific the drop down list is made up of San Angelo and Mertzon. When the user selects San...
4
2373
by: dekk | last post by:
I am trying to populate a text field from the combo selection. I have the following update event in the combo box Private Sub cboContract_AfterUpdate() Me!txtDesc = Me!cboContract.Column(1) Me!txtPrinc = Me!cboContract.Column(2) Me.Refresh Me.tbl_TimeData.Form.cboAct.Requery End Sub
2
2204
by: zsolt szabo | last post by:
hi, im having problems writing multiple text boxes in one file. i`ve got a code, but its stands for save only, and delete any previous information from the file. my code: private sub command1_click() Dim sTextMixed As String 'Mixing the textboxes sTextMixed = Text5.Text & vbCrLf & Text4.Text 'Saving it
0
7732
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7638
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6336
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5523
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5247
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
974
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.