default value for combo box based on another combo box

[Jeff @ CI]

In A2K. On a form, the user enters contact data for people registering to
attend an event. On the form is a combo box (#2) that lists the events to be
chosen from - this combo box has an event already listed based upon a master
list of registerees and is pulled from a combo box (#1) of events. The
second combo box (#2) is used for when a person wants to switch events.

The problem I have is with a third combo box in which I list a presenter's
name. I have used the following to narrow the presenter list available for
the combo box to just the presenter associated with event:

SELECT [Speaker] FROM SpeakerQuery WHERE
[SpeakerQuery].[Event]=[Forms]![ContactMain].[Event];

The goal is to have the presenter's name automatically populate this field.
The purpose is to track - by registeree - performance measures on the
presenter. I have tried to set the default value to the same statement above
and tried to use the ItemData() in this area to have the name fill the combo
box. I get an error that says I need to use parentheses in the expression.
The second combo box above uses a simple =[Forms]![ContactMain].[Event] to
autopopulate the box. Using =[Queries]![SpeakerQuery].[Speaker] gives me a
name? error in the combo box.

Any other ideas?

TIA

Jeff

 

[Graham Mandeno]

Hi Jeff

I'm a little confused by your question, so forgive me if I have interpreted
you wrongly.

I understand that after selecting the event in combo #2, you want to requery
combo #3 (the list of speakers) and automatically select one of the list
(which one? the first?)

OK, so in the AfterUpdate event of combo #2, requery combo #3 and set its
value (not its default value):

With Me.Combo3
.Requery
If .ListCount = 0 then
.Value = Null
Else
.Value = .ItemData(0)
End If
' and maybe you want to indicate if there is further choice
If .ListCount > 1 Then
.SetFocus
.Dropdown
End If
End With

 

[Jeff @ CI]

Hi Graham,

Thanks for the reply. I apologize for the confusion. Basically, I need to
auto fill a combo box or a text box (now that I think of it) with a name. I
can narrow the selection down to one name in the combo box, but cannot get it
to auto fill. I do not need to look at other names, just the one associated
with the event to be filled with each new registration entry.

Your advice is to requery. I am not sure I need that since I can pull the
correct name into a single entry combo box. When I tried a text box, I got a
blank field.

I noticed that you used a lot of periods in your solution. A curiosity
question. How do they work per your example? Looking at it, it seems to be
missing something. (yes, I am a newbie).

Anyhow, if this helps, all I am looking to do is to auto fill the associated
speaker's name into the individual record when it is added to the database.
I think your solution minus the requery will work.

Thanks

Hi Jeff

I'm a little confused by your question, so forgive me if I have interpreted
you wrongly.

I understand that after selecting the event in combo #2, you want to requery
combo #3 (the list of speakers) and automatically select one of the list
(which one? the first?)

OK, so in the AfterUpdate event of combo #2, requery combo #3 and set its
value (not its default value):

With Me.Combo3
.Requery
If .ListCount = 0 then
.Value = Null
Else
.Value = .ItemData(0)
End If
' and maybe you want to indicate if there is further choice
If .ListCount > 1 Then
.SetFocus
.Dropdown
End If
End With
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

In A2K. On a form, the user enters contact data for people registering
to
attend an event. On the form is a combo box (#2) that lists the events to
be
chosen from - this combo box has an event already listed based upon a
master
list of registerees and is pulled from a combo box (#1) of events. The
second combo box (#2) is used for when a person wants to switch events.

The problem I have is with a third combo box in which I list a presenter's
name. I have used the following to narrow the presenter list available
for
the combo box to just the presenter associated with event:

SELECT [Speaker] FROM SpeakerQuery WHERE
[SpeakerQuery].[Event]=[Forms]![ContactMain].[Event];

The goal is to have the presenter's name automatically populate this
field.
The purpose is to track - by registeree - performance measures on the
presenter. I have tried to set the default value to the same statement
above
and tried to use the ItemData() in this area to have the name fill the
combo
box. I get an error that says I need to use parentheses in the
expression.
The second combo box above uses a simple =[Forms]![ContactMain].[Event] to
autopopulate the box. Using =[Queries]![SpeakerQuery].[Speaker] gives me
a
name? error in the combo box.

Any other ideas?

TIA

Jeff

 

[Graham Mandeno]

Hi Jeff

Do you mean that each event has only one speaker?

If so, then add the speaker as an extra column in your event selecting
combo. For example, you might have:
Select EventID, EventName, EventSpeaker from Events
order by EventName;

Set ColumnCount=3 and ColumnWidths=0;;0
(this hides the first and third columns and allows the second to have all
the available space)

Then add a textbox with this control source:
=cboEvent.Column(2)

The references starting with a dot are relative to the entity in the With
statement.

With Me.Combo3
.Requery
If .ListCount = 0 then
.Value = Null
Else
...
End With

is equivalent to:

Me.Combo3.Requery
If Me.Combo3.ListCount = 0 then
Me.Combo3.Value = Null
Else
...

but it doesn't wear out the fingers so much <g>
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for the reply. I apologize for the confusion. Basically, I need
to
auto fill a combo box or a text box (now that I think of it) with a name.
I
can narrow the selection down to one name in the combo box, but cannot get
it
to auto fill. I do not need to look at other names, just the one
associated
with the event to be filled with each new registration entry.

Your advice is to requery. I am not sure I need that since I can pull the
correct name into a single entry combo box. When I tried a text box, I
got a
blank field.

I noticed that you used a lot of periods in your solution. A curiosity
question. How do they work per your example? Looking at it, it seems to
be
missing something. (yes, I am a newbie).

Anyhow, if this helps, all I am looking to do is to auto fill the
associated
speaker's name into the individual record when it is added to the
database.
I think your solution minus the requery will work.

Thanks

Hi Jeff

I'm a little confused by your question, so forgive me if I have
interpreted
you wrongly.

I understand that after selecting the event in combo #2, you want to
requery
combo #3 (the list of speakers) and automatically select one of the list
(which one? the first?)

OK, so in the AfterUpdate event of combo #2, requery combo #3 and set its
value (not its default value):

With Me.Combo3
.Requery
If .ListCount = 0 then
.Value = Null
Else
.Value = .ItemData(0)
End If
' and maybe you want to indicate if there is further choice
If .ListCount > 1 Then
.SetFocus
.Dropdown
End If
End With
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

In A2K. On a form, the user enters contact data for people
registering
to
attend an event. On the form is a combo box (#2) that lists the events
to
be
chosen from - this combo box has an event already listed based upon a
master
list of registerees and is pulled from a combo box (#1) of events. The
second combo box (#2) is used for when a person wants to switch events.

The problem I have is with a third combo box in which I list a
presenter's
name. I have used the following to narrow the presenter list available
for
the combo box to just the presenter associated with event:

SELECT [Speaker] FROM SpeakerQuery WHERE
[SpeakerQuery].[Event]=[Forms]![ContactMain].[Event];

The goal is to have the presenter's name automatically populate this
field.
The purpose is to track - by registeree - performance measures on the
presenter. I have tried to set the default value to the same statement
above
and tried to use the ItemData() in this area to have the name fill the
combo
box. I get an error that says I need to use parentheses in the
expression.
The second combo box above uses a simple =[Forms]![ContactMain].[Event]
to
autopopulate the box. Using =[Queries]![SpeakerQuery].[Speaker] gives
me
a
name? error in the combo box.

Any other ideas?

TIA

Jeff