INSERT a Table (32 items) into another Table - Re subform

  • Thread starter Thread starter Billp
  • Start date Start date
B

Billp

Hi,

I have a form with a subform. The subform is linked parent to child. The
subform has a field that has a combo box.
From the combo there are two choices.
On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32
LINES" the idea is to take the contents of a separate table line by line and
insert into the subform / subtable.
I Have the following code
If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then
'do the input from the table tblProjectNotes_Default

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotesDefault_1", dbOpenDynaset)

If rst.RecordCount <> 0 Then 'records exist
Do While Not rst.EOF

strOtherFields = ",Notes_Default"

strOtherFields = ",Notes_Default"
strsql = "INSERT INTO [tblsubProject_Notes]" _
& "(Works_Number" & strOtherFields & ")" _
& "SELECT '" & Forms!frmProject_Notes_1!Works_Number & "' As
NEWWorks_Number" _
& strOtherFields & " FROM tblProjectNotesDefault_1" _
& "WHERE Works_Number='" & Forms!frmProject_Notes_1!Works_Number
& "';"


DBEngine(0)(0).Execute strsql, dbFailOnError

rst.MoveNext
Loop

Else
'Do nothing
End If

rst.Close
Set rst = Nothing
Set db = Nothing

Else

Me!Project_Notes = Me!cboProject_Notes.Column(0)

End If

I get a Syntax error in FROM clause.

Other than that any suggestions, and where I have gone wrong or a better way
to do things is really really appreciated.
 
Billp said:
I have a form with a subform. The subform is linked parent to child. The
subform has a field that has a combo box.
From the combo there are two choices.
On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32
LINES" the idea is to take the contents of a separate table line by line and
insert into the subform / subtable.
I Have the following code
If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then
'do the input from the table tblProjectNotes_Default

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotesDefault_1", dbOpenDynaset)

If rst.RecordCount <> 0 Then 'records exist
Do While Not rst.EOF

strOtherFields = ",Notes_Default"

strOtherFields = ",Notes_Default"
strsql = "INSERT INTO [tblsubProject_Notes]" _
& "(Works_Number" & strOtherFields & ")" _
& "SELECT '" & Forms!frmProject_Notes_1!Works_Number & "' As
NEWWorks_Number" _
& strOtherFields & " FROM tblProjectNotesDefault_1" _
& "WHERE Works_Number='" & Forms!frmProject_Notes_1!Works_Number
Click to expand...


You need a space after the 1 or before the WHERE

I don't understand what you are trying to do enough to
comment.

You can delete one of the strOtherFields = ",Notes_Default"
 
Hi Marshall,

I have 32 items/lines in a table - tblProjectNotesDefault_1, in a field
called Notes_Default.
When the selection is "INSERT ALL...." the 32 lines of the table are
inserted into the subform with the Works_Number added also. The main form is
ProjectNotes the subform fsubProjectNotes. The two are linked by Works_Number
where for each new line the Works_Number is the linking feild - Parent /
Child.
Thus the subform gains 32 lines prefilled with text of some description.

Thank You
Bill

Marshall Barton said:
Billp said:
I have a form with a subform. The subform is linked parent to child. The
subform has a field that has a combo box.
From the combo there are two choices.
On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32
LINES" the idea is to take the contents of a separate table line by line and
insert into the subform / subtable.
I Have the following code
If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then
'do the input from the table tblProjectNotes_Default

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotesDefault_1", dbOpenDynaset)

If rst.RecordCount <> 0 Then 'records exist
Do While Not rst.EOF

strOtherFields = ",Notes_Default"

strOtherFields = ",Notes_Default"
strsql = "INSERT INTO [tblsubProject_Notes]" _
& "(Works_Number" & strOtherFields & ")" _
& "SELECT '" & Forms!frmProject_Notes_1!Works_Number & "' As
NEWWorks_Number" _
& strOtherFields & " FROM tblProjectNotesDefault_1" _
& "WHERE Works_Number='" & Forms!frmProject_Notes_1!Works_Number
Click to expand...


You need a space after the 1 or before the WHERE

I don't understand what you are trying to do enough to
comment.

You can delete one of the strOtherFields = ",Notes_Default"
Click to expand...
 
Hi,

I hope this may explain what I am trying to do,
Say I have a table with 32 distinct items for example
Red
Green
Blue
Orange ....
These are only in one feild as the table only has one feild called
Notes_Default

On my subform there is a combo in a feild called "Project_Notes" on a Line
so that once, if insert all is selected
Red goes on one line in the feild 'Project_Notes"
Green goes on another line in the feild "Project_Notes" etc

And as they do the Works_Number is inserted also in the Works_Number Feild
The main form has a feild called Works_Number the subform links to this form
by Works_Number and has a feild which is completed with each new line that is
added in the subform

So
We would have in the subtable
Works Number,Red
Works_Number,Green
Works_Number, Blue etc.

This is waht I am trying to do but not going far with it.

Thanks In advance
Bill


Marshall Barton said:
Billp said:
I have a form with a subform. The subform is linked parent to child. The
subform has a field that has a combo box.
From the combo there are two choices.
On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32
LINES" the idea is to take the contents of a separate table line by line and
insert into the subform / subtable.
I Have the following code
If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then
'do the input from the table tblProjectNotes_Default

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotesDefault_1", dbOpenDynaset)

If rst.RecordCount <> 0 Then 'records exist
Do While Not rst.EOF

strOtherFields = ",Notes_Default"

strOtherFields = ",Notes_Default"
strsql = "INSERT INTO [tblsubProject_Notes]" _
& "(Works_Number" & strOtherFields & ")" _
& "SELECT '" & Forms!frmProject_Notes_1!Works_Number & "' As
NEWWorks_Number" _
& strOtherFields & " FROM tblProjectNotesDefault_1" _
& "WHERE Works_Number='" & Forms!frmProject_Notes_1!Works_Number
Click to expand...


You need a space after the 1 or before the WHERE

I don't understand what you are trying to do enough to
comment.

You can delete one of the strOtherFields = ",Notes_Default"
Click to expand...
 
If you want to insert all the records in
tblProjectNotesDefault_1 into the subform's source table,
then I have to ask why you are opening a record set and
inserting one record at a time.

It seems to me that after the records have been copied you
need to Requery the subform to make the inserted records
show up.

Did you get past the original error message?
--
Marsh
MVP [MS Access]

I have 32 items/lines in a table - tblProjectNotesDefault_1, in a field
called Notes_Default.
When the selection is "INSERT ALL...." the 32 lines of the table are
inserted into the subform with the Works_Number added also. The main form is
ProjectNotes the subform fsubProjectNotes. The two are linked by Works_Number
where for each new line the Works_Number is the linking feild - Parent /
Child.
Thus the subform gains 32 lines prefilled with text of some description.


Marshall Barton said:
Billp said:
I have a form with a subform. The subform is linked parent to child. The
subform has a field that has a combo box.
From the combo there are two choices.
On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32
LINES" the idea is to take the contents of a separate table line by line and
insert into the subform / subtable.
I Have the following code
If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then
'do the input from the table tblProjectNotes_Default

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotesDefault_1", dbOpenDynaset)

If rst.RecordCount <> 0 Then 'records exist
Do While Not rst.EOF

strOtherFields = ",Notes_Default"

strOtherFields = ",Notes_Default"
strsql = "INSERT INTO [tblsubProject_Notes]" _
& "(Works_Number" & strOtherFields & ")" _
& "SELECT '" & Forms!frmProject_Notes_1!Works_Number & "' As
NEWWorks_Number" _
& strOtherFields & " FROM tblProjectNotesDefault_1" _
& "WHERE Works_Number='" & Forms!frmProject_Notes_1!Works_Number
Click to expand...


You need a space after the 1 or before the WHERE

I don't understand what you are trying to do enough to
comment.

You can delete one of the strOtherFields = ",Notes_Default"
Click to expand...
Click to expand...
 
So you need a query like

Dim strWorksNumber as String
Dim strSQL as String
Dim Db as DAO.Database

Set Db = CurrentDb()
'Get the works number from the main form
strWorksNumber = Forms!frmProject_MainForm!Works_Number

StrSQL = "INSERT INTO tblsubProject_Notes(WorksNumber, Project_Notes)" & _
" SELECT """ & strWorksNumber & """, Project_Notes" & _
" FROM tblProjectNotesDefault_1"

Db.Execute StrSQL, dbFailOnError

'Force a requery of the subform


You might expand the SQL string to keep from adding duplicates by modifying
the select statement to check eliminate records that already exists

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I hope this may explain what I am trying to do,
Say I have a table with 32 distinct items for example
Red
Green
Blue
Orange ....
These are only in one feild as the table only has one feild called
Notes_Default

On my subform there is a combo in a feild called "Project_Notes" on a Line
so that once, if insert all is selected
Red goes on one line in the feild 'Project_Notes"
Green goes on another line in the feild "Project_Notes" etc

And as they do the Works_Number is inserted also in the Works_Number Feild
The main form has a feild called Works_Number the subform links to this form
by Works_Number and has a feild which is completed with each new line that is
added in the subform

So
We would have in the subtable
Works Number,Red
Works_Number,Green
Works_Number, Blue etc.

This is waht I am trying to do but not going far with it.

Thanks In advance
Bill


Marshall Barton said:
Billp said:
I have a form with a subform. The subform is linked parent to child. The
subform has a field that has a combo box.
From the combo there are two choices.
On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32
LINES" the idea is to take the contents of a separate table line by line and
insert into the subform / subtable.
I Have the following code
If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then
'do the input from the table tblProjectNotes_Default

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProjectNotesDefault_1", dbOpenDynaset)

If rst.RecordCount <> 0 Then 'records exist
Do While Not rst.EOF

strOtherFields = ",Notes_Default"

strOtherFields = ",Notes_Default"
strsql = "INSERT INTO [tblsubProject_Notes]" _
& "(Works_Number" & strOtherFields & ")" _
& "SELECT '" & Forms!frmProject_Notes_1!Works_Number & "' As
NEWWorks_Number" _
& strOtherFields & " FROM tblProjectNotesDefault_1" _
& "WHERE Works_Number='" & Forms!frmProject_Notes_1!Works_Number
Click to expand...

You need a space after the 1 or before the WHERE

I don't understand what you are trying to do enough to
comment.

You can delete one of the strOtherFields = ",Notes_Default"
Click to expand...
Click to expand...
 
Back
Top