Full Version: Table Not To Update If Sendobject Cancelled
UtterAccess Forums > Microsoft® Access > Access Forms
Hi all,
eed help with this one big time if that’s ok.
I have a form (Form1). This form will be used by HR/Personal staff to enter in how employees have responded to a particular question. Once the employee has been located on the form a combo box (Answer) is used to enter in how the employee has answered. If the employee has said anything but ‘Yes’ then an email is fired off using SendObject to their manager. The user of the form gets a chance to preview the email before pressing ‘Send’. And this is where my problem is.
If the user of the form cancels the email at the point of previewing it then they are returned to the form whereupon the form refreshes itself so it is blank and ready for the next employee (this is all good and what I want). However, the employee’s answer has already been put into the table connected to the form (Table1). What I need to know is how do I ‘remove’ the answer from the table so that in effect if the email is cancelled/not sent then their answer is removed from the table?
Thanks in advance!
Why not just hold those answers in variables and update the tables after you know the sendobject went through?
However, to answer your question, if you have an id # for the record, just use a simple delete in your error trapping code. That way when you get the error back that the sendobject failed, it will delete the entry. Example:
dsql = "DELETE * FROM Your_Table WHERE RecordID = " & someVariable"
Currentdb.Execute dsql
Thanks Bulsatar, I love that piece of code. My unique identifier would be their employee number (EmployeeNumber) and my Table is called Table1. Would you mind telling me how the code will look with that information plugged into it. I'm not understanding the &some variable part of your email.
any thanks
My bad, I had a set of extra quotes at the end...
That we are doing is sending a SQL string to the jet engine to execute. To put a string together with actual variables, we need to combine them in some way which is where the & sign comes in. So we start our delete statement and then add on the little bit of information we need identify which record needs to be deleted. That little bit of information has to come from somewhere (the form you are working in has the unique id for that record already up perhaps???). So the SomeVariable may actually be something like...Me.Textbox1.Value. Or, when you save the record to begin with on the answer, you can get the record id at that point and set it a variable to use for later equal to that. With out knowing how your forms and everything are setup, gotta be kinda general in the answer.
However, with the info you gave, it would look something like this....
someVariable = Textbox1.Value
dsql = "DELETE * FROM Table1 WHERE [EmployeeNumber] = " & someVariable
Currentdb.Execute dsql

When sending these statements to the jet engine, you have to make sure that the variables (or generally the information) is surrounded by the correct identifiers. So, when you are sending numbers, you don't need anything around the variables. However, with strings and dates you do. For strings the tail end would look like this "...= '" & strVariable & "' "
Notice the single quotes that will end up surrounding the string. For dates and times the tail end would look like this "...= #" & dtVariable & "# "
When the code runs, it will look for what the value of the variable is and plug it into the string where you tell it to go. So if your variable equals John Doe, then the completed delete string would look like this: "DELETE * FROM Table1 WHERE [Employee_Name] = 'John Doe'"
THere are a couple of references you might want to take a look at to further your understanding of vba and sql....
SQL training
Declaring Variables
Reference controls on a form and subform
Hope that helps
Wow!!! That is brilliant!! Thank's a lot for spending your time answering my post in such a comprehensive fashion! I shall try the code when i get back to work on Monday. Off to the boozer now.
eace out fella.
Thanks matey! That code works great!!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.