Prev: Records and Associated Notes Query
Next: loan or RD deduction using dtpicker
From: CJ on 23 Jan 2010 19:44
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.

--
Thanks for taking the time!

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

From: John W. Vinson on 23 Jan 2010 19:58
On Sat, 23 Jan 2010 17:44:05 -0700, "CJ" <private(a)newsgroups.com> wrote:

>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 [MVP]
From: CJ on 23 Jan 2010 20:32
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!
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:cm6nl5lbmf2k48e6kg7gbd3rl5l3vgdtlk(a)4ax.com...
> On Sat, 23 Jan 2010 17:44:05 -0700, "CJ" <private(a)newsgroups.com> wrote:
>
>>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 [MVP]

From: John W. Vinson on 23 Jan 2010 22:25
On Sat, 23 Jan 2010 18:32:40 -0700, "CJ" <private(a)newsgroups.com> wrote:

>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 [MVP]


From: CJ on 23 Jan 2010 23:54
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!
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:3afnl59uju73c6afe94sjeb6au3hcsra6s(a)4ax.com...
> On Sat, 23 Jan 2010 18:32:40 -0700, "CJ" <private(a)newsgroups.com> wrote:
>
>>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 [MVP]
>
>

 |  Next  |  Last
Pages: 1 2
Prev: Records and Associated Notes Query
Next: loan or RD deduction using dtpicker