complete the rest of new form from exisitng table

[BYarn]

I have been trying for some time to resolve an issue with no luck.Let me show an example.
(existingTABLE):
First name Last name customer# payment received
john smith 1234 yes
mary jones 5678 no

(new table
First name Last name customer# address phone #

I want to have a form (or whatever) where I can fill in a customer# or last name, etc and it will AUTOMATICALLY complete the rest of the info we have from the existing table. So if I put in 1234, the 1st and last name will fill in and I only have to add say the phone and address.

The idea is that when new info is received like phone number and address, we don't have to re-enter what we already have

This may be a lousy example of what we are doing but no we can't just use the exisitng table and add new fields. The fact is that we have a substantial database with there is some over lap but the new data is unrelated to the old data

 

[jkl0]

You need to use a combo box.
The following can help you refine it a little more.
faq702-6304

 

[BYarn]

Thanks. It doesn't seem to be doing what I had hoped-maybe I wasn't clear or it can't be done. In my example
the fields in the form were:

First name Last name customer# address phone #

I have some of that data in another table.If I use a combo box, I can type and select say the customer # and at that point I want first name, last name be be completed from the existing table. I would just have to add the new info-address and phone

 

[lespaul]

are you planning on keeping both the existing table AND the new table? If so, you're breaking normalization rules by having duplicate data....



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BYarn]

Yes, but the original table with the basic customer data will NEVER be updated by the new table. The original table is only being used to extract the parts of the customer data we already have. The new table will then require say only 3 fields to be filed in instead of all say 6 since the other 3 fields will come from what we have.
The basic goal is that my boss wants it to be speedy so when our clerical staff are logging data they can do it faster

THANKS!

 

[lespaul]

So you want to be able to enter a phone number and if it's found in the original table then show the existing information in the form and add it to the new table; if the phone number doesn't exist in the original table make the user enter all the data?

Does that accurately describe what you want to occur?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BYarn]

Yes that's it. Sorry if I am not describing it clearly! What would probably happen is that we would enter the client acct# or name and then as you say show the existing information in the form and add it to the new table. The only difference is that there would be a couple of new fields that would be completed in the new table (to go along with the matched data).In our situation there would probably always be a match.
THANKS!!