access 97: INSERT and Append queries

[JMCN]

is it possible to insert a value into a field when you append the new
records to the table instead of appending
the one new record at a time in order to insert a new value?

for example, i would like to insert the next "ET Number" when I append
new records into the tblImportET.

= [Etracking]![ET Number] + 1

i tried this sql code but i received an input pop-up box indicating i
need to enter the "[Etracking]![ET Number]
parameter value". the Etracking is the table that I want to append the
new records to it.

INSERT INTO ETracking ( [ET Number], [ET LN Shortname], [ET Amount],
[ET EC Code] )
SELECT tblImportET.[ET Number], tblImportET.[ET LN Shortname],
tblImportET.[ET Amount], tblImportET.[ET EC Code]
FROM tblImportET
WHERE (((tblImportET.[ET Number])=[Etracking]![ET Number]+1));

if anyone has suggestions, it would be appreciated .
thanks in advance - jung.

[Larry Linson]

What, in your statement, would be telling it to add one to the _last ET
number? In fact, the expression you used doesn't appear to select the ET
number for any _particular_ record.

Take a look in Help at the DMax function, if this is a number that must be
displayed to people and could, thus, cause some consternation if it were not
exactly sequential. If it is not to be viewed by people, but only internally
for joins and foreign keys, then consider using an AutoNumber field.

Larry Linson
Microsoft Access MVP

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...

is it possible to insert a value into a field when you append the new
records to the table instead of appending
the one new record at a time in order to insert a new value?

for example, i would like to insert the next "ET Number" when I append
new records into the tblImportET.

= [Etracking]![ET Number] + 1

i tried this sql code but i received an input pop-up box indicating i
need to enter the "[Etracking]![ET Number]
parameter value". the Etracking is the table that I want to append the
new records to it.

INSERT INTO ETracking ( [ET Number], [ET LN Shortname], [ET Amount],
[ET EC Code] )
SELECT tblImportET.[ET Number], tblImportET.[ET LN Shortname],
tblImportET.[ET Amount], tblImportET.[ET EC Code]
FROM tblImportET
WHERE (((tblImportET.[ET Number])=[Etracking]![ET Number]+1));

if anyone has suggestions, it would be appreciated .
thanks in advance - jung.

[rkc]

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...

is it possible to insert a value into a field when you append the new
records to the table instead of appending
the one new record at a time in order to insert a new value?

for example, i would like to insert the next "ET Number" when I append
new records into the tblImportET.

= [Etracking]![ET Number] + 1

i tried this sql code but i received an input pop-up box indicating i
need to enter the "[Etracking]![ET Number]
parameter value". the Etracking is the table that I want to append the
new records to it.

INSERT INTO ETracking ( [ET Number], [ET LN Shortname], [ET Amount],
[ET EC Code] )
SELECT tblImportET.[ET Number], tblImportET.[ET LN Shortname],
tblImportET.[ET Amount], tblImportET.[ET EC Code]
FROM tblImportET
WHERE (((tblImportET.[ET Number])=[Etracking]![ET Number]+1));

What you are hoping for would look more like the following, except for
the fact that it doesn't work. The query selects the highest ET Number
from ETracking, adds one to it and uses that same value for each
record appended.

INSERT INTO ETracking (
[ET Number],
[ET LN Shortname],
[ET Amount],
[ET EC Code]
)
SELECT
(SELECT MAX([ET Number])+1 AS ETracking FROM ETracking),
tblImportET.[ET LN Shortname],
tblImportET.[ET Amount],
tblImportET.[ET EC Code]
FROM tblImportET

Perhaps someone who actually knows SQL has a real solution.

[JMCN]

"rkc" <rk*@yabba.dabba.do.rochester.rr.nope> wrote in message news:<iY*******************@twister.nyroc.rr.com>. ..

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...

is it possible to insert a value into a field when you append the new
records to the table instead of appending
the one new record at a time in order to insert a new value?

for example, i would like to insert the next "ET Number" when I append
new records into the tblImportET.

= [Etracking]![ET Number] + 1

i tried this sql code but i received an input pop-up box indicating i
need to enter the "[Etracking]![ET Number]
parameter value". the Etracking is the table that I want to append the
new records to it.

INSERT INTO ETracking ( [ET Number], [ET LN Shortname], [ET Amount],
[ET EC Code] )
SELECT tblImportET.[ET Number], tblImportET.[ET LN Shortname],
tblImportET.[ET Amount], tblImportET.[ET EC Code]
FROM tblImportET
WHERE (((tblImportET.[ET Number])=[Etracking]![ET Number]+1));

What you are hoping for would look more like the following, except for
the fact that it doesn't work. The query selects the highest ET Number
from ETracking, adds one to it and uses that same value for each
record appended.

INSERT INTO ETracking (
[ET Number],
[ET LN Shortname],
[ET Amount],
[ET EC Code]
)
SELECT
(SELECT MAX([ET Number])+1 AS ETracking FROM ETracking),
tblImportET.[ET LN Shortname],
tblImportET.[ET Amount],
tblImportET.[ET EC Code]
FROM tblImportET

Perhaps someone who actually knows SQL has a real solution.

Okay - thanks the suggestions!

I have tried to rewrite the DMax sql but I receive the compile error
message of "line number or lable or statemetn or end of statement".
It's probably very obvious but I'm have looked at it for so long that
I can't tell what's wrong with the syntax anymore. Any help would be
greatly appreciated again.
thanks in advance - jung.

If rs.RecordCount > 0 Then
Do While Not rst.EOF
strSQL = "INSERT INTO ETracking ([ET Number],[ET LN
Shortname],[ET Amount],[ET EC Code]);"
'error message received on the next line
"SELECT(DMax([ET Number])+1) AS [ET Number] FROM
ETracking, tblImportET.[ET LN Shortname], tblImportET.[ET Amount],
tblImportET.[ET EC Code] FROM tblImportET";
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
rst.MoveNext

Loop
end if

[Mike Withers]

JMCN wrote:

is it possible to insert a value into a field when you append the new
records to the table instead of appending
the one new record at a time in order to insert a new value?

for example, i would like to insert the next "ET Number" when I append
new records into the tblImportET.

= [Etracking]![ET Number] + 1

i tried this sql code but i received an input pop-up box indicating i
need to enter the "[Etracking]![ET Number]
parameter value". the Etracking is the table that I want to append the
new records to it.

INSERT INTO ETracking ( [ET Number], [ET LN Shortname], [ET Amount],
[ET EC Code] )
SELECT tblImportET.[ET Number], tblImportET.[ET LN Shortname],
tblImportET.[ET Amount], tblImportET.[ET EC Code]
FROM tblImportET
WHERE (((tblImportET.[ET Number])=[Etracking]![ET Number]+1));

if anyone has suggestions, it would be appreciated .
thanks in advance - jung.

Try changing the last line of your SQL to read :

" .... WHERE (((tblImportET.[ET Number])=" & [Etracking]![ETNumber] & "
+1 )); "

Mike W :)