Comand "Email Inquiry Sent" text to a subform.

[Guest]

Alright, I have a database of customers. I have a two tables: Customers &
Notes. The databases are related to as One to many (customers to notes) I
have no problems with this, on my CustomersForm, it displays the NotesSubform
no problem. Ok, now what I want to do is, hit an exsisting command button on
the CustomersForm, besides creating an email, I also want it to put in a note
that an email was sent.

So basically what I want to do is add a new note on the subform, for
instance "Email Inquiry Sent". by pressiong a command button on the
CustomersForm (Related to the client of course)

YOUR ASSISTANCE IS APPRECIATED

 

[Guest]

From your question, I am assuming it already sends the E-Mail from your
command button and you want it to add data to your notes table to say so ?
This code also assumes your Notes table has 2 fields, CustomerID and Notes
In the code for your button click add something like this

' Code to send E-Mail is here
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Notes (CustomerID,Notes) VALUES (" & Me.CustomerID
& ", 'E-Mail Enquiry Sent')"
DoCmd.SetWarnings True
' May need to refresh the subform display here

 

[jixq_007]

niaho what is you name

 

[Guest]

YOU ARE THE MAN!!!!!!!!!!! I've spent a week on this and everyone seemed to
have a long long WRONG ANSWER.. You got it right, FIRST CALL!

THANK YOU THANK YOU THANK YOU THANK YOU!

 

[Guest]

Another Quick Question, OK This Works

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Notes (ClientID,Notes) VALUES (" & Me.ID & ",
'Client Booked')"
DoCmd.SetWarnings True
Me.Notes_subform1.Requery

However, I also want to add the Date/Time to the Time Field, so I tried:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Notes (ClientID,Notes,Time) VALUES (" & Me.ID & ",
'Client Booked', " & Now() & ")"
DoCmd.SetWarnings True
Me.Notes_subform1.Requery

Now I Get Run-Time error '3134'
Syntax error in Insert INTO Statement.

Any thoughts?

 

[Tony Toews]

' Code to send E-Mail is here
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Notes (CustomerID,Notes) VALUES (" & Me.CustomerID
& ", 'E-Mail Enquiry Sent')"
DoCmd.SetWarnings True

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

However, I also want to add the Date/Time to the Time Field, so I tried:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Notes (ClientID,Notes,Time) VALUES (" & Me.ID & ",
'Client Booked', " & Now() & ")"
DoCmd.SetWarnings True
Me.Notes_subform1.Requery

Now I Get Run-Time error '3134'
Syntax error in Insert INTO Statement.

You need to insert #s around the date/time field as a delimiter.
However SQL statements require that the dates be either completely
unambiguous or in mm/dd/yy, or mm/dd/yyyy format. Otherwise
Access/Jet will do it's best to interpret the date with unknown
results depending on the specific date it is working with. You can't
assume that the system you are working on is using those date formats.
Thus you should use the logic at the following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm