Lookup 2 values to determine if a duplicate record

[Guest]

I my database I have a form called claims. On this form, the ClaimID is an
autonumber - works great !! What I now need to do is assess 2 other controls
on the form (UnitID and WOStep) to see if this combination already exists in
the underlying table. If it does, I want to be able to have the option to
review the matching record(s) but also have the flexibility to carry on
"unaccosted" so to speak.

How do I do this ??

 

[Rob Oldfield]

You can do it using a dcount... the tricky bit being getting the criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text... should give you
something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then

 

[Guest]

Rob,
Thanks for the response !!!

I think I understand what you are suggesting. If the number of records
matching the criteria is greater than 0 then do whatever. My only question
is the use of the [ClaimID] reference in the dcount statement.

The ClaimID is the primary key in the underlying table (named
WarrantyClaims) and is not involved in the lookup at all. Can I use a "fake"
field name here as I don't have a field that contains the concatenated UnitId
+ WOStep ??.

You can do it using a dcount... the tricky bit being getting the criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text... should give you
something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then

I my database I have a form called claims. On this form, the ClaimID is an
autonumber - works great !! What I now need to do is assess 2 other controls
on the form (UnitID and WOStep) to see if this combination already exists in
the underlying table. If it does, I want to be able to have the option to
review the matching record(s) but also have the flexibility to carry on
"unaccosted" so to speak.

How do I do this ??

 

[Rob Oldfield]

To be honest, I'm not sure if you can use a fake field, but I don't think
you can. Having said that, it doesn't really matter. The dcount just needs
*something* to count - provided it's non-null. If you have a personnel
table containing ages, sexes, and first names - and 10 people that meet
whatever criteria you've specified, then it doesn't matter which one you
count. You have 10 ages, 10 sexes, and 10 names. I just tend to use the PK
field as it's guaranteed to be non-null.

Rob,
Thanks for the response !!!

I think I understand what you are suggesting. If the number of records
matching the criteria is greater than 0 then do whatever. My only question
is the use of the [ClaimID] reference in the dcount statement.

The ClaimID is the primary key in the underlying table (named
WarrantyClaims) and is not involved in the lookup at all. Can I use a "fake"
field name here as I don't have a field that contains the concatenated UnitId
+ WOStep ??.

You can do it using a dcount... the tricky bit being getting the criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text... should give you
something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then

I my database I have a form called claims. On this form, the ClaimID

is
an

autonumber - works great !! What I now need to do is assess 2 other controls
on the form (UnitID and WOStep) to see if this combination already

exists
in

the underlying table. If it does, I want to be able to have the option to
review the matching record(s) but also have the flexibility to carry on
"unaccosted" so to speak.

How do I do this ??

 

[Guest]

Got It - thanks !!!

One more thing and then I promise to go away !!!

If I want to use the Recordset.findfirst to go to the potentially duplicated
record, how do I concatenate the Unit ID and WOStep fields at the beginning.
Any other Findfirst commands I have used have been looking for just 1 field
to findfirst on, not 2.

To be honest, I'm not sure if you can use a fake field, but I don't think
you can. Having said that, it doesn't really matter. The dcount just needs
*something* to count - provided it's non-null. If you have a personnel
table containing ages, sexes, and first names - and 10 people that meet
whatever criteria you've specified, then it doesn't matter which one you
count. You have 10 ages, 10 sexes, and 10 names. I just tend to use the PK
field as it's guaranteed to be non-null.

Rob,
Thanks for the response !!!

I think I understand what you are suggesting. If the number of records
matching the criteria is greater than 0 then do whatever. My only question
is the use of the [ClaimID] reference in the dcount statement.

The ClaimID is the primary key in the underlying table (named
WarrantyClaims) and is not involved in the lookup at all. Can I use a "fake"
field name here as I don't have a field that contains the concatenated UnitId
+ WOStep ??.

You can do it using a dcount... the tricky bit being getting the criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text... should give you
something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then


I my database I have a form called claims. On this form, the ClaimID is
an
autonumber - works great !! What I now need to do is assess 2 other
controls
on the form (UnitID and WOStep) to see if this combination already exists
in
the underlying table. If it does, I want to be able to have the option to
review the matching record(s) but also have the flexibility to carry on
"unaccosted" so to speak.

How do I do this ??

 

[Rob Oldfield]

Very similar.

rs.findfirst crit 'where crit is the same string as in my original post

Got It - thanks !!!

One more thing and then I promise to go away !!!

If I want to use the Recordset.findfirst to go to the potentially duplicated
record, how do I concatenate the Unit ID and WOStep fields at the beginning.
Any other Findfirst commands I have used have been looking for just 1 field
to findfirst on, not 2.

To be honest, I'm not sure if you can use a fake field, but I don't think
you can. Having said that, it doesn't really matter. The dcount just needs
*something* to count - provided it's non-null. If you have a personnel
table containing ages, sexes, and first names - and 10 people that meet
whatever criteria you've specified, then it doesn't matter which one you
count. You have 10 ages, 10 sexes, and 10 names. I just tend to use the PK
field as it's guaranteed to be non-null.

Rob,
Thanks for the response !!!

I think I understand what you are suggesting. If the number of records
matching the criteria is greater than 0 then do whatever. My only question
is the use of the [ClaimID] reference in the dcount statement.

The ClaimID is the primary key in the underlying table (named
WarrantyClaims) and is not involved in the lookup at all. Can I use a "fake"
field name here as I don't have a field that contains the concatenated UnitId
+ WOStep ??.

:

You can do it using a dcount... the tricky bit being getting the criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text... should

give
you

something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then


I my database I have a form called claims. On this form, the

ClaimID
is

an
autonumber - works great !! What I now need to do is assess 2 other
controls
on the form (UnitID and WOStep) to see if this combination already exists
in
the underlying table. If it does, I want to be able to have the option to
review the matching record(s) but also have the flexibility to

carry
on

"unaccosted" so to speak.

How do I do this ??

 

[Guest]

Rob,

I must be missing something cause I can't get it to work. Neither the undo
or the findfirst do anything. Here is the code:

Private Sub WorkOrderandStep_BeforeUpdate(Cancel As Integer)
Dim crit As String
If Me.NewRecord Then
crit = "[UnitID]='" + Me.UnitID + "' and [WorkOrderandStep]='" +
Me.WorkOrderandStep + "'"

If DCount("[ClaimID]", "Warranty Claims", crit) > 0 Then
If MsgBox("A claim has been found with the same Unit and WO. Do you
want to cancel these changes and go to that record instead?", _
vbQuestion + vbYesNo, "Duplicate Claim") = ybYes Then
Cancel = True
Me!WorkOrderandStep.Undo
Me!UnitID.Undo
Me.Recordset.Findfirst "[UnitID]='" + Me.UnitID + "' and
[WorkOrderandStep]='" + Me.WorkOrderandStep + "'"
End If

End If
End If

End Sub

Very similar.

rs.findfirst crit 'where crit is the same string as in my original post

Got It - thanks !!!

One more thing and then I promise to go away !!!

If I want to use the Recordset.findfirst to go to the potentially duplicated
record, how do I concatenate the Unit ID and WOStep fields at the beginning.
Any other Findfirst commands I have used have been looking for just 1 field
to findfirst on, not 2.

To be honest, I'm not sure if you can use a fake field, but I don't think
you can. Having said that, it doesn't really matter. The dcount just needs
*something* to count - provided it's non-null. If you have a personnel
table containing ages, sexes, and first names - and 10 people that meet
whatever criteria you've specified, then it doesn't matter which one you
count. You have 10 ages, 10 sexes, and 10 names. I just tend to use the PK
field as it's guaranteed to be non-null.


Rob,
Thanks for the response !!!

I think I understand what you are suggesting. If the number of records
matching the criteria is greater than 0 then do whatever. My only
question
is the use of the [ClaimID] reference in the dcount statement.

The ClaimID is the primary key in the underlying table (named
WarrantyClaims) and is not involved in the lookup at all. Can I use a
"fake"
field name here as I don't have a field that contains the concatenated
UnitId
+ WOStep ??.

:

You can do it using a dcount... the tricky bit being getting the
criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text... should give
you
something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then


I my database I have a form called claims. On this form, the ClaimID
is
an
autonumber - works great !! What I now need to do is assess 2 other
controls
on the form (UnitID and WOStep) to see if this combination already
exists
in
the underlying table. If it does, I want to be able to have the
option to
review the matching record(s) but also have the flexibility to carry
on
"unaccosted" so to speak.

How do I do this ??

 

[Rob Oldfield]

I always dislike using BeforeUpdate - it's always a bit confusing about what
state various things are in. By doing the Cancel=True thing all that you're
telling Access to do is to not bother carrying out whatever action is
telling Access to save the record. There's nothing there that actually
tells Access to get rid of that newly inserted record.

I would tend to do it in a slightly different way - maybe not the most
efficient, but it works for me....

Run the code in AfterUpdate instead.
Use the dcount to check for >1 (as the record would now have been saved)
If the user says they want to cancel the change, then run a delete query to
get rid of that newly added record.
(You'll need to still use BeforeUpdate to identify whether it's a new record
or not.)

As for why the FindFirst isn't working.... at the moment you're just finding
the record in a recordset that isn't immediately linked to the form... try
something like this...

dim rs as recordset
set rs=me.recordset.clone
rs.findfirst crit
me.bookmark=rs.bookmark

Rob,

I must be missing something cause I can't get it to work. Neither the undo
or the findfirst do anything. Here is the code:

Private Sub WorkOrderandStep_BeforeUpdate(Cancel As Integer)
Dim crit As String
If Me.NewRecord Then
crit = "[UnitID]='" + Me.UnitID + "' and [WorkOrderandStep]='" +
Me.WorkOrderandStep + "'"

If DCount("[ClaimID]", "Warranty Claims", crit) > 0 Then
If MsgBox("A claim has been found with the same Unit and WO. Do you
want to cancel these changes and go to that record instead?", _
vbQuestion + vbYesNo, "Duplicate Claim") = ybYes Then
Cancel = True
Me!WorkOrderandStep.Undo
Me!UnitID.Undo
Me.Recordset.Findfirst "[UnitID]='" + Me.UnitID + "' and
[WorkOrderandStep]='" + Me.WorkOrderandStep + "'"
End If

End If
End If

End Sub

Very similar.

rs.findfirst crit 'where crit is the same string as in my original post

Got It - thanks !!!

One more thing and then I promise to go away !!!

If I want to use the Recordset.findfirst to go to the potentially duplicated
record, how do I concatenate the Unit ID and WOStep fields at the beginning.
Any other Findfirst commands I have used have been looking for just 1 field
to findfirst on, not 2.


:

To be honest, I'm not sure if you can use a fake field, but I don't think
you can. Having said that, it doesn't really matter. The dcount

just
needs

*something* to count - provided it's non-null. If you have a personnel
table containing ages, sexes, and first names - and 10 people that meet
whatever criteria you've specified, then it doesn't matter which one you
count. You have 10 ages, 10 sexes, and 10 names. I just tend to

use
the PK

field as it's guaranteed to be non-null.


Rob,
Thanks for the response !!!

I think I understand what you are suggesting. If the number of records
matching the criteria is greater than 0 then do whatever. My only
question
is the use of the [ClaimID] reference in the dcount statement.

The ClaimID is the primary key in the underlying table (named
WarrantyClaims) and is not involved in the lookup at all. Can I use a
"fake"
field name here as I don't have a field that contains the concatenated
UnitId
+ WOStep ??.

:

You can do it using a dcount... the tricky bit being getting the
criteria
expression correct...

dim crit as string
crit="[UnitID]="+me.unitid+" and [WOStep]='"+me.wostep+"'"

(..that's assuming that UnitID is numeric and WOStep text...

should
give

you
something like...

[UnitID]=1 and [WOStep]='whatever'

You can then just check...

if dcount("[ClaimID]","TableOrQueryName",crit)>0 then


I my database I have a form called claims. On this form, the ClaimID
is
an
autonumber - works great !! What I now need to do is assess 2 other
controls
on the form (UnitID and WOStep) to see if this combination already
exists
in
the underlying table. If it does, I want to be able to have the
option to
review the matching record(s) but also have the flexibility to carry
on
"unaccosted" so to speak.

How do I do this ??