Blank Combo Boxes on Continuous Form

[DocBrown]

Using techniques from earching these posts, I'm REALLY close to getting this
working... Hopefully there's a solution to the 'last' part? It looks like I
need a solution to what's been termed the 'messy problem' with this.

I have a combo box on a continuous form. The list values to be displayed are
dependent on another field. I have a Row Source Query that gets the values I
want. When the form is displayed, only the first record has a value
displayed. In all other rows the cbox is blank. If I click in the blank
record, the correct value is displayed and the other goes blank. Apparently,
the messy part is how to get a value displayed on ALL the records.

Here's details:

ComboBox: Focus_Box

Control Source: VolunteerID

Row Source Query:
SELECT VolunteerFocus.VolunteerID, FocusList.VolunteerFocus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]));

The Forms OnCurrent and VolunteerID_Box AfterUpdate events has in [Event
Procedure]:
Me.Focus_Box.Requery

Any direction is greatly appreciated.

John S.

 

[Graham Mandeno]

Hi John

The trick is to add the reference table (FocusList) and the text field
(VolunteerFocus) to your form's recordsource query. Then add a textbox to
your form, bound to the VolunteerFocus field. Set its Locked property and
position it right over the text portion of the combo box. Use Format>Bring
to front to ensure it is in front of the combo box. Lastly, give the
textbox a GotFocus event procedure which transfers focus to the combo box:
Me.Focus_Box.SetFocus

Now the textbox will always display the correct text, but if you click on it
then the combo box will get the focus and pop to the front, but *only in the
current record*.

 

[DocBrown]

Thank you for replying. This problem is stopping me in my tracks for what I
hope to do on this project.

This seems to have a problem. Originally, the form's record source was just
the Volunteers table. When I use a query that has the Volunteer table and
FocusList table, and add the text box bound to the VolunteerFocus, the form
displays a record with the Volunteer data for every VolunteerFocus related to
the volunteer in the FocusList Table.

For example if I have two volunteers and each volunteer has multiple focus
then I get records such as:

Volunteer Name: Focus:
Harry Smith Exhibits
Harry Smith Programs
Tom Jones Exhibits
Tom Jones Birthday parties
Tom Jones Water Ways

This is exactly the records that executing the query to DataSheet View
displays. If I create the query without the relationship, I still get all the
records, but the order seems to be the order of the entries in the FocusList.

Am I missing something else?

Hi John

The trick is to add the reference table (FocusList) and the text field
(VolunteerFocus) to your form's recordsource query. Then add a textbox to
your form, bound to the VolunteerFocus field. Set its Locked property and
position it right over the text portion of the combo box. Use Format>Bring
to front to ensure it is in front of the combo box. Lastly, give the
textbox a GotFocus event procedure which transfers focus to the combo box:
Me.Focus_Box.SetFocus

Now the textbox will always display the correct text, but if you click on it
then the combo box will get the focus and pop to the front, but *only in the
current record*.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using techniques from earching these posts, I'm REALLY close to getting
this
working... Hopefully there's a solution to the 'last' part? It looks like
I
need a solution to what's been termed the 'messy problem' with this.

I have a combo box on a continuous form. The list values to be displayed
are
dependent on another field. I have a Row Source Query that gets the
values I
want. When the form is displayed, only the first record has a value
displayed. In all other rows the cbox is blank. If I click in the blank
record, the correct value is displayed and the other goes blank.
Apparently,
the messy part is how to get a value displayed on ALL the records.

Here's details:

ComboBox: Focus_Box

Control Source: VolunteerID

Row Source Query:
SELECT VolunteerFocus.VolunteerID, FocusList.VolunteerFocus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]));

The Forms OnCurrent and VolunteerID_Box AfterUpdate events has in [Event
Procedure]:
Me.Focus_Box.Requery

Any direction is greatly appreciated.

John S.

 

[Graham Mandeno]

Hi John

I'm a bit confused. If Harry has multiple focus records, do you not want to
see all these records in your continuous form?

It sounds like you have a classic many-to-many relationship here:

Volunteers <- VolunteerFocus -> FocusList

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Looking more closely at your RowSource query, it seems that every row in
your combo box will have the same first column (VolunteerID).

Can you please post some more details about the structure of your tables and
their relationships and exactly what you want to have displayed on your
form?

--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you for replying. This problem is stopping me in my tracks for what
I
hope to do on this project.

This seems to have a problem. Originally, the form's record source was
just
the Volunteers table. When I use a query that has the Volunteer table and
FocusList table, and add the text box bound to the VolunteerFocus, the
form
displays a record with the Volunteer data for every VolunteerFocus related
to
the volunteer in the FocusList Table.

For example if I have two volunteers and each volunteer has multiple focus
then I get records such as:

Volunteer Name: Focus:
Harry Smith Exhibits
Harry Smith Programs
Tom Jones Exhibits
Tom Jones Birthday parties
Tom Jones Water Ways

This is exactly the records that executing the query to DataSheet View
displays. If I create the query without the relationship, I still get all
the
records, but the order seems to be the order of the entries in the
FocusList.

Am I missing something else?

Hi John

The trick is to add the reference table (FocusList) and the text field
(VolunteerFocus) to your form's recordsource query. Then add a textbox
to
your form, bound to the VolunteerFocus field. Set its Locked property
and
position it right over the text portion of the combo box. Use
Format>Bring
to front to ensure it is in front of the combo box. Lastly, give the
textbox a GotFocus event procedure which transfers focus to the combo
box:
Me.Focus_Box.SetFocus

Now the textbox will always display the correct text, but if you click on
it
then the combo box will get the focus and pop to the front, but *only in
the
current record*.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using techniques from earching these posts, I'm REALLY close to getting
this
working... Hopefully there's a solution to the 'last' part? It looks
like
I
need a solution to what's been termed the 'messy problem' with this.

I have a combo box on a continuous form. The list values to be
displayed
are
dependent on another field. I have a Row Source Query that gets the
values I
want. When the form is displayed, only the first record has a value
displayed. In all other rows the cbox is blank. If I click in the blank
record, the correct value is displayed and the other goes blank.
Apparently,
the messy part is how to get a value displayed on ALL the records.

Here's details:

ComboBox: Focus_Box

Control Source: VolunteerID

Row Source Query:
SELECT VolunteerFocus.VolunteerID, FocusList.VolunteerFocus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]));

The Forms OnCurrent and VolunteerID_Box AfterUpdate events has in
[Event
Procedure]:
Me.Focus_Box.Requery

Any direction is greatly appreciated.

John S.

 

[DocBrown]

Hi Graham,

Thank you for replying.

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Yes, I want to use a single combo box to list all the focus areas for Harry.
Yes, the combo box is bound to VolunteerID in the Volunteers table.

Can you please post some more details about the structure of your tables and
their relationships and exactly what you want to have displayed on your
form?

The relationships are as follows:

Volunteers 1->Many VolunteerFocus many<-1 FocusList.
Volunteers to VolunteerFocus is related on VolunteerID
FocusList to VolunteerFocus is related on TypeID

FocusList is a lookup table that translates the TypeId in VolunteerFocus to
a text display. This is so a user can add new items to the FocusList via a
cmdButton. Then these new focusAreas can be assigned to the volunteers. This
also works correctly on my main data entry form for Volunteers.

What I want is that when the combo box does not have focus, then only the
first item in the list should be displayed. When the user clicks the arrow on
a particular record, all the focus areas come into view.

I have this working with the exception that all the combo boxes are blank
except for the current record that is selected by clicking in any field of
the record.

I'm also trying this with a listbox as the display control but I'm not
getting that to work either. I know the list box has a size property that I
think I can deal with once I get the contents to display correctly.

It is also true that if I use a single form, then I use a subform to display
the FocusAreas as a continuous form linked on VolunteerID. But Subforms are
not allowed on continuous forms.

Thanks for your time,
John S.

Hi John

I'm a bit confused. If Harry has multiple focus records, do you not want to
see all these records in your continuous form?

It sounds like you have a classic many-to-many relationship here:

Volunteers <- VolunteerFocus -> FocusList

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Looking more closely at your RowSource query, it seems that every row in
your combo box will have the same first column (VolunteerID).

Can you please post some more details about the structure of your tables and
their relationships and exactly what you want to have displayed on your
form?

--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you for replying. This problem is stopping me in my tracks for what
I
hope to do on this project.

This seems to have a problem. Originally, the form's record source was
just
the Volunteers table. When I use a query that has the Volunteer table and
FocusList table, and add the text box bound to the VolunteerFocus, the
form
displays a record with the Volunteer data for every VolunteerFocus related
to
the volunteer in the FocusList Table.

For example if I have two volunteers and each volunteer has multiple focus
then I get records such as:

Volunteer Name: Focus:
Harry Smith Exhibits
Harry Smith Programs
Tom Jones Exhibits
Tom Jones Birthday parties
Tom Jones Water Ways

This is exactly the records that executing the query to DataSheet View
displays. If I create the query without the relationship, I still get all
the
records, but the order seems to be the order of the entries in the
FocusList.

Am I missing something else?

Hi John

The trick is to add the reference table (FocusList) and the text field
(VolunteerFocus) to your form's recordsource query. Then add a textbox
to
your form, bound to the VolunteerFocus field. Set its Locked property
and
position it right over the text portion of the combo box. Use
Format>Bring
to front to ensure it is in front of the combo box. Lastly, give the
textbox a GotFocus event procedure which transfers focus to the combo
box:
Me.Focus_Box.SetFocus

Now the textbox will always display the correct text, but if you click on
it
then the combo box will get the focus and pop to the front, but *only in
the
current record*.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using techniques from earching these posts, I'm REALLY close to getting
this
working... Hopefully there's a solution to the 'last' part? It looks
like
I
need a solution to what's been termed the 'messy problem' with this.

I have a combo box on a continuous form. The list values to be
displayed
are
dependent on another field. I have a Row Source Query that gets the
values I
want. When the form is displayed, only the first record has a value
displayed. In all other rows the cbox is blank. If I click in the blank
record, the correct value is displayed and the other goes blank.
Apparently,
the messy part is how to get a value displayed on ALL the records.

Here's details:

ComboBox: Focus_Box

Control Source: VolunteerID

Row Source Query:
SELECT VolunteerFocus.VolunteerID, FocusList.VolunteerFocus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]));

The Forms OnCurrent and VolunteerID_Box AfterUpdate events has in
[Event
Procedure]:
Me.Focus_Box.Requery

Any direction is greatly appreciated.

John S.

 

[DocBrown]

I think I have a solution, but I'm not sure if there's a more efficient way.

Using your suggestion of having a text box in front of the combo, I figured
out how to get text box to display the desired value:

=DLookUp("[Focus]","[FocusList]","[TypeID]="&
Dlookup("[typeID]","[VolunteerFocus]","[VolunteerID]="&VolunteerID_Box))

Because the text I want to display is in the look up table I constructed
this Dlookup within a Dlookup to get the value based on the VolunteerID_Box
on the form. This finds the first Focus entry which is exactly what the combo
box will display in it's first row when selected.

There is a noticable delay for the fields to display everytime I click on
any field. I don't know if there's a way to minimize it. I'm concerned that
when I get many records, the delay will be significant.

Thanks for your help.
John S.

Hi Graham,

Thank you for replying.

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Yes, I want to use a single combo box to list all the focus areas for Harry.
Yes, the combo box is bound to VolunteerID in the Volunteers table.

Can you please post some more details about the structure of your tables and
their relationships and exactly what you want to have displayed on your
form?

The relationships are as follows:

Volunteers 1->Many VolunteerFocus many<-1 FocusList.
Volunteers to VolunteerFocus is related on VolunteerID
FocusList to VolunteerFocus is related on TypeID

FocusList is a lookup table that translates the TypeId in VolunteerFocus to
a text display. This is so a user can add new items to the FocusList via a
cmdButton. Then these new focusAreas can be assigned to the volunteers. This
also works correctly on my main data entry form for Volunteers.

What I want is that when the combo box does not have focus, then only the
first item in the list should be displayed. When the user clicks the arrow on
a particular record, all the focus areas come into view.

I have this working with the exception that all the combo boxes are blank
except for the current record that is selected by clicking in any field of
the record.

I'm also trying this with a listbox as the display control but I'm not
getting that to work either. I know the list box has a size property that I
think I can deal with once I get the contents to display correctly.

It is also true that if I use a single form, then I use a subform to display
the FocusAreas as a continuous form linked on VolunteerID. But Subforms are
not allowed on continuous forms.

Thanks for your time,
John S.

Hi John

I'm a bit confused. If Harry has multiple focus records, do you not want to
see all these records in your continuous form?

It sounds like you have a classic many-to-many relationship here:

Volunteers <- VolunteerFocus -> FocusList

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Looking more closely at your RowSource query, it seems that every row in
your combo box will have the same first column (VolunteerID).

Can you please post some more details about the structure of your tables and
their relationships and exactly what you want to have displayed on your
form?

--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi John

OK, so the combo box is not used to select anything - only to display the
list if focus areas for the given volunteer if the dropdown arrow is
clicked?

I suggest you create a query based on VolunteerFocus and FocusList (call it
"qryVolunteerFocusList") with the fields VolunteerID and VolunteerFocus,
ordered by VolunteerID and VolunteerFocus.

Then write a small public function to return a comma-separated list of focus
areas for a volunteer, something like this:

Public Function VolunteerFocusList(vID as Variant) as string
Dim rs as DAO.Recordset
Dim sList as string
If IsNumeric(vID) then
Set rs = CurrentDb.OpenRecordset("Select VolunteerFocus " _
& "from qryVolunteerFocus where VolunteerID=" & vID, _
dbOpenForwardOnly)
With rs
Do Until .EOF
sList = sList & !VolunteerFocus & ", "
.MoveNext
Loop
.Close
End With
VolunteerFocusList = Left (sList, Len(sList)-2)
End If
End Function

Now, add a calculated field to your form's RecordSource query:
FocusList: VolunteerFocusList([VolunteerID])
and bind your masking textbox to this field.

Finally, your new query can be used to simplify the rowsource of your combo
box, and the rowsource only needs to be set when the combo box gets the
focus, so, in your Focus_Box_GotFocus event procedure, use this code:

Focus_Box.RowSource = _
"Select * from qryVolunteerFocus where VolunteerID=" & VolunteerID_Box

You can then remove and Focus_Box.Requery statements.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thank you for replying.

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Yes, I want to use a single combo box to list all the focus areas for
Harry.
Yes, the combo box is bound to VolunteerID in the Volunteers table.

Can you please post some more details about the structure of your tables
and
their relationships and exactly what you want to have displayed on your
form?

The relationships are as follows:

Volunteers 1->Many VolunteerFocus many<-1 FocusList.
Volunteers to VolunteerFocus is related on VolunteerID
FocusList to VolunteerFocus is related on TypeID

FocusList is a lookup table that translates the TypeId in VolunteerFocus
to
a text display. This is so a user can add new items to the FocusList via a
cmdButton. Then these new focusAreas can be assigned to the volunteers.
This
also works correctly on my main data entry form for Volunteers.

What I want is that when the combo box does not have focus, then only the
first item in the list should be displayed. When the user clicks the arrow
on
a particular record, all the focus areas come into view.

I have this working with the exception that all the combo boxes are blank
except for the current record that is selected by clicking in any field of
the record.

I'm also trying this with a listbox as the display control but I'm not
getting that to work either. I know the list box has a size property that
I
think I can deal with once I get the contents to display correctly.

It is also true that if I use a single form, then I use a subform to
display
the FocusAreas as a continuous form linked on VolunteerID. But Subforms
are
not allowed on continuous forms.

Thanks for your time,
John S.

Hi John

I'm a bit confused. If Harry has multiple focus records, do you not want
to
see all these records in your continuous form?

It sounds like you have a classic many-to-many relationship here:

Volunteers <- VolunteerFocus -> FocusList

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Looking more closely at your RowSource query, it seems that every row in
your combo box will have the same first column (VolunteerID).

Can you please post some more details about the structure of your tables
and
their relationships and exactly what you want to have displayed on your
form?

--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you for replying. This problem is stopping me in my tracks for
what
I
hope to do on this project.

This seems to have a problem. Originally, the form's record source was
just
the Volunteers table. When I use a query that has the Volunteer table
and
FocusList table, and add the text box bound to the VolunteerFocus, the
form
displays a record with the Volunteer data for every VolunteerFocus
related
to
the volunteer in the FocusList Table.

For example if I have two volunteers and each volunteer has multiple
focus
then I get records such as:

Volunteer Name: Focus:
Harry Smith Exhibits
Harry Smith Programs
Tom Jones Exhibits
Tom Jones Birthday parties
Tom Jones Water Ways

This is exactly the records that executing the query to DataSheet View
displays. If I create the query without the relationship, I still get
all
the
records, but the order seems to be the order of the entries in the
FocusList.

Am I missing something else?

:

Hi John

The trick is to add the reference table (FocusList) and the text field
(VolunteerFocus) to your form's recordsource query. Then add a
textbox
to
your form, bound to the VolunteerFocus field. Set its Locked property
and
position it right over the text portion of the combo box. Use
Format>Bring
to front to ensure it is in front of the combo box. Lastly, give the
textbox a GotFocus event procedure which transfers focus to the combo
box:
Me.Focus_Box.SetFocus

Now the textbox will always display the correct text, but if you click
on
it
then the combo box will get the focus and pop to the front, but *only
in
the
current record*.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using techniques from earching these posts, I'm REALLY close to
getting
this
working... Hopefully there's a solution to the 'last' part? It looks
like
I
need a solution to what's been termed the 'messy problem' with this.

I have a combo box on a continuous form. The list values to be
displayed
are
dependent on another field. I have a Row Source Query that gets the
values I
want. When the form is displayed, only the first record has a value
displayed. In all other rows the cbox is blank. If I click in the
blank
record, the correct value is displayed and the other goes blank.
Apparently,
the messy part is how to get a value displayed on ALL the records.

Here's details:

ComboBox: Focus_Box

Control Source: VolunteerID

Row Source Query:
SELECT VolunteerFocus.VolunteerID, FocusList.VolunteerFocus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]));

The Forms OnCurrent and VolunteerID_Box AfterUpdate events has in
[Event
Procedure]:
Me.Focus_Box.Requery

Any direction is greatly appreciated.

John S.

 

[Graham Mandeno]

Oh, and add Focus_Box.Dropdown to your GotFocus event proc.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi John

OK, so the combo box is not used to select anything - only to display the
list if focus areas for the given volunteer if the dropdown arrow is
clicked?

I suggest you create a query based on VolunteerFocus and FocusList (call
it "qryVolunteerFocusList") with the fields VolunteerID and
VolunteerFocus, ordered by VolunteerID and VolunteerFocus.

Then write a small public function to return a comma-separated list of
focus areas for a volunteer, something like this:

Public Function VolunteerFocusList(vID as Variant) as string
Dim rs as DAO.Recordset
Dim sList as string
If IsNumeric(vID) then
Set rs = CurrentDb.OpenRecordset("Select VolunteerFocus " _
& "from qryVolunteerFocus where VolunteerID=" & vID, _
dbOpenForwardOnly)
With rs
Do Until .EOF
sList = sList & !VolunteerFocus & ", "
.MoveNext
Loop
.Close
End With
VolunteerFocusList = Left (sList, Len(sList)-2)
End If
End Function

Now, add a calculated field to your form's RecordSource query:
FocusList: VolunteerFocusList([VolunteerID])
and bind your masking textbox to this field.

Finally, your new query can be used to simplify the rowsource of your
combo box, and the rowsource only needs to be set when the combo box gets
the focus, so, in your Focus_Box_GotFocus event procedure, use this code:

Focus_Box.RowSource = _
"Select * from qryVolunteerFocus where VolunteerID=" & VolunteerID_Box

You can then remove and Focus_Box.Requery statements.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thank you for replying.

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Yes, I want to use a single combo box to list all the focus areas for
Harry.
Yes, the combo box is bound to VolunteerID in the Volunteers table.

Can you please post some more details about the structure of your tables
and
their relationships and exactly what you want to have displayed on your
form?

The relationships are as follows:

Volunteers 1->Many VolunteerFocus many<-1 FocusList.
Volunteers to VolunteerFocus is related on VolunteerID
FocusList to VolunteerFocus is related on TypeID

FocusList is a lookup table that translates the TypeId in VolunteerFocus
to
a text display. This is so a user can add new items to the FocusList via
a
cmdButton. Then these new focusAreas can be assigned to the volunteers.
This
also works correctly on my main data entry form for Volunteers.

What I want is that when the combo box does not have focus, then only the
first item in the list should be displayed. When the user clicks the
arrow on
a particular record, all the focus areas come into view.

I have this working with the exception that all the combo boxes are blank
except for the current record that is selected by clicking in any field
of
the record.

I'm also trying this with a listbox as the display control but I'm not
getting that to work either. I know the list box has a size property that
I
think I can deal with once I get the contents to display correctly.

It is also true that if I use a single form, then I use a subform to
display
the FocusAreas as a continuous form linked on VolunteerID. But Subforms
are
not allowed on continuous forms.

Thanks for your time,
John S.

Hi John

I'm a bit confused. If Harry has multiple focus records, do you not
want to
see all these records in your continuous form?

It sounds like you have a classic many-to-many relationship here:

Volunteers <- VolunteerFocus -> FocusList

Did you want to use a single combo box to list all the Focus areas for
Harry? If so, is it a bound combo box? If so, what is it bound to?

Looking more closely at your RowSource query, it seems that every row in
your combo box will have the same first column (VolunteerID).

Can you please post some more details about the structure of your tables
and
their relationships and exactly what you want to have displayed on your
form?

--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you for replying. This problem is stopping me in my tracks for
what
I
hope to do on this project.

This seems to have a problem. Originally, the form's record source was
just
the Volunteers table. When I use a query that has the Volunteer table
and
FocusList table, and add the text box bound to the VolunteerFocus, the
form
displays a record with the Volunteer data for every VolunteerFocus
related
to
the volunteer in the FocusList Table.

For example if I have two volunteers and each volunteer has multiple
focus
then I get records such as:

Volunteer Name: Focus:
Harry Smith Exhibits
Harry Smith Programs
Tom Jones Exhibits
Tom Jones Birthday parties
Tom Jones Water Ways

This is exactly the records that executing the query to DataSheet View
displays. If I create the query without the relationship, I still get
all
the
records, but the order seems to be the order of the entries in the
FocusList.

Am I missing something else?

:

Hi John

The trick is to add the reference table (FocusList) and the text
field
(VolunteerFocus) to your form's recordsource query. Then add a
textbox
to
your form, bound to the VolunteerFocus field. Set its Locked
property
and
position it right over the text portion of the combo box. Use
Format>Bring
to front to ensure it is in front of the combo box. Lastly, give the
textbox a GotFocus event procedure which transfers focus to the combo
box:
Me.Focus_Box.SetFocus

Now the textbox will always display the correct text, but if you
click on
it
then the combo box will get the focus and pop to the front, but *only
in
the
current record*.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using techniques from earching these posts, I'm REALLY close to
getting
this
working... Hopefully there's a solution to the 'last' part? It
looks
like
I
need a solution to what's been termed the 'messy problem' with
this.

I have a combo box on a continuous form. The list values to be
displayed
are
dependent on another field. I have a Row Source Query that gets
the
values I
want. When the form is displayed, only the first record has a value
displayed. In all other rows the cbox is blank. If I click in the
blank
record, the correct value is displayed and the other goes blank.
Apparently,
the messy part is how to get a value displayed on ALL the records.

Here's details:

ComboBox: Focus_Box

Control Source: VolunteerID

Row Source Query:
SELECT VolunteerFocus.VolunteerID, FocusList.VolunteerFocus
FROM FocusList INNER JOIN VolunteerFocus ON FocusList.TypeID =
VolunteerFocus.TypeID
WHERE (((VolunteerFocus.VolunteerID)=[Forms]![Search Query
Prompts]![VolunteerID_Box]));

The Forms OnCurrent and VolunteerID_Box AfterUpdate events has in
[Event
Procedure]:
Me.Focus_Box.Requery

Any direction is greatly appreciated.

John S.