maggiemel
Posts: 75
|
| Posted: 11/09/2004, 8:38 AM |
|
I have a table which stores a UserName and BinderID (users may be associated with many binders, binders may be associates with many users). I want to prevent an insert where the UserName already exists, but ONLY if the BinderID is also the same. Using the "Unique" property for the input control right now, I get the "The value in field UserName is already in database" message, even if that UserName is associated with a different BinderID.
Is there a way for me to modify or add some code that would first check to see whether the user was in the table and THEN check to see if the BinderID were the same and THEN prevent the insert from happening? Do I need to make a combination primary key on these two fields in my table, and if so, how do I then get my record to check against both fields for the duplicate combination before allowing an insert?
Thanks in advance for your help!
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
 |
 |
Tuong Do
|
| Posted: 11/09/2004, 2:34 PM |
|
Use onvalidate event of the record form
<code>
Dim counts
Dim WhereSQL
WhereSQL = "Username=" & CCToSQL(EventCaller.Username.value, "Text") & "
AND BinderID=" & Evencaller.BinderID.value
Counts = Clng(CCdlookup("count(*)", "yourTable", WhereSQL ,
yourDBConnection1) )
If Counts > 0 Then
EventCaller.Errors.AddError "Duplicate Username and Binder ID"
End If
<code>
"maggiemel" <maggiemel@forum.codecharge> wrote in message
news:64190f268400cd@news.codecharge.com...
>I have a table which stores a UserName and BinderID (users may be
>associated
> with many binders, binders may be associates with many users). I want to
> prevent an insert where the UserName already exists, but ONLY if the
> BinderID
> is also the same. Using the "Unique" property for the input control right
> now,
> I get the "The value in field UserName is already in database" message,
> even if
> that UserName is associated with a different BinderID.
>
> Is there a way for me to modify or add some code that would first check to
> see
> whether the user was in the table and THEN check to see if the BinderID
> were
> the same and THEN prevent the insert from happening? Do I need to make a
> combination primary key on these two fields in my table, and if so, how do
> I
> then get my record to check against both fields for the duplicate
> combination
> before allowing an insert?
>
> Thanks in advance for your help!
> _________________
> Melissa Cahill
> http://www.hellcatmaggie.net/
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
maggiemel
Posts: 75
|
| Posted: 11/10/2004, 4:31 AM |
|
Thanks, Tuong Do -- that works perfectly!
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
|
