Appending Codes from a list.

[Fred]

Let's say I have a field "codes" in a table named "companies" and a little
table "Codes_LU" which is just to create a dropdown list. And I have a
form "CompanyForm" which shows the company table.

I'd like to add a little box (combo box or whatever you recommend) which
would show a dropdown list from Codes_LU and after I pick one, it would
append my selection onto the end of whatever is already in the "Codes" field
in the shown record of the companies table.

I'm experienced with Access but an absolute novice to coding and such boxes
in forms (I've been putting the dropdowns in the tables) so please treat
me like a dummy when you tell me how.

Thank you in advance.

Sincerely,

Fred

 

[KARL DEWEY]

it would append my selection onto the end of whatever is already in the
"Codes" field
The term 'append' in Access mean to add records but your further comment
sound like you want something else.

Can you elaborate some more on what you are trying to do?

 

[Fred]

Karl, thanks for the response.


I made a mistake in my orginal post, I forgot to say that the Codes_LU table
has a second field "CodeDefinition" which explains/defines what the code
means which I would also like to show in the dropdown, but otherwise not do
anything with.

That said, answering your question

It's a field which contains a string of codes delimited by commas (the comma
is a part of a code) So, for example, if a company is both an potential OEM
customer and a supplier, the dropdown list would contain the following codes
(plus many others)

Code CodeDefinition
OEM, Current or potential OEM CUstomer
Supplier, Supplier

This has been working well for us for 8 years but the entry process is
cumbersome. We have two fields "Codes" and "CodesAdd" with table-based
dropdown fields. To get both codes into the field, the user would (e.g.)
select "OEM," for the codes field and "Supplier," for the CodesAdd field.
Then about once a day our maintenance Macro runs two queries, one which
appends all of the "CodesAdd" fields to the "Codes" Fields, and then a second
when erases the contens of all of the "CodesAdd" fields.

Hoping to skip all that and get rid of the "codesAdd" field. Each time a
user selects a code, it gets appended to the Codes field.

Also this could take the lookups out of the tables so that the expert
developers won't beat me up when they hear about those.

I know that this scheme is not fully normalized, but a second linked "Codes"
table would make our filtering & querying based on this field too complex and
abstract for the nature of use that it gets.

Thanks

Fred

 

[Fred]

PS I think that I used the wrong word "append". I think it S/B concantate.

 

[KARL DEWEY]

UNTESTED UNTESTED
On the combo box AfterUpdate event call macro to SetValue.
set -- [Forms]![YourFormName]![Codes]
with -- [Forms]![YourFormName]![Codes] & ", " &
[Forms]![YourFormName]![YourCombo]

Yeah, get rid of table lookup fields.

 

[Fred]

Dear Karl,

Thanks for the reply. But you might have forgot to treat me like a Dummy
regarding cod and combo boxes.

When you said:

On the combo box AfterUpdate event call macro to SetValue.

set -- [Forms]![YourFormName]![Codes]
with -- [Forms]![YourFormName]![Codes] & ", " &
[Forms]![YourFormName]![YourCombo]


Did you literall mean to create and Access Macro and cal it from the
AfterUpdate event? If so, what goes in the left "action" column?
Normally that is something like "open form" etc.

And where would that text / code that you gave me actually go?

Thanks

Fred

 

[KARL DEWEY]

what goes in the left "action" column?
SetValue

 

[Fred]

Dear Karl,

Thanks for the responses. However, I fear that I may have made the example
overly complicated / lengthy, and you aren't explaining it very much /
assuming too much for someone who is at my dummy level in this area of
Access. I'll repost with a cleaner example.

Thanks again

Fred

 

[John W. Vinson]

Let's say I have a field "codes" in a table named "companies" and a little
table "Codes_LU" which is just to create a dropdown list. And I have a
form "CompanyForm" which shows the company table.

I'd like to add a little box (combo box or whatever you recommend) which
would show a dropdown list from Codes_LU and after I pick one, it would
append my selection onto the end of whatever is already in the "Codes" field
in the shown record of the companies table.

Why?

Are you trying to store multiple values in a single text field? Not only is
this redundant (storing the same information in more than one place), it's
much harder to sort or search than storing it properly, in a related table.

Are you quite certain that you want to do this? If so, please explain the
business logic.