Inventory Entry Form

[chrish47]

Hi,

This may be something simple, but I am having problems getting it to work in Access. I have a master product list in one table (with a autonumber primary key). I want to set up a data entry page that selects a subset of the data (based on Project and Account fields in the table), and assigns a Dept to the all the rows, and then allows a Amount and Description to be entered for each row. I then want to store only the rows were a value was entered into a new table.

I have set up a form with Project and Account listboxes, and I have a subform that displays the resulting data when the Project and Account are changed in the listbox. What I am having problemw with is adding the Amount and Description to each row in the subform (I put them there, but they were static - ie putting a 1 in the first row, resulted in all rows having a 1, etc).

I guess the question is what is the best method to accomplish this (I know it shouldn't be too complicated), and are there any samples that I could take a look at. What I think the inventory table should have is a autonumber key, a Dept (assigned from the form header - so that it doesn't have to be entered on each row), an Amount and Description for every row where a value is entered, and the primary key from my inventory listing, along with a date and userid from the form header.

I need help with working with the data in the subform (it is currently selected by setting the Row Source = to a SQL statement based on the values from the two combo boxes), but I am thinking that I might need to create a recordset in VBA and then use VBA to assign the rows where amount <>0 to the new table. Am I on the right track here?

Thanks,
Chris

 

[grnzbra]

When you make a selection in the Project and Account listboxes, what is the nature of the records in the subform?
How is the subform displayed (continuous forms, datasheet)? Are the amount and description items you add manually, or do they come from the master product list?

Or is this an attempt to add to the master product list?

 

[chrish47]

After selecting the two listboxes, the records in the subform come from a SQL statement (selected from the Master list where Project and Account = what was entered in the listboxes). I think the subform is displayed as a datasheet (but I think I tried other methods as well). Currently the Amount and description items were added manually (I edited the Query that selects the subform and tried adding AMOUNT: [Amount] and DESCR: [Description] as fields in the query, which created the columns, but they all have the same value - there is only 1 Account & Descr for all rows in the subform. This is why I was thinking that I needed to select the subform data as a recordset somehow, so that I could add an amount and descr for each row.

I do not want to add to the master list, I want to use the Primary Key from the master list to reference the data and store it in a separate inventory record. Something like this:

Master List
Primary Key Project Account XXX XXX XXX
00100 12345 55555 Top Red Left
00200 12355 66666 Side Blue Right

Inventory List (table I want to create
Primary Key Set Item Amount Descr
0005 X7 00100 10 Excellent
0006 X7 00200 5 Poor

So the form and subform display a set of data from the Master List (100-200 rows), I want to go thru the subform, enter Amount and Descr for each row where I have an amount (there may only be 5-10 rows out of the 100-200 that actually get a quantity entered), and then press a button on the form to add the rows with data to the Inventory List. Hopefully this explains things a little more clearly. My Master List will continue to get items added to it, and I want to be able to continue to use it as a basis for the data entry and report generation (once I get this part working).

Thanks for taking a look.
Chris

 

[grnzbra]

You base the main form on the SQL statement, and show only one record from the master table (based on the project and account fields)? If you then create a sub form based on the table inventory list table (does it have amount and description fields?) or a query derived from that table. You said you tried adding those fields to the query which makes is sound like they are not part of the table.

You now have two forms. Move the sub form to the main form and set up the parent/child releationship for the form and sub form.