Auto-update Text Fields Using a Combo Box

[Guest]

I'm creating a Form that displays data from a query.

The unique identifier in my query is the Name column.

What I'm trying to do is use a combo box to select a specific Name, then
have multiple text fields auto-update with other selected columns from the
query.

If I need a command button to do this, instead of it auto-updating, that's
fine too; but I mainly need to know what controls I need to use for the text
fields to link them to the selction in the combo box.

Thanks!!

 

[Guest]

You can do it be specifying the column number in the combo RowSource, the
count start with 0.
So, if you have a RowSource
Select Field1, Field2 From TableName

And you want to assign the value from the second column (Field2) in the
combo to a text box, you can write on the after update event of the combo

Me.TextBox = Me.ComboName.Column(1)

Or, you can write in the text box ontrol source and it will be updated
automatically
=[ComboName].Column(1)


Again: the column count start with 0

 

[Guest]

I see; so basically, the cbo itself already has the info stored in it, just
hidden. And the text box won't actually call from the table/query itself,
but rather the hidden info in the cbo?

Seems a little unusual, but it works perfectly.

Thank you much!!

 

[John Vinson]

On Mon, 17 Jul 2006 09:58:03 -0700, SSgt Chris Kennedy <SSgt Chris

I'm creating a Form that displays data from a query.

The unique identifier in my query is the Name column.

That's probably a Bad Idea. Have you never met another person named
Chris Kennedy? I've encountered several other people named John
Vinson.

Names are *NOT* good unique identifiers, because they're... ummm...
not unique.

What I'm trying to do is use a combo box to select a specific Name, then
have multiple text fields auto-update with other selected columns from the
query.

And if you're trying to STORE this information redundantly in a second
table, that's a Bad Idea too.

Consider instead using a unique (numeric, autonumber is convenient)
PersonID, and just *displaying* the other fields from the combo box,
rather than trying to store them in your other table. Use
controlsource properties like

=comboboxname.Column(n)

where n is the zero based index of the field in the combo's rowsource
query.

John W. Vinson[MVP]

 

[Guest]

Thank you for your advice!! I agree with everything you've said, and I
normally would follow that. To help settle your concerns, the tables
themselves are primary keyed to an auto-number, it's just the combo box that
uses people's names as a selector. Also, this is just for internal training
records in an office of less than 15 people; as someone rotates out, their
name will be removed from our records, so it's highly unlikely that we'll get
two people of the same name in here at the same time, let alone of the same
rank (which I've also got in the primary table).

As far as the redundancy, it's not a database that I will be the primary
updater of so storing people's names in every table, while redundant, is only
for clarification for the people who actually will be updating the tables
with more info.

Your info, though, is definately useful to anyone making a database, and
should be considered standard before a more tailored solution is attempted
(not that I necessarily consider myself competent enough to do that, but I at
least know where to go to ask questions

Thanks for the good advice!!