have field on form = value from a TABLe/not ref on form

[babs]

sorry - I posted yesterday and have not received an answer

I have an append query that is able to append in the customer name - we use
to have the user enter this all manually - now if appended I would like the
customer code to be automatically input if it is null - after the customer
name gets appended in-

control name on the form is txtcustomer code

table name is AR-Customers

thinking I need to use an event procedure-not sure what to event to tie it
to(afterupdate of customer name?)

thinking iif(txtcustomercode is null, ??????? not sure how to reference to
grab customer code value for that customer name in the AR-Customers Table??)

Previously on the Form the user manually input the customer code from a
dropdown and the customer Name was automatically put in since a column in the
dropdown bos( i get how to do this)

On the underlying TABLE- the only item that can be appended in is the
customer Name --there is a table AR customers in the database that has the
customer code, and customer name - again not sure how to GRAB this customer
code - based on the customer Name put in(appended in)



Thanks,
Barb

 

[Beetle]

So these customer codes and names already exist in
one of the tables in your database, and you want a
user to manually enter a customer name on a form
somewhere, then you want to programmatically find
that existing customer name and code from that table
and append both of those values to another table?

Hopefully that is *not* what you are trying to do, but
that's what it sounds like from your post. Can you clarify?

 

[babs]

the customer Name is appended into a table - that a form is based off of.

I want on the form to have once the record with the customer Name is
appended into the table that the form is based off of - to have the customer
CODE be looked up in another table(AR customers) and grab that customer code
based on the customer Name appended in - and then feed-or append that
customer code into that table based on the customer name that was appended,

thanks so much for helping,
barb

 

[Beetle]

There are a few pitfalls with what you're trying to do here
(at least as I understand it).

First and foremaost, if the customer name and code already
exist, you should not be redundantly storing them in another
table. If the customer code is the primary key value for the
customer, then it would be appropriate to store that value in
another related table as a foreign key, but not the name.
You would typically use a combo box on a form to allow the
user to select a customer name and the customer's PK value
would automatically be stored in the related table. No appending
and extra coding necessary.

The other potential problem with your method (besides the fact
that it's extra work for the users to have to completely type in the
customer name every time) is that a user may spell a name
incorrectly. Now you have an incorrectly typed name appended
to your secondary table, and when you ask the application to look
up the customer code for that name, it won't be able to find it.
Now you have bad data in your secondary table which will require
still more work to go back and correct.