After appending a record from table A into B, how can we continue to allow data entry on Table B?

[KKT3870]

Hello,
I am new to MS Access.
I need help to create query and form for an Inventory Item DB.

Scenario:
1. Select the desired item with a query on the Items table.
2. Map the fields (Item name, ID and On-hand qty) from the Items table into a
'Check-out' transaction table. I guess we could convert the Select query to Append Query?
3. Once the record is appended, how can I (in the same form/sub-form) open the newly appended
record and allow user to enter the remaining transaction data fields? Such as date, user's name,
checkout qty; then compute the resulting qty and update the on-hand qty in the Items table.



Any help is greatly appreciated..

[June7]

Not really understanding the difficulty here.

Bind form to Transaction table, select item from combobox, continue entering data into other fields of record.

Before you commit to saving 'on-hand' qty in table, review http://allenbrowne.com/AppInventory.html

[KKT3870]

Hi June7, thanks very much for your reply.
I did create a form for new record entry to Transaction table.
But I have trouble to populate the selected item name and on-hand Qty (from the combo box) into the
respective fields on the Form.

Would you mind please to take a look of this simple DB form and point out my issue?
I can attach the zipped DB file (a small one for my learning purpose).
Please advise. Thanks a million.

[mike60smart]

No file attached

[KKT3870]

MS LTC Nursing Inventory-2.zip
Thank you for your help.

[mike60smart]

Hi
I am hoping that this is a database that is in development?

You need to study Relationships.

Your table Inventory has a TextField as the Primary Key when it is recommended that
ALL Tables should have an Autonumber field as the Primary Key.
It also has fields set as Lookups and tables fields should NOT have fields set as Lookups.
Your Vendor & Category should be changed from Text to Number and on your Data Input form you would use
Combobox's to Lookup the values from the Vendor & Category Tables.

The same applies to your Transactions Table.
UnitOrg, Item & Transaction are set as Lookup fields. These need to be changed to Number Data Types vice Text.
You should not have a field named Date because Date is a Reserved Word in Access,

[KKT3870]

Thanks.. I inherited the tables from someone else.. helping a charity organization as volunteer... and it's my first time using Access.
I'll correct them all and try again. Thanks.
Will post again later if I need further help. Thanks very much.

[KKT3870]

I have corrected all the tables' design.. Am updating the Db.. hope you can help straighten me out.
I have created a form 'AddItem' to add Inventory records.
Then I created form 'Checkout Item' to add a new record into the Transaction table
1. Pull selected ItemID and OnHand_Qty from the ItemMaster table
2. Now need to append/insert the selected ItemID and OnHandQty into Transaction tbl's Itemid and BeforeQty.
3. User will enter: Date, user name (UpdatedBy field), and checkout Qty.
4. User also select OrgNameID from ConsumerUnit table, and select TransactionActionID = Removal from Action tbl
5. Now I need to calculate the AfterQty from BeforeQty-Qty --> and save into AfterQty (and ensure it is >=0)

I have done most of 1, 2,3 and 4.
But I need help to determine what (and how) is best to do 5.

Do I create multiple queries (append and update?) into the same 'Additem' form?
Any pointers is greatly appreciated.

I am uploading the DB next. Thanks very much.

[KKT3870]

SLTC Nursing Inventory-1.zip

Zipped DB file attached.

[June7]

Did you review the Allen Browne link I referenced earlier?

Saving aggregate data is usually not necessary. Calculate quantity on hand when needed.

[ssanfu]

@KKT3870

See
Table and PK design tips

The evils of lookup fields
The Ten Commandments of Access



Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


---------------------------


This is how I name objects:

Take the time to give useful/meaningful names to objects. Having 9 tables with the PK field named "ID" gets confusing at best.
Primary key fields:
For the Vendor table, I would use "VendorID_PK"
For the Location table, I would use "LocationID_PK"
"Location" refers to the table, "ID" (for me) means it is a numeric (Autonumber) field and "PK" means it is the primary key field.

Foreign key fields:
If the field name is like "VendorID_FK" or "LocationID_FK", then
"LocationID_FK" -->> "Location refers to the linking table, "ID" means it is a numeric - type Long Integer, and "FK" means it is a foreign key field.

Do not have a "Default Value" for "FK" field

For tables, queries, forms, reports, I use a 3 letter prefix (lower case)
Tables -->> "tbl" --> "ItemMaster" would be "tblItemMaster"
Queries -->> "qry" --> "qryVendor"
Forms -->> "frm" --> "frmMainMenu"
Reports -->> "rpt" --> "rptItemsSold"



It really helps if you develop a naming style/convention and stick with it.


Good luck with your project.....

[KKT3870]

Steve, thanks you very much for the tips.. Very good points.
I am also struggling with the way to add query to grab the desired item record, calculate the updatedQty and then replace the OnHandQty in the ItemMaster table.

That is, in the nutshell, how to update both the transaction record (new) and the ItemMaster table.
Will study more and proceed. Thanks for your help.

[ssanfu]

Did you read the link that June7 posted in Post #2?
I really think you are headed down the wrong path by storing the OnHandQty in the ItemMaster table.
(The wrong path looks like )......



Good luck.

[KKT3870]

Awww.. Thank yo very much, Steve.. You lighted the bulb for me... I was on a wrong path as you mentioned.

I missed June7's point (although I read the linked post, but I thought just different way to implement the solution).
So I'll have a table for Checkout qty and a table for Replenish qty. Then I will calculate the Onhand qty when needed.
This is a very small DB.. There should not be loading issue... Will also need to find out how to use the Dsum() function..?

Would that be a regular Access function that I can execute in a query? Or need to add in the code as listed on that post?
Hope you can point me to the right direction on this one as well.
Thanks very much.

[ssanfu]

So I'll have a table for Checkout qty and a table for Replenish qty.

So why two tables? What are the structures (fields) of the tables?

If you had one table with an additional field for Checkout or Replenish, you could just sum the values, providing one of the options (maybe Checkout) value was negative.
Or you could use a DSum function in a query, but some functions in queries can be agonizingly slow.
Either way, you shouldn't store the Qty-on-hand in a field in a table.


I haven't seen the latest & greatest version of your dB......so not sure which direction you are headed now.