Displaying a list of entered records that updates with each entry

[epb]

I am having some trouble figuring this out. I have two tables, one for "Parts" and the other for "inventory". Each box in inventory has data that needs to be entered, such as Part Number, quantity, Revision, location, etc. Parts and inventory are keyed on [Part No].

I would like to enter inventory records on a form and in a subform display all inventory records already entered for that part number. Basically I would like an add button and a save button but am not understanding how to do this - work with the record data and then save. Once saved I want this record to be added in a running list of inventory for that part.

I am having trouble with getting the list to display because I am working with entry into the list but am not sure how to setup add record and save record code. How do I work with the data and then save it and have it pop into the running list of inventory for that part??

[June7]

You want a main form in Single view to enter an inventory record then a subform to list all inventory records for the same part? So both main and subform are bound to the same table?

DoCmd.RunCommand asCmdSaveRecord
Me.subformname.Requery

[epb]

Yes, that would be the idea. So both can be bound to the same table? OK...didn't know.

DoCmd.RunCommand asCmdSaveRecord
Me.subformname.Requery

I assume this code would go in an "Accept Record" button??

I am also having a problem with entering into a subform bound to the parent table "Parts". The add record * is grayed out and I am not sure why.

Thanks for the information!

[June7]

I suppose both could be bound to same table, never done that, might be an issue if try to edit the same record in both. An alternative would be a listbox instead of subform.

You have a subform bound to Parts that is on main form bound to Inventory? Why?

Yes, code in button Click event procedure.

[epb]

I suppose both could be bound to same table, never done that, might be an issue if try to edit the same record in both. An alternative would be a listbox instead of subform.

I have worked in other databases but have only a little time using MS Access. I am inputting inventory records and I want to see all inventory already input for a specific part or location. The part lookup would be based on a combo box which is a query of part numbers from the PARTS table. Once the part is selected in the combo box I would like to see all current inventory and then make an entry if required. What is the proper way to list the records after selection of the combo box? I also want to be able to edit a record already listed. For example, when a cycle count is done and a change needs to be made.

Right now I am just looking to input inventory based on each distinct unit on the shelf (part or box). The person who setup the database had put inventory fields in the PARTS table but that only shows a total quantity and location and does not show the details. i.e. some boxes are in multiple locations and may have different states of revision, etc. My next step is to add the ability to make a transaction that would be saved and show the history of items going into and out of stock.

I also want to work on the record and only add or change an entry when I save the record. Not sure how this works in Access. Are records always being edited directly? or is there a way to load a copy of the record and then save it.

You have a subform bound to Parts that is on main form bound to Inventory? Why?

Actually, I had made two forms. I was not sure how to accomplish the above and I tried a form bound to the PARTS table that is related to the INVENTORY table (the form is not bound to both tables it is bound to the PARTS table). This form and subform work when selecting a part number in the combo box. The listing of inventory items shows in the subform but the subform is not allowing additions?? The actual new record "star" is greyed out. I can't figure out why this is so.

I also had many problems just getting the combo box to show the correct records and link properly. I have had no real problem showing related records in other databases. If it's related and you develop a subform the records are displayed. I am just going through a learning curve in Access. Some things are not as straightforward as I would like but I am trying to figure them out. Hit and miss right now.

[June7]

Data entry/edit on bound forms are committed to table when form closes, move to another record, or run code. Until one of those events takes place, entry/edit can be canceled.

Is the subform AllowAdditions property set to no?

Want to provide db for analysis? Follow instructions at bottom of my post.

[epb]

Hello June7,

I would like to provide my db for analysis but it has one linked table. How do I manage this??

Thanks.

[June7]

Make copy of db remove the link and import the table.

[epb]

Ok, database is attached to this post.

There are three forms and one subform. I have one form called ENGDATA1 that shows a lot of Part info (Parts are from the Engdata1 table) with an inventory subform , one form called frmEngDataInventory that shows minimum Part info and an inventory subform and one form called frmInventory that has the inventory record entry and a subform to show what is already entered for a particular part. I would like this third form to update the list of entered locations with each new record. I have not been able to enter records unless I use the table itself.

1) In all cases where the subform is used it will not allow entry in the subform. Not sure why. I have checked preferences and this looked ok.

2) The frmEngDataInventory seemed to be working and showing the proper records in the subform but I was having problems with being locked out of editing in some cases and kept getting error messages that my computer had kept others from locking the data or form. I seem to have a lot of this type of problem where locks do not go away even when all users are out of the db. most of the time it is saying my computer is locking the db more then once in the lock file.

If I can find out what I am doing wrong I can get some things done and fix some problems. At some point I need to add the ability to do transactions to and from stock and I tend to think this would be another file linked to Inventory.

Thanks for looking at this. I am not understanding some things and they are probably simple but once I know what is wrong I will have learned something.

One other question...If I want the DB form to open at a particular part or record, how do I do this. Would it be by bookmark or is there another way to open at the last edited record or the last record before EOF?Inventory.zip

[June7]

I am not understanding data structure. You do have a relationship set up but I still don't understand how these tables relate. What is purpose of each table? Is ENGDATA1 supposed to be the 'Parts' table?

There is no PartNo field an either table. Is that what PrintNo is supposed to be? There is no primary key identified for tblInventory. In ENGDATA1 some records have no ItemNo, one record is missing PrintNo.

Why do both tables have similar fields - stock quantity, stock location, unfinished/incomplete quantity? Recommend names not use all caps, spaces, special characters, punctuation (underscore is exception). Better would be ItemNo or Item_Num, UnfinishedQtyInStock or Unfinished_Qty_In_Stock.

Suggest you review some basic tutorials:
http://forums.aspfree.com/microsoft-...es-208217.html
http://office.microsoft.com/en-us/ac...010098674.aspx

Might look at Inventory and Asset database templates.
http://office.microsoft.com/en-us/te...in=HA001234159
http://office.microsoft.com/en-us/te...in=HA001234159

[epb]

Hello June7 and thanks for your response. Most of the things you mention are as a result of working on a database that someone else had created and now I am trying to Normalize and fix a number of things that are not even close to working correctly in the real world. The database has many problems. This is just the first thing that needed to be done. The full db has Normalization problems, Naming convention problems, problems with accepting improper entry, missing entries, and on and on. You should see the rest of the database. It is messed up.

You are correct in saying there is no PartNo field in either table. [PRINT NO] in the ENGDATA1 table and strPrintNo in the Inventory table are the Part numbers. I was using these field to relate the tables. In the real world the items in stock are Parts and not Prints. You don't have Prints in stock and you are not making Prints. the prints tell you how to make the part. Sorry I didn't make this more clear when writing.

from your comments...the Inventory table was added and has some fields that are the same as the ENGDATA1 table for the time being. The initial records that are in tblInventory were copied out of the ENGDATA1 table. When finished, these fields will be removed from ENGDATA1 table. PrintNo is the Part number as explained above. The ENGDATA1 table also has a number of fields removed that I didn't think were necessary for fixing the problem and not necessarily something I wanted others to see.

The ENGDATA1 table is the Part file. Beyond PartNo (PrintNo) it had description, part class, part class 2, fields that need to be known when looking at inventory records. The problem with ENGDATA1 was a normalization issue. The Inventory table was added because for each particular PartNo there can be many boxes in stock and more then one location specified. You can also have boxes of product at various stages of completion. The Original ENGDATA1 table can not keep track of all these things if looking at the columns in normal form.

I appreciate the links to information and example databases. I am hoping that I can figure some of this out. I think I can fix the structure and then will see how the subforms work. I do like the Inventory sample database approach to transactions and will make some changes to match.

[June7]

Seems to me PrintNo should be unique in ENGDATA1 but it is set to allow duplicates. This is partially why I was confused about relationships. You have the PrintNo fields as the Master/Child links in the forms. I suggest that either you save the ID pk field values in ENGDATA1 as the fk in tblInventory and use the ID as Master/Child links or change PrintNo to the pk in ENGDATA1. Fix the pk/fk and Master/Child links then the subform should be editable. Point is, Master/Child linking on form/subform must be pk/fk fields.

If PrintNo becomes pk, then textbox for PrintNo on frmEngDataInventory should be bound to PrintNo field, not an expression referencing combobox and strPrintNo textbox in subform should be locked, or better not even visible.

The RecordSource query for tblInventory subform has a criteria under strPrintNo field - why?

Opening frmEngDataInventory errors in the form Current event.

Description textbox has invalid ControlSource.

That's enough for now.

[epb]

Hello June7, I have made many changes to the database based on your recommendations and some example databases. I have the Inventory subform now working but have some problems that I am not able to solve after many attempts.

I have focused my attention on the ENGDATA1Frm and having that working. As I said, the subform is working but have the following issues:

1) The subform now allows entry but always displays the next new record (the record with the *). I would like this to not be displayed until Someone actually requests to make a transaction by a command button to "Add Record". Not sure how to do this unless you set record entry to "Off" and then have a button to turn it back on...but there is more that I would like this button to do. Which I will try to describe later in item 3.

2) I want to have a field in the subform footer and on the Parent form that calculate the total qty in stock based on [StockMade] - [StockSold] + [StockAdj], etc. This is what will be reported in the Parent Form. I have made numerous attempts to show this calculation and it is not working. Even given taking the code out of an example database. I do see at some point that there will be many, many transactions for a part. When will the number of transactions be too great for this to be calculated efficiently or is there such a point?

3) Input of stock transactions - beyond not wanting to show the next new record until an entry is actually wanted, I have in many cases multiple boxes going to stock or coming out of stock at one time. Note: transactions are based on a certain box with a certain qty going to stock (or coming out of stock or a box can have an adjustment in qty). I want the user to be able to make duplicate records with the same parameters. As an example, for [partNo] XYZ123 we may have 5 boxes at 320 pcs, 1 box at 310 pcs and 1 box at 250 pcs. The underlying other parameters will be identical for each box...Same [StockRev] "B", [TransactionDescription] "Made Product", [StockLoc] "2B3", [StockQtyXXX] --> will be based on the [TransactionDescription] which is chosen, "In this case 'Made' product", [DateStockTrx] Date, [TraceID] Job#, etc.

I would like to know what you think is the most logical way to handle duplicate stock transactions. I thought the best way would be to have a new record button that opened a dialog that allowed the number of total boxes, the qty per box with qty's separated by commas in that field (Maybe for more then one box another dialog opens with the right number of fields and the user enters a qty in each field), and then entry for the other parameters also in the dialog box. I also thought another way would be to have entry into the subform by clicking the addRecord button and then the new record would always default to the prior records field info until it is changed or the form is closed. I think it would be best if a user could take care of all identical transactions with one dialog box and not have to enter records multiple times.

I also need to know if "Accept" and "Cancel" buttons are needed in the subform. I would think this is so but what is correct?

I am attaching an updated cop of the database.
Inventory.zip

[June7]

1. Set AllowAdditions property to No and the NEW line will not show. Then VBA code to allow addition, move to new record, disable allow addition.

2. Don't refer to textbox names in calc, use field names.

3. You want to carry forward data from previous record? Review
http://allenbrowne.com/ser-24.html
http://bytes.com/topic/access/answer...evious-records

Buttons for "Accept" and "Cancel" probably won't work well on Continuous form.

[epb]

Hello Again,

Summing the columns is not working after using the field names. Here is the code:

=Sum(Nz([Inventory]![StockQtyMade])-Nz([Inventory]![StockQtySold])+Nz([Inventory]![StockQtyShrink]))

I am also not getting the Allowadditions, New Record, and Don't allow additions to work. No record appears after running the following code:

Private Sub NewRecord_Click()
    
        Me.AllowAdditions = True
        DoCmd.GoToRecord , , acNewRec
        Me.AllowAdditions = False
    
End Sub

This should not be this difficult. I am obviously doing something wrong here. What I need is some explanations on why these things are not working.