How can I create a Sales form that can update both the Product and Sales Tables

[Bill_ekit via AccessMonster.com]

Am creating a Product-Sales Database, and I would like the corresponding
Sales made in the Sale Table to be automatically deducted or to be reflected
in the Product Table. The product table contains all my stock and has a
relationship with the Sales Table. The Sale Table does not necessarily
include the Stock. How can I create possibly a Sales Form that will be used
as an entry point for all the products (stock) sold and automatically
register the sold products in the Sales Table and at the same time make the
required adjustments in the Products Table.

 

[Arvin Meyer [MVP]]

Create a combo box using a rowsource from the product table and bound to the
Product ID in the Sales table, as you choose a product you will be entering
it in the Sales table. To determine inventory levels write a query linked on
the product ID from both tables. Subtract the total quantity in the Sales
table from the total quantity in the Products table. You can base a form or
report on that query. You can use a DLookup to that query to display the
inventory level on the Sales form.

 

[Bill_ekit via AccessMonster.com]

Create a combo box using a rowsource from the product table and bound to the
Product ID in the Sales table, as you choose a product you will be entering
it in the Sales table. To determine inventory levels write a query linked on
the product ID from both tables. Subtract the total quantity in the Sales
table from the total quantity in the Products table. You can base a form or
report on that query. You can use a DLookup to that query to display the
inventory level on the Sales form.

Am creating a Product-Sales Database, and I would like the corresponding
Sales made in the Sale Table to be automatically deducted or to be

[quoted text clipped - 10 lines]

Hi Arvin Meyer for your contribution! I'll try it out and keep your post! Kit

 

[Bill_ekit via AccessMonster.com]

Create a combo box using a rowsource from the product table and bound to the
Product ID in the Sales table, as you choose a product you will be entering
it in the Sales table. To determine inventory levels write a query linked on
the product ID from both tables. Subtract the total quantity in the Sales
table from the total quantity in the Products table. You can base a form or
report on that query. You can use a DLookup to that query to display the
inventory level on the Sales form.

Am creating a Product-Sales Database, and I would like the corresponding
Sales made in the Sale Table to be automatically deducted or to be

[quoted text clipped - 10 lines]

Hi Arvin Meyer,

Thanks for your contribution! I'll try it out and keep your post! Kit

 

[Bill_ekit via AccessMonster.com]

Create a combo box using a rowsource from the product table and bound to the
Product ID in the Sales table, as you choose a product you will be entering

[quoted text clipped - 8 lines]

 

[Bill_ekit via AccessMonster.com]

Create a combo box using a rowsource from the product table and bound to the
Product ID in the Sales table, as you choose a product you will be entering
it in the Sales table. To determine inventory levels write a query linked on
the product ID from both tables. Subtract the total quantity in the Sales
table from the total quantity in the Products table. You can base a form or
report on that query. You can use a DLookup to that query to display the
inventory level on the Sales form.

Am creating a Product-Sales Database, and I would like the corresponding
Sales made in the Sale Table to be automatically deducted or to be

[quoted text clipped - 10 lines]

Hi Arvin

Thanks so much! Could you kindly paraphrase for me this: "...using a
rowsource from the product table and bound to the Product ID in the Sales
table..."

Well, assuming that we've got both the PRODUCTS Table and the SALES Table,
and the SALES Form that is going to act as an Interface. All the Inventory is


already entered and saved in the PRODUCT Table. The SALES Table is only
keeping track of the sold products (items) from the PRODUCST Table. Well, at
a later stage we shall deal with Totals, but at the moment let us focus on
RECORDS in the PRODUCTS Table. For instance, we've 50 Intex keyboards in a
PRODUCTS table as a RECORD (row). Though we might have the same FIELDS in
the SALES Table.The SALES Table only

handles products that are sold. In this case, using the SALES Form, we want
to sell 3 keyboards. These 3 keyboards sold are registered/reflected in the

SALES Table and automatically they are subtracted from the PRODUCT Table,
hence having 47 keyboards in the PRODUCTS Table!

Thanks, Kit

 

[Arvin Meyer [MVP]]

I prefer to also have a Purchases table. Instead of trying to keep inventory
in one place where multiple people could easily make changes. It also gives
me the opportunity to maintain more than 1 supplier for each product. Total
of purchases - total of sales = product inventory.

If you wish to have the inventory maintained in the Products table, use an
update query in the AfterUpdate event of the form. Something like (Aircode,
substitute your table, field, and control names):

Sub Form_AfterUpdate()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From tblProducts Where ProductID =" & Me.txtProductID

Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenDynaSet)

With rst
.Edit
!ProductID = !ProductID - Me.txtSalesQty
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
MsgBox Err.Number
Resume Exit_Here
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Create a combo box using a rowsource from the product table and bound to
the
Product ID in the Sales table, as you choose a product you will be
entering
it in the Sales table. To determine inventory levels write a query linked
on
the product ID from both tables. Subtract the total quantity in the Sales
table from the total quantity in the Products table. You can base a form
or
report on that query. You can use a DLookup to that query to display the
inventory level on the Sales form.

Am creating a Product-Sales Database, and I would like the corresponding
Sales made in the Sale Table to be automatically deducted or to be

[quoted text clipped - 10 lines]

Hi Arvin

Thanks so much! Could you kindly paraphrase for me this: "...using a
rowsource from the product table and bound to the Product ID in the Sales
table..."

Well, assuming that we've got both the PRODUCTS Table and the SALES Table,
and the SALES Form that is going to act as an Interface. All the Inventory
is


already entered and saved in the PRODUCT Table. The SALES Table is only
keeping track of the sold products (items) from the PRODUCST Table. Well,
at
a later stage we shall deal with Totals, but at the moment let us focus on
RECORDS in the PRODUCTS Table. For instance, we've 50 Intex keyboards in a
PRODUCTS table as a RECORD (row). Though we might have the same FIELDS in
the SALES Table.The SALES Table only

handles products that are sold. In this case, using the SALES Form, we
want
to sell 3 keyboards. These 3 keyboards sold are registered/reflected in
the

SALES Table and automatically they are subtracted from the PRODUCT Table,
hence having 47 keyboards in the PRODUCTS Table!

Thanks, Kit

 

[Bill_ekit via AccessMonster.com]

I prefer to also have a Purchases table. Instead of trying to keep inventory
in one place where multiple people could easily make changes. It also gives
me the opportunity to maintain more than 1 supplier for each product. Total
of purchases - total of sales = product inventory.

If you wish to have the inventory maintained in the Products table, use an
update query in the AfterUpdate event of the form. Something like (Aircode,
substitute your table, field, and control names):

Sub Form_AfterUpdate()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From tblProducts Where ProductID =" & Me.txtProductID

Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenDynaSet)

With rst
.Edit
!ProductID = !ProductID - Me.txtSalesQty
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
MsgBox Err.Number
Resume Exit_Here
End Sub[quoted text clipped - 40 lines]

Thanks, Kit

Thanks a million Arvin!
I'll give it a shout and keep you posted! Kit