If record exists, update otherwise make new.

[EdmCath]

I am using ADO to update a database from autocad. I've been able to update existing records and create new records but I haven't figured out how to find if a record exists, update, if not addnew. Does anyone have any ideas on this?

 

[Wiszh]

You need to do something like this:

if (rs.find CRITERIA)
rs.AddNew
EndIf

rs.fields(.....) 'make changes here

rs.update

 

[formerTexan]

I presume you are opening an ADO recordset, so:

If not rs.BOF and not rs.EOF then 'record returned
'do one thing
Else 'no records returned
'then add new record
End If


Cheers,
Bill

 

[EdmCath]

When I try your method I get an error, Either BOF or EOF is true or the current record is deleted. I'm sure I'm doing something stupid, could anyone offer any solutions.

Here is a piece of my code.

oStyleName = oSpace.StyleName
oArea = oSpace.Area
oAECObjectID = oSpace.ObjectID
oRecordSet.MoveFirst
oRecordSet.Find "ObjectID = '" & oAECObjectID & "'"
If Not oRecordSet.BOF And Not oRecordSet.EOF Then
oRecordSet!ObjectID = oAECObjectID
oRecordSet!Style = oStyleName
oRecordSet!Area = oArea
Else
oRecordSet.AddNew
oRecordSet!ObjectID = oAECObjectID
oRecordSet!Style = oStyleName
oRecordSet!Area = oArea
End If

 

[RoyVidar]

Using the .Find method, only testing for .eof should be sufficient. If .eof then the record isn't found, so:

[tt] If Not oRecordSet.EOF Then
'oRecordSet!ObjectID = oAECObjectID
oRecordSet![Style] = oStyleName
oRecordSet!Area = oArea
Else
oRecordSet.AddNew
oRecordSet!ObjectID = oAECObjectID
oRecordSet![Style] = oStyleName
oRecordSet!Area = oArea
End If
oRecordSet.update[/tt]

- should do the trick (added an .update at the end) - since you've "found" (hopefully) objectid, there shouldn't be any need to "edit" it. But I should have thought your solution should have worked... Try stepping thru the code (hit F9 on the first executable line, then F8 to run line by line). Style is a property in Access, hence the [brackets], but don't know if that's anything to do with it.

Roy-Vidar