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

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

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


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

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

This discussion thread is closed

Replies have been disabled for this discussion.