Updating two tables via a form

Question

Hi all 

Hoping you can help.

I am trying to create a form that updates two tables. I have a Customer table and an AccountCustomer table. The form I'm trying to create is to update the customer information when they become an account customer. My idea is to use a combo box to select the CustomerID(PK) from the Customer table which populates the form including the Account Customer information if there is any (Not all customers have accounts ). I would then like to update the form and submit which then updates the tables. Nothing that I have read in forums or watched on YouTube tutorials has been able to help. 

Fingers crossed!

Answer 1

Can you explain why you have two tables? That seems very strange, because there is a simple alternative of having a single table with a CustomerStatus field, which could have values like Lead, Prospect, AccountCustomer, etc.

---

-Tom. Microsoft Access MVP

Answer 2

The Customer table has the customer details in it - 

CustID, LastName, FirstName, Email, Address, PhoneNumber, CustAccountFlag

The AccountCustomer table has account information(if there is any)

CustBusinessName, CustBusinessAddress, CustContactName, CustAccountNumber.

It is for a course I'm doing and we need to create a  prototype. There is also an Order table which gets populated via an order form. You have to be an account customer to be able to use it. Hence the AccountCustomer table

Answer 3

Typically, that second table would only be needed if customers can have multiple accounts.  Otherwise, I second Tom's advice to use a single table, even in the scenario you are describing. 

If your customers can hold multiple accounts, you could use a form/subform setup to easily update both tables:

Create a Form That Contains a Subform

---

Miriam Bizup Access MVP

Answer 4

This is very frustrating as it is an assessment I am doing for a course where the tables were pre-defined. We were also given pictures on how the forms should be set out. I am extremely new to this however I have managed to complete all other parts of prototype (order form, customer registration form, product entry form, relationships, tables, queries and reports) Just this one form is causing me no end of grief.

I've tried using the subform but can't seem to get the subform to populate with the linked data. Tables are linked by the CustomerID field . When you select CustomerID it would populate all fields from both tables. You could then modify and update. This leads to the next issue, I want to be able to use a button on the form to update the records. Using the SaveRecord option creates a new record rather than updating the existing.

Any help would be so appreciated as I have spent/wasted so many hours it is ridiculous.

Thanks again

Answer 5

The two-table model doesn't make a lot of sense, but given that you are forced to work with it, what you have is a one-to-one relationship type between Customer and AccountCustomer on CustomerID.  CustomerID in the latter is both its primary key, and a foreign key referencing the primary key of Customer.  This means of course, that while the primary key of Customer can be an autonumber, that of AccountCustomer cannot; it must be a straightforward long integer number.  The relationship must be enforced.

The interface would be a form, in single form view, bound to Customer, and a subform, also in single form view, bound to AccountCustomer.  The LinkMasterFields and LinkChildFields properties of the subform control will both be CustomerID.  A customer record will firstly need to be inserted into the parent form.  Only then can you insert a record into the subform, as to do otherwise would violate referential integrity.

Your question about a button to 'update' the records is confusing.  In a bound form a record is automatically saved when you move to another record, close the form or otherwise specifically save the record.  In your case the parent form's record is automatically saved when you move focus to the subform.  The subform's record is automatically saved when you move the parent form to another customer record or close the form.  So, what do you mean by 'update'?

When you refer to 'selecting' a customer, does this imply that there is an unbound control, usually a combo box, in the parent form in which you wish to select a customer and move to that customer record?  If so you'll find an example in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

---

Ken Sheridan, Stafford, England

Answer 6

Hi again

Firstly thank you to all that have taken the time to add your thoughts I really have appreciated this. Secondly, I have been able to get my form working. Thirdly, sorry if I was not clear enough in describing my problem...

The answer was a simple query! I'm sure I had tried it before but must not have set the right query.

Joined by the CustomerID to include all records from the Customer table and only those records from the AccountCustomer table where the joined fields are equal.

This also enables me to edit the records and they apply when you click the next record. I can work with this and should be able to get around the buttons.

This has solved something that I have been trying to do for weeks. The suggestion of the answer came about by randomly asking an enterprise architect who sits next to me at work. Wish I'd asked him weeks ago!