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

[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,Do main,[Criteria])

DLookup("FieldN ame" , "TableName" , "Criteria = n")

DLookup("FieldN ame" , "TableName" , "Criteria= 'string'")

DLookup("FieldN ame" , "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)[B][U][I], 0[/I][/U][/B])
Me.txtFather = _
    DLookup(Nz("FullName", _
            "tblMain", _
            "MainID = " & intDad)[B][I][U], "No Relative Found"[/U][/I][/B])

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.