Copy to table

[mbhw99]

I'm trying to get my code to copy certain fields from one table into another after a button click. I got it to where I stopped getting error messages, but my tables aren't updating. I want data from tblForm2Input copied to a new record to tblBosTracking.

Private Sub saveandnew_Click()
DoCmd.Save
Me.Refresh
CurrentDb.Execute ("UPDATE tblForm2Input INNER JOIN tblBosTracking ON (tblForm2Input.[Observed By] = tblBosTracking.[Observed By]) AND (tblForm2Input.[Input Date] = tblBosTracking.[Input Date]) SET tblForm2Input.[Input Date] = tblBosTracking![Input Date], tblForm2Input.[Observed By] = tblBosTracking![Observed By]")
Me.Refresh
MsgBox "Record Saved Successfully!"
DoCmd.RunCommand acCmdRecordsGoToNew
Me!F2Date.SetFocus
End Sub

I'm sure as usual it's probably something simple that I missed. Thank you for your time.

 

[pbaldy]

Well, that's an update query. It sounds like you want an append query:

INSERT INTO TargetTable (FieldList)
SELECT FieldList
FROM SourceTable
WHERE Whatever

 

[mbhw99]

Thanks again pbaldy. Although I seem to be having trouble implementing that code into mine. I keep getting

Compile error:

Expected: list separator or )

 

[pbaldy]

Not being able to see your code, hard to say what's causing the error.

 

[mbhw99]

Private Sub saveandnew_Click()
DoCmd.Save
Me.Refresh
CurrentDb.Execute ("INSERT INTO tblBosTracking [Observed By], [Input Date]")
[COLOR="Red"]SELECT [Observed By], [Input Date][/COLOR]
FROM tblForm2Input
Me.Refresh
MsgBox "Record Saved Successfully!"
DoCmd.RunCommand acCmdRecordsGoToNew
Me!F2Date.SetFocus
End Sub

The red gave me a new error since I figured out the other:

Compile error:
Expected: Case

 

[pbaldy]

You ended the SQL string after the first line (I was just showing the syntax). For now, try keeping it all on one line.

 

[mbhw99]

Opps. Alright then.

I have a new error:

Run-time error '3134'

Syntax error in INSERT INTO statement.

CurrentDb.Execute ("INSERT INTO tblBosTracking [Observed By], [Input Date] SELECT [Observed By] , [Input Date] FROM tblForm2Input")

I do appreciate your time and patience with me.

 

[pwbrown]

Maybe it's because you missed brackets:
CurrentDb.Execute ("INSERT INTO tblBosTracking ([Observed By], [Input Date]) SELECT [Observed By] , [Input Date] FROM tblForm2Input")

 

[mbhw99]

doh...

I tried putting brackets between all of them, but not just between the INSERT INTO part.

Once again, thank you and the code works perfectly!

 

[mbhw99]

...but I knew I'd forget something. What do I add if I just want the current record I'm inputting added, and not the whole table?

 

[pbaldy]

Add a criteria. The WHERE clause I included with my sample syntax.

 

[mbhw99]

ok, but guess what? I'm missing something again.

Compile error:

Expected: list seperator or )

CurrentDb.Execute ("INSERT INTO tblBosTracking ([Observed By], [Input Date]) SELECT [Observed By] , [Input Date] FROM tblForm2Input WHERE (tblBosTracking.BOSID = " & Me.ID"))

 

[pwbrown]

Your problem lies here : tblBosTracking.BOSID = " & Me.ID"
Assuming you have a field called BOSID in the tblBostracking table your problem is:
& Me.ID

Is that meant to be getting a value from a form?

 

[pbaldy]

Try

CurrentDb.Execute ("INSERT INTO tblBosTracking ([Observed By], [Input Date]) SELECT [Observed By] , [Input Date] FROM tblForm2Input WHERE tblBosTracking.BOSID = " & Me.ID)

 

[pwbrown]

I may be wrong but I would of done something like Forms!tblForm2Input!ID instead of & Me.ID.

 

[mbhw99]

Is that meant to be getting a value from a form?

Yes.

Try

CurrentDb.Execute ("INSERT INTO tblBosTracking ([Observed By], [Input Date]) SELECT [Observed By] , [Input Date] FROM tblForm2Input WHERE tblBosTracking.BOSID = " & Me.ID)

I now get a run-time error '3061'
Too few parameters. Expecting 1.

I may be wrong but I would of done something like Forms!tblForm2Input!ID instead of & Me.ID.

Thank you. I gave this a shot, and I get the same error as a few posts up:

Compile error:

Expected: list separator or )

 

[pwbrown]

Try this:
CurrentDb.Execute ("INSERT INTO tblBosTracking ([Observed By], [Input Date]) SELECT [Observed By] , [Input Date] FROM tblForm2Input WHERE ((tblBosTracking.BOSID = " & Me.ID")))

 

[mbhw99]

Try this:
CurrentDb.Execute ("INSERT INTO tblBosTracking ([Observed By], [Input Date]) SELECT [Observed By] , [Input Date] FROM tblForm2Input WHERE ((tblBosTracking.BOSID = " & Me.ID")))

Same error. The above code in red is highlighted with it.

:banghead:

 

[pbaldy]

Me will work fine as long as the code resides in the form with that value.

 

[pbaldy]

Sorry, didn't realize there was a second page. Pw's well intentioned suggestion contains an error. I just noticed that the SQL I cut/pasted has the wrong table in the WHERE clause. It should be the same as the one in the SELECT clause.