Table or Query?

F

FJ

I've been racking by brain and various books to find some answers, but to no
avail. I need assistance with a simple task.

I have a combo box that looks up items from a table. Once an item is
selected, I do not want it to appear again. Should I be using a query for my
control source?

Please help!! Any assistance would be greatly appreciated.

Thank you!!!
 
K

Klatuu

When you say "Once an item is selected, I do not want it to appear again.",
what is the scope? Do you mean never ever again or do you mean while the
form is open?

It makes a difference in how you will approach this.
 
F

FJ

Thank you for responding & sorry to be so vague. I do not want the item to
appear ever again....I want it to be strictly for that particular record......

fyi - the field is set as the primary key, and I have it indexed with No
Duplicates. If the item is selected for a record, and you move to a new
record, the same item will appear, but if selected will indicate that the
item will create duplicate records - only after all other fields within the
record are populated and the user attempts to create a new record.

Hope this makes sense....

Thanks!
Francine
 
K

Klatuu

This part I don't understand:

If the item is selected for a record, and you move to a new
record, the same item will appear, but if selected will indicate that the
item will create duplicate records - only after all other fields within the
record are populated and the user attempts to create a new record.
Click to expand...

It sounds like you are using a bound combo box to search for records. If
this is the case, what is happening is you are changing the value of the
record's primary key and then you are wanting to create a new record, but
because you changed the value in the previous record, the value is already in
the table.
 
K

Klatuu

add a field to your table. in the after update event of the combo, update
the field to indicate it should not be used. Filter your combo on that field.

I doubt that will fix the problem based on your previous post, but since you
wish to igore it, the above is the answer to your question.
 
F

FJ

Thanks for replying. Maybe this will shed more light:

On my Entry Form, I have a combo-box that is linked to records in a table.
This table contains pre-set serial numbers that will be assigned to a
particular customer.

For instance, I have serial numbers 111, 112, 113, 114, 115, etc. When the
operator completes the form for record 111, and goes to a new record, I do
not want record 111 to be displayed as being available.

What do I need to do in order for this to work?
 
F

FJ

Thanks again....I'm a newbie - would you please elaborate on how to do
this...I'm assuming I would use a Code Builder....

Thanking you in advance...
 
K

Klatuu

add a field to your table. in the after update event of the combo, update
the field to indicate it should not be used. Filter your combo on that field.
Open your table in design view. Add a new field to the table. I would use
a Yes/No field. For example purposes, call it [NUMBER_USED] Make the default
value False (no).

Now change the row source of your combo to filter on this field:

SELECT ...... WHERE NOT [NUMBER_USED]

Then in the After Update event of form (That way we know the number was
actually used), update the field in the table:

CurrentDb.Execute("UPDATE NumberTable SET [NUMBER_USED] = -1 WHERE
[THE_NUMBER = " & Me.cboNumber & ";"), dbFailOnError
 
Top