Add record to table, form with cbo

[faca]

Hello!

I have "MATERIAL" table:
MaterialID
DeliveryNumber


PartNumber
PartCode
PartName
Unit
...

i made new table called "STOCK" with same info as in "MATERIAL" table but i also add field: Stock

What i want is to have "STOCK" form where ill select material that i have on stock and than selected material will be displayed on "STOCK" form and saved in "STOCK" table

Problem is that i made 2 comboboxes that get info from "MATERIAL" table but i don't know how to save this in "STOCK" table and at same time display in this "STOCK" form


Thanks

[rpeare]

How are you imagining this?

Like your 'in' and 'out' transactions on different tables? So you can calculate how much of any given item you have on hand?

If so you do not need separate tables to do that, you can put it all in the same table and just have an additional field populated with a 'status' like

tblStatus
S_ID  S_Desc
1     Received
2     Sold
3     Pending

if that's your goal it's way easier to handle on a single table than two or more tables.

[faca]

With stock i mean how many items i have on stock...

I select material from cbo and than enter how many i have on stock (example... 3,4 or 5)

[rpeare]

yeah, that's my point. Why bother typing it in if you can calculate it based on receipts/disbursements of material. If you were to have two active orders that had a combined requirement of 6 of item A, but you only had 5 in stock it would be fairly easy for two different people think they had enough of the item to fill their order.

Maybe I'm not understanding the problem.

[faca]

yeah, that's my point. Why bother typing it in if you can calculate it based on receipts/disbursements of material. If you were to have two active orders that had a combined requirement of 6 of item A, but you only had 5 in stock it would be fairly easy for two different people think they had enough of the item to fill their order.

Maybe I'm not understanding the problem.

There is no orders here and no receipts... only for private use

i just want items that are on stock (that i have them home) in separate table instead of having list of all material and than i need to check which is on stock which isn't..

[rpeare]

I am totally lost then. You descriptions are not helping me. Perhaps if you upload your database here I can take a look, just zip it up and upload it and I will take a look at what you have.

[faca]

I am totally lost then. You descriptions are not helping me. Perhaps if you upload your database here I can take a look, just zip it up and upload it and I will take a look at what you have.

DB attached

[rpeare]

A few things

1. I still don't see what you are trying to do. I think you may need to rethink your data structure. See below comments
2. You should only need to store the material ID in your stock table. The whole point of relational databases is to avoid repeating data entry where it is not necessary. You can perform lookups by just storing the primary key/foreign key in any of your tables.
3. You'll need to make some small changes to your form. I am attaching a modified version of your database as well I have made a query qryStock connecting your stock to your material table to look up the part number, code and name, my assumption is that one of these tables will hold the 'base' price information per unit and one will be holding the actual orders but, again, this needs to be rethought

Your table structure should be something closer to:

tblPart
P_ID  P_Number  P_BasePrice  P_Description   -----> other part related fields
1     PCK8297   22.33         FILTER KARZBINE
2     7PK1795   16.01         REBRASTI JERMEN

tblCustomer
C_ID  C_Name ---> other customer related fields
1     Customer A
2     Customer B

tblStore
S_ID  C_ID  S_Name  S_Address  S_Phone ----> other store related fields
1     1     Store A 1 1st St   111111111
2     2     Store B 2 1st St   222222222

tblOrder
O_ID  O_Date    O_ShipDate  C_ID
1     1/1/2019  5/1/2019    1
2     1/1/2019  4/1/2019    2

tblOrderDetail
OD_ID  O_ID  P_ID  P_Qty  P_Cost  S_ID
1      1     1     5      111.66  1
2      1     2     10     160.10  1

This'll give you more flexibility to ship items to different stores on the same order then you can do things like apply a percentage discount to an order etc based on the base unit cost of the material.

DatabaseE.zip

[faca]

A few things

1. I still don't see what you are trying to do. I think you may need to rethink your data structure. See below comments
2. You should only need to store the material ID in your stock table. The whole point of relational databases is to avoid repeating data entry where it is not necessary. You can perform lookups by just storing the primary key/foreign key in any of your tables.
3. You'll need to make some small changes to your form. I am attaching a modified version of your database as well I have made a query qryStock connecting your stock to your material table to look up the part number, code and name, my assumption is that one of these tables will hold the 'base' price information per unit and one will be holding the actual orders but, again, this needs to be rethought

Your table structure should be something closer to:

tblPart
P_ID  P_Number  P_BasePrice  P_Description   -----> other part related fields
1     PCK8297   22.33         FILTER KARZBINE
2     7PK1795   16.01         REBRASTI JERMEN

tblCustomer
C_ID  C_Name ---> other customer related fields
1     Customer A
2     Customer B

tblStore
S_ID  C_ID  S_Name  S_Address  S_Phone ----> other store related fields
1     1     Store A 1 1st St   111111111
2     2     Store B 2 1st St   222222222

tblOrder
O_ID  O_Date    O_ShipDate  C_ID
1     1/1/2019  5/1/2019    1
2     1/1/2019  4/1/2019    2

tblOrderDetail
OD_ID  O_ID  P_ID  P_Qty  P_Cost  S_ID
1      1     1     5      111.66  1
2      1     2     10     160.10  1

This'll give you more flexibility to ship items to different stores on the same order then you can do things like apply a percentage discount to an order etc based on the base unit cost of the material.

DatabaseE.zip

Thanks for your help!

Anyway i got what i want here -> https://www.youtube.com/watch?v=rSPS...L&index=4&t=0s