Possible - Onclick Event - Insert Text into a Field in Another Tab

  • Thread starter Thread starter Guest
  • Start date Start date
It isn't working for me. These are the control names for the fields in
Followup Notes:

Contact ID
Call Date
Call Time
Subject
Read

I simply want text to be inserted into the Subject field, date & time are
automatically done with =date() & =time() in the input mask of the table &
form itself...

Curtis

1) I believe Crystal assumed (as did I) that you were putting the data to be
entered in text boxes on your form. If that's the case, let's assume that
the text boxes on your form are named txtContactID, txtCallDate, txtCallTime
and txtSubject. You'd change the code you've got to:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & Me.txtContactID & ", " _
& Format(CDate(Me.txtCallDate), "\#mm\/dd\/yyyy\#") & ", " _
& Format(CDate(Me.txtCallTime), "\#hh\:nn\:ss\#") & ", " _
& "'" & Me.txtSubject & "', " _
& "False)"

If you're not putting the text in controls on the form, how do you want to
get it from the user?

2) The failure's likely happening since you're not giving actual values to
the SQL. Once you make the correction above, if it's still not working,
what's being written to the Immediate Window by your Debug.Print strSQL line
of code?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Curtis Stevens said:
1. Where do I put the text I want inserted into the code, where in the
code?
That is the whole purpose of this, is to insert some specific text.....

2. I tried yours and it says: Run-time error '2465" MA can't find the
field "|" referred to in your expression.

In case you need it, here is the code I have as the onclick:

==========CODE=============
Private Sub Decals___Signage_Sent_Click()
Dim strSQL As String

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & [Contact ID] & ", " _
& Format(CDate([Call Date]), "\#mm\/dd\/yyyy\#") & ", " _
& Format(CDate([Call Time]), "\#hh\:nn\:ss\#") & ", " _
& "'" & [Subject] & "', " _
& "False)"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

CurrentDb.tabledefs.Refresh
DoEvents

Me.Followup_Notes_Subform.Form.Requery

End Sub
============CODE===========

Thanks
Curtis


In addition to the advice you're getting from Crystal, your quotes are
wrong. Specifically, you've got an unnecessary double quote in front of
[Call Date_controlname], and [Contract ID_controlname] shouldn't be in
quotes. As well, since you're trying to insert a specific set of values
(rather than a subset of values from another table), you shouldn't be
using
the INSERT INTO ... SELECT construct: you should be using the INSERT INTO
.... VALUES construct:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & [Contact ID_controlname] & ", " _
& "#" & [Call Date_controlname] & "#, " _
& "#" & [Call Time_controlname] & "#, " _
& "'" & [Subject_controlname] & "', " _
& "False)"

As well, since you cannot be sure of what your users have their Short
Date
and Short Time formats set to, it's prudent to force the date and time
values into formats with which Access will work correctly:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & [Contact ID_controlname] & ", " _
& Format(CDate([Call Date_controlname]), "\#mm\/dd\/yyyy\#") &", " _
& Format(CDate([Call Time_controlname]), "\#hh\:nn\:ss\#") & ", " _
& "'" & [Subject_controlname] & "', " _
& "False)"

Finally, it's FAR better to combine date and time into a single field,
rather than store them in separate fields. Should you only require the
date
portion or the time portion at some point, you can use the DateValue or
TimeValue functions to extract those specific parts.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
That didn't work. Here is the code I used. Still says error on the #
symbol
where I mentioned it. Also having a brain fart, but when you say
change
the
control name, you mean "Contact ID" is the control name right?

----------------

Dim strSQL As String

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " SELECT [Contact ID_controlname], " _
& "#" & "[Call Date_controlname] & "#, " _
& "#" & [Call Time_controlname] & "#, " _
& "'" & [Subject_controlname] & "', " _
& "False;"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

CurrentDb.tabledefs.Refresh
DoEvents

Me.Followup_Notes_Subform.Form.Requery
Click to expand...
Click to expand...
Click to expand...
 
What does "isn't working for me" mean? Are you getting an error? If so, what
it is? If not, what's happening that shouldn't be happening (or isn't
happening when it should be)?

Make sure you're using the correct names in the SQL. Note that Access is
never happy with spaces in names: to refer to them, put underscores where
the spaces are:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & Me.Contact_ID & ", " _
& Format(CDate(Me.Call_Date), "\#mm\/dd\/yyyy\#") & ", " _
& Format(CDate(Me.Call_Time), "\#hh\:nn\:ss\#") & ", " _
& "'" & Me.Subject & "', " _
& "False)"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Curtis Stevens said:
It isn't working for me. These are the control names for the fields in
Followup Notes:

Contact ID
Call Date
Call Time
Subject
Read

I simply want text to be inserted into the Subject field, date & time are
automatically done with =date() & =time() in the input mask of the table &
form itself...

Curtis

1) I believe Crystal assumed (as did I) that you were putting the data to
be
entered in text boxes on your form. If that's the case, let's assume that
the text boxes on your form are named txtContactID, txtCallDate,
txtCallTime
and txtSubject. You'd change the code you've got to:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & Me.txtContactID & ", " _
& Format(CDate(Me.txtCallDate), "\#mm\/dd\/yyyy\#") & ", " _
& Format(CDate(Me.txtCallTime), "\#hh\:nn\:ss\#") & ", " _
& "'" & Me.txtSubject & "', " _
& "False)"

If you're not putting the text in controls on the form, how do you want
to
get it from the user?

2) The failure's likely happening since you're not giving actual values
to
the SQL. Once you make the correction above, if it's still not working,
what's being written to the Immediate Window by your Debug.Print strSQL
line
of code?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
1. Where do I put the text I want inserted into the code, where in the
code?
That is the whole purpose of this, is to insert some specific text.....

2. I tried yours and it says: Run-time error '2465" MA can't find
the
field "|" referred to in your expression.

In case you need it, here is the code I have as the onclick:

==========CODE=============
Private Sub Decals___Signage_Sent_Click()
Dim strSQL As String

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & [Contact ID] & ", " _
& Format(CDate([Call Date]), "\#mm\/dd\/yyyy\#") & ", " _
& Format(CDate([Call Time]), "\#hh\:nn\:ss\#") & ", " _
& "'" & [Subject] & "', " _
& "False)"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

CurrentDb.tabledefs.Refresh
DoEvents

Me.Followup_Notes_Subform.Form.Requery

End Sub
============CODE===========

Thanks
Curtis



In addition to the advice you're getting from Crystal, your quotes are
wrong. Specifically, you've got an unnecessary double quote in front
of
[Call Date_controlname], and [Contract ID_controlname] shouldn't be in
quotes. As well, since you're trying to insert a specific set of
values
(rather than a subset of values from another table), you shouldn't be
using
the INSERT INTO ... SELECT construct: you should be using the INSERT
INTO
.... VALUES construct:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & [Contact ID_controlname] & ", " _
& "#" & [Call Date_controlname] & "#, " _
& "#" & [Call Time_controlname] & "#, " _
& "'" & [Subject_controlname] & "', " _
& "False)"

As well, since you cannot be sure of what your users have their Short
Date
and Short Time formats set to, it's prudent to force the date and time
values into formats with which Access will work correctly:

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " VALUES ( " & [Contact ID_controlname] & ", " _
& Format(CDate([Call Date_controlname]), "\#mm\/dd\/yyyy\#") &", " _
& Format(CDate([Call Time_controlname]), "\#hh\:nn\:ss\#") & ", " _
& "'" & [Subject_controlname] & "', " _
& "False)"

Finally, it's FAR better to combine date and time into a single field,
rather than store them in separate fields. Should you only require the
date
portion or the time portion at some point, you can use the DateValue
or
TimeValue functions to extract those specific parts.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
That didn't work. Here is the code I used. Still says error on the
#
symbol
where I mentioned it. Also having a brain fart, but when you say
change
the
control name, you mean "Contact ID" is the control name right?

----------------

Dim strSQL As String

strSQL = "INSERT INTO [Followup Notes] " _
& "([Contact ID], [Call Date], [Call Time], " _
& " [Subject], [Read]) " _
& " SELECT [Contact ID_controlname], " _
& "#" & "[Call Date_controlname] & "#, " _
& "#" & [Call Time_controlname] & "#, " _
& "'" & [Subject_controlname] & "', " _
& "False;"

Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

CurrentDb.tabledefs.Refresh
DoEvents

Me.Followup_Notes_Subform.Form.Requery
Click to expand...
Click to expand...
Click to expand...
 
Back
Top