Copy one table to another

[whojstall11]

Can somebody please help me understand this code

[LIST=1]
[*] Private Sub CheckBoxName_AfterUpdate()
[*] Dim strSQL As String
[*]
[*]   strSQL = "INSERT INTO TableB (FieldB1, FieldB2, FieldB3) " & _
[*]                 "SELECT " & Me.Field1 & " As Expr1, " & Me.Field2 & _
[*]                 " As Expr2, " & Me.Field3 & " As Expr3;"
[*]
[*]   CurrentDb.Execute strSQL, dbFailOnError
[*]
[*] End Sub
[/LIST]

I understand everything up into the "As Expr1" ? What does this mean?

 

[pbaldy]

It's simply an alias name for the field. Given what this is doing, it may not be necessary. I'd use a VALUES clause instead of SELECT, since you're only appending a single record.

 

[whojstall11]

This is my code does is the syntax correct? IF it is, its not compiling.

DoCmd.RunSQL "INSERT INTO qryDiscontinue (Medications, Date)" VALUES & _
(Forms![qryCurrentmeds]![Medications],Forms![qryCurrentmeds]![Date])"

 

[pbaldy]

No, you stopped the string and never restarted it. Why don't you keep it all on one line for now? You're also not concatenating the form values into the string, though you may not have to with RunSQL.

 

[whojstall11]

This code works, it adds the fields i need to my tables but on my form they are not updating. I tried a docmd.requery but access says it cant find my subform!!!!

Private Sub Discontinue_AfterUpdate()


 
 DoCmd.RunSQL "INSERT INTO [tblNotesCurrentMeds] (Medications, [Date], MRN) VALUES ([Current Meds].[Medications],[Current Meds].[Date], [Current Meds].MRN);"
DoCmd.Requery "qryDiscontinue2"
End Sub

 

[pbaldy]

I'd requery the form, not a query.

 

[whojstall11]

So this is my code it works but instead of appending to the table it adds a new form that is exactly the same as the previous form . do you have any ideas of why this happens.

DoCmd.RunSQL "INSERT INTO [tblNotesCurrentMeds](Medications, [Date], MRN) VALUES (  [Current Meds].[Medications] , [Current Meds].[Date] , [Current Meds].[MRN]);"

 

[pbaldy]

That's the same SQL you said worked correctly. It adds a form? That doesn't seem possible.

 

[whojstall11]

Yea so i have 330 records when i run the sqlstatement and refresh i have 331 records.

 

[pbaldy]

So it's added a record, as it should have. What am I missing?

 

[whojstall11]

It adds a new form along with the new record. So instead of form #330 and #331 being identical, I just need the statement to update form #330 and not make a new form (#331).

 

[pbaldy]

Perhaps you want an update query instead of an append query. The first modifies an existing record, the second creates a new record.