Combo Box Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JWeaver - 30 Jul 2008 20:16 GMT

I have a Text Box on a Form that I am trying to change to a Combo Box. I
pull the last name and first names from the Employees Table to show in the
combo boxes. Every time I do this, either with the wizard or manually, the
last name still displays in the first name box. I tried telling it that the
bound column is the 3rd one since the Emp ID is also in the combo box
although not displayed but this doesn't work. The properties of the combo
box indicate there are 3 columns with the Row Source being:

SELECT Employees.[Emp ], Employees.[Last Name], Employees.[First Name] FROM
Employees ORDER BY [Last Name], [First Name];

The one I converted for Last Name worked fine when I changed the bound
column to 2 but the one for First Name doesn't work if I change the bound
column to 3. What am I missing?

Signature

JWeaver

Reply to this Message

JWeaver - 30 Jul 2008 20:51 GMT

Disregard. I changed the SELECT order and was able to get it to display the
first name in the box.

Signature

JWeaver

> I have a Text Box on a Form that I am trying to change to a Combo Box. I
> pull the last name and first names from the Employees Table to show in the
[quoted text clipped - 10 lines]
> column to 2 but the one for First Name doesn't work if I change the bound
> column to 3. What am I missing?

Reply to this Message

Klatuu - 30 Jul 2008 21:59 GMT

You really proabably don't need two boxes. I would suggest:
SELECT EmID, FirstName & " " & LastName AS Full Name FROM tblEmployee;

That way, both first and last names will be visible. If you are using the
combo for a search, make Column 1 the bound column and set the column widths
so the first column is hidden. For example 0";2.5"
Then the name will show, the ID will be hidden, and you search on the ID.

Signature

Dave Hargis, Microsoft Access MVP

> Disregard. I changed the SELECT order and was able to get it to display the
> first name in the box.
[quoted text clipped - 13 lines]
> > column to 2 but the one for First Name doesn't work if I change the bound
> > column to 3. What am I missing?

Reply to this Message

JWeaver - 31 Jul 2008 16:35 GMT

Thanks, Dave! I tried your suggestion and it works. However, my problem is
that the names that were already in the Last Name field are not updated to
include the First Name also. How do I do this?

Your suggestion works much better than mine since I noticed when I went back
over my data on my Form that even though the First Name or Last Name was
correct, the corresponding other half of the name may not have been if there
were more than one with the same name, i.e., Michael as First Name or Smith
as Last Name. I am afraid that this would mess up my data if I left this so
I turned all of the combo boxes I had back to Text boxes. I don't like the
Text boxes though because my data could become inconsistent which is why I
wanted to use Combo Boxes instead.

Signature

JWeaver

> You really proabably don't need two boxes. I would suggest:
> SELECT EmID, FirstName & " " & LastName AS Full Name FROM tblEmployee;
[quoted text clipped - 21 lines]
> > > column to 2 but the one for First Name doesn't work if I change the bound
> > > column to 3. What am I missing?

Reply to this Message

Klatuu - 31 Jul 2008 16:47 GMT

Are you saying you are using names from one table to enter into another table?
If so, you really should not do that. It violates the data redundancy rules
for database normalization.

The correct design is to carry the name fields in only one table. In the
other table, you would use the EmployeeID as a foreign key and theny use a
query when you want to show the name with the other record.

And, it sounds like you are using the combo as a bound control. So, I need
a bit more detail on what it is you are trying to accomplish, please/

Signature

Dave Hargis, Microsoft Access MVP

> Thanks, Dave! I tried your suggestion and it works. However, my problem is
> that the names that were already in the Last Name field are not updated to
[quoted text clipped - 34 lines]
> > > > column to 2 but the one for First Name doesn't work if I change the bound
> > > > column to 3. What am I missing?

Reply to this Message

JWeaver - 31 Jul 2008 18:41 GMT

Yes, unfortunately, I have the names listed in 2 tables. I inherited a
database that includes all data in one large table but should really be
broken down into several smaller tables. I have been trying to figure out
the best way to do this.

I have one large Payroll table but I also have an Employees table and a
recently created Clients table. I know that I need to add PK ID fields to
the Employee and Client tables and use these ID's as FK's in the Payroll
table instead of the actual names but I've been afraid to remove the
duplicate values from the larger table for fear that I will corrupt the data
that is already in the table.

I'm considering whether I should start over and create all of the tables I
need from scratch or to just break down the larger table into smaller tables
of related data (maybe using the Table Analyzer as a starting point). One
fear is that I have a great deal of reports already created using the
database like it is and I am afraid that if I start over that I will not be
able to get the reports designed correctly.

What do you suggest?

Signature

JWeaver

> Are you saying you are using names from one table to enter into another table?
> If so, you really should not do that. It violates the data redundancy rules
[quoted text clipped - 45 lines]
> > > > > column to 2 but the one for First Name doesn't work if I change the bound
> > > > > column to 3. What am I missing?

Reply to this Message

Klatuu - 31 Jul 2008 19:02 GMT

I understand your problem. At this point, remediating such a mess would
actually be a rewrite.

The basic idea to fix your problem is to use the After Update event of the
combo box to populate a text box that is bound to the name that is not
getting populated.

Signature

Dave Hargis, Microsoft Access MVP

> Yes, unfortunately, I have the names listed in 2 tables. I inherited a
> database that includes all data in one large table but should really be
[quoted text clipped - 66 lines]
> > > > > > column to 2 but the one for First Name doesn't work if I change the bound
> > > > > > column to 3. What am I missing?

Reply to this Message