J
jessica
Hello,
I posted this question 2 days ago and sadly did not get any reply, would
someone kindly follow up? Thanks.
I developed an Inventory Control database using MS Access. I customized the
db based on the Microsoft business templates for the "Inventory management
database".
However, the crazy thing is every time a “product" is going to be used(in
our words, they call it "release"), someone need manually input the related
"inventory transactions" records in the inventorytransaction subform to
update/reflect the right amount of "Units On Hand" of the product. Actually
in the company, most of the products we purchased will be used to assemble to
another part(we call it "Assembly Parts") for different projects. So right
now I have been assigned another task after the inventory control database.
1). To setup a database(or in the inventory database) to store the "Assembly
Parts" information/table, in another words, an assembly part will be
consisted of a list of products in the inventory database, and one product in
the inventory database can belong to many "assembly parts".
It is not difficult to achieve, in theory, I think I could setup another two
tables with the following design to setup many-to-many relations between
"products" table and "assemblyparts" table:
"AssemblyParts" table:
------------------
AssemblyPartsID (Primary Key)
Name
Description
Project
....
-------------------
"Products_In_Assembly" table:
---------------------
ID (PrimaryKey, autonumber)
Description
AssemblyPartID (Foreign Key, is linked to "AssemblyPart" table, one-to-many)
ProductPartID( Foreign Key, is linked to "Products" table, one-to-many)
....
UnitPrice
#Units
....
----------------------
"Products" table:
-----------------------
ProductPartID(Primary Key)
Name
Description
.......
-----------------------
"InventoryTransaction" table:
---------------------------
TransactionID(Primary Key, autonumber)
TransactionDate
ProductPartID(Foreign Key, is linked to "Products" table, one-to-many)
PurchaseOrderID(Foreign Key, is linked to "PurchaseOrder" table, one-to-many)
Description
UnitsPrice
Currency
UnitsOrdered
UnitsReceived
UnitsReleased
-------------------------------------
Is there anyone could give me any suggestions about the design or aware of
any good template?
2). The ability to release(or say use) the products in the inventory
database directly from the "AssemblyPart" table.
In detail, if a user add an "Assembly Part" record and choose the related
products to build that specific assembly part, first there is one function
could check the amount of "UnitsOnHand" for that specific product, to find
out whether there is enough number of units in stock to allow the user to
pick up from the products table, and give user different
messages(Afterupdate event of the #Units textbox, right?)
Here is most difficult part, following up the above example, after the
"assemblypart" record is saved, the user should have the ability to "Release"
the composed "products" from the "AssemblyPart" form directly, rather than to
manually release each product from the "Inventory Transactions" subform of
the "products" form. My plan is using a button called "Release" to acheive
the function by adding the records to "Inventory Transaction" table/form
automatically by coding, something like:
add a record to inventorytransaction ("TransactionID", currentdate in
"TransactionDate" field, ... , 3 in "UnitsReleased" field to release 3 units,
for example)
and after adding, the "UnitsOnHand" for that product will be reduced by 3
because some units was used, which is nice and should be.
I know from table/form(normally will use form) to allow user to add the
records in the table, I am wondering whether I can do the same thing via VBA
code. To be honest with you, I am faily new with the VBA. So anyone could
tell me clearly whether this method(adding a record by programming) could be
implemented? And where is the similar VBA code I could refer to? Or if you
have a better idea to achieve the same function? Thanks.
Jessica
I posted this question 2 days ago and sadly did not get any reply, would
someone kindly follow up? Thanks.
I developed an Inventory Control database using MS Access. I customized the
db based on the Microsoft business templates for the "Inventory management
database".
However, the crazy thing is every time a “product" is going to be used(in
our words, they call it "release"), someone need manually input the related
"inventory transactions" records in the inventorytransaction subform to
update/reflect the right amount of "Units On Hand" of the product. Actually
in the company, most of the products we purchased will be used to assemble to
another part(we call it "Assembly Parts") for different projects. So right
now I have been assigned another task after the inventory control database.
1). To setup a database(or in the inventory database) to store the "Assembly
Parts" information/table, in another words, an assembly part will be
consisted of a list of products in the inventory database, and one product in
the inventory database can belong to many "assembly parts".
It is not difficult to achieve, in theory, I think I could setup another two
tables with the following design to setup many-to-many relations between
"products" table and "assemblyparts" table:
"AssemblyParts" table:
------------------
AssemblyPartsID (Primary Key)
Name
Description
Project
....
-------------------
"Products_In_Assembly" table:
---------------------
ID (PrimaryKey, autonumber)
Description
AssemblyPartID (Foreign Key, is linked to "AssemblyPart" table, one-to-many)
ProductPartID( Foreign Key, is linked to "Products" table, one-to-many)
....
UnitPrice
#Units
....
----------------------
"Products" table:
-----------------------
ProductPartID(Primary Key)
Name
Description
.......
-----------------------
"InventoryTransaction" table:
---------------------------
TransactionID(Primary Key, autonumber)
TransactionDate
ProductPartID(Foreign Key, is linked to "Products" table, one-to-many)
PurchaseOrderID(Foreign Key, is linked to "PurchaseOrder" table, one-to-many)
Description
UnitsPrice
Currency
UnitsOrdered
UnitsReceived
UnitsReleased
-------------------------------------
Is there anyone could give me any suggestions about the design or aware of
any good template?
2). The ability to release(or say use) the products in the inventory
database directly from the "AssemblyPart" table.
In detail, if a user add an "Assembly Part" record and choose the related
products to build that specific assembly part, first there is one function
could check the amount of "UnitsOnHand" for that specific product, to find
out whether there is enough number of units in stock to allow the user to
pick up from the products table, and give user different
messages(Afterupdate event of the #Units textbox, right?)
Here is most difficult part, following up the above example, after the
"assemblypart" record is saved, the user should have the ability to "Release"
the composed "products" from the "AssemblyPart" form directly, rather than to
manually release each product from the "Inventory Transactions" subform of
the "products" form. My plan is using a button called "Release" to acheive
the function by adding the records to "Inventory Transaction" table/form
automatically by coding, something like:
add a record to inventorytransaction ("TransactionID", currentdate in
"TransactionDate" field, ... , 3 in "UnitsReleased" field to release 3 units,
for example)
and after adding, the "UnitsOnHand" for that product will be reduced by 3
because some units was used, which is nice and should be.
I know from table/form(normally will use form) to allow user to add the
records in the table, I am wondering whether I can do the same thing via VBA
code. To be honest with you, I am faily new with the VBA. So anyone could
tell me clearly whether this method(adding a record by programming) could be
implemented? And where is the similar VBA code I could refer to? Or if you
have a better idea to achieve the same function? Thanks.
Jessica