SQL code to insert data from one table into another

  • Thread starter Thread starter Mark Kubicki
  • Start date Start date
M

Mark Kubicki

on a form with its data source: Notes, I have a command buttom with the
following code:

Private Sub cmdAddStandardProjectNotes_Click()
Call DoSQLAddProjectNotes(Me) 'see code below
Me.Requery
Me.Refresh
End Sub

----
- i have 2 problems:
this SQL code doesn't add anything

Public Sub DoSQLAddProjectNotes(frm As Access.Form)
Dim sSQL As String
sSQL = "INSERT INTO Notes(vbTrue, null, Options) " & _
"from optNotes " & _
"WHERE OptionRequired = vbTrue ;"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Sub

- table Notes has 3 feilds with data types: yes/no, number, memo
- table optNotes has 3 fields with data types: memo, number, yes/no (which I
now note is a different order than the first table; however, the only value
being inserted from the 2nd into the 1st is the field with the "memo"
datatype which is named "Options" in both tables)
- OptionRequired is a yes/no field in the optNotes table
 
The syntax is

sSQL = "INSERT INTO Notes(Field1, Field2, Field3) " & _
"SELECT Field3, Field2, Field1 from optNotes " & _
"WHERE OptionRequired = True ;"

Replace Field1, Field2 and Field3 with the actual field names in the two
tables.
 
To avoid confusion, what Doug meant to say was:

sSQL = "INSERT INTO Notes(Field1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 from optNotes " & _
"WHERE OptionRequired = True ;"

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Douglas J. Steele said:
The syntax is

sSQL = "INSERT INTO Notes(Field1, Field2, Field3) " & _
"SELECT Field3, Field2, Field1 from optNotes " & _
"WHERE OptionRequired = True ;"

Replace Field1, Field2 and Field3 with the actual field names in the two
tables.
Click to expand...
 
I'm not sure what confusion you're trying to avoid, Dale! <g>

Mark said "table Notes has 3 feilds with data types: yes/no, number, memo"
and "table optNotes has 3 fields with data types: memo, number, yes/no".
That's the reason why I showed the field numbers in a different order in the
two places. I suppose it would have been better to use

sSQL = "INSERT INTO Notes(YesNoField, NumericField, MemoField) " & _
"SELECT YesNoField, NumericField, MemoField from optNotes " &
_
"WHERE OptionRequired = True ;"

However, rereading, I see he went on to say "the only value being inserted
from the 2nd into the 1st is the field with the "memo" datatype which is
named "Options" in both tables", so I suppose

sSQL = "INSERT INTO Notes(YesNoField, NumericField, Options) " & _
"SELECT True, Null, Options from optNotes " & _
"WHERE OptionRequired = True ;"

is less confusing!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dale Fye said:
To avoid confusion, what Doug meant to say was:

sSQL = "INSERT INTO Notes(Field1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 from optNotes " & _
"WHERE OptionRequired = True ;"

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Douglas J. Steele said:
The syntax is

sSQL = "INSERT INTO Notes(Field1, Field2, Field3) " & _
"SELECT Field3, Field2, Field1 from optNotes " & _
"WHERE OptionRequired = True ;"

Replace Field1, Field2 and Field3 with the actual field names in the two
tables.
Click to expand...
Click to expand...
 
Dear gentleman,

I have a similar problem to this:
1. I have 02 tables sourcing 02 forms.
2. Table1 (tags) stores all the current data of tags that have been
approved. I use Form1 (tags) to view all the data in this table, in view
only mode.
3. Table2 (tags_redline) stores the modified data for tags that will be
approved later. I use Form2 (tags_redline) to view all the tags added to
this table. Also this table has few more columns for providing change
control info, name, date etc, mandatorily.
4. When someone wants to make a change to a tag in table1, he wants to copy
it to Table2 (tags_redline), and make the required changes (they don't want
retype all data). Then provide mandatory data before being able to save it.

What really requires is when you click a button at the table1, it should
copy the currently displayed tag data to table2, and open its form
tags_redline), with the just copied data. Then the mandatory data can be
added throguh this form, and save it.

I will be highly thankful to you guys, if you crack this for me. I am not a
software professional, but asked to do this part of my process automation
project.

Regards,
Vijay

Douglas J. Steele said:
I'm not sure what confusion you're trying to avoid, Dale! <g>

Mark said "table Notes has 3 feilds with data types: yes/no, number, memo"
and "table optNotes has 3 fields with data types: memo, number, yes/no".
That's the reason why I showed the field numbers in a different order in the
two places. I suppose it would have been better to use

sSQL = "INSERT INTO Notes(YesNoField, NumericField, MemoField) " & _
"SELECT YesNoField, NumericField, MemoField from optNotes " &
_
"WHERE OptionRequired = True ;"

However, rereading, I see he went on to say "the only value being inserted
from the 2nd into the 1st is the field with the "memo" datatype which is
named "Options" in both tables", so I suppose

sSQL = "INSERT INTO Notes(YesNoField, NumericField, Options) " & _
"SELECT True, Null, Options from optNotes " & _
"WHERE OptionRequired = True ;"

is less confusing!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dale Fye said:
To avoid confusion, what Doug meant to say was:

sSQL = "INSERT INTO Notes(Field1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 from optNotes " & _
"WHERE OptionRequired = True ;"

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
Click to expand...
Click to expand...
 
Back
Top