Inventory Combo Box Issue

[Phan]

I have tried my best to find the answer in another thread, but haven't found anything that worked.

I am making a simple inventory database with Purchases, Sales Orders, and Transactions tables.

Purchases has Item ID (PK), Item Name, and Cost. Sales Orders has Sales ID (PK), Sales Date, and Customer. Transactions has Transaction ID (PK), Sales ID, and Item ID and Sale Price.

I have a Query that determines Current Inventory by a Join Relationship.

I have a Purchases form that works perfectly. My Sales Order Form has a sub form called SFTransactions.

My trouble is with a combo box on the Sales Order Form. I can record multiple transactions with one Sales ID on a single form. I can use the Item ID in the first column to enter and change items in the form, but the Item ID isn't useful to a user. When I put Item Name in the first column and the BoundColumn to the Item ID (usually 3rd column), my forms don't display the items already entered in the subform. My transactions table keeps the original values. The Item ID column in the subform goes blank. I can change the transactions table by changing items in the subform, but you can't see the items; they're just blank.

It works just fine using the Item IDs.

After changing the BoundColumn and order of the built in query so the Item Name is first and should show up, this happens to the subform in datasheet view. The Item ID column should be populated and the corresponding table still is.

Of course the form won't show it either, but it shows the options correctly.

It will correctly show the Item Name and correctly change the Transactions table, but the Item Name will appear blank next time it is opened.

It's really an appearance issue. How do I get it to show up? I think it is because the query finds Items 12 and 13, and doesn't recognize any other numbers and shows them as blank. It doesn't requery so when 12 or 13 is entered, it will show up.

Thanks a bunch.

 

[dkinley]

Not sure I have all of it right ... but here goes.

Your ItemID field should be a long number field - you won't be able to store the item name in it or do a lookup on it - that is perhaps why everything is disappearing on you.

So, lets just pretend you are starting from scratch ....

The combo box control source is the ItemID field. The query that generates the combo box should have the ItemID in the first column and the whatever else after - name, etc.

The column count in the combo properties should equal the number of columns you have in the query. The bound column of the combo box should be 1 and the format of the combo box should be 0";1";1";1". The trailing 1" keep repeating for all of the columns you have in the query and adjust size accordingly (but it seems you have this part down).

I agree, a matter of appeareance, but most importantly the matter of what you are binding to the table.

-dK

 

[vbaInet]

I think you had it spot on dkinley.

dkinley has given you the solution. As long as the bound column of the combo box is the ID, it would synch with the current record of the form.

 

[Phan]

I think you could figure it out, but that's not really the issue I'm having. I can do as you say with all the Purchase records displayed in the combo box so that a user can pick an Item Name and the field will show the Item Name but still correctly store the Item ID which is an autonumber. I need to display only current inventory items though.

Rereading my original post, I didn't clearly phrase my problem. I will try again.

The problem that I am having is when I use a query for the row source of a combo box and requery to show changes in current inventory. The combo box has an afterupdate macro that requeries the row source when an item is chosen. This allows real time updates on current inventory--when an item is chosen, it is no longer in current inventory. Without this, the user could potentially sell the same unique item multiple times on the same sale order. If I clicked on 13 as shown in the first picture, 13 would be in the text box on the form, and the combo box choices would then display 2 and 12 after the afterupdate requery even. It is essentially swapping the 2 and 13.

I believe the combo box does not display correctly because a chosen item is no longer in its row source. It still correctly records the Item ID though so that part is handled.

I may need to start this part from scratch or use VBA instead of a macro, but the form and bound controls work great whether I show Item Name or Item ID as suggested. The necessary requery messes up the display on only the form. I need a workaround or another way of gathering this information from a user.

 

[vbaInet]

Got you now.

That's the behaviour of a datasheet. If you requery the control when in datasheet view, that whole column gets affected. The same applies to other properties like Enable, Locked etc.

 

[vbaInet]

Look at the Continuous Form Combo example db on this site:
http://www.databasedevelopment.co.uk/examples.htm

Also, look at this too:
http://www.mvps.org/access/forms/frm0047.htm

Those links would give you ideas how to circumvent the problem.

Not as straightforward as you would have thought

 

[dkinley]

Alright. I think I am getting it, too. I was under the impression it was being populated from a general parts list - not a 'rolling inventory'.

Of course, it may still be doing that, but if that part is in inventory - the inventory is being decremented by the quantity ordered or purchased.

And so the combo-box does not like the update? Or the form doesn't like the update? Or perhaps a little of both?

I am curious if the there are individual requerys for the (sub)form/datasheet and one directed to the combo box on the AfterUpdate event?

I'm booked all day tomorrow but will try to put up a sample the following day if you don't have it resolved.

-dK

 

[Phan]

Thanks guys, I feel there's hope!

First, for dK. I have no need for quantities. Inventory is restaurant equipment but it is easier to think of in terms of an antique shop. There is only one of everything.

As for the type of requery, I have found that I have to requery the underlying query, the one it's based on. I don't have a crystal clear idea of what that means, but I saw the terminology before and it's the only way it works without clicking off and back on the record. It is as simple as can be (hopefully that's not the problem). The Item ID combo box's Property Sheet has an embedded macro in the AfterUpdate event that is simply the Requery action. Not even an argument.

The continuous form database did give me an idea. Since I think the problem is that the query does not include the current value of the combo box (the value is 2, but the box only has options for 12 and 13), is it possible to append the current value of each record into the row source of each combo box? If the value is 2, then I need both current inventory from the query (12 and 13) AND the current value of 2. Then it will be recognized and display correctly. (Side note: That would be better for the user as well. If he doesn't want to change an item, he could just click on the original value instead of hitting escape.)

If the combo box in the first picture could have a 2 in the drop down options, I think dK's original suggestion would work.

Problem is, is it possible? And if it is, how do I do it? I'm guessing I need to either append the current value to the row source itself, or to the query. Is there a variable that represents the current value?

I am also unfamiliar with filtering. Would it be better to have the row source as the table instead of the query, and then to filter the results based on a L-R Query or expression? I haven't been successful in doing so if it's possible. This is another option, but the first one would be better.

 

[dkinley]

Hey Phan ...

Not sure if this is what you wanted or not - but a very boiled down example.

All it does is use a junction table to tie the two items together and the drop down box will pick the inventory part. Once you add the quantity a DLookup and a bit of SQL will update the current inventory. Again, just for the 'rolling inventory' portion of what you are doing.

I didn't turn off the warnings or add any conditional logic, error checking, rollback method (in the case or removing the quantity from the subform so it adds back to the inventory) etc. etc.

There are many methods of doing this an I am only offering a quick solution for a demonstration of some logic. In this demo, it can't be ordered unless it is in inventory so you will have to add another table, yada, yada .....

Anyhoosier ... hope this is on the mark and it helps.

-dK

EDIT: Probably not on the mark since quantities aren't what you are after - however, the same method should be able to be used except instead of an UPDATE query, a DELETE query may be called for unless you just want to use the quantity field for a 0 or 1 as state reference ... if you could perhaps strip out any sensitive data and/or unneccessary forms and post a sample it might help in the understanding.

 

[vbaInet]

I've had to re-read your initial post and this was what struck me (which I believe to be the reason why you posted):

...
My trouble is with a combo box on the Sales Order Form. I can record multiple transactions with one Sales ID on a single form. I can use the Item ID in the first column to enter and change items in the form, but the Item ID isn't useful to a user.....

I've had to "steal" dkinley's database - so, thank you dkinley - and just changed a few properties of the combo box in concern fkInventoryID.

So for that control, look at:
1. the Row Source property. You will find that the ID comes 2nd (position-wise).
2. the Column Widths property. The 0 indicates that the ID column should be hidden.
3. the Bound Column property. The number 2 binds the Item_Name and the ID to ensure that the item in the combo box is unique.

So making a selection ensures that the ID is hidden and the item name is displayed. Also works with intellisense. The value returned as well will be the ID and not the item.

See if that's what you're after.

 

[dkinley]

It's not theft if I offered it ....

I figured it be easier putting something up for us to work with to define the issue, visualize the objective and mod the heck out of till we have resolution instead of blindly stabbing in the dark.

-dK

 

[vbaInet]

It's not theft if I offered it ....

Obviously dk, it was supposed to be a joke