Need help designing database.

[oxicottin]

Hello, I need help designing this database for collecting a number count for each products lengths on a single date like shown in the
image example. I need to find a way that once I select a date, shift and employee from [frm_InventoryOverview] form then
[sfrm_InventoryDetails] form will create a record for every product and every size for that product so I can scroll down each section in a continuous form.

 

[plog]

I understand tbl_Employees and tbl_InventoryOverview. I do not understand what differentiates the other 3 tables. Seems like you only need 2 of them. Unfortunately I don't know which two.

1. Can you explain what each of those 3 remaining tables purpose is?

2. Explain your terms. What's the difference between a product and an item? Is a ProductData not just an item? Is an item just a specific length of a product or is there more to it?

Normally your inventory table would be directly connected to the table at the lowest level you are inventorying. I don't think tbl_Inventory Details should directly be related to tbl_Products but tbl_ProductData. You're inventory table isn't at a low enough level.

 

[MajP]

I do not think this table is correct
tbl_InventoryDetails tbl_InventoryDetails

InventoryDetailsID	InventoryOverviewID	Item	ProductID	Count	Length
1​	1​	​	1​	​

You need to only save the ProductDataID not Item, ProductID, and Length.
If you save the productDataID you then know the Item, ProductID, and length.

You need to do a Select Into Query where you Select all the ProductDataIDs from tblProductData where ProductID = cboProduct

 

[June7]

Pretty much agree with other commenters. Aside from that, you want to batch create a set of records, something like:

CurrentDb.Execute "INSERT INTO tbl_InventoryDetails(InventoryOverviewID, ProductID, Length) SELECT " & Forms!frm_InventoryOverview!InventoryOverviewID & ",ProductID, strProductLength FROM tbl_ProductData"

 

[MajP]

Also you do not want that cboProductID to be bound.

 

[June7]

cboProductID should not be bound if it is used to search/filter records. There is no code associated with this control.

If you want to use it for entry of single record, then it should be bound and in Detail section.

 

[oxicottin]

You need to only save the ProductDataID not Item, ProductID, and Length.
If you save the productDataID you then know the Item, ProductID, and length.

You need to do a Select Into Query where you Select all the ProductDataIDs from tblProductData where ProductID = cboProduct

Ok, I changed the [tbl_InventoryDetails] by removing the other fields specified that wasn't needed. I then unbounded the cboProductID and I changed the relationships to below.

Now, im not sure how you guys are explaining how to have it auto enter all the lengths and product names into the subform for the date on the main form so I can enter a count for each length next to them?

@MajP is the query look like this?

SELECT * INTO tbl_InventoryDetails
FROM tbl_ProductData
WHERE [ProductID]= "ProductID"

 

[June7]

Post #4 shows how to batch create records by executing INSERT SELECT action SQL. Afterward, subform would need to be requeried to display records.

Have to figure out how to trigger the action. Want user to click a command button?

 

[oxicottin]

Post #4 shows how to batch create records by executing INSERT SELECT action SQL. Afterward, subform would need to be requeried to display records.

Have to figure out how to trigger the action. Want user to click a command button?

@June7 I removed the "Length" from [tbl_InventoryDetails] as MjaP instructed so that would change your batch insert to what because I'm only saving the count in there now. I probably would want a button to insert all the records for now...

 

[MajP]

See if this is what you mean. Use the combo to add a product to the inventory.

 

[MajP]

If you want to have the combo also filter the inventory to just that product, I would rethink the design. That would be confusing for both adding and filtering.

 

[oxicottin]

See if this is what you mean. Use the combo to add a product to the inventory.

@MajP, Yes its sort of what I'm after..... how could I get rid of the combo box all together and "auto batch" add a record for every product/length instead of selecting them from a combo box.

 

[June7]

If you don't want to retrieve and save Length field then modify the SQL to not reference. It's a simple change.
Did you even read the SQL I suggested? It does not filter by ProductID. It pulls all products.

Private Sub btnInvDet_Click()
CurrentDb.Execute "INSERT INTO tbl_InventoryDetails(InventoryOverviewID, ProductID) " & _
                "SELECT " & Me.InventoryOverviewID & ",ProductID FROM tbl_ProductData"
End Sub

Set InventoryOverviewID and ProductID fields as compound unique index in tbl_InventoryDetails to prevent duplication of these pairs in case user accidentally clicks button again.

 

[MajP]

That would be easier. Just remove the product criteria in the insert.

 

[oxicottin]

Did you even read the SQL I suggested? It does not filter by ProductID. It pulls all products.

Set InventoryOverviewID and ProductID fields as compound unique index in tbl_InventoryDetails to prevent duplication of these pairs in case user accidentally clicks button again.

@June7 I read it earlier I forgot it didn't reference the combo box in your example SQL.... Sorry! Also, thanks for adding the accidental clicks that was my next question! Thanks so much for your help!

@MajP Thanks again for your help!

 

[oxicottin]

That would be easier. Just remove the product criteria in the insert.

@MajP I noticed something in my database that I used the code from your example. If you click the button to add records then it "Looks like" your adding records BUT they are records from the previous record and not a set of new records. I found this out when I added numbers in the count control and when I went to a new record and tried to create records in the subform it just brings up the previous record. You have to move off a new main record and back to the record and it wont show any records in the subform but if you hit the button to create records AGAIN then it will create them.

 

[MajP]

Modify the code so that it fails on error and before running the code set dirty = false

Private Sub CmdAdd_Click()
 Dim rtn As Long
  Dim strSql As String
    rtn = MsgBox("Do you want to add all items", vbYesNo, "Add Items")
    If rtn = vbYes Then
      Me.Dirty = False
      strSql = "Insert into tbl_InventoryDetails (ProductDataID_FK,InventoryOverviewID) Select ProductDataID, " & InventoryOverviewID & " AS OID from tbl_ProductData WHERE IsInactive = False"
      Debug.Print strSql
      CurrentDb.Execute strSql, dbFailOnError
    End If
    Me.tbl_InventoryDetails_subform.Requery
End Sub

the insert was failing, but dbfailonerror was not included so no message.

The problem is that the indices on the inventoryDetails table are all wrong causing the insert to fail. Delete them except the primary key index. Then create a composite index with the inventory overview foreign key and the product foreign key. Make that unique. Each inventory can have a product once.