Need Help in Syntax of Insert Sql

[Emily]

Hi,

I am very new to coding in Access. I am trying insert a record to the 'Timelog change history' table when user make changes to 'Timelog' Table.
The 'Timelog Change history' table contain all the same fields as 'Timelog' plus 2 more fields [date Changed] and [TimeStamp].

I am using a Form, in the After-Update event I have the following insert statment and everytime I run it, I keep getting 'Too few parameters. Expected 1'
I 've been stuck for days and trying with a lot of trial and errors with the syntax, and nothing seems to work. Any help is appreciated!!

Thanks in Advance


Private Sub Form_AfterUpdate()
Dim db As Database
Dim logdate As String
Dim Logtime As String
Dim DateTime As String

Set db = CurrentDb
logdate = Format(Date, "dd mmm yyyy")
Logtime = Format(Now(), "Long Time")
DateTime = logdate & " " & Logtime

db.Execute "INSERT INTO [Timelog Changes History] ([TimeStamp])" _
& " SELECT *,[DateTime]As [TimeStamp]" _
& " FROM [Timelog] WHERE [Timelog].[id] =" & Me![id] & ";"

Set db = Nothing


End Sub

 

[Uncle Gizmo]

db.Execute "INSERT INTO [Timelog Changes History] ([TimeStamp])" _
& " SELECT *,[DateTime]As [TimeStamp]" _
& " FROM [Timelog] WHERE [Timelog].[id] =" & Me![id] & ";"

I don't think the "insert into" statement will work in the format you've shown.

I think you need a format more like this:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Taken from the second box on this page:
http://www.w3schools.com/sql/sql_insert.asp

 

[Emily]

Thanks for your quick response. I needed to write the same record in Timelog onto the History table. If I don't use the select statement format, how do I assign the values of the timelog table to History in the format you suggested?

would it be something like

INSERT INTO table_name (column1, column2, column3,...)
VALUES ([timelog].[id], [timelog].[field2], [timelog].[field3[...) ?

 

[bparkinson]

Hi,

I am very new to coding in Access. I am trying insert a record to the 'Timelog change history' table when user make changes to 'Timelog' Table.
The 'Timelog Change history' table contain all the same fields as 'Timelog' plus 2 more fields [date Changed] and [TimeStamp].

I am using a Form, in the After-Update event I have the following insert statment and everytime I run it, I keep getting 'Too few parameters. Expected 1'
I 've been stuck for days and trying with a lot of trial and errors with the syntax, and nothing seems to work. Any help is appreciated!!

Thanks in Advance


Private Sub Form_AfterUpdate()
Dim db As Database
Dim logdate As String
Dim Logtime As String
Dim DateTime As String

Set db = CurrentDb
logdate = Format(Date, "dd mmm yyyy")
Logtime = Format(Now(), "Long Time")
DateTime = logdate & " " & Logtime

db.Execute "INSERT INTO [Timelog Changes History] ([TimeStamp])" _
& " SELECT *,[DateTime]As [TimeStamp]" _
& " FROM [Timelog] WHERE [Timelog].[id] =" & Me![id] & ";"

Set db = Nothing


End Sub

INSERT statements need to have columns on both sides balance. So, it's fine to use a SELECT as the right side, but you have to remove the *, and it should work.

As an aside, I use this SELECT technique to insert multiple rows with a single INSERT statement, by selecting against a control table.

 

[Uncle Gizmo]

Thank you for that bparkinson, I have learnt something again today. I usually use one SQL statement to extract the values I want, and an insert statement to insert them individually with a loop!

I did a bit of Googling and came up with this link: http://www.techonthenet.com/sql/insert.php

might be helpful.

Cheers Tony

 

[bparkinson]

Thank you for that bparkinson, I have learnt something again today. I usually use one SQL statement to extract the values I want, and an insert statement to insert them individually with a loop!

I did a bit of Googling and came up with this link: http://www.techonthenet.com/sql/insert.php

might be helpful.

Cheers Tony

Good link.

I was very lucky to work with pretty brilliant SQL Server developers early on, and they taught me a lot. Set-based thinking is sometimes easy, like in this simple INSERT case, and can be much much harder. I'm grateful to the folks I learned from.

 

[Emily]

Thank you both, I expanded the insert statement and played around with the syntax on the literals and comma and finally got it working. Thank you so much!!! I always have problem not knowing when to use a single quote ' or double " and when to use ampersand &. Do you know of any good site to learn VB and SQL?

The following is the working version of my insert statement. It was ampersand and the quotes that got me stuck for so long.

Thanks!

db.Execute "INSERT INTO [Timelog Changes History]([id],[EmployeeId],[Date],[Project Id],[Category Code]," _
& " [FromTime],[ToTime],[Desc1],[Desc2],[Work Description],[Date Changed],[TimeStamp])" _
& " SELECT [id],[EmployeeId],[Date],[Project Id]," _
& " [Category Code],[FromTime],[ToTime],[Desc1]," _
& " [Desc2],[Work Description]," _
& " """ & [logdate] & """, """ & [DateTime] & """" _
& " FROM [Timelog] WHERE [Timelog].[id] =" & Me![id] & ";"

 

[pbaldy]

Do you know of any good site to learn VB and SQL?

Jason has a decent tutorial here:

http://www.baldyweb.com/BuildSQL.htm

 

[bparkinson]

Thank you both, I expanded the insert statement and played around with the syntax on the literals and comma and finally got it working. Thank you so much!!! I always have problem not knowing when to use a single quote ' or double " and when to use ampersand &. Do you know of any good site to learn VB and SQL?

The following is the working version of my insert statement. It was ampersand and the quotes that got me stuck for so long.

Thanks!

db.Execute "INSERT INTO [Timelog Changes History]([id],[EmployeeId],[Date],[Project Id],[Category Code]," _
& " [FromTime],[ToTime],[Desc1],[Desc2],[Work Description],[Date Changed],[TimeStamp])" _
& " SELECT [id],[EmployeeId],[Date],[Project Id]," _
& " [Category Code],[FromTime],[ToTime],[Desc1]," _
& " [Desc2],[Work Description]," _
& " """ & [logdate] & """, """ & [DateTime] & """" _
& " FROM [Timelog] WHERE [Timelog].[id] =" & Me![id] & ";"

freevbcode.com for lots of good, usable code.
vbforums.com. Just like here, only VB.

There's lot's of advice one could give about writing good VB6/VBA code. The most important, in my opinion, is use classes to modularize your code and de-couple code from forms.

 

[Emily]

Thank you guys for referring those useful sites!!! I am so excited and looking forward to really learn some VB. This forum has really help me so much and I just want to thank all the people who posted in this forum. It is amazing how much one can learn by just reading these post !!! thanks again everyone!!!