creating multiple records with one form. (1 Viewer)

[dale_j1992]

good evening, i am in the process of making a database to record the booking out of PPE equipment however i have reached a small issue when operators are having too book for example ear plugs and gloves because they are having too complete the form 2 times which is wasted time. Is there any way too have for instance the same field twice or more on a form then the rest of info once, so if someone books out more than one piece it creates a record for each. My form has Data_ID which is my autonumber, Worker, P.P.E (drop down list) Date/Time, i would like too repeat the PPE drop down if possible the. depending on how many selections are made, there is a record created for each with remaining info, name date etcc, duplicated onto each. any help is greatly appreciated

 

[MarkK]

This sounds like a one-to-many relationship where one object, say an Order, might contain many products. This is modeled in the data using two related tables--where the child row has a foreign key field pointing to the primary key of the parent--and modeled in Access Forms using a Mainform/Subform arrangement.

Modelling this in other ways, namely putting product1, product2, ..., productN fields in the parent table, seems simpler at first glance, but is in fact a mistake, since SQL summary tools don't work on multiple fields in a single row, rather, SQL summary tools work on a single field in multiple rows, and this is a very important difference.

Hope this helps,

 

[dale_j1992]

Many thanks for the reply, i am relatively new to access is there any chance you could show me a sample of this as i am struggling from your explanation how to do it, if you cannot is there any links you know of which would show me in more detail how to achieve this. kind regards

 

[MarkK]

Here are some search terms that might get you started...

• database normalization
• one-to-many relationship
• database relational model
• entity-relationship model

Hope this helps,

 

[smig]

here is a quick draft

Table 1 - tblOrders
OrderID (AutoNumber)
OrderDate
OrderName
.....


Table 2 - tblOrdersItems
OrderItemID (AutoNmumber)
OrderID (To link to tblOrders)
ItemCode
ItemName
....


Do some readings, as MakrkK said

 

[mjdemaris]

Much reading is good for the mind. That is how I have been learning Access, SQL and VBA!

Also, try using google advanced search for this site and others like it.

 

[Uncle Gizmo]

MarkK's description of the problem is one of the best descriptions of the trap that many fall in to I've seen.

Smig has also pointed you in the right direction with the table structures.

Once you have those table structures set up you will need a main form with a subform. This is one of the best features of MS Access, the ability to add a subform to a main form so that you can display related records directly to the user, and also enter related records directly in the same form.

If I understand your question correctly then one of your problems is the entering of repetitive data. If that's the case, then the main form subform arrangement solves much of this nicely.

The main form would carry these details:-

Data_ID (autonumber)
Worker, P.P.E (drop down list)
Date/Time
name date etc

(Entered only once per order)

Now all you need to add in the subform is the gloves; number of gloves: ear plugs and number of ear plugs: and anything else. The gloves and earplugs etc can be listed via a combobox allowing dropdown selection of the items.


Sent from my SM-G925F using Tapatalk