Using MS Access/Creating multiple new child records and auto filling part of the records
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.
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.
---------- 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.
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 ids for Access Community Record Keeping
"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.