insert into select

[JMCN]

i received a runtime error message 3346 when i ran the insert into
select code. i have check to see if the query values and destination
fields are the same and it appears so. atlas, i have no idea why my
insert into query does not work. if anyone has suggestions, it would
be appreciated. thanks in advance.

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

If rst.RecordCount > 0 Then
Do While Not rst.EOF
db.Execute " INSERT INTO ETracking " _
& " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
Code])" _
& " SELECT(Max([ET Number])+1) AS [ET Number] FROM
ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] " _
& "FROM [tblImportET];"
rst.MoveNext

Loop

End If

[Steve Jorgensen]

The syntax of your SELECT is not correct. It looks as if you intend for the
first select column to be a subquery result, but you don't have a SELECT
statement inside the parentheses before the MAX function call.

On 2 Jan 2004 12:21:59 -0800, pi******@yahoo.fr (JMCN) wrote:

i received a runtime error message 3346 when i ran the insert into
select code. i have check to see if the query values and destination
fields are the same and it appears so. atlas, i have no idea why my
insert into query does not work. if anyone has suggestions, it would
be appreciated. thanks in advance.

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

If rst.RecordCount > 0 Then
Do While Not rst.EOF
db.Execute " INSERT INTO ETracking " _
& " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
Code])" _
& " SELECT(Max([ET Number])+1) AS [ET Number] FROM
ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] " _
& "FROM [tblImportET];"
rst.MoveNext

Loop

End If

[JMCN]

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<4f********************************@4ax.com>. ..

The syntax of your SELECT is not correct. It looks as if you intend for the
first select column to be a subquery result, but you don't have a SELECT
statement inside the parentheses before the MAX function call.

i thought the select was my statement that added the new [et number].
thanks for the suggestion !!

jung On 2 Jan 2004 12:21:59 -0800, pi******@yahoo.fr (JMCN) wrote:

i received a runtime error message 3346 when i ran the insert into
select code. i have check to see if the query values and destination
fields are the same and it appears so. atlas, i have no idea why my
insert into query does not work. if anyone has suggestions, it would
be appreciated. thanks in advance.

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

If rst.RecordCount > 0 Then
Do While Not rst.EOF
db.Execute " INSERT INTO ETracking " _
& " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
Code])" _
& " SELECT(Max([ET Number])+1) AS [ET Number] FROM
ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] " _
& "FROM [tblImportET];"
rst.MoveNext

Loop

End If

[Steve Jorgensen]

The key thing is you had 2 from clauses and only one select.

On 3 Jan 2004 16:53:11 -0800, pi******@yahoo.fr (JMCN) wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<4f********************************@4ax.com>. ..

The syntax of your SELECT is not correct. It looks as if you intend for the
first select column to be a subquery result, but you don't have a SELECT
statement inside the parentheses before the MAX function call.

i thought the select was my statement that added the new [et number].
thanks for the suggestion !!

jung

On 2 Jan 2004 12:21:59 -0800, pi******@yahoo.fr (JMCN) wrote:

>i received a runtime error message 3346 when i ran the insert into
>select code. i have check to see if the query values and destination
>fields are the same and it appears so. atlas, i have no idea why my
>insert into query does not work. if anyone has suggestions, it would
>be appreciated. thanks in advance.
>
>Set db = DBEngine(0)(0)
>Set rst = db.OpenRecordset("ETracking")
>
>If rst.RecordCount > 0 Then
> Do While Not rst.EOF
> db.Execute " INSERT INTO ETracking " _
> & " ([ET Number], [ET LN Shortname], [ET Amount], [ET EC
>Code])" _
> & " SELECT(Max([ET Number])+1) AS [ET Number] FROM
>ETracking), tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
>tblImportET.[ET EC Code] " _
> & "FROM [tblImportET];"
> rst.MoveNext
>
> Loop
>
>End If

[JMCN]

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<vq********************************@4ax.com>. ..

The key thing is you had 2 from clauses and only one select.

thanks steve! i have finally added another select statement within my
insert into.

now what i am running to problems with is the recordset.
i have exactly 104 records. when i tested the code, the end result
was multiple records
for the same loan. where did i go wrong?
so i tried a couple of different do... loop statements and ended up
getting
the same results, that is having multiple records of the same loan.
so if anyone has any suggestions, it would be appreciated :)
thanks in advance and all who gave me advice. jung

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("ETracking")

''If rst.RecordCount > 0 Then
''Do While Not rst.EOF
Do Until rst.EOF
db.Execute " INSERT INTO ETracking([ET Number], [ET LN
Shortname], [ET Amount], [ET EC Code])" _
& " SELECT(SELECT(Max([ET Number])+1) FROM ETracking),
tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] FROM [tblImportET]; "
rst.MoveNext
Loop

Set rst = Nothing
Set tdf = Nothing
Set db = Nothing
'End If