access 97: insert into and multiple records of the same loan problems

[JMCN]

i am running into 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(ET LN Shortname). 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(ET LN
Shortname). 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

[JMCN]

hello -
here is an updated version. for some reason the [ET Amount] is a
string data type instead of currency when looking in the debug window.
i checked out both tables "Etracking" and "tblImportET" to make sure
the the field [ET Amount] is data type currency and it is . could it
be something with my insert into value statement that makes the [ET
Amount] a string?

Set db = DBEngine(0)(0)
Set rstET = db.OpenRecordset("tblImportET")
Set rst2 = db.OpenRecordset("Etracking")

rstET.MoveFirst
Do Until rstET.EOF

y = rstET![ET LN Shortname].Value
z = rstET![ET Amount].Value
x = "INSERT INTO ETracking([ET Number], [ET LN Shortname], [ET
Amount]) " _
& "VALUES (200000, '" & rstET![ET LN Shortname].Value & "'
, " & rstET![ET Amount].Value & ");"

'DoCmd.SetWarnings False
DoCmd.RunSQL (x)
'DoCmd.SetWarnings True
rstET.MoveNext
Loop
rstET.Close

'End With

Set rstET = Nothing
Set tdf = Nothing
Set db = Nothing

[Jung Normand]

hello -
here is an updated version. for some reason the [ET Amount] is a string
data type instead of currency when looking in the debug window. i
checked out both tables "Etracking" and "tblImportET" to make sure the
the field [ET Amount] is data type currency and it is . could it be
something with my insert into statement that makes the [ET Amount] a
string?

Set db = DBEngine(0)(0)
Set rstET = db.OpenRecordset("tblImportET")
Set rst2 = db.OpenRecordset("Etracking")

rstET.MoveFirst
Do Until rstET.EOF

y = rstET![ET LN Shortname].Value
z = rstET![ET Amount].Value
x = "INSERT INTO ETracking([ET Number], [ET LN Shortname], [ET
Amount]) " _
& "VALUES (200000, '" & rstET![ET LN Shortname].Value & "' ,
" & rstET![ET Amount].Value & ");"

'DoCmd.SetWarnings False
DoCmd.RunSQL (x)
'DoCmd.SetWarnings True
rstET.MoveNext
Loop
rstET.Close

'End With

Set rstET = Nothing
Set tdf = Nothing
Set db = Nothing
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!