Problem!! listbox

[goldstar19821982]

Hello All,

I have been trying to resolve a running query

what is happenin is i currently have an order form where i can select an item from a list and press ok this would drop the item within a listbox.
Then if i want to select another item i select the drop down list again and this would show me all the items again to be selected.
I want to limit it so each item can only be selected once, for example
itemID= 01 then next time i select on the list this item would not be ther for this order but would appear for the next order again;

Any help would be much appreciated

 

[Moniker]

Make your drop-down three fields, like this:

ItemID   ItemName   IsSelected
1        ItemA      Yes
2        ItemB      No
3        ItemC      No
.        .          .
.        .          .
X        ItemX      X

Setup that drop-down like this in the properties:

RowSourceType = Table/Query
RowSource = "SELECT * FROM YourTableName WHERE IsSelected = False"
ColumnCount = 3
ColumnWidths = 0;2;0
BoundColumn = 1

Change YourTableName to be the table that has the suggested structure above (or something similar).

When the user selects an item from the drop-down, add that item to your listbox. I assume the listbox is a subset of all items, and the drop-down is the items that haven't been selected. Then, update the table behind the drop-down like this:

CurrentDb.Execute "UPDATE YourTableName SET IsSelected=True WHERE ItemID= " & YourComboBoxName

The YourTableName above is the same as the first table that the drop-down is based on. Finally, requery the drop-down like this:

YourComboBoxName.Requery

That's it.

Hopefully, you have Item IDs and everything already setup. If need be, I can whip up a sample of that in 10 minutes if you're confused.

 

[goldstar19821982]

moniker if u dont mind, im still a bit confused....
Thank you it is much appreciated

 

[Moniker]

Here you go. I made one table (t_Items) and one form (f_Main). Open the form f_Main. It'll work. I made a "Reset" button for you so that you can select stuff from the drop-down and see how it works, and then resetting it will deselect everything and redo the combobox.

 

[goldstar19821982]

Update

Hi Monika,

I understand what you mean now, but i am getting an error message

Too few parameters expected two?

 

[ajetrumpet]

Goldstar,

At first glance here:

Parameter errors usually pop up because there are errors in your syntax blocks somewhere. Try checking all of those first.

 

[goldstar19821982]

syntax

This is the line in which the error is found....

CurrentDb.Execute "UPDATE tblitem SET IsSelected = True WHERE itemID = " & cboItemID

I have tried playing around with the syntax, sometimes it shows expected 1, or somtimes 2 and a couple of times it has shown 3

Any ideas would be appreciated

 

[Moniker]

If your cboItemID is alphanumeric (stored as text instead of a number), change it to this:

CurrentDb.Execute "UPDATE tblitem SET IsSelected = True WHERE itemID = '" & cboItemID & "'"

That would be the missing parameter.

 

[ajetrumpet]

CurrentDb.Execute "UPDATE tblitem SET IsSelected = True WHERE itemID = " & cboItemID

Have you tried different form syntax in place of just the control name?:

itemID = " & cboItemID

Try:

forms!form!cboItemID

and then try:

me.cboItemID (if the code is behind the form itself, that is)

Those work?

 

[goldstar19821982]

Hi there,

You are correct the field is alphanumeric, but the too few parameters expected one still appears, even when the code provided has been tried.
Adam - i have tried the suggestions you have made and it does not like these eitehr.

 

[Moniker]

Is your IsSelected field set to Yes/No or something else? that's the only other parameter in there.

 

[goldstar19821982]

yeah, its set to yes/no. Ive tried everything else not sure what the issue could be

 

[ajetrumpet]

It would be easier to upload what you have.

Moniker is good at fixing things like that...

 

[Moniker]

Compact/Repair the DB, the zip it and post it.

 

[goldstar19821982]

Not to worry Monikar, i worked out what i was doing wrong, The field IsSelected, was stored in my table as selected only and therefore the error was occuring....

Thanks for all your help, and you too Adam......

 

[Moniker]

Glad it was a simple naming thing. I will suggest not using "Selected" as a field name as that's also a keyword. Make it ItemIsSelected or something as now that I think about it, "IsSelected" is also a keyword in certain situations.

At least is wasn't acViewNormal printing your report out or something weird.

 

[ajetrumpet]

Hey di**head; don't remind me!