You are here:

Using MS Access/Creating multiple new child records and auto filling part of the records

Advertisement


Question
Machine3
Machine3  
QUESTION: Hi Manish
I am creating a database where part of the function is to create a Machine record (parent) with Components records (child) which are linked. This machine is then sold to a customer.

If there are 10 parts attached to the machine I will need 10 child records.

As you can see from the attached ER diagram I have a Machines, Components and Parts tables linked.

I need to be able to create a form so that when you press the create NEW MACHINE button it opens up a new machine record and opens up 10 new Component records with the Part Names (via the Parts table) and the Replacedate already filled in in the components record. The ten parts would be a bit different depending on the machine type and model.

The PartSerialNumber can then be scanned from the barcode on the actual Part and entered into the records. Each part is different.

Background information:
The reason for the Ownership table is that a customer can transfer it to another customer and I need to keep track of the machine. (Unique situation)

A sales order can be used to purchase one or MORE machines.

The sales order number used for the purchase of the machine can also be used to purchase a replacement part.

The customer can also decide to use a new sales order number to purchase a replacement part.

If a machine is moved to another customer then a new sales order will be given when a replacement part is needed.

ANSWER: Hello Bruno,

As per your problem as below:

"when you press the create NEW MACHINE button it opens up a new machine record and opens up 10 new Component records with the Part Names (via the Parts table)"


What I understand is the below:
You have a Master/Detail form, the Master form consists of fields related to a Machine. The Detail form consists of fields for Components which are related to each Machine.


I feel below is the solution that you're looking for ?
1.When a blank Machine Master/Detail form opens, it will have a Combo box listing all Machines.
2.When the user selects the name of a Machine from this Combo box, there should be a button or an AfterUpdate Event on the Combo to fetch all Components for the selected Machine and have them populated/listed in the Component detail subform.

Please confirm if that's your functional requirement. So I can further offer you tips to get around it.

Best regards
Manish Batola.






---------- FOLLOW-UP ----------

QUESTION: Hi Manish,
Thank you for your reply.

The Machine serial number will only be known when the sales order is put through.

The Manager will let me know when the sales order is processed.

Once I have the Machine Serial number I can open up a new Machine record type in the serial number and other Machine information and then press a button to pre fill 10 component records with predetermine part numbers.

The part number may vary depending on the Market it has to go into. Eg the Power supply for the Machine might be different for a different Market.

You mentioned in your reply that “there should be a button or an AfterUpdate Event on the Combo to fetch all Components for the selected Machine and have them populated/listed in the Component detail subform.”

Questions:

1.   How do you open and populate 10 or more records with predetermined part numbers in the Component detail form.

2.   Based on the Background Information  and the ER diagram that was already sent to you, do you think that the ER diagram will basically accomplish what I am trying to achieve.

I am not sure how to reply with Yahoo/Hotmail id’s for Access Community Record Keeping

Answer
Hi Bruno,

"1.   How do you open and populate 10 or more records with predetermined part numbers in the Component detail form."
- You need to have a table which will have all Machine names
- You need to have a table which will have all Component names
- You need to have a 3rd table where Each Component is linked to a Machine Name

So on your Master/Detail form when a Button is clicked  to fetch all Components for the selected Machine and have them populated/listed in the Component detail subform, you need to create an "Append Query"
- This Append query will append all Components for the selected Machine name in the Master form, and have all the components appended in the Components Subform which is the detail form.
- Once the append operation is done successfully, you then need to refresh the forms to see the latest appended values.

For the above to happen your Primary Keys should be perfectly organized as Foreign keys in their relevant tables.

Hope this helps you organize your schema accordingly.

Best regards
Manish Batola

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Manish Batola

Expertise

ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.

Experience

29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Organizations
Worked in various multinationals catering from Aerospace, Oil & Gas etc.

Education/Credentials
-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 About.com. All rights reserved.