Set Field Value Based on Entry in another field

[Mike]

Access 2003 and total noob here. Sorry - I can't seem to find anything that
answers my question through searches here...maybe I'm too new so I don't
recognize the solution when I see it.

I have a form where users select their organization name from a menu. The
menu is populated from a query against the organization table.

The organization table has three columns (ID (pk), ORGNAME, ORGID). The
ORGID is a two character acronym for the organization.

I need to populate a hidden field on the form with the ORGID based on the
selection made in the Org Name menu. How do I do that? I need that ORGID
because it's being used in a calculation based on a change event.

For example:

ORG TABLE
ID ORGNAME ORGID
1 Fred's Bait and Tackle FB
2 Avery's Aviary AA
3 Zelda's Crochet Shop ZC

The user chooses "Fred's Bait and Tackle" in the Org Name menu, the Org ID
field should show "FB". If "Zelda's Crochet Shop" then Org ID should be "ZC",
etc.

Does that provide enough info to explain what I'm trying to do? With
InfoPath, I could do it pretty easily - but my new users don't have InfoPath,
so I'm building this in Access and will be splitting the DB.

Thanks in advance for any help, advice, pointers y'all might have!

Mike

 

[Duane Hookom]

In Access, these controls are Combo Boxes, not Menus.

If you want a non-visible column to display in another text box on the form,
you can usually set the control source of the text box to:
=cboOrgName.Column(x)
x is the column number with 0 being the first column.

If you think you actually need to store this information rather than just
display, you can add code to the After Update event of the combo box like:
Me.txtMyTextBox = cboOrgName.Column(x)

 

[Steve]

Your use of ORGID in a calculation based on a change event is very odd. If
you want, excplain what you are doing and maybe we can help you do it in a
better way.

Steve
(e-mail address removed)

 

[Loterken]

i may be totally wrong here, but should the primary key not be ORGID?
can 2 different organizations have the same ORGID ?

 

[John... Visio MVP]

Your use of ORGID in a calculation based on a change event is very odd. If
you want, excplain what you are doing and maybe we can help you do it in a
better way.

Steve

Excplain? How can you help when you can not even spell?

John... Visio MVP

 

[John W. Vinson]

I need to populate a hidden field on the form with the ORGID based on the
selection made in the Org Name menu. How do I do that? I need that ORGID
because it's being used in a calculation based on a change event.

For example:

ORG TABLE
ID ORGNAME ORGID
1 Fred's Bait and Tackle FB
2 Avery's Aviary AA
3 Zelda's Crochet Shop ZC

Why the extra step? Cannot your calculation simply be based on the selected
value in the combo box? As Loterken suggests, you could make ORGID the Primary
Key and the bound column of your combo box (Microsoft tries to imply that
every table must have an autonumber as a primary key, but that is not the
case; the PK must be unique and should be stable and short, and your ORGID
qualifies).

 

[De Jager]

Access 2003 and total noob here. Sorry - I can't seem to find anything
that
answers my question through searches here...maybe I'm too new so I don't
recognize the solution when I see it.

I have a form where users select their organization name from a menu. The
menu is populated from a query against the organization table.

The organization table has three columns (ID (pk), ORGNAME, ORGID). The
ORGID is a two character acronym for the organization.

I need to populate a hidden field on the form with the ORGID based on the
selection made in the Org Name menu. How do I do that? I need that ORGID
because it's being used in a calculation based on a change event.

For example:

ORG TABLE
ID ORGNAME ORGID
1 Fred's Bait and Tackle FB
2 Avery's Aviary AA
3 Zelda's Crochet Shop ZC

The user chooses "Fred's Bait and Tackle" in the Org Name menu, the Org ID
field should show "FB". If "Zelda's Crochet Shop" then Org ID should be
"ZC",
etc.

Does that provide enough info to explain what I'm trying to do? With
InfoPath, I could do it pretty easily - but my new users don't have
InfoPath,
so I'm building this in Access and will be splitting the DB.

Thanks in advance for any help, advice, pointers y'all might have!

Mike

 

[joelgeraldine]

kjjkjkj