Duplicates in a continuous form

[Arlend Floyd]

How can I restrict a user from selecting 1 of three options in a bound combo
box more then once in a continuous Subform. Here is what i have...

CboBox with these three choices.
"Primary Insurance"
"Other Insurance"
"Legal Dept"

There can only be 1 "Primary Insurance" and there can be many of the other
two choices.

Thanks...

 

[John Smith]

In the before_update of the combo, if "Primary Insurance" has been selected
then query the table to see if there is a record for the same customer(?) with
a different primary key and a value of "Primary Insurance". If there is then
show a MsgBox and cancel the event.

HTH
John
##################################
Don't Print - Save trees

 

[Arlend Floyd]

Thanks, do you have a sample code to preform this?

Thanks in advance..

 

[John W. Vinson]

Thanks, do you have a sample code to preform this?

Thanks in advance..

Since John hasn't replied I'll jump in. It's pretty straightforward:

Private Sub Product_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerID]", "[TableName]", _
"[CustomerID] = " & Me!CustomerID & _
" AND Product = '" & Me!Product & "'")) Then
Cancel = True
MsgBox "Only one Primary Insurance record per customer please", vbOKOnly
End If
End Sub

 

[Arlend Floyd]

Thanks very much...Just what i needed

Thanks, do you have a sample code to preform this?

Thanks in advance..

Since John hasn't replied I'll jump in. It's pretty straightforward:

Private Sub Product_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerID]", "[TableName]", _
"[CustomerID] = " & Me!CustomerID & _
" AND Product = '" & Me!Product & "'")) Then
Cancel = True
MsgBox "Only one Primary Insurance record per customer please", vbOKOnly
End If
End Sub

 

[John Smith]

Thanks John, I've only just got back to the news group!

John
##################################
Don't Print - Save trees

Thanks, do you have a sample code to preform this?

Thanks in advance..

Since John hasn't replied I'll jump in. It's pretty straightforward:

Private Sub Product_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerID]", "[TableName]", _
"[CustomerID] = " & Me!CustomerID & _
" AND Product = '" & Me!Product & "'")) Then
Cancel = True
MsgBox "Only one Primary Insurance record per customer please", vbOKOnly
End If
End Sub

 

[Arlend Floyd]

Thanks, do you have a sample code to preform this?

Thanks in advance..

Since John hasn't replied I'll jump in. It's pretty straightforward:

Private Sub Product_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[CustomerID]", "[TableName]", _
"[CustomerID] = " & Me!CustomerID & _
" AND Product = '" & Me!Product & "'")) Then
Cancel = True
MsgBox "Only one Primary Insurance record per customer please", vbOKOnly
End If
End Sub

I Get this error " Run-Time error "2001" you canceled the previous operation
" with the code listed below please help me correct it.

Thanks,


Private Sub CboDefendantType_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[AccountNum]", "[TblDefendants]", _
"[AccountNum] = " & Me!AccountNum & _
" AND DefendantType = '" & Me!CboDefendantType & "'")) Then
Cancel = True
MsgBox "Only one Primary Defendant per Patient", vbOKOnly
End If

End Sub

 

[John W. Vinson]

I Get this error " Run-Time error "2001" you canceled the previous operation
" with the code listed below please help me correct it.

Undo the combo box in addition to cancelling the update:

Private Sub CboDefendantType_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[AccountNum]", "[TblDefendants]", _
"[AccountNum] = " & Me!AccountNum & _
" AND DefendantType = '" & Me!CboDefendantType & "'")) Then
Cancel = True
Me.cboDefendentType.Undo
MsgBox "Only one Primary Defendant per Patient", vbOKOnly
End If

If you want to cancel the entire record use Me.undo instead.