Form that pulls data from tbl for new record Access forms


This may be easy, but I trying to create a form that searches the table and then once that data is returned have a button to take that data and put that into a form to add a new record.

I want to take certain and address, ect and use this to enter a new record.  Don't want user to enter data twice just to reuse same data.

How would I be able to do this?  I have a form that has an unbound control and a subform that requeries that data which is linked to the main form's control...this pulls the data and shows it to me.  I want then to take that and address, phone #, license information and put that into a new form to enter a new record but without entering that data again....just have the fields populated for a new record.

Thought maybe I can just create a form that links the fields


would this be the best way?
Ernest GroggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Unless you have a distinct reason for doing so, you should NOT store data in that manner. Instead, you should use relationships between your table to manage that data. Essentially, you store the Primary Key value of the "Parent" table in the "Child" table, instead of storing the full detalls of the "parent" item.

For example, if I have an Invoicing system, I'd have tables like this:

etc etc

etc etc

etc etc

There are 2 distinct relationships between those three tables:

tInvoiceHeader is "related" to tCustomer on the tInvoiceHeader.CustomerID field.
tINvoiceDetail is "related" to tInvoiceHeader on the tInvoiceDetail.INvoiceID field.

So when I create a new Invoice, I"d select the Customer, and the value of CustomerID would be stored in the table. When I add a new Detail line to my Invoice, the value of InvoiceID would be stored in the INvoiceDetail table.

There are times when you want to store data of this nature - for example, in the InvoiceDetail table, you'd have a Cost field, and a Description field, perhaps. Those values could change over time, but you want to know what those values were when the record is created - so you'd store those values in the InvoiceDetail table.
Ernest GroggAuthor Commented:
OK I see what you are saying...I set it up wrong....I get it.

I have 2 tables already. (1 to Many relationship)


so then since I am new at really making a form that will search off of a customerID and show the data and then be able to enter the new data into the form, how can I set this up.

Next question then would be to set a trigger if the customerID is not there then trigger a box to ask to add a new customerID.

sorry, this part I am relatively new at developing....I am a little better at queries....

There are a lot of variations on how address data might be stored but it usually comes down to how it is used.  The two most common reasons for isolating address data in a separate table are.
1. multiple customers may use the same address.
2. the customer needs multiple addresses some of which might be the same.

#1 might exist in a subscription type model such as Readers' Digest.  Some customers have a winter home and a summer home so the magazine is sent to the winter home from Nov-Mar and the summer home the rest of the year.  The customer may also have a third address for billing which is separate from either delivery address.
#2 is common in order entry applications where the customer always has a billing address and a shipping address that may or may not be the same.

To implement either of these correctly, a third table is required.  the third table is sometimes called a junction table and it creates the relationship between customer and address.  That allows one customer to have multiple addresses and one address to be used for multiple purposes/customers.  A 1-m relationship where you store AddrID in customer means that you need to predefine all relationships so that you would have one column for billingAddrID and a second for shippingAddrID, and a third for whatever, etc.  This still allows one address to be used for multiple customers but violates first normal form and requires a schema change should you need a new address type.  Going the other way where you put customerID in the address table, you would still need to include a type column so you can find the billing or shipping or whatever.  This method is very restrictive though and does not allow for reuse of an address record as both shipping and billing so it is generally only used by accident and then when the developer discovers the error, he adds new type codes that combine types so you have bill, ship, and bs - a generally poor idea.

The recommended relation table includes three columns (and possibly a fourth autonumber column but that isn't usually necessary).


This allows customerA to use Addr1 for shipping and customerB to use Addr1 for billing.  Definitely odd but I've actually seen this in the real world.  The customers were subsidiaries and co-located.

The three keys of the junction table should be used to create a compound unique index to prevent duplicates.
Don't want user to enter data twice just to reuse same data.
That is the whole idea in a nutshell ;)
sorry, this part I am relatively new at developing
It never hurts to review the basics and be sure you have them down pat
This remain the best data normalization tutorial I have encountered.

There is no end of evil that occurs because the data schema was not well-planned and executed before the construction of forms and reports began.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ernest GroggAuthor Commented:

From reading that article from Nick67, I found that I was separating the data into the correct tables but misunderstood the idea with Primary Keys and Foreign Keys.  I used unique keys on my tables and did a "Many to Many" relationship.  

What I needed was, a One to Many giving me Unique records for the Customer (using a primary key that cannot have dups), and a Many table that can have multiple entries that carry a "Foreign Key".

Since this db doesn't order products but simply keeps track of customer entry onto an area, two tables I think were only needed to accomplish the task.

I had the right idea but the wrong type of relationship created.  Creating a Primary and Foreign Key relationship allows me to create the type of form I was looking for.

I am able to pull the customers information and just add the data entry time, and other details needed.  Since the customer is always going to be unique based on my selection of identification process, it makes sense that I can enter a new customer now and also add detail if the customer has been there before.

Makes my life a little easier and allows the customer asking to just reenter new "entry detail" to a previous customer.  But it becomes complicated if the customer has not "signed out" so therefore I already created the ability to make sure that customer gets signed out before signing into the area again.

Great information!  Thanks so much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.