Default value of combo box based on another combo box

[Hugh self taught]

Hi Guys & Gals,

I've read various threads on the default value of a combo box but nothing I
try will give me a result.

I have 2 cbo's retrieving their data from the same sources. In another table
there is a PK with a FK for the man & a FK for the woman which is the PK in
the 1st table. The relevant fields are from the 1st table namely Comp_Idx,
FirstName, SurName. The cbo is bound to Column(0) which is hidden in both
cases. The 1st cbo is to show the Male name & the 2nd the Female name. I have
a hidden text box on the form which gives me the value of Column (3) which in
the sql is the PK for the woman in the 1st table.

How can I get the Afterupdate of the male cbo to set the default value of
the female cbo to show the relevant record for the female?

You build & you learn. Then someone helps you & you build some more........

 

[Dirk Goldgar]

Hi Guys & Gals,

I've read various threads on the default value of a combo box but nothing
I
try will give me a result.

I have 2 cbo's retrieving their data from the same sources. In another
table
there is a PK with a FK for the man & a FK for the woman which is the PK
in
the 1st table. The relevant fields are from the 1st table namely Comp_Idx,
FirstName, SurName. The cbo is bound to Column(0) which is hidden in both
cases. The 1st cbo is to show the Male name & the 2nd the Female name. I
have
a hidden text box on the form which gives me the value of Column (3) which
in
the sql is the PK for the woman in the 1st table.

How can I get the Afterupdate of the male cbo to set the default value of
the female cbo to show the relevant record for the female?

Something like this will do what you *say* you want:

'----- start of example code #1 -----
Private Sub cboMale_AfterUpdate()

Me.cboFemale.DefaultValue = Me.cboMale.Column(3)

End Sub
'----- end of example code #1 -----

Note that cboMale.Column(3) is the *fourth* coumn of the combo box.

HOWEVER, setting the default value of the combo box will only apply when a
new record is being created -- at the moment something is first entered for
any of the fields in the record -- so you may not get the behavior you want
with the above code. Maybe you would be better served with this version
instead, which sets the value, not the default value, of the combo box:

'----- start of example code #2 -----
Private Sub cboMale_AfterUpdate()

With Me.cboFemale
If IsNull(.Value) Then
.Value = Me.cboMale.Column(3)
End If
End With

End Sub
'----- end of example code #2 -----

That will set the value of the "female" combo, but only if the combo is
currently Null.

 

[Hugh self taught]

Hi Dirk,

You've been my hero in the past but this time I'm baffled.

I've resorted to the following still without success.

Me.EvtNonFemale.Value = Me.EvtNonMale.Column(3)
Debug.Print Me.EvtNonFemale.Value
Debug.Print Me.EvtNonMale.Value
Me.EvtNonFemale.Requery

The Debug values are showing the correct values but the cbo box is blank.
Could it be because the female cbo box bound column is hidden?

 

[Dirk Goldgar]

Hi Dirk,

You've been my hero in the past but this time I'm baffled.

I've resorted to the following still without success.

Me.EvtNonFemale.Value = Me.EvtNonMale.Column(3)
Debug.Print Me.EvtNonFemale.Value
Debug.Print Me.EvtNonMale.Value
Me.EvtNonFemale.Requery

The Debug values are showing the correct values but the cbo box is blank.
Could it be because the female cbo box bound column is hidden?

No, I don't think so. More likely it's that the value you are assigning to
it is not in the rowsource. What are the following properties of each combo
box:

Row Source
Column Count
Bound Column

?
What does your debugging show you as values for EvtNonFemale.Value,
EvtNonMale.Value, and EvtNonMale.Column(3) ?

 

[Hugh self taught]

Morning Dirk,

Here goes

Row Source of Male cbo
SELECT tblNonCouples.PtsCpl_Idx, [Male].[Surname]+',
'+[Male].[First_Name] AS Male, [Female].[Surname]+',
'+[Female].[First_Name] AS Female, tblNonCouples.FemaleCpl
FROM tblNonCompetitors AS Male INNER JOIN (tblNonCouples INNER JOIN
tblNonCompetitors AS Female ON tblNonCouples.FemaleCpl = Female.PtsComp_Idx)
ON Male.PtsComp_Idx = tblNonCouples.MaleCpl
ORDER BY [Male].[Surname]+', '+[Male].[First_Name];

Row source of Female cbo
SELECT tblNonCouples.PtsCpl_Idx, [Female].[Surname]+',
'+[Female].[First_Name] AS Female, [Male].[Surname]+',
'+[Male].[First_Name] AS Male
FROM tblNonCompetitors AS Male INNER JOIN (tblNonCouples INNER JOIN
tblNonCompetitors AS Female ON tblNonCouples.FemaleCpl = Female.PtsComp_Idx)
ON Male.PtsComp_Idx = tblNonCouples.MaleCpl
ORDER BY [Female].[Surname]+', '+[Female].[First_Name];

Column Count Male = 4 0;5;4.5;1
Column Count Female = 3 0;5;4.5

Bound Column 1 on both

Debug.Print Me.EvtNonFemale.Value
Debug.Print Me.EvtNonMale.Value
Debug.Print Me.EvtNonMale.Column(3).Value

Result (copy & paste)
411
77

The Debug.Print Me.EvtNonMale.Column(3).Value gives me an "Object Required"
error

The following is the values (copy & paste) from the text boxes on the form
411
77

Hope you can see something that I'm missing

 

[Dirk Goldgar]

Morning Dirk,

Here goes

Row Source of Male cbo
SELECT tblNonCouples.PtsCpl_Idx, [Male].[Surname]+',
'+[Male].[First_Name] AS Male, [Female].[Surname]+',
'+[Female].[First_Name] AS Female, tblNonCouples.FemaleCpl
FROM tblNonCompetitors AS Male INNER JOIN (tblNonCouples INNER JOIN
tblNonCompetitors AS Female ON tblNonCouples.FemaleCpl =
Female.PtsComp_Idx)
ON Male.PtsComp_Idx = tblNonCouples.MaleCpl
ORDER BY [Male].[Surname]+', '+[Male].[First_Name];

Row source of Female cbo
SELECT tblNonCouples.PtsCpl_Idx, [Female].[Surname]+',
'+[Female].[First_Name] AS Female, [Male].[Surname]+',
'+[Male].[First_Name] AS Male
FROM tblNonCompetitors AS Male INNER JOIN (tblNonCouples INNER JOIN
tblNonCompetitors AS Female ON tblNonCouples.FemaleCpl =
Female.PtsComp_Idx)
ON Male.PtsComp_Idx = tblNonCouples.MaleCpl
ORDER BY [Female].[Surname]+', '+[Female].[First_Name];

Column Count Male = 4 0;5;4.5;1
Column Count Female = 3 0;5;4.5

Bound Column 1 on both

Debug.Print Me.EvtNonFemale.Value
Debug.Print Me.EvtNonMale.Value
Debug.Print Me.EvtNonMale.Column(3).Value

Result (copy & paste)
411
77

The Debug.Print Me.EvtNonMale.Column(3).Value gives me an "Object
Required"
error

That's because the .Column property does not have a .Value property. You
should just have entered:

Debug.Print Me.EvtNonMale.Column(3)

.... which is what I posted, I think.

Hope you can see something that I'm missing

If I'm reading this right, the rowsource for the female combo is selecting
tblNonCouples.PtsCpl_Idx -- the key from the "couples" table -- as its bound
column, but your code is assigning the value of tblNonCouples.FemaleCpl --
which is equal to Female.PtsComp_Idx, the key of the "females" table -- to
that combo. Thus, the value you are assigning is not necessarily in the
combo's list.

Since this combo box seems to be for specifically picking a female *who may
not necessarily be the one paired with the male in the "couples" table*, the
rowsource of the EvtNonFemale combo box should be selecting
Female.PtsComp_Idx as the first, bound column. However, I may be
misunderstanding the way you have this set up.

 

[Hugh self taught]

Hi Dirk,

I've had to re-think some of the structure (goal posts moved) & as such this
particular need has become redundant. However I am keeping this copy of the
DB & will continue to experiment when I have the chance as it's something I
must get right for future knowledge & functionality.

I will repost at that time if I don't get it right.

Thanks for all the help you've given to date
Hugh

Morning Dirk,

Here goes

Row Source of Male cbo
SELECT tblNonCouples.PtsCpl_Idx, [Male].[Surname]+',
'+[Male].[First_Name] AS Male, [Female].[Surname]+',
'+[Female].[First_Name] AS Female, tblNonCouples.FemaleCpl
FROM tblNonCompetitors AS Male INNER JOIN (tblNonCouples INNER JOIN
tblNonCompetitors AS Female ON tblNonCouples.FemaleCpl =
Female.PtsComp_Idx)
ON Male.PtsComp_Idx = tblNonCouples.MaleCpl
ORDER BY [Male].[Surname]+', '+[Male].[First_Name];

Row source of Female cbo
SELECT tblNonCouples.PtsCpl_Idx, [Female].[Surname]+',
'+[Female].[First_Name] AS Female, [Male].[Surname]+',
'+[Male].[First_Name] AS Male
FROM tblNonCompetitors AS Male INNER JOIN (tblNonCouples INNER JOIN
tblNonCompetitors AS Female ON tblNonCouples.FemaleCpl =
Female.PtsComp_Idx)
ON Male.PtsComp_Idx = tblNonCouples.MaleCpl
ORDER BY [Female].[Surname]+', '+[Female].[First_Name];

Column Count Male = 4 0;5;4.5;1
Column Count Female = 3 0;5;4.5

Bound Column 1 on both

Debug.Print Me.EvtNonFemale.Value
Debug.Print Me.EvtNonMale.Value
Debug.Print Me.EvtNonMale.Column(3).Value

Result (copy & paste)
411
77

The Debug.Print Me.EvtNonMale.Column(3).Value gives me an "Object
Required"
error

That's because the .Column property does not have a .Value property. You
should just have entered:

Debug.Print Me.EvtNonMale.Column(3)

... which is what I posted, I think.

Hope you can see something that I'm missing

If I'm reading this right, the rowsource for the female combo is selecting
tblNonCouples.PtsCpl_Idx -- the key from the "couples" table -- as its bound
column, but your code is assigning the value of tblNonCouples.FemaleCpl --
which is equal to Female.PtsComp_Idx, the key of the "females" table -- to
that combo. Thus, the value you are assigning is not necessarily in the
combo's list.

Since this combo box seems to be for specifically picking a female *who may
not necessarily be the one paired with the male in the "couples" table*, the
rowsource of the EvtNonFemale combo box should be selecting
Female.PtsComp_Idx as the first, bound column. However, I may be
misunderstanding the way you have this set up.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)