Search Before adding New

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

I know what I want to do, but not exactly how to proceed.

I currently add a new recordset using code, but just realized that I never
verify that it doesn`t already exist and so, now I find myself with tons of
duplicates... Oh the joys of cleaning up after oneself!

I am using the following code
With Me![frm_ing]!frm_dtManuals.Form.RecordsetClone
.AddNew
![PlanifIngId] = iIngId
![dtManuelP] = dtManu
.Update
End With

I would first need to validate if the'dtManu' already exists and only
perform the .AddNew if it does not. How would I code this type of scenario?

Thank you for your help,

QB
 
See FindFirst and NoMatch for your clone. You may need to explicitly set the
recordset to get this to work though.


Set rst = Me.....RecordsetClone
rst.FindFirst "[dtManuelP] = #" & dtManu & "#"
If rst.NoMatch Then
...
Enter your records here
...
End If



http://www.blueclaw-db.com/access_findfirst.htm


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Thank you! Works Beautifully!!!

Now I just need to clean up a small mess of duplicates, at least that isn`t
too hard/time consuming to do.



Jack Leach said:
See FindFirst and NoMatch for your clone. You may need to explicitly set the
recordset to get this to work though.


Set rst = Me.....RecordsetClone
rst.FindFirst "[dtManuelP] = #" & dtManu & "#"
If rst.NoMatch Then
...
Enter your records here
...
End If



http://www.blueclaw-db.com/access_findfirst.htm


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



QB said:
I know what I want to do, but not exactly how to proceed.

I currently add a new recordset using code, but just realized that I never
verify that it doesn`t already exist and so, now I find myself with tons of
duplicates... Oh the joys of cleaning up after oneself!

I am using the following code
With Me![frm_ing]!frm_dtManuals.Form.RecordsetClone
.AddNew
![PlanifIngId] = iIngId
![dtManuelP] = dtManu
.Update
End With

I would first need to validate if the'dtManu' already exists and only
perform the .AddNew if it does not. How would I code this type of scenario?

Thank you for your help,

QB
Click to expand...
Click to expand...
 
That works IF all the records in the table are in the recordset of the form.

You might be safer using DLookup or DCount to check for the existence of
the record in the table

Something like the following
If DCount("*","NameOfSourceTable","dtManuelP=" & _
Format(dtManu,"#yyyy-mm-dd#) = 0 THEN
'add the new record
Else
'Do something else
End if


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thank you! Works Beautifully!!!

Now I just need to clean up a small mess of duplicates, at least that isn`t
too hard/time consuming to do.



Jack Leach said:
See FindFirst and NoMatch for your clone. You may need to explicitly set the
recordset to get this to work though.


Set rst = Me.....RecordsetClone
rst.FindFirst "[dtManuelP] = #" & dtManu & "#"
If rst.NoMatch Then
...
Enter your records here
...
End If



http://www.blueclaw-db.com/access_findfirst.htm


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



QB said:
I know what I want to do, but not exactly how to proceed.

I currently add a new recordset using code, but just realized that I never
verify that it doesn`t already exist and so, now I find myself with tons of
duplicates... Oh the joys of cleaning up after oneself!

I am using the following code
With Me![frm_ing]!frm_dtManuals.Form.RecordsetClone
.AddNew
![PlanifIngId] = iIngId
![dtManuelP] = dtManu
.Update
End With

I would first need to validate if the'dtManu' already exists and only
perform the .AddNew if it does not. How would I code this type of scenario?

Thank you for your help,

QB
Click to expand...
Click to expand...
Click to expand...
 
Back
Top