Form that pulls data from tbl for new record Access forms

Posted on 2014-12-22
Medium Priority
Last Modified: 2014-12-25

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 data...name 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 data....name 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?
Question by:Ernest Grogg
LVL 85
ID: 40514663
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.

Author Comment

by:Ernest Grogg
ID: 40514678
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....

LVL 41

Expert Comment

ID: 40514890
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.
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 40515341
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.

Author Comment

by:Ernest Grogg
ID: 40517695

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!

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question