Updating, Adding and Deleting 1

[Shirley]

Dose anyone have any coding that would check a database, check to see if the SSN is there, if it is then update the information with the new information. And also check to see if the SSN is not there to add the information, or if specified to delete that SSN completly. If so could you please help me.

 

[Guest_imported]

What? <p>John Vogel<br><a href=mailto:john@computerwiz.net>john@computerwiz.net</a><br><a href=

http://www.deja.com/~computerwiz>Deja.com

Computerwiz Community</a><br>---==============================---<br>
I am currently working on

http://www.computerwiz.net.<br>

It should be LIVE 1/12/00 or before. I would appreciate suggestions, comments and the like. Please go look and help. Thanks.<br>
---========================

 

[rochelle]

This sounds pretty straightforward.<br>
<br>
if DeleteCheckBox = true then<br>
docmd.openquery &quot;DeleteSSNRecord&quot;<br>
else<br>
if isnull(dlookup(&quot;[SSN]&quot;,&quot;MyTable&quot;,&quot;[SSN]=Forms![MyForm]![MySSNField])) then<br>
docmd.openquery &quot;AppendSSNRecord&quot;<br>
Else<br>
docmd.openquery &quot;EditSSNRecord&quot;<br>
endif<br>
endif<br>
<br>
Write the appropriate queries as you see fit.

 

[DougP]

Dlookup is slow if you have a lot of records<br>
Use SQL instead.<br>
<br>
If DeleteCheckBox = True Then<br>
DoCmd.OpenQuery &quot;DeleteSSNRecord&quot;<br>
Else<br>
Dim SQL As String, db As DAO.database, rst As DAO.Recordset<br>
Set db = CurrentDb<br>
SQL = &quot;Select [SSN] from Employee Where [SSN]= &quot; & Forms![MyForm]![MySSNField]<br>
Set rst = db.OpenRecordset(SQL)<br>
If rst.RecordCount = 0 Then<br>
DoCmd.OpenQuery &quot;AppendSSNRecord&quot;<br>
Else<br>
DoCmd.OpenQuery &quot;EditSSNRecord&quot;<br>
End If<br>
End If<br>
<br>
' It seems like a lot more code but SQL is lightening fast<br>

 

[rochelle]

Thanks, Doug. I didn't realize that opening a recordset is so much faster than DLookup.<br>
<br>
BTW, do you have to add <br>
Set rst = nothing<br>
Set db = nothing<br>
to the end of your function or sub to release your objects or are they realeased as soon as execution reaches End Function or End Sub?

 

[DougP]

Yes Access is actually based on a SQL database<br>
so using SQL statements is fastest.<br>
It can be intimidating at first to understand.<br>
Rumor has it Bill Gates had a falling out with Oracle and used Sybase as a basis for Access. Don't quote me on that.<br>
<br>
Yes it's best to close the recordsets etc.<br>
I think they are released but good coding practice would be to close what you opened.<br>
Good point rochelle.<br>
<br>
I usually use<br>
rst.close<br>
db.close <br>
in that order<br>
<br>
Another point is if you want to refer to a recordset object such as <br>
X=rst.fields(&quot;abc&quot <br>
that you not close the objects before the above statement.<br>
or you will get an error 3420 &quot;Object invalid or no longer set&quot;<br>
<br>

 

[Shirley]

Thanks for your help I really appreciate it.