Need some help with auto-updating fields from tables

[BigToe]

Hello,

I'm creating a simple database for my dad so he can keep track of online purchases for his company.

Here's the main format of the database:-

One table called "Customers" which contains details of the customers including name, address, telephone etc. The primary key for this table is "email" as this will be unique to each customer.

The second table is called "Purchases" and is basically used everytime a customer makes a purchase. It contains details of the item purchase, the cost and the current status of the order etc. The primary key for this is an auto-number field called "Job ref.". In order to identify the customer in the purchase, I have a field to enter the customer's email address. What I want to happen is this: When the email address is entered, I want the corresponding customer's full name to be displayed in a field automatically - as opposed to having to enter the name manually every time. So basically, I want the email address to cross-reference so I can see details on the customer in the purchase.

Hope this makes sense.

Any help much appreciated, I am quite a n00b when it comes to MS Access.

Thanks in advance

 

[BigToe]

Thank you Pat, but unfortunately that's not quite what I was looking for.

However, I've managed to find a way to make it do what I want

I found that by simply creating a form with fields from the Purchases table AND Customers table, with a one-to-many relationship between Customers.email and Purchases.email, it would do what I want - if I type in the email address into the Purchases.email field, the corresponding customer information from the Customers table is displayed automatically.

Told you I was an Access n00b

But now I have another question

If I call customer data up by their email address in the aforementioned manner, I have found that it won't let me edit these details.
For instance, say I type in joe@bloggs.com into the Purchases.email and his data (from the Customers table) appears in the appropriate fields, when I try and change his telephone number (stored in Customers.telephone), I get an error beep and can't progress records until I press escape.

Any ideas?

TIA

 

[BigToe]

Thanks for the reply, I'll check tomorrow when I'm at work.

 

[BigToe]

Ok, now Access is letting me change the customer details on-the-fly

How bizarre. It must have been a mistake on my part.

Now I know you are probably getting sick of me by now, but I have another question.

In regard to the question about editing Joe Blogg's telephone number earlier, is it possible to type in a random NEW email address and have it create a brand-new empty customer record for me to edit without having to manually go into the customers table and do it the traditional way?

Example: I am entering the details of another purchase and realise that I haven't created a new customer account for this particular customer. What I want to do is type his/her email address in (which would normally bring up their record) and be able to simply enter the details for them and have the database automatically enter this new record into the Customers table.

If I try and do this at the moment, I get the following error message: The Microsoft Jet database engine cannot find a record in the table 'Customers' with key matching field(s) 'Purchases_email'. Which to my knowledge means it is searching for a record with the new email address, yet naturally it doesn't exist yet.

As usual, any help much appreciated