Enter data into multiple tables from one form (1 Viewer)

Ctang · Jan 27, 2019

Hi, just wondering if I could have some help. I have created a 3 table relational database about car sales. So a customer table, a stock table and a transaction table. I would like to create a transaction form that will allow me to add the data into all three tables, but it will only allow data to be added to one. Any help would be greatly appreciated

theDBguy · Jan 27, 2019

Hi. If the tables are related, the usual approach is to use a form/subform setup.

Ctang · Jan 27, 2019

I created a form with sub forms, but it will only enter data into the stock table. I keep getting an error message telling me the customer does not exist and the transaction table remains empty

June7 · Jan 27, 2019

What is the form/subform design? Is main form bound to Customers and subform bound to Transactions with combobox to select stock? Or have you flipped around and have combobox to select customer? Whichever, if item is not in combobox list, use NotInList event to enter new item record 'on-the-fly' during data entry. https://blueclawdatabase.com/notinlist-event-code/

If you want to provide db for analysis, follow instructions at bottom of my post.

Ctang · Jan 28, 2019

Thank you, I will give that a try now

Ctang · Feb 4, 2019

Hi June7, I tried your solution with my students but they just cannot understand it, is there a way to do this without having to code?

mike60smart · Feb 4, 2019

Hi

As previously suggested the normal approach is a MainForm andSubforms

You do not need any code if the tables are related correctly.

Can you upload a zipped copy of the Db??

Uncle Gizmo · Feb 4, 2019

It sounds like you've got the design wrong somewhere. As others have said, you should probably be using a subform. You identify it as a Car sales database. You also identify some tables:- Customers, Stock (I assume that's cars and sundry items), and a Transaction table. I would assume that you have a main form in which you select a customer, and then on that main form you would have a subform representing transactions. In that transactions subform you would be able to choose a car or cars which would be sold to the customer. You could possibly sell other things to the customer at the same time, like a Warranty. This would be listed as an item in your stock table. This table you identify as a transaction table would normally be referred to as a "line item table" and the process is generally described as invoicing. If that sounds more like what you want, then have a look at the northwind sample database provided by Microsoft, as it is basically this sort of system set up for you. Be careful though, Some versions of the Northwind database have some issues that need rectifying before you can use them in a production environment.

jdraw · Feb 4, 2019

Further to the advice provided so far, could you give us an overview of the"system" in simple terms? You have mentioned Customer, Stock and Transactions, but what are the rules/facts in which they participate?

June7 · Feb 4, 2019

The form/subform arrangement can be built without code. However, if customer does not exist then cannot be selected from combobox list. Without code, would have to manually switch to customer form, add new customer. Then return to transaction form and click 'refresh all' on ribbon or reopen the transaction form so the combobox will list the new customer.

Same applies to Stock table.

The more user friendly, the more code.

mike60smart · Feb 4, 2019

Hi June7

I was going down the route of the user selects a Customer and then carries out a Transaction(s).

June7 · Feb 4, 2019

What do you mean by 'carries out'?

You still have not described the form structure.

As a general rule, one form edits one table, hence the form/subform structure.

Comments in my previous post still apply.

mike60smart · Feb 5, 2019

Uncle Gizmo describes the structure in Nr8 above

June7 · Feb 5, 2019

Which is basically same description I asked about in post 4 and has yet to be confirmed by the OP.

Ctang, if you want to attach db for analysis, follow instructions at bottom of my post.

Ctang · Feb 6, 2019

Hi, I have uploaded the database as it stands so far. All the data inside is fictional. You will see two forms - sales and part exchange, which have really stumped me. I really appreciate the effort everyone has been putting into my problem. Here's hoping for a quick solution!

June7 · Feb 6, 2019

Should avoid punctuation/special characters (underscore only exception) in naming convention, such as the ? mark in [PartExchange?], better would be IsPartExchange.

The form and subform have incorrect RecordSource. Main form should be bound to CustomerTbl and subform should be bound to Transactions. The subform should have combobox to select item from StockTbl. Saving SalePrice of selected stock item will require code (macro or VBA).

Having said that, can include StockTbl in the subform RecordSource but only to display related info describing the car, not to edit StockTbl data. Any controls bound to fields of StockTbl should be Locked Yes and TabStop No. Don't use INNER JOIN.

SELECT Transactions.*, StockTbl.* FROM StockTbl RIGHT JOIN Transactions ON StockTbl.[VehicleRegNo] = Transactions.[VehicleRegNo];

Also advise not to use identical field names in multiple tables.

Instead of ValueList for MakeOfCar, use an SQL statement that pulls from StockTbl.

mike60smart · Feb 6, 2019

Hi

See the attached.

The Main form is now based on tblCustomers

The subform is based on Transactions and NOT TRansactions AND Stock

View attachment CarSales.zip

As June7 has described above

Ctang · Feb 6, 2019

Thank you so much both of you for your help. I will now get my head around this so I can teach it to my students. Once again, thank you very much I really appreciate the help.

Ctang · Feb 12, 2019

Hi, really sorry about this. I have tried to re-create the answer provided (which by the way was fantastic) however whenever I try it I keep getting an error message! Please help! I have attached my poor attempt at it.

mike60smart · Feb 12, 2019

Hi

Here with the Db updated

Main form based on Customer and Subform based on Transaction table

View attachment Newest version.zip

Enter data into multiple tables from one form (1 Viewer)

Registered User.

I’m here to help

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Nifty Access Guy

Super Moderator

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Similar threads

Users who are viewing this thread