By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

insert into select

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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


Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.