Delete record fields before show or after insert?????

[seanbrown93]

I have an employee referral form. When an open job position comes up, an employee can refer someone they know. I have the following fields: DatePosted, Position, SalaryRange, DateReferred, EmployeeReferring, Applicant, Notes.

When someone referrs an new applicant, it writes to the database and sends the HR department an email. The problem is when more than one person wants to refer someone for the same position, it brings up the last information entered into the database form.

How can I clear specific fields either after the email is sent or before the record is shown? I would like to delete the following fields: DateReferred, EmployeeReferring, Applicant, Notes.

I am using Access 2000.

Any help is greatly appreciated. I am sorry if this is trivial, but I am not a programmer.

 

[onpnt]

Hi there, I guess I'm confused but not confused. confusing?

is all you want to know is how to DELETE data from the DB.
database design should really prevent these issues from coming up. Key references should be referenced to prevent data crossing.

BUT, if you need to and from how it sounds the data is useless after emailing the information entered so just use the SQL DELETE statement to get rid of it

http://www.w3schools.com/sql/sql_delete.asp

eg:
<%
' after email script confirms sent mail
' delete last given values on ID
' note: use key to force the correct deletion
' or time reference on initial submission
' create your connection object if need be and not already done
' Dim sql variable
Dim sqltxt
sqltxt = &quot;DELETE FROM table_name WHERE DateReferred = here would be a running variable to base on&quot;

' this deletes all matches to
' DateReferred, EmployeeReferring, Applicant, Notes
%>

That running variable you would need to set at insert time. That will prevent any otehr insert values that may have or been occuring at session time to be deleted along with it.


____________________________________________________

http://www.onpntwebdesigns.com

 

[seanbrown93]

Yes, the data is useless after the email is sent, so I would like to delete it, but only specific columns within the table.

The table name is OpenJobs.

Can you help me out with the exact string I will need to use to do this?

I guess I don't really understand how to string together multiple variables (columns).

Dim sqltxt
sqltxt = &quot;DELETE FROM OpenJobs WHERE DateReferred = ?????

Thanks.

 

[onpnt]

first thing is you'll need to allow empty values (null) to be in the fields. under formatting in design view
faq333-3048 has some good examples of concatination of the
variables to the SQL statement
along with other concerns like data types.
eg: Reference 8 mainly

here's a syntax example of specific deletions if these are all strings along with the date value enclosed in # # for access syntax

Dim sqltxt
sqltxt = &quot;DELETE FROM OpenJobs WHERE DateReferred = #&quot; & DateVariable & &quot;#, &quot;
sqltxt = sqltxt & &quot;'&quot; & DateReferred & &quot;','&quot; & EmployeeReferring & &quot;', &quot;
sqltxt = sqltxt & &quot;'&quot; & Applicant & &quot;','&quot; & Notes & &quot;'&quot;
' always test the output for formatting PRIOR to execution
Response.Write sqltxt

output was
DELETE FROM OpenJobs WHERE DateReferred = ##, '','', '',''

and looking good.

now all you need is a
connectionObject.Execute(sqltxt)

to execute this satement. no recordset's etc.. are needed in deletions

____________________________________________________

http://www.onpntwebdesigns.com

 

[onpnt]

alright, I must have drank too much coffee this morning cause I started writing that and drifted after reading the graph thread.

I apologize, that is completely wrong and should be a update.
you need to update the values that match the date. the delte will not work in that fassion.

talk about getting my typing mixed up

same syntax though as the delete

http://www.w3schools.com/sql/sql_update.asp

so just update them and set the values to delete (set to empty) to ''


____________________________________________________

http://www.onpntwebdesigns.com

 

[onpnt]

sqltxt = &quot;UPDATE OpenJobs &quot;
sqltxt = sqltxt & &quot;SET DateReferred='&quot; & DateReferred & &quot;',EmployeeReferring='&quot; & EmployeeReferring & &quot;', &quot;
sqltxt = sqltxt & &quot;Applicant='&quot; & Applicant & &quot;',Notes='&quot; & Notes & &quot;'&quot;
sqltxt = sqltxt & &quot; WHERE DateReferred = #&quot; & DateVariable & &quot;#&quot;

____________________________________________________

http://www.onpntwebdesigns.com

 

[seanbrown93]

LOL. I know the feeling of going off on the wrong tangent.

Are you using DateReferred as the key? If so, I'm sorry that I didn't tell you, but ID (autonumbered) is the primary key. Would that change anything in your code?

Thanks again for the help!

Sean

 

[onpnt]

no, the reference to the Date will probably be your most unique value in regards to insert time and update time. depending on how many processes you go through between the actual insert occurs till the update execute there are several ways for you to hold that value present int he session.
1) session variables
eg:
get teh date() for inserting and for a variable
Dim InsertTime
InsertTime = date()
this should be used for both the SQL insert and down the line
then simply
session(&quot;InsertTime&quot = InsertTime

2) cookies, which I wouldn't use for this process. not enough need.

3) if localized in the same script (page) just the simply variable.

____________________________________________________

http://www.onpntwebdesigns.com

 

[seanbrown93]

Well, I'm going to keep trying, but I haven't been able to get it to work yet. I'll let you know how it turns out.

Thanks for all the help.

Sean