Data in form not filling background table field

L

lbodell

I've two synchronised comboboxes in a form 1st one selecting staff names, 2nd
one automatically selects their department number. This works fine, however,
information on the 2nd combo does not appear on corresponding background
table's field. Is there a way to solve this problem easily?

Thanks in anticipation
 
J

John Vinson

I've two synchronised comboboxes in a form 1st one selecting staff names, 2nd
one automatically selects their department number. This works fine, however,
information on the 2nd combo does not appear on corresponding background
table's field. Is there a way to solve this problem easily?

Thanks in anticipation
Click to expand...

Please post your code.

Just setting the second combo's RowSource will not populate the field;
you'll need to set its Value property as well - but I have no idea how
you're doing the synchronization.

John W. Vinson[MVP]
 
L

lbodell

Hi Rick

mmmmm no. I'm almost embarrassed to say how i've synchronised these fields.

[FULLNAME] field controls both [FULLNAME] and [Dept]. The [Dept] contains
two columns (Dept and FULLNAME) with the Dept column first. When [FULLNAME]
is selected the corresponding [Dept] number automatically appears.

How should I have done this?
 
J

John Vinson

How should I have done this?
Click to expand...

Avoided storing any redundant data, and avoided storing names in two
different tables; they're NOT stable and they're not unique.

If you need to store the identity (name, contact information,
department, etc.) of a person, store it ONCE and once only, in a
People table; in other tables, store *just* the unique ID from the
people table as a link.

John W. Vinson[MVP]
 
L

lbodell

Hi John

Thank you for keeping with me in my struggles.

As you mention, I have a 'people table' which has 3 columns [unique id;
name; dept no]. (I'm using 2000 by the way.)

I then used the lookup wizard in a another table (consultation) to bring the
[staff name] and [dept]. I've tried your suggestion of using the unique id
field only, but it isn't producing the effect I need. Any other suggestions?

thank you Lyn ...
 
J

John Vinson

Hi John

Thank you for keeping with me in my struggles.

As you mention, I have a 'people table' which has 3 columns [unique id;
name; dept no]. (I'm using 2000 by the way.)

I then used the lookup wizard in a another table (consultation) to bring the
[staff name] and [dept]. I've tried your suggestion of using the unique id
field only, but it isn't producing the effect I need. Any other suggestions?
Click to expand...

What is "the effect you need"?

If it is to see the name and department in the Table datasheet,
rethink your assumptions! The "Lookup Field" is of VERY limited
capability (one field only for example), and most developers avoid
using it altogether.

Table are NOT designed for data presentation or editing. They should
stay "under the hood"; you as the user, or your users, should see ONLY
forms for data interaction.

On a Form you can put a combo box bound to the uniqueID but displaying
the name - just include all three fields in the Query upon which the
combo box is based. Set the column widths property so that the ID is
of zero width, but it's the bound column; make the name the first
visible column, so that it will be shown when the combo is not dropped
down; and put a separate textbox on the Form next to the combo with a
control source

=comboboxname.Column(2)

to display the department (the third field in the combo's row source
query; the Column property is zero based).

John W. Vinson[MVP]
 
Top