Adding a subform where 3 relationships have been formed and multil

[Rob M.]

Hello, I may have more than 1 question here but, I want to enter supplier
invoice info on a subform and require a line for each inventory item(could be
more than 1 on a supplier invoice) will the subform allowme to enter more
than one item...line would be as follows cboPartNum, qty, unit cost, discount.

Is the best way to keep all supplier transactions together by supplier. I
also wanted to charge an accounting account I.e "Utilities" via cboAccounts;
and update inventory for inventory units in stock.

I may need the function for the inventory calculation. Sorry for the multi
part question. I'm reaaly stuck on this fence.

Thanks,

Rob M.

 

[Ken Sheridan]

Firstly take a look at the orders form and its subform in the sample
Northwind database. This works in pretty much the same way as an invoice
and gives you a basic model to work from. In your case the corresponding
tables to those used in Northwind would be Suppliers, Invoices, InvoiceLines
and Inventory.

When it comes to referencing the account to be charged you simply need to
add a column (field) to the InvoiceLines table such as AccountID which
references the primary key of a table Accounts, and include a combo box in
the subform for this.

As regards updating the inventory stock-in-hand value you don't need to do
this, and to do so would introduce redundancy (and the consequent risk of
inconsistent data) as the stock-in-hand value per stock item can be computed,
being the sum of quantities received into stock per item less the sum of
quantities removed from stock per item. You'll need to include a means of
recording items written off of course to include in the latter. This can be
done in a query or in code by means of the DCount function, but the details
will depend on what tables are used to record removals and write-offs from
stock.

Ken Sheridan
Stafford, England

 

[Rob M.]

Hello Ken and thanks for taking the time read my multi-question. I will look
at the Northwinds database and look at the Orders form and subform. As far
as adding the combo box for the account and requiring the Accound Id as an
extra column, that sounds good and so does the advice about not updating
inventory. I agree it may cause alot of problems when you pull in other
tables for the outgoing inventory. I once tried a query, but I never used
theDCount function. Thanks in case I want to try that.

Thanks Ken, I hope I can read that Northwinds database and insert that multi
-line feature. I appreciate your help. Sincerely Rob M
--
Rob M. Thanks for your help and If I helped I''''m glad to be of assistance.

Firstly take a look at the orders form and its subform in the sample
Northwind database. This works in pretty much the same way as an invoice
and gives you a basic model to work from. In your case the corresponding
tables to those used in Northwind would be Suppliers, Invoices, InvoiceLines
and Inventory.

When it comes to referencing the account to be charged you simply need to
add a column (field) to the InvoiceLines table such as AccountID which
references the primary key of a table Accounts, and include a combo box in
the subform for this.

As regards updating the inventory stock-in-hand value you don't need to do
this, and to do so would introduce redundancy (and the consequent risk of
inconsistent data) as the stock-in-hand value per stock item can be computed,
being the sum of quantities received into stock per item less the sum of
quantities removed from stock per item. You'll need to include a means of
recording items written off of course to include in the latter. This can be
done in a query or in code by means of the DCount function, but the details
will depend on what tables are used to record removals and write-offs from
stock.

Ken Sheridan
Stafford, England