Combo Field not showing value but storing it

G

Guest

I have a combo field on a form that is working fine, i.e. can select info,
stores the data etc. but the field does not show the value on entry to the
form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on [COMPETENCY_ID] for
every row, I have an On Entry Requery Me![SIP Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM [Lookup
People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier] WHERE
((([Lookup Competency].[Competency Identifier])=[COMPETENCY_ID])) ORDER BY
[Lookup People].PEOPLE_NAME;
 
R

Rick Brandt

Paul said:
I have a combo field on a form that is working fine, i.e. can select
info, stores the data etc. but the field does not show the value on
entry to the form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on
[COMPETENCY_ID] for every row, I have an On Entry Requery Me![SIP
Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM
[Lookup People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier]
WHERE ((([Lookup Competency].[Competency
Identifier])=[COMPETENCY_ID])) ORDER BY [Lookup People].PEOPLE_NAME;
Click to expand...

When you have a ComboBox store one value, while displaying another, the
displayed value is only available by being pulled from the RowSource query. If
you change the RowSource query per-record it affects ALL instances of the
ComboBox in a continuous form, not just the current row.

So...if the query used when you move to record 2 does not include the displayed
value for record 1 then record 1's ComboBox will be blank. No way to prevent
that really.

You could overlay a TextBox on top of the ComboBox and use DLookup() to dispay
the value, but that might not be very efficient if you have lots of records in
the form.
 
B

Baz

OK, so the combo box has two columns. Which one do you want displayed?
Assuming it's the name, set the Column Widths property to zero, so as to
hide the first column.
 
G

Guest

already set to zero

Baz said:
OK, so the combo box has two columns. Which one do you want displayed?
Assuming it's the name, set the Column Widths property to zero, so as to
hide the first column.

Paul Dennis said:
I have a combo field on a form that is working fine, i.e. can select info,
stores the data etc. but the field does not show the value on entry to the
form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on [COMPETENCY_ID] for
every row, I have an On Entry Requery Me![SIP Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM [Lookup
People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier] WHERE
((([Lookup Competency].[Competency Identifier])=[COMPETENCY_ID])) ORDER BY
[Lookup People].PEOPLE_NAME;
Click to expand...
Click to expand...
 
B

Baz

So is it a continuous form, as suggested by Rick?

Paul Dennis said:
already set to zero

Baz said:
OK, so the combo box has two columns. Which one do you want displayed?
Assuming it's the name, set the Column Widths property to zero, so as to
hide the first column.

Paul Dennis said:
I have a combo field on a form that is working fine, i.e. can select info,
stores the data etc. but the field does not show the value on entry to the
form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on
Click to expand...
[COMPETENCY_ID]
for
every row, I have an On Entry Requery Me![SIP Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM [Lookup
People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier] WHERE
((([Lookup Competency].[Competency Identifier])=[COMPETENCY_ID])) ORDER BY
[Lookup People].PEOPLE_NAME;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

yes - I have overlayed 2 form fields both against the same field with one no
enabled for update and that works but looks a little odd.

Baz said:
So is it a continuous form, as suggested by Rick?

Paul Dennis said:
already set to zero

Baz said:
OK, so the combo box has two columns. Which one do you want displayed?
Assuming it's the name, set the Column Widths property to zero, so as to
hide the first column.

I have a combo field on a form that is working fine, i.e. can select info,
stores the data etc. but the field does not show the value on entry to the
form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on [COMPETENCY_ID]
for
every row, I have an On Entry Requery Me![SIP Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM [Lookup
People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier]
WHERE
((([Lookup Competency].[Competency Identifier])=[COMPETENCY_ID])) ORDER BY
[Lookup People].PEOPLE_NAME;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

done what you mentioned. I have got 2 form fields against the same table
field, one with the complex lookup and the other with a simple lookup but the
field not enabled and then overlayed them. You can tell that the field is
different but at least it works - thx.

Rick Brandt said:
Paul said:
I have a combo field on a form that is working fine, i.e. can select
info, stores the data etc. but the field does not show the value on
entry to the form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on
[COMPETENCY_ID] for every row, I have an On Entry Requery Me![SIP
Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM
[Lookup People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier]
WHERE ((([Lookup Competency].[Competency
Identifier])=[COMPETENCY_ID])) ORDER BY [Lookup People].PEOPLE_NAME;
Click to expand...

When you have a ComboBox store one value, while displaying another, the
displayed value is only available by being pulled from the RowSource query. If
you change the RowSource query per-record it affects ALL instances of the
ComboBox in a continuous form, not just the current row.

So...if the query used when you move to record 2 does not include the displayed
value for record 1 then record 1's ComboBox will be blank. No way to prevent
that really.

You could overlay a TextBox on top of the ComboBox and use DLookup() to dispay
the value, but that might not be very efficient if you have lots of records in
the form.
Click to expand...
 
B

Baz

Yes, I've done that before, it's very tricky to get it working really well.

Paul Dennis said:
yes - I have overlayed 2 form fields both against the same field with one no
enabled for update and that works but looks a little odd.

Baz said:
So is it a continuous form, as suggested by Rick?

Paul Dennis said:
already set to zero

:

OK, so the combo box has two columns. Which one do you want displayed?
Assuming it's the name, set the Column Widths property to zero, so as to
hide the first column.

I have a combo field on a form that is working fine, i.e. can
Click to expand...
select
info,
stores the data etc. but the field does not show the value on
Click to expand...
entry to
the
form or after selection.

Field = [SIP Approver ID]
Row Source is based on another field called [COMPETENCY_ID]
Bound Col = 1
Column count = 2

Since the Row Source SQL needs to be different based on [COMPETENCY_ID]
for
every row, I have an On Entry Requery Me![SIP Approver ID].Requery

The SQL in Row Source is

SELECT [Lookup People].PEOPLE_ID, [Lookup People].PEOPLE_NAME FROM [Lookup
People] INNER JOIN [Lookup Competency] ON [Lookup
People].PEOPLE_COMPETENCY=[Lookup Competency].[Competency Identifier]
WHERE
((([Lookup Competency].[Competency Identifier])=[COMPETENCY_ID])) ORDER BY
[Lookup People].PEOPLE_NAME;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box 2
Configuring a lookup field to display a different column 2
Slow Combo Box (intermittent) 1
Insert data into Combo 2
Combo Box Lookup Value 4
Combo box assigned to a field not working 1
Combo box selection updates multiple source fields in underlying t 1
Combo not showing value 1

Top