Creating multiple records from one form (1 Viewer)

[Atthe]

Hi All,

I have two tables in my database [tbltools] which has 10 fields (tools1 to tools10) and [tbltoolgroups] which has 7 fields (toolgroups1 to toolgroups7)

I am in the process of creating a form that the end user can use to define a tool group (will be stored in [tbltoolgroups])

The basic layout of the form consists of a top section with textboxes for fields toolgroups1 to toolgroups5 and then in the section below x60 multicolumn comboboxes (These will use a 4 fields from [tbltools ]I need 60 as the tool groups range between 2 & 60 tools) with an adjacent quantity box(To determine how many of the specified tool is required) with the idea being that the user can then populate as many or as little of comboboxes and quantity boxes as required.

I have created the above mentioned top section of the form and have created one of the multicolumn comboboxes but I am not sure how to deal with creating multiple records depending on how many comboboxes are populated? As I could need to enter up to 60 records into [tbltoolgroups] at once but with only fields toolgroups2 and toolgroups6 changing based on the users selection from the comboboxes.

Hope this makes sense

Thanks for the help
Ash

 

[mike60smart]

Hi Ash

The structure does not sound correct.

Are you able to upload a zipped copy of the database so we can see the tables?

Or even a screenshot of the Relationship window

 

[CJ_London]

not really understanding the requirement - can you mock up something that looks like the result you require

your image shows 7 toolgroups but you talk about adding up to 60 records into tbltoolgroups - which I would have thought would contain just the 7 records and your tools would go into another table

also sounds like an awful lot of controls required either 120 or 840 (which is not possible as the limit for an access form is 700+)

 

[Uncle Gizmo]

10 fields (tools1 to tools10)

It sounds to me like you've got a design in Excel which you are moving into MS Access. I imagine an Excel spreadsheet with 10 columns, one for each tool. The problem is MS Access is not Excel, moving an Excel type table into Access, although you can, you are unable to take advantage of the full features of MS Access. You will very quickly run into unsurmountable problems. Luckily, it appears you are at a very early stage in your development. This means you should be able to correct your mistake at this early stage, saving yourself a lot of headaches. I strongly advise you to read my blog explaining the problems and issues of your current approach.

Excel in Access - Problem & Solution!​

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[Atthe]

Are you able to upload a zipped copy of the database so we can see the tables?

not really understanding the requirement - can you mock up something that looks like the result you require

Sorry on re reading my post my explanation wasn't very clear at all, I have built a basic mockup database with sample data. I have also added two forms with frmcreategroup being the one I am currently having difficulties with.

Thanks

It sounds to me like you've got a design in Excel which you are moving into MS Access. I imagine an Excel spreadsheet with 10 columns, one for each tool.

You are correct this is based on an excel design, Although I think my wording in my post didn't help as the 10 columns or fields are for data regarding the each individual tool, with each tool being stored in rows.

Having said that I am more than open for suggestions on an alternative way of dealing with the data and will read your blog.

Thanks

 

[Uncle Gizmo]

I am more than open for suggestions on an alternative way of dealing with the data and will read your blog.

My blog is applicable to most scenarios, but you may well have something that falls outside of the general problems.

It might be an idea if you gave an overall description of what you are trying to do, with some example data. Data before, and data demonstrating the results you want.

It's not very often we come across something we haven't seen before, and there may well already be a recognised solution to your problem.

If you do have a problem we haven't seen before, then I am certain you will get some expert help, because we like nothing better than something we haven't tackled before!

 

[MajP]

I looked at the tables and cannot make heads or tails of them, but can tell you that most definitely the table design is incorrect. You need to explain your data structure and forget about the forms and reports for now. You need to redesign the tables first.
Are these groups in some way sub groups of each other? Is it like an assembly where where Group 1 consists of group 2 and each group 2 consists of items from group 3...

x60 multicolumn comboboxes (These will use a 4 fields from [tbltools ]I

That sounds like a horrible design. I would not consider doing that.
You get your table design correct, and I am sure we can come up with a far simpler and user friendly approach.

 

[Atthe]

My blog is applicable to most scenarios, but you may well have something that falls outside of the general problems.

It might be an idea if you gave an overall description of what you are trying to do, with some example data. Data before, and data demonstrating the results you want.

It's not very often we come across something we haven't seen before, and there may well already be a recognised solution to your problem.

If you do have a problem we haven't seen before, then I am certain you will get some expert help, because we like nothing better than something we haven't tackled before!

The idea is that the end user can use a form to define what tooling is required from tbltooling for a particular part number.
In the form the user needs to be able to assign 5 fields of inforomation about the part number itself and then be able to
choose which tools from tbltools are used and how many of each (Could be up to 60 tools per part number and of varying quantity,
with the idea then being that this information is then stored in tbltoolgroups so the user can obtain a setup sheet for the
required part number using frmsetupsheet.

Hope this helps?

Thanks

 

[Atthe]

I looked at the tables and cannot make heads or tails of them, but can tell you that most definitely the table design is incorrect. You need to explain your data structure and forget about the forms and reports for now. You need to redesign the tables first.
Are these groups in some way sub groups of each other? Is it like an assembly where where Group 1 consists of group 2 and each group 2 consists of items from group 3...

Okay so each tool has a unique number stamped on it which I need in the database and then I need another 9 fields for additional data about that tool (Part Number, Drawing Number, Location, Calibration Number, Machine, Tool Type, Line, Tool Condition, SAP number and notes)

The groups is basically defining needing x2 of tool unique number 2 and x1 of too unique number 3 etc for a particular assembly number

That sounds like a horrible design. I would not consider doing that.
You get your table design correct, and I am sure we can come up with a far simpler and user friendly approach.

I agree but with my very limited knowledge I don't know of a better way to do this as the amount of tools required in the tool group can vary from 2 tools to 60 tools

Thanks

 

[Uncle Gizmo]

I don't understand what a tool group is?

Is it the tools to create a certain part, or something else?

 

[Atthe]

I don't understand what a tool group is?

Is it the tools to create a certain part, or something

Yes, it is just a group or selection of tools to create a certain part

Thanks

 

[MajP]

Okay so each tool has a unique number stamped on it which I need in the database and then I need another 9 fields for additional data about that tool (Part Number, Drawing Number, Location, Calibration Number, Machine, Tool Type, Line, Tool Condition, SAP number and notes

None of that exists in the database. There is no PartNumber, DrawingNumber, ... SAP Number, Notes fields in any of the tables.

 

[Atthe]

None of that exists in the database. There is no PartNumber, DrawingNumber, ... SAP Number, Notes fields in any of the tables.

Basically I had just assigned generic field names in my mock up, please see my attached edited version

 

[MajP]

OK that makes way more sense. You need to create a many to many relationship which will require another table called a Junction table and modify the group table. You need an Assembly table and the junction table is tblAssemblies_Tools that assigns tools to one or more assembly.
TblAssemblies needs a primary key which I assume AssemblyNumberSpec

tblAssemblies
AssemblyNumberSpec - Primary Key
AsseblySapNumber
AssemblyDrawingNumber
'any other fields unique to an assembly
get rid of Tool information that goes in junction table

tblAssemblies_Tools
AssemblyNumberSpec_FK
ToolNumber_FK
ToolQuanity

So if you have in tblAssemblies_tools
AR42-9012 7 1
AR42-9012 8 2

then in assembly AR42-9012
you have 1 of tool 7, and 2 of tool 8
using a query you can pull in data from both the assembly and tool table.

 

[MajP]

See demo. See if this makes sense. You create an Assembly. Then you pick a tool and add it to the assembly along with the quantity.

 

[Atthe]

See demo. See if this makes sense. You create an Assembly. Then you pick a tool and add it to the assembly along with the quantity.
View attachment 95656

This seems ideal, I will have a look at sorting my data out in this style and see how it all works
Thanks

 

[MajP]

This is the standard method in access for having a Many to Many and a form to add/edit the data.
FYI. Since it is a many to many you can do this in the opposite direction depending on your perspective.
You can build the form where the main is Tools, and then you assign a tool to one or more existing assemblies.

So if you are assigning a student to classes you can have a form to pick a class and then a subform to add students. Or a form to pick a student and assign them their classes.

 

[Atthe]

This is the standard method in access for having a Many to Many and a form to add/edit the data.
FYI. Since it is a many to many you can do this in the opposite direction depending on your perspective.
You can build the form where the main is Tools, and then you assign a tool to one or more existing assemblies.

So if you are assigning a student to classes you can have a form to pick a class and then a subform to add students. Or a form to pick a student and assign them their classes.

I have transferred all my data and all works as expected, I have added a delete button to the form which works as expected however it only removes the record from tblAssemblies (To be expected) but I still have the related tools from the Assembly in the tblAssembly_Tools - Should I be concerned about this?

 

[MajP]

The purpose for establishing relationships in the Relationships window is to ensure data integrity. One of these is to ensure that you do not create orphans. An orphan is a child record without a parent record.
In your Relationships you need to have a relationship between Assemblies to Assembly_Tools to Tools
Ensure Cascade deletes is established. I did this in my demo.
In my demo if I delete an Assembly it deletes the child records in Assembly_Tools. The same if I delete a Tool. You need the same

What are cascade deletes, how to use them, and what they mean

What are cascade deletes, how to use them, and what they mean.

www.alphapresspublishing.com

 

[Atthe]

The purpose for establishing relationships in the Relationships window is to ensure data integrity. One of these is to ensure that you do not create orphans. An orphan is a child record without a parent record.
In your Relationships you need to have a relationship between Assemblies to Assembly_Tools to Tools
Ensure Cascade deletes is established. I did this in my demo.
In my demo if I delete an Assembly it deletes the child records in Assembly_Tools. The same if I delete a Tool. You need the same

Thanks for that, I have sorted that out and all working fine now.

I have now created a basic form with a multicolumn combobox (AssemblySAPNumber and Assembly NumberSpec) and a button.

The idea being that the user clicks the button and it opens a report based on a query that returns certain fields from all 3 tables.
the criteria is AssemblySAPNumber and I need to return the all the assembly information and tool information relating to just this assembly group

I've used [Forms]![formname]![comboname] as criteria on AssemblySAPNumber but am being asked to enter parameter value

Any ideas?

Thanks