Append Current Record on Click event 1

[hefly]

I would like to append the current record on a form to a different table on click event.

Writing my sql statement, I am advised that INSERT INTO isn't valid.

What is wrong with this code?

Thanks.

Private Sub Append_Click()
Dim strSQL As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

If Me.Dirty Then Me.Dirty = False 'force record to be saved

 
   strSQL = 'INSERT INTO tblRunsheet ( InstrID, Link, Location, Description, Instrument," _&
   "Dated, Filed, Grantor, Grantee, Comments, Comments, Exclude, Tract, Copy, Include )" _&

"SELECT" &  me.InstrID, me.[$Link], me.[$DOC#], me.[$DOCRM], me.[$DOCTYP], me.[$FILEDT], me.[$FILEDT], me.[$P1], me.[$P2], me.Comments, me.Notes, me.Exclude, me.Tract, me.Copy, me.Include

"FROM" tbl_db1;

Set rs = Nothing

CurrentDb.Execute (strSQL), dbFailOnError

End Sub

 

[TheAceMan1]

How are ya hefly . . .

Your not concatenating properly:
[ol][li][blue]Line continuation characters[/blue] are wrong:

[blue][red]" _&[/red] should be [red]" & _[/red][/blue]

[/li]
[li]Missing Space:

[blue]Copy, Include )[COLOR=blue red] [/color]"[/blue]

[/li]
[li]Another [blue]missing space[/blue]:

[blue]"SELECT[COLOR=blue red] [/color]"[/blue]

[/li]
[li]The [blue]comma's[/blue] in your select statement have to be concatenated as well:

[blue]"SELECT " & me.InstrID & [red]", "[/red] & me.[$Link] [red]& ", " &[/red] me.[$DOC#] [red]& ", " &[/red] me.[$DOCRM] [red]& ", " &[/red] .....[/blue]

[/li]
[li]A double quote and line continuation characters are missing:

[blue]me.Copy, me.Include [red]" & _[/red][/blue]

[/li]
[li]The [blue]FROM[/blue] is concatenated wrong:

[blue][red]"FROM" tbl_db1;[/red] should be [red]"FROM tbl_db1;"[/red][/blue]

[/li]
[li]You don't need the recordset since your reading directly from the form with [blue]Me[/blue].[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

BTW . . . [blue]Stay Focused![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hefly]

Thanks for pointing out all those errors. Wow!

That fixed the SQL. But, I am doing something else wrong, because I can't get the event to work.

 

[dhookom]

hefly,
Please provide your current code and tell us what you mean by "something else wrong". What happens? Have you tried debugging methods?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Thefly . . .

I see the problem! ... In your Select statement, [blue]fieldnames[/blue] are [blue]represented by control values[/blue] on the form. That is to say ... no value in the [blue]InstrID[/blue] field will have the value [red]InstrID![/red]. Not only that ... your getting the data from [blue]tbl_db1[/blue] instead of the current record on the form! I hope you see this ... [blue]It can't work in its present state.[/blue]

This can be fixed using the [blue]VALUES(....)[/blue] statement, but first we need to know the datatype (numeric, text, date ...) of the fields you've presented.

I also found two other errors:
[ol][li]You start the SQL with a single quote:

[blue]   strSQL = [red][b]'[/b][/red]INSERT INTO[/blue]

[/li]
[li]Two fields with the same name:

[blue]   Grantee, [red][b]Comments[/b][/red], [red][b]Comment[/b][/red], Exclude
should be
   Grantee, Comments, [red][b]Notes[/b][/red], Exclude[/blue]

[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hefly]

Thank you for not only correcting the code, but taking time to go through the debugging process. That was invaluable to seeing my mistakes.

 

[TheAceMan1]

... and the data types asked for?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hefly]

Thanks for your help.

I fixed the field names, and I created a new table with the data types I wanted, then ran an update. I was able to update a memo field from a field with text data type.

Thanks!

Hefly