duplicated records

  • Thread starter Thread starter taco
  • Start date Start date
T

taco

Hi everyone;

First of all, thanks in advance for your time and help. Here is the drill;
On my WL table, one customer can be registered only once per day. But there
are times, we have same customer more than once on paper. What I would like
to do is; after data entered and "Add New Record" button clicked, if that
customer has record already, code should find that record, add supplementary
data over existing one and cancel the last record which is duplicate. I've
tried with "docmd.findrecord" but it did not work.

Thanks a lot.
 
taco said:
Hi everyone;

First of all, thanks in advance for your time and help. Here is the drill;
On my WL table, one customer can be registered only once per day. But
there
are times, we have same customer more than once on paper. What I would
like
to do is; after data entered and "Add New Record" button clicked, if that
customer has record already, code should find that record, add
supplementary
data over existing one and cancel the last record which is duplicate. I've
tried with "docmd.findrecord" but it did not work.
Click to expand...

Since there are 2 values which affect whether or not there is a duplicate,
you need routine which checks the value of both controls.

Sub IsDuplicate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

'Search for a matching record
rst.FindFirst "[Field1] = " & Me![Field1] & " And [Field2] = " &
Me![Field2]

If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If

Set rst = Nothing
End Sub

Sub Field1_AfterUpdate()
IsDuplicate
End Sub

Sub Field2_AfterUpdate()
IsDuplicate
End Sub
 
Thanks a lot for your answer.. I'll try that tonight... I really appreciated.

Regards;

Arvin Meyer said:
taco said:
Hi everyone;

First of all, thanks in advance for your time and help. Here is the drill;
On my WL table, one customer can be registered only once per day. But
there
are times, we have same customer more than once on paper. What I would
like
to do is; after data entered and "Add New Record" button clicked, if that
customer has record already, code should find that record, add
supplementary
data over existing one and cancel the last record which is duplicate. I've
tried with "docmd.findrecord" but it did not work.
Click to expand...

Since there are 2 values which affect whether or not there is a duplicate,
you need routine which checks the value of both controls.

Sub IsDuplicate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

'Search for a matching record
rst.FindFirst "[Field1] = " & Me![Field1] & " And [Field2] = " &
Me![Field2]

If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If

Set rst = Nothing
End Sub

Sub Field1_AfterUpdate()
IsDuplicate
End Sub

Sub Field2_AfterUpdate()
IsDuplicate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Hi Arvin;

Once again thanks a lot. With a little modification, the code works
perfectly. Exactly what I needed.
Thank you very much.

Regards;

Arvin Meyer said:
taco said:
Hi everyone;

First of all, thanks in advance for your time and help. Here is the drill;
On my WL table, one customer can be registered only once per day. But
there
are times, we have same customer more than once on paper. What I would
like
to do is; after data entered and "Add New Record" button clicked, if that
customer has record already, code should find that record, add
supplementary
data over existing one and cancel the last record which is duplicate. I've
tried with "docmd.findrecord" but it did not work.
Click to expand...

Since there are 2 values which affect whether or not there is a duplicate,
you need routine which checks the value of both controls.

Sub IsDuplicate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

'Search for a matching record
rst.FindFirst "[Field1] = " & Me![Field1] & " And [Field2] = " &
Me![Field2]

If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If

Set rst = Nothing
End Sub

Sub Field1_AfterUpdate()
IsDuplicate
End Sub

Sub Field2_AfterUpdate()
IsDuplicate
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
 
Back
Top