Comments to Append to Table? (1 Viewer)

BC_Seattle · Sep 4, 2019

So I have a comment box and you have to scroll all the way to the bottom to leave a new Comment. I am trying to add a new boxes with an Add button that will append the new comment to the Table. I am researching and coming up with this but it errors out. I am not sure if I am doing something wrong here.

CurrentDb.Execute " INSERT INTO Comments ( ProjectID, Owner, Comment, Date) " & " VALUES(" & Me.ProjectID & "','" & Me.Newowner & "','" & Me.NewComments & "','" & Me.newdate & "')", dbFailOnError

Column 1 = Auto number ID
Column 2 = Project ID Number
Column 3 = Owner
Column 4 = Comment
Column 5 = Today's date

All of these would be a variable pulled out of the form. I am new to this Syntax so I am trying to troubleshoot what I don't fully understand. *Attached Error I am getting.

arnelgp · Sep 4, 2019

its with the newdate:

Code:

Me.NewComments & "',#" & Format( Me.newdate,"mm\/dd\/yyyy")  & "#)", dbFailOnError

theDBguy · Sep 4, 2019

Hi. Is Project ID Number a Text field? Also, "Date" is reserved word in Access. Is that really the name of your field? If so, you might consider changing it to something like CommentDate to avoid potential problems in the future.

Gasman · Sep 4, 2019

Firstly you are surrounding the comma with single quotes? You do not do that. You do however surround strings with single quotes or double double quotes.

Date might need to be sruurounded by #

Always best to make this all a string and then debug.print the string to spot any anomalies.

Code:

strSQL = " INSERT INTO Comments ( ProjectID, Owner, Comment, Date) " & " VALUES(" & Me.ProjectID & ",'" & Me.Newowner & "','" & Me.NewComments & "',#" & Me.newdate & "#)"

I think that is correct

Try it out anyway
Assuming NewOwner and NewComments to be strings

HTH

BC_Seattle · Sep 4, 2019

Ill try the rename of Date I added a screenshot of what I am trying to do hoping that it might help relay

BC_Seattle · Sep 4, 2019

Gasman said:
Firstly you are surrounding the comma with single quotes? You do not do that. You do however surround strings with single quotes or double double quotes.

Date might need to be sruurounded by #

Always best to make this all a string and then debug.print the string to spot any anomalies.

Code:

strSQL = " INSERT INTO Comments ( ProjectID, Owner, Comment, Date) " & " VALUES(" & Me.ProjectID & ",'" & Me.Newowner & "','" & Me.NewComments & "',#" & Me.newdate & "#)"

I think that is correct Try it out anyway
Assuming NewOwner and NewComments to be strings

HTH

Same error but doesnt say there is anything wrong with the code.... Which is why I was stumped. I did Add the Single thinking it would need to read as TEXT but didn't work tried several ways. Does it have anything to do with my table?

BC_Seattle · Sep 4, 2019

Pic of my table ID is auto number

theDBguy · Sep 4, 2019

BC_Seattle said:
Pic of my table ID is auto number

Hi. I asked about "ProjectID," which I can see now is a Text field. Thanks.

BC_Seattle · Sep 4, 2019

Yes sorry I have added everything to a textbox and a combobox (owner) I am just trying to get it to grab info off the form and enter it to the table. I am not that great with Access.I was voluntold so I am just learning as I go and been trying to research it just finally thought I would ask since you guys are the magic makers.

Sorry if I wasn't being clear.

BC_Seattle · Sep 4, 2019

arnelgp said:
its with the newdate:

Code:

Me.NewComments & "',#" & Format( Me.newdate,"mm\/dd\/yyyy") & "#)", dbFailOnError

Same error with this one too.

Gasman · Sep 4, 2019

OK, I took project ID to be numeric. You had missed the single quote to start that string.

Code:

strSQL = " INSERT INTO Comments ( ProjectID, Owner, Comment, Date) " & " VALUES('" & Me.ProjectID & "','" & Me.Newowner & "','" & Me.NewComments & "',#" & Format( Me.newdate,"mm\/dd\/yyyy")  & "#)"


CurrentDb.Execute strSQL, dbFailOnError

HTH

edit:
I get below displayed from the code above

INSERT INTO Comments ( ProjectID, Owner, Comment, Date) VALUES('projectID','newowner','newcomments',#09/04/2019#)

BC_Seattle · Sep 4, 2019

Gasman said:
OK, I took project ID to be numeric. You had missed the single quote to start that string.

Code:

strSQL = " INSERT INTO Comments ( ProjectID, Owner, Comment, Date) " & " VALUES('" & Me.ProjectID & "','" & Me.Newowner & "','" & Me.NewComments & "',#" & Format( Me.newdate,"mm\/dd\/yyyy") & "#)" CurrentDb.Execute strSQL, dbFailOnError

HTH

It is but I am reading from a text field. I tried this and still same error. Project ID is also the same as Tracking Number. Could it not be reading it? Like maybe its a query on the form and pulls it from elsewhere? Maybe ill try using the Tracking # see if it makes any difference.

BC_Seattle · Sep 4, 2019

BC_Seattle said:
It is but I am reading from a text field. I tried this and still same error. Project ID is also the same as Tracking Number. Could it not be reading it? Like maybe its a query on the form and pulls it from elsewhere? Maybe ill try using the Tracking # see if it makes any difference.

Nevermind I noticed the Name has a # in it.

BC_Seattle · Sep 4, 2019

Still Puzzled on this one I see there is like several ways to do it and I think I am missing something or just not understanding Ill come back to it later today. Brain is melting.

Another way I seen it written out is like this? Any thoughts?

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim arrFieldList(0 To 3) As Variant
Dim arrFieldValues(0 To 3) As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("MyTable1", , , adLockBatchOptimistic)
'set field array
arrFieldList(0) = "MyField1"
arrFieldList(1) = "MyField2"
arrFieldList(2) = "MyField3"
arrFieldList(3) = "MyField4"
'set value array
arrFieldValues(0) = 10000
arrFieldValues(1) = 1100000
arrFieldValues(2) = "ranomd text"
arrFieldValues(3) = "more random text"
'add new record
Call objRecordset.AddNew(arrFieldList, arrFieldValues)
'update the recordset object
objRecordset.UpdateBatch
End Sub

Gasman · Sep 4, 2019

Seems a lot of work for just not getting the syntax correct to me?

arnelgp · Sep 5, 2019

last resort is to use Querydef:

Code:

Dim strSQL As String
strSQL = _
 "INSERT INTO Comments (ProjectID, Owner, Comment, Date) SELECT p0, p1, p2, p3"
With CurrentDb.CreateQueryDef("", strSQL)
    .Parameters(0)=Me.ProjectID
    .Parameters(1)=Me.Newowner
    .Parameters(2)=Me.NewComments
    .Parameters(3)=Me.newdate
    .Execute dbFailOnError
End With

Gasman · Sep 5, 2019

I'd hardly call that a last resort.

That is quite a tidy solution, so you do not have to worry about all the different types of fields and values.

I seem to recall that someone uploaded a function to surround each parameter with the correct characters as well.

BC_Seattle · Sep 5, 2019

Yeah doesn't look like that is gonna work either. Not sure if it has anything to do with the BE data or whats causing the issue. This thing is growing beyond my capabilities

. If anyone wants to take a look at it I can see if I can send it to you.

Comments to Append to Table? (1 Viewer)

Registered User.

Attachments

..forever waiting... waiting for jellybean!

I’m here to help

Enthusiastic Amateur

Registered User.

Attachments

Registered User.

Registered User.

Attachments

I’m here to help

Registered User.

Registered User.

Enthusiastic Amateur

Registered User.

Registered User.

Registered User.

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Enthusiastic Amateur

Registered User.

Similar threads

Users who are viewing this thread