Solved

Form that pulls data from tbl for new record Access forms

Posted on 2014-12-22
5
251 Views
Last Modified: 2014-12-25
Hello,

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

=Forms!frmSearch!Phone

would this be the best way?
0
Comment
Question by:Ernest Grogg
5 Comments
 
LVL 84
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:

tCustomer
------------------
CustomerID
CustomerName
etc etc

tInvoiceHeader
-------------------------
InvoiceID
CutomerID
InvoiceNumber
InvoiceDate
etc etc

tINvoiceDetail
----------------------
InvoiceDetailID
InvoiceID
ItemID
Quantity
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.
0
 

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)

CustomerAddress
CustomerData

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....

thanks!
0
 
LVL 34

Expert Comment

by:PatHartman
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).

RecType
CustomerID
AddrID

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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 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
http://phlonx.com/resources/nf3/
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.
0
 

Author Comment

by:Ernest Grogg
ID: 40517695
So,

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!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now