How to build a form which takes multiple inputs

[jurw]

Hey guys,

I am trying to build a stock database for our company.
I managed to set up the database correctly so I can store every value the way I want to.
This is the set up of the database:

Table 1: Products
- ID
- CompanyID (our recognizable ID of a product within our company)
- Brand
- Model
- Description
- Size
- Color
- EAN
- Comments

Table 2: Pallets
- ID
- pallet_number
- pallet_location

Table 3: PalletProduct
- record_id
- pallet_Number
- product
- amount

The relations between the 3 tables are as following:
pallet_number of table 2 <-> pallet_number of table 3
CompanyID of Table 1 <-> product of table 3

When new products arrive we have to enter it in the database, preferrably through a form for usability.

This is the form I had in my head to enter all the values:


Brand: [Textbox]
Model: [Textbox]
Description: [Textbox]
Size: [Textbox]
Color: [Textbox]
EAN: [Textbox] (preferably by scanning a barcode)
Comments: [Textbox]
Pallet:
[List]

Now the last bit is getting tricky I think. Because we can get a lot of items of the same sort it is possible we have to spread it over multiple pallets. So I was wondering if it is possible to add multiple pallets and enter the pallet_number and amount to it.

For example: (NOTE: none of the brands I use below have anything to do with our company whatsoever)

Brand: Samsung
Model: SM-G930x
Description: Samsung Galaxy S7 Gold
Size: [empty]
Color: Gold
EAN: 9461523764512
Comments: [empty]
Pallet: {number: 201, amount: 50}
{number: 202, amount 40}
{number: 203, amount 40}

As you can see I want to achieve so I can 'dynamically' add multiple pallets (minimum of 1, maximum no limit) and add it to my database in the correct way.

I tried to use SubForms but I can't figure out how to use them correctly so it behaves like I want to.

I hope any of you can help me

Thanks in advance

Jur

[ranman256]

yes, build a form to enter ,the master table has the detail ,and have a temp table as the subform to enter all the palette#s.
Then run a query (or queries) to take the data and append them to the correct tables.

[ssanfu]

pallet_number of table 2 <-> pallet_number of table 3

Can the pallet number 201 be used multiple times?
Will it ever be duplicated? If yes, then you should stop and revisit your table structures/relationships.

Pallet:

[List]

Do you get/have an order (for lack of a better word) with multiple products that are on 1 or more pallets?
To have a list of pallets, you will need another table.
Consider:




Still hazy on what your process is. Would you explain what you are trying to do? No computer jargon - explain it like I am a 10 year old.


Also, in table Products: "Description" and "Size" are reserved words in Access and shouldn't be used for object names.
Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html