Combobox column not displaying although there is data

  • Thread starter Thread starter Maverick
  • Start date Start date
M

Maverick

This one has really got me going crazy. I can get it to work in one database,
but can't get it to work in another (the concept that is). I have a table
that stores employee names (tblStaff) and a table that stores events
(tblEvents) they go to. I have a combobox whose rowsource grabs the employee
names from the tblStaff via a query which concatenates the fields FirstName
and LastName and filters by the field Active. The idea is to have the
combobox only display the names of active employees as part of its list for
selection.

I can get the combobox list limited to only active staff, but when the
employee becomes inactive they no longer show up on the form...even though
the underlying record still has the information. In addition, if I change the
properties of the combobox to display the ID field (which is the Primary
Key), it does this with no problem.

For example, Column 1 has the ID and its value is 5; Column 2 has StaffName
and its value is Fred Flintstone. Column 1 is the BoundColumn. I have the
column count as 2 and the widths of the columns are 0";1.25". With this
setup, nothing appears in the combobox and if I create a textbox and have the
value of this textbox equal to Column(1) so that it refers to the second
column, it returns no value either. If I switch things around and have
0.25";0" as the column widths, then the value 5 is displayed in the combobox.

I hope I described my issue well enough to at least get the idea out there.
This happend in both Access 2003 and 2007, so it's not limited to one
version. The oddest part of it all is that I have another database that I do
the same thing with but of course have a different table for the data (both
have tblStaff of course).

Please help as I am at my wits end. Thank you so much. :-)

--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.
 
Maverick said:
This one has really got me going crazy. I can get it to work in one
database,
but can't get it to work in another (the concept that is). I have a table
that stores employee names (tblStaff) and a table that stores events
(tblEvents) they go to. I have a combobox whose rowsource grabs the
employee
names from the tblStaff via a query which concatenates the fields
FirstName
and LastName and filters by the field Active. The idea is to have the
combobox only display the names of active employees as part of its list
for
selection.

I can get the combobox list limited to only active staff, but when the
employee becomes inactive they no longer show up on the form...even though
the underlying record still has the information. In addition, if I change
the
properties of the combobox to display the ID field (which is the Primary
Key), it does this with no problem.

For example, Column 1 has the ID and its value is 5; Column 2 has
StaffName
and its value is Fred Flintstone. Column 1 is the BoundColumn. I have the
column count as 2 and the widths of the columns are 0";1.25". With this
setup, nothing appears in the combobox and if I create a textbox and have
the
value of this textbox equal to Column(1) so that it refers to the second
column, it returns no value either. If I switch things around and have
0.25";0" as the column widths, then the value 5 is displayed in the
combobox.

I hope I described my issue well enough to at least get the idea out
there.
This happend in both Access 2003 and 2007, so it's not limited to one
version. The oddest part of it all is that I have another database that I
do
the same thing with but of course have a different table for the data
(both
have tblStaff of course).
Click to expand...


The problem is that, since you aren't showing the bound column and the
combo's list doesn't contain the current value of that column, Access
doesn't know what to show in the combo box. There's no way for Access to
lookup the current ID in the list and show the second column, because the ID
isn't in the list.

To get around this on a single form (not continuous), you can set up the
combo box to query all the active staff records *plus* the record for the
current record, and requery the combo box each time a new record becomes
current. For example, you could have a rowsource like this:

SELECT StaffID, StaffName FROM tblStaff
WHERE Active <> 0
OR ID = [Forms]![frmEvents]![cboStaffD]

And then an event procedure for the form's Current event like this:

Private Sub Form_Current()

Me.cboStaffID.Requery

End Sub

Something along those lines should work, though (as I said) only for a
single-form view.
 
Thanks for the reply. Currently my RowSource is as follows:

SELECT qryEmployeesExtended.ID, qryEmployeesExtended.EmployeeName,
qryEmployeesExtended.Company FROM qryEmployeesExtended WHERE
(((qryEmployeesExtended.Active)=Yes)) ORDER BY qryEmployeesExtended.Company
DESC , qryEmployeesExtended.LastName;


When I click on Run in the query builder, I see all of the result with
information for all of the columns. It's all there in the query, but only the
ID will show for the combobox.

I am also requerying the combobox already.

Thanks again for your reply.

--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.


Dirk Goldgar said:
Maverick said:
This one has really got me going crazy. I can get it to work in one
database,
but can't get it to work in another (the concept that is). I have a table
that stores employee names (tblStaff) and a table that stores events
(tblEvents) they go to. I have a combobox whose rowsource grabs the
employee
names from the tblStaff via a query which concatenates the fields
FirstName
and LastName and filters by the field Active. The idea is to have the
combobox only display the names of active employees as part of its list
for
selection.

I can get the combobox list limited to only active staff, but when the
employee becomes inactive they no longer show up on the form...even though
the underlying record still has the information. In addition, if I change
the
properties of the combobox to display the ID field (which is the Primary
Key), it does this with no problem.

For example, Column 1 has the ID and its value is 5; Column 2 has
StaffName
and its value is Fred Flintstone. Column 1 is the BoundColumn. I have the
column count as 2 and the widths of the columns are 0";1.25". With this
setup, nothing appears in the combobox and if I create a textbox and have
the
value of this textbox equal to Column(1) so that it refers to the second
column, it returns no value either. If I switch things around and have
0.25";0" as the column widths, then the value 5 is displayed in the
combobox.

I hope I described my issue well enough to at least get the idea out
there.
This happend in both Access 2003 and 2007, so it's not limited to one
version. The oddest part of it all is that I have another database that I
do
the same thing with but of course have a different table for the data
(both
have tblStaff of course).
Click to expand...


The problem is that, since you aren't showing the bound column and the
combo's list doesn't contain the current value of that column, Access
doesn't know what to show in the combo box. There's no way for Access to
lookup the current ID in the list and show the second column, because the ID
isn't in the list.

To get around this on a single form (not continuous), you can set up the
combo box to query all the active staff records *plus* the record for the
current record, and requery the combo box each time a new record becomes
current. For example, you could have a rowsource like this:

SELECT StaffID, StaffName FROM tblStaff
WHERE Active <> 0
OR ID = [Forms]![frmEvents]![cboStaffD]

And then an event procedure for the form's Current event like this:

Private Sub Form_Current()

Me.cboStaffID.Requery

End Sub

Something along those lines should work, though (as I said) only for a
single-form view.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
After thinking about your reply, I now understand what you were getting at.
It makes sense and best of all it works. Now the name only appears in the
dropdown list when it is the name that is the value of the combobox.
Otherwise, it is suppressed from the list like I want.

I figured out why my other database was working and this one was not. It was
a matter of bound fields and what was being pushed to the tables.

Thanks so much!

--

HTH

Don''''t forget to rate the post if it was helpful!

Please reply to newsgroup only, so that others may benefit as well.


Dirk Goldgar said:
Maverick said:
This one has really got me going crazy. I can get it to work in one
database,
but can't get it to work in another (the concept that is). I have a table
that stores employee names (tblStaff) and a table that stores events
(tblEvents) they go to. I have a combobox whose rowsource grabs the
employee
names from the tblStaff via a query which concatenates the fields
FirstName
and LastName and filters by the field Active. The idea is to have the
combobox only display the names of active employees as part of its list
for
selection.

I can get the combobox list limited to only active staff, but when the
employee becomes inactive they no longer show up on the form...even though
the underlying record still has the information. In addition, if I change
the
properties of the combobox to display the ID field (which is the Primary
Key), it does this with no problem.

For example, Column 1 has the ID and its value is 5; Column 2 has
StaffName
and its value is Fred Flintstone. Column 1 is the BoundColumn. I have the
column count as 2 and the widths of the columns are 0";1.25". With this
setup, nothing appears in the combobox and if I create a textbox and have
the
value of this textbox equal to Column(1) so that it refers to the second
column, it returns no value either. If I switch things around and have
0.25";0" as the column widths, then the value 5 is displayed in the
combobox.

I hope I described my issue well enough to at least get the idea out
there.
This happend in both Access 2003 and 2007, so it's not limited to one
version. The oddest part of it all is that I have another database that I
do
the same thing with but of course have a different table for the data
(both
have tblStaff of course).
Click to expand...


The problem is that, since you aren't showing the bound column and the
combo's list doesn't contain the current value of that column, Access
doesn't know what to show in the combo box. There's no way for Access to
lookup the current ID in the list and show the second column, because the ID
isn't in the list.

To get around this on a single form (not continuous), you can set up the
combo box to query all the active staff records *plus* the record for the
current record, and requery the combo box each time a new record becomes
current. For example, you could have a rowsource like this:

SELECT StaffID, StaffName FROM tblStaff
WHERE Active <> 0
OR ID = [Forms]![frmEvents]![cboStaffD]

And then an event procedure for the form's Current event like this:

Private Sub Form_Current()

Me.cboStaffID.Requery

End Sub

Something along those lines should work, though (as I said) only for a
single-form view.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top