Prev: Preventing Duplicates in a Continuous Form Bound to N:N Join Table
Next: Crosstab query needing column averages
From: Banana on 14 Jul 2010 17:52
On 7/14/10 2:26 PM, David W. Fenton wrote:
> For instance, if you're viewing InventoryID 100 and it's already got
> a record in the subform for CustomerID 3000, I don't want the user
> to be able to even attempt to add a new record in that subform with
> CustomerID 3000. If I allowed the user to attempt that, they get the
> nasty duplicate value in index error message (because the primary
> key of the join table is the composite key of InventoryID +
> CustomerID).

If this is an Access standard error dialog, the only way to trap this is
via Form's OnError event which you can write custom VBA code to check
the DataErr parameter then suppress the default messagebox and show your
own or whatever you prefer.

> Now, I can't eliminate already-linked Customers from the combo box,
> because then the combo box for existing records will be blank.

I recall seeing a sample overlaying the textbox atop the combobox so you
can eliminate the duplicate listing without blanking out the names. I've
not tried that sample, though.

Here's a link that claims to do something similar:
http://metrix.fcny.org/wiki/display/dev/Continuous+Forms--Combo+Box+with+Variable+Row+Source


HTH.
From: John Spencer on 15 Jul 2010 09:04
David,

The code in the form's error event should be something like the following.
I'm not sure what the error number is for your error (3022?). So you may need
to uncomment the Debug.Print DataErr line to get the correct error number.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'Debug.Print DataErr
If DataErr = 3022 Then
MsgBox "Whoops! Duplicate entered"
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End Sub

I am guessing that your attempt was different from the above since you did not
post your code.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:
>
> I thought about that and didn't follow through on it.
>
> OK, just tried putting an error handler in the form's OnError event,
> and it doesn't fire even when I force save the record. The default
> error message comes before then. But that error message is:
>
> The value in the field or record violates the validation rule for
> the record or field...
>
> There are no validation rules anywhere in any of these fields or at
> the table level, so I assumed this was referring to the index.
> Forcing the save does trigger an error message, the usual one about
> duplicate values in the index, but it's not being delivered through
> the form's OnError event, since it's not the MsgBox() I'm returning
> (which includes both error number and description; the error number
> is absent from the error message I'm getting).
From: John Spencer on 16 Jul 2010 08:58
If you want immmediate confirmation of the possible duplicate, all you need to
do is to use the after update event of the control(s) involved to search the
table for an existing value.

If Me.NewRecord Then
If DCount("*","NameOfJoinTable","InventoryID=" & txtINventoryID & " AND
CustomerID = " & txtCustomerID)>0 THEN
MsgBox "This Customer already has this item"
'Choose one of the following or do something else.
'Me.Undo 'Undo the entire form
'Me.txtInventory.Undo 'Undo just the control

End if
End If

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

David W. Fenton wrote:
> John Spencer <JSPENCER(a)Hilltop.umbc> wrote in
> news:i1n113$anp$1(a)news.eternal-september.org:
>
>> The code in the form's error event should be something like the
>> following. I'm not sure what the error number is for your error
>> (3022?). So you may need to uncomment the Debug.Print DataErr
>> line to get the correct error number.
>>
>> Private Sub Form_Error(DataErr As Integer, Response As Integer)
>> 'Debug.Print DataErr
>> If DataErr = 3022 Then
>> MsgBox "Whoops! Duplicate entered"
>> Response = acDataErrContinue
>> Else
>> Response = acDataErrDisplay
>> End If
>> End Sub
>>
>> I am guessing that your attempt was different from the above since
>> you did not post your code.
>
> You're right -- I didn't understand that I had to use the DataErr
> parameter rather than just using the default error object.
>
> The problem here is that it's not happening in the right place -- it
> only occurs when the record is saved, and I want it to happen as
> soon as the user selects the duplicate from the dropdown list.
>
> I just don't think there's any solution to this problem in a
> continuous form.
>
 | 
Pages: 1
Prev: Preventing Duplicates in a Continuous Form Bound to N:N Join Table
Next: Crosstab query needing column averages