Sales and Inventory Control problem.

That's a lot you're asking for in one question.

First of all, how do you want users to select which items they want to sell? What you have above is a list of all items which the users then go down filling the number of sales for each item. Would you be open to using a combo box instead, to select the item? That is, the form is initially blank, and each line has a combo box that only shows the items available in the category you have selected.

Ideally, there would be one combobox for Category selection and another for Item selection on each line, then text boxes for fill in of price(it's not a fixed number) and number of items with a total that automagically updates when the price and number of items were filled in. Barring that, I could go with a combobox of only the Items table. Initially at least it won't be so extensive as to make filling in the form a chore.

Thanks.
RWW

Hmm, tricky to filter for Category on each line of a continuous form. Not impossible though.

First, you need to create a continuous form based on your ItemSales form. The ItemID should be bound to a combo box that selects all of the relevant information from the Items table. The main difficulty here is - does your ItemSales table include the Category field? If it doesn't you can't filter on each table (a control on a continuous form must be bound to a field, otherwise it doesn't work properly).

A better solution might be to create a temporary table identical to ItemSales, and that includes the Category field. You can base your continuous form on this table instead. This also means that users aren't entering items into the "real" table until they are ready to finalise the invoice. The Category field should be bound into a combo box that selects all the unique categories. This is important - the ItemID combo box must have its "LimitToList" property set to No. When you've done that, paste the SQL of the ItemID combo's RowSource, and we'll take it from there.

I think your DB needs another table at least. Do you sell items or Categories? Can one item have different categories - and so on. I think you would be better served to have a good look at the Northwind sample DB that came with Access. It has good samples of what you are trying to do.

To: shanesuebsahakarn

Thanks for responding, however I'm not sure I'm following what you're saying. I understand in your first paragraph creating the continuous form and binding the ItemId to the combo box. In answer to your question, yes, the SalesItems table contains the CategoryID field.

Secondly, what are you referring to by "temporary table", and how would I do that? Via a query that had all the tables fields?

Thanks.
RWW

No, basically, you'd create a new table that is identical to the SalesItems table. You don't have to do this but in some circumstances it can make the system more robust. It would work something like this:

User inputs into temporary table.
User clicks "Save invoice" button.
You transfer records from the temporary table into the real table and clear the temp table.

OK, now that you have your continuous form, the CategoryID needs to be bound into a combo box that selects all of the available categories. Can you paste the SQL of the ItemID combo box? We need to change it so that it only shows the Items associated to the CategoryID of the current record.

To: GRayL

Thanks for responding. I have taken a look at the Northwind sample and have gotten a lot of info from there, however, I don't think it has samples of what I have in mind. On the Orders form in the sample database, they are listing all of the order items in a dropdown, which I could do, however I'd like to be able to first select the category, which would then limit the item dropdown to only those items within that category, rather than including everything in the inventory.

Secondly, and probably more important to me is how to change the number of items in the inventory field of the Items table based on what is entered on the sales form, for example, if there are 50 items in inventory and I sell 7 of them on one order form, I want to be able to update the inventory field to say 43. That number would be one that I would show on the sales form so that I don't oversell an item. Eventually, I would set a filter to remove the items showing 0 inventory from the list that displays on the sales form.

The Northwinds sample is acceptable other than for those two considerations.

I hope that's a bit clearer than it was.

Thanks.
RWW

Under these circumstances, I'd definitely use a temporary table. The bit of code that appends from there to the real table can also update the inventory count at the same time.

To: shanesuebsahakarn

Here is the sql from the SalesTemp table that is given in the row source for the Category combo box
   SELECT DISTINCTROW [Categories].[CategoryID], [Categories].[CategoryName] FROM [Categories];

Thanks.
RWW

To: shanesuebsahakarn

I should probably mention that the item description also shows up as a combo box list.

Sorry, I needed the SQL of the Item combo box rather than the Category combo. Basically, if it says:

SELECT * FROM Items

you need to change it to:
SELECT * FROM Items WHERE [CategoryID]=Form!cboCategoryID

where cboCategoryID is the combo box that displays the category. Now, you need to put this code in the AfterUpdate event of the Category combo box:
Me!cboItems.Requery
(cboItems is the name of the Item combo box)

You also need to put some code in the BeforeUpdate event of Item combo box:
If IsNull(DLookUp("[CategoryID]","Categories","[CategoryID]=" & Me!cboCategoryID)) Then
   MsgBox "Invalid category!"
   Cancel=True
End If

This is so that the user can't enter an invalid category. We need this because the NotInList property is set to No, and we have to do that in order to be able to filter on a row-by-row basis. We also need some code in the OnCurrent event of the form:
Me!cboItems.Requery

in order to make sure the Item combo box always shows the right data.

Phew, that'll do to start with! Let me know when you have this working, or if any of it is unclear.

To: shanesuebsahakarn

OK that works like a charm. I had a problem with an error message when I tried to open the event editor for the Item BeforeUpdate, but repairing the database seemed to eliminate it.

I'm doing this in the using a form created with the temporary table you suggested, BTW, so there are only two pieces missing: how do I get the current Inventory count from the Items table, and how do I update it for the number of items sold on the order form, and secondly, how do I write what is in the temp form to the permanent table and then blank the form to start over?

Thanks.
RWW

OK, let's assume that your inventory count field is always correct and shows the current values of the items in stock (we'll look at this in a moment). You can include that value in your combo box so that the user always gets the correct inventory count, and you can filter it to show only available items by changing the combo's recordsource to something like:
SELECT * FROM Items WHERE [CategoryID]=Form!cboCategoryID AND [Inventory]<>0

Now, you create a "Save invoice" button on the form. The save invoice button has this code:
CurrentDb.Execute "UPDATE Items INNER JOIN tmpItemSales SET [Items].[Inventory]=[Items].[Inventory]-[tmpItemSales].[QuantitySold]"
CurrentDb.Execute "INSERT INTO ItemSales (Field1,Field2...) SELECT Field1,Field2... FROM tmpItemSales"
CurrentDb.Execute "DELETE * FROM tmpItemSales"

The first line updates the inventory value in the Items table to deduct the sales you've just input. Change the names to match your own tables.
The second one copies records from the temp table into the real one - change Field1,Field2 etc. to all of the fields in your tmpItemSales table excluding any autonumber fields you may have. The second set of field1,field2 should be in the same order as the first. Finally, the third line clears the temporary table. You may also want to do:
Me.Requery

after the above lines to refresh your form if you aren't closing it immediate. Let me know if you have any problems.

Before I go forward, I realized that the Limit to List property for the Item box is set to yes, so per your instructions I tried to change it and got a message about the first visible column, which is determined by the Column Widths property isn't equal to the bound column, and to adjust the column widths property first. I have for the Item box ItemID and ItemDescription for the two columns and the first of those has it's width set to 0 so that it doesn't show in the list. The bound column is 1.

How do I fix this before proceeding?

Thanks. You've got the points when we finish this and your previous post looks like it should do it.
RWW

Ah ok, the first column has to be visible under these circumstances I'm afraid. However, what you could do is fake it slightly. Set the first column width to the absolute minimum (say 0.1). Size the combo box so that only the drop down arrow shows. Now, in front of the combo box (i.e. to the left), create a new textbox. Set its ControlSource to:
=Form!cboCategory.Column(1)
Access will put some bracketing in for you. It will look slightly messy, but with a bit of shifting around, you can achieve more or less the effect you need.

Hmmm. This is strange. I made the form using the Form wizard, then made the changes you suggested originally, but missed the one about the ListProperty limit, and it worked as it should, ie. changing what was in the Item list after the category was set to only those items in that category. Then I discovered the Limit to List property, changed the 1st column width to .001, changed the Limit to List value to no, and now, even though I can't see the ItemID when I click on the drop down arrow and the list shows the items, when I select one, the only thing that shows in the list is the ItemID, not the ItemDescription as it did prior to "fixing" it. If I try to type something in the item box, however, it only tells me that it's an invalid category, and I can't go back to choose a category without getting an invalid category message, even if I erase what was typed in the box.

So is this something I'm doing wrong, or what?

Ok, Now I follow. Long day, too many irons in the fire, etc.

I'm going to have to go back and sort out some other issues, too, because there's one value that's calculated in a query that I'd like to have posted on the form that's relevant to each item. Part of this is that the Items table contains information concerning prices paid for incoming stock that has to be converted from dollars to a foreign currency in order to calculate the "recommended sales price" so for example, I have an item that sells for $10 US and the current exchange rate is 3 pesos to the dollar, the query multiplys the price paid by 3 to come up with the price to sell for. Then there are items that come in lots, so the price is divided by the number per lot to get per item selling price. Since that's a calculated figure, I haven't figured out how to get it on the form.

You earned the points though, so I'll figure that if I have any other questions afterward, I'll know where to come.

Thanks. I've learned a bunch.
RWW

No problem, glad I could help!

You said above with respect to the problem with the Item combo box displaying only the item ID to resize the combo box to just the button, then put a text box to the left of the button with the following control source:

=Form!cboCategory.Column(1)

I thought you made a mistake with giving it the Category combo box name rather than for the Item, so I changed it to:

=[Form]![ItemID].[Column](2)

thinking that it was the second column that is displayed in the Item combo box, not the first column in the Category combo box. Is my thinking correct here?

In any case, I can't get anything other than the Item ID number to display there.
Thanks.
RWW

Since we've resized the category combo box to "hide" the Category name, what we need to do is make this text box show that information instead, by setting it to show the second column of the Category combo box. It basically replaces the part of the combo box that you've hidden.

I think you lost me. The combo box that is the problem is the Item combo box, not the Category combo box. The combo box shows the Category name and sets the list of related items in the Item combo box, and it's there that the only thing that shows up is the ItemID, rather than the ItemDescription.

Further, going back to the defiinition of using the tmpSales form to collect user input, then having it inseert that info into the Sales table on a button onclick event, something is awry. I used the same approach to insert items into the Item table and it works flawlessly, but it's not inserting anything into the Sales table. I get no error message and the DELETE * FROM tmpSales is working, so I don't know where to look. I've checked and double checked my table names and field names, and can't find the problem. Here is the INSERT line:

CurrentDb.Execute "INSERT INTO Sales (SalesDate,CategoryID,AuctionID,Price,NumberSold) SELECT SalesDate,CategoryID,AuctionID,Price,NumberSold FROM SalesTemp"

Thanks again.
RWW

Oops. Sorry, I'm not quite with it, you're absolutely right.

Does:
=[Form]![ItemID].[Column](1)

do it? Column(1) actually shows the data in the second column of the combo box, since Access counts columns starting from 0.

Yep, that fixed it. I thought I had tried it that way before but apparently not. I also figured out the INSERT problem. It was an extra required field in the Sales table that I wasn't inserting anything too.