Prevent duplicate selection from combobox

[CJ]

Hi Groupies

On my continuous subform, I would like to prevent the user from selecting
the same item twice from the combo box. I have the following code in the
Before Update event but it is not firing.

If DCount("Product", "[Weights In]", "Product=" & Me.TICKET) > 0 Then
MsgBox "Invalid Entry. Product already selected."
Cancel = True
End If

For each ticket, they should only be able to pick the product once.
Any help would be appreciated.

 

[John W. Vinson]

Hi Groupies

On my continuous subform, I would like to prevent the user from selecting
the same item twice from the combo box. I have the following code in the
Before Update event but it is not firing.

If DCount("Product", "[Weights In]", "Product=" & Me.TICKET) > 0 Then
MsgBox "Invalid Entry. Product already selected."
Cancel = True
End If

For each ticket, they should only be able to pick the product once.
Any help would be appreciated.

One handy way to do this is to base the combo box on an "unmatched" query,
selecting only those products which have not been chosen for this order. If
you need help with the query, please post the SQL view of the combo's current
rowsource and the subform's record source.

 

[CJ]

Hi John

Thanks for popping in. I'm going to take you up on your offer because
I can't quite get it.

The SQL for the combo is just:

SELECT qryProductRates.Product, qryProductRates.ProductName
FROM qryProductRates
ORDER BY qryProductRates.ProductName;

The Subform is all from one table:

SELECT [Weights In].lngWeightID, [Weights In].TICKET, [Weights In].PRODUCT,
_
[Weights In].WEIGHT, [Weights In].strNotes
FROM [Weights In];

Thanks for looking at this for me.
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi Groupies

On my continuous subform, I would like to prevent the user from selecting
the same item twice from the combo box. I have the following code in the
Before Update event but it is not firing.

If DCount("Product", "[Weights In]", "Product=" & Me.TICKET) > 0 Then
MsgBox "Invalid Entry. Product already selected."
Cancel = True
End If

For each ticket, they should only be able to pick the product once.
Any help would be appreciated.

One handy way to do this is to base the combo box on an "unmatched" query,
selecting only those products which have not been chosen for this order.
If
you need help with the query, please post the SQL view of the combo's
current
rowsource and the subform's record source.

 

[John W. Vinson]

Hi John

Thanks for popping in. I'm going to take you up on your offer because
I can't quite get it.

The SQL for the combo is just:

SELECT qryProductRates.Product, qryProductRates.ProductName
FROM qryProductRates
ORDER BY qryProductRates.ProductName;

The Subform is all from one table:

SELECT [Weights In].lngWeightID, [Weights In].TICKET, [Weights In].PRODUCT,
_
[Weights In].WEIGHT, [Weights In].strNotes
FROM [Weights In];

Thanks for looking at this for me.

Try changing the combo's SQL to

SELECT qryProductRates.Product, qryProductRates.ProductName
FROM qryProductRates LEFT JOIN [Weights In]
ON qryProductRates.Product = [WEIGHTS IN].PRODUCT
WHERE [WEIGHTS IN].[PRODUCT] IS NULL
ORDER BY qryProductRates.ProductName;

 

[CJ]

Hi John

The SQL you gave me works, Thanks! Except that if I look up an existing
record, the previously selected products are not showing up in the combo
field
they are blank.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

Hi John

Thanks for popping in. I'm going to take you up on your offer because
I can't quite get it.

The SQL for the combo is just:

SELECT qryProductRates.Product, qryProductRates.ProductName
FROM qryProductRates
ORDER BY qryProductRates.ProductName;

The Subform is all from one table:

SELECT [Weights In].lngWeightID, [Weights In].TICKET, [Weights
In].PRODUCT,
_
[Weights In].WEIGHT, [Weights In].strNotes
FROM [Weights In];

Thanks for looking at this for me.

Try changing the combo's SQL to

SELECT qryProductRates.Product, qryProductRates.ProductName
FROM qryProductRates LEFT JOIN [Weights In]
ON qryProductRates.Product = [WEIGHTS IN].PRODUCT
WHERE [WEIGHTS IN].[PRODUCT] IS NULL
ORDER BY qryProductRates.ProductName;

 

[John W. Vinson]

The SQL you gave me works, Thanks! Except that if I look up an existing
record, the previously selected products are not showing up in the combo
field
they are blank.

ummmm... exactly.

That way the user cannot select it twice.

 

[CJ]

OK then.

I will just capture the product so that they have a record of what was
selected.

Thanks John!