Hi Andy,
This is how I do it for a subform in datasheet view, but it doesn't involve
the BeforeUpdate event procedure. Instead, I create a unique index (no
duplicates) in table design view for the field in question. Then, I trap for
error 3022 in the Form_Error event procedure. The example shown below also
ensures that the parent record has been written first, so that you are not
attempting to enter items into the subform without first having a record in
the parent form. The code shown below is in the subform.
Private Sub Form_Error(DataErr As Integer, Response As Integer)
' Errors can occur here if the user has not already entered a member for
' the record on the main form. If there is an error and pkMemberIDTo is Null
' inform the user and cancel the entry made in the subform.
If DataErr > 0 Then
If IsNull(Me.Parent!pkMemberID) Then
MsgBox "Enter member's information before entering items
ordered.", _
vbOKOnly + vbInformation, "Team Transplant"
Me.Undo
Me.Parent!cboSalutation.SetFocus
Response = acDataErrContinue
Else
If DataErr = 3022 Then
MsgBox "This item has already been added." & vbCrLf & _
"Please choose another item.", _
vbOKOnly + vbInformation, "Item Already Added..."
Me.Undo
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End If
End If
End Sub
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
"Andy" wrote:
[Quoted Text] > Hi;
>
> Need code in "Before Update" that would warn a user that a product has
> already been selected in the current record.
>
> Have read other post replies concerning this issue, and although they seem
> to work for others the answers I have found are not working in this
> database.
>
> Have Tables:
> tblCustomer MainTblID (AutoNumber)
> tblProductBought ProductID (AutoNumber) & MainTblID (Number) & ProductName
> (Text)
> Relation is one to many MainTblID / MainTblID
>
> Forms:
> frmCustomer (it contains a bound control to MainTblID from tblCustomer.)
> sfrmProductBought (it also contains a bound control to MainTblID from
> tblProductBought.)
> Note: Added MainTblID to both for testing purposes.
>
> Tried modifying Microsoft's example:
> ' SAMPLE FOR NUMERIC LOOKUP
> ' =DLookUp("[LastName]", "Employees", _
> "[EmployeeID] = Form![EmployeeID]")
>
> Private Sub cbxProductName_BeforeUpdate(Cancel As Integer)
> Dim X As Variant
>
> X = DLookup("[ProductName]", "tblProductBought ", _
> "[MainTblID ]= Form![MainTblID]")
>
> If Not IsNull(X) Then
> Beep
> Msg = MsgBox("That ''Product'' has already been selected.",
> vbOKOnly, "Our Company Name")
> Cancel = True
> Response = acDataErrDisplay
> End If
>
> The above code does detect that the product was already added to the current
> record but it ALSO shows the MsgBox if the same product was selected in a
> different Record. Meaning Record 1 bought a hat, when a user selects a hat
> for Record 2 the MsgBox is displayed. Record 2 does not contain a hat as
> yet.
> I know that means the code is returning the selection from a different
> Record.
>
> Have tried this modification to refer to the MainTblId on the frmCustomer
> X = DLookup("[ProductName]", "tblProductBought ", _
> "[MainTableID] = Forms![frmCustomer].Form![MainTblID]")
> ' and tried "[MainTableID] = Forms![frmCustomer]!Form![MainTblID]")
>
> And this modification to refer to the control on the sfrmProductBought.
> X = DLookup("[ProductName]", "tblProductBought ", _
> "[MainTblID] = Forms![frmCustomer]![sfrmProductBought].Form![MainTblID]")
> ' also tried !
>
> In all cases it shows that the product was selected in the Current Record if
> that product had been selected in another Record.
>
> Any suggestions.
>
> Thank You for taking the time to read this post.
>
> Andy
|