Loop and Insert Problem

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I have to move X number of records from one table to another table so this
is a 2 part question. First I need to run the loop the number of times that
TxtCount equals. Second I need to insert from the CheckDetails table to the
Transfer table, however they have different fields. How can I do this?
Any help appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Me.TxtLeftID), 0)

Do Until Me.TxtCount
Me.TxtTransferID = Nz(DMax("[TransferID]", "tblTransfers"), 0) + 1
CurrentDb.Execute "INSERT Into tblTransfers(TransferID,TransferCheckID, " &
_
"TransferFromID,TransferItem,TransferTableFrom,TransferTableTo, " & _
"TransferToID,TransferQTY,TransferDate,TransferTime) " & _
"VALUES(" & Forms!frmTransferItem!TxtTransferID & ", " & _
"" & Forms!frmTransfer!TxtSalesID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & ", " & _
"" & Forms!frmTransferItem!TxtItemID & ", " & _
"" & Forms!frmTransfer!TxtOldTableNum & ", " & _
"" & Forms!frmTransferItem!TxtRightID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & "," & 1 & ", " & _
"#" & Date & "#, " & _
"#" & Time & "#)"
Loop
 
Hi DS,

firstly, you need to delimit non-numeric fields

text is delimited with ' or "
dates are delimited with #

for example
"" & Forms!frmTransferItem!TxtItemID & ", "
should probably be
"'" & Forms!frmTransferItem!TxtItemID & "', "

1 is not a variable, it is a literal value
so this:
& 1 & ", "
should be this:
& "1, "

also, why are you not storing TransferDate and TransferTime together?

TransferDateTime --> "#" & Now & "#"


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
I have to move X number of records from one table to another table so this
is a 2 part question. First I need to run the loop the number of times that
TxtCount equals. Second I need to insert from the CheckDetails table to the
Transfer table, however they have different fields. How can I do this?
Any help appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Me.TxtLeftID), 0)

Do Until Me.TxtCount
Me.TxtTransferID = Nz(DMax("[TransferID]", "tblTransfers"), 0) + 1
CurrentDb.Execute "INSERT Into tblTransfers(TransferID,TransferCheckID, " &
_
"TransferFromID,TransferItem,TransferTableFrom,TransferTableTo, " & _
"TransferToID,TransferQTY,TransferDate,TransferTime) " & _
"VALUES(" & Forms!frmTransferItem!TxtTransferID & ", " & _
"" & Forms!frmTransfer!TxtSalesID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & ", " & _
"" & Forms!frmTransferItem!TxtItemID & ", " & _
"" & Forms!frmTransfer!TxtOldTableNum & ", " & _
"" & Forms!frmTransferItem!TxtRightID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & "," & 1 & ", " & _
"#" & Date & "#, " & _
"#" & Time & "#)"
Loop
Click to expand...

Well, you don't need any code, nor any looping. You just need an Append query
with the aid of a handy auxiliary table NUM, with one field N having values
form 0 to some large number (65535 maybe):

INSERT INTO tblTransfers(TransferID + N + 1, TransferCheckID, TransferFromID,
TransferItem, TransferTableFrom, TransferTableTo, TransferToID, TransferToQty,
TransferTime)
SELECT TransferID, SalesID, OldServerID, txtItmeID, txtOldTableNum,
txtRightID, txtOldServerID, 1, Now()
FROM <the recordsource of frmTransfer>, Num
WHERE tblTransfers.TransferID = DMax("[TransferID]", "tblTransfers")
AND N < Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Forms!frmTransfer!TxtLeftID), 0)

Not sure about frmTransferItem vs. frmTransfer - do you have both forms open??

John W. Vinson [MVP]
 
delimiters
---

Hi DS,

actually, John has the best method pegged... but to answer your question:

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

strSQL = strSQL &numfield
strSQL = strSQL & "'" & txtfield & "'"
strSQL = strSQL & "#" & datetimefield & "#"


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Thanks for the advice
What does delimited do? It sounds like something that I should have been
doing and didn't. Any help appreciated
Thanks
DS
strive4peace said:
Hi DS,

firstly, you need to delimit non-numeric fields

text is delimited with ' or "
dates are delimited with #

for example
"" & Forms!frmTransferItem!TxtItemID & ", "
should probably be
"'" & Forms!frmTransferItem!TxtItemID & "', "

1 is not a variable, it is a literal value
so this:
& 1 & ", "
should be this:
& "1, "

also, why are you not storing TransferDate and TransferTime together?

TransferDateTime --> "#" & Now & "#"


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have to move X number of records from one table to another table so
this is a 2 part question. First I need to run the loop the number of
times that TxtCount equals. Second I need to insert from the
CheckDetails table to the Transfer table, however they have different
fields. How can I do this?
Any help appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Me.TxtLeftID), 0)

Do Until Me.TxtCount
Me.TxtTransferID = Nz(DMax("[TransferID]", "tblTransfers"), 0) + 1
CurrentDb.Execute "INSERT Into tblTransfers(TransferID,TransferCheckID, "
& _
"TransferFromID,TransferItem,TransferTableFrom,TransferTableTo, " & _
"TransferToID,TransferQTY,TransferDate,TransferTime) " & _
"VALUES(" & Forms!frmTransferItem!TxtTransferID & ", " & _
"" & Forms!frmTransfer!TxtSalesID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & ", " & _
"" & Forms!frmTransferItem!TxtItemID & ", " & _
"" & Forms!frmTransfer!TxtOldTableNum & ", " & _
"" & Forms!frmTransferItem!TxtRightID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & "," & 1 & ", " & _
"#" & Date & "#, " & _
"#" & Time & "#)"
Loop
Click to expand...
Click to expand...
Click to expand...
 
Wow Thanks! I guess I was headig in the wrong Direction! This is great!
Thanks
DS
John W. Vinson said:
I have to move X number of records from one table to another table so this
is a 2 part question. First I need to run the loop the number of times
that
TxtCount equals. Second I need to insert from the CheckDetails table to
the
Transfer table, however they have different fields. How can I do this?
Any help appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Me.TxtLeftID), 0)

Do Until Me.TxtCount
Me.TxtTransferID = Nz(DMax("[TransferID]", "tblTransfers"), 0) + 1
CurrentDb.Execute "INSERT Into tblTransfers(TransferID,TransferCheckID, "
&
_
"TransferFromID,TransferItem,TransferTableFrom,TransferTableTo, " & _
"TransferToID,TransferQTY,TransferDate,TransferTime) " & _
"VALUES(" & Forms!frmTransferItem!TxtTransferID & ", " & _
"" & Forms!frmTransfer!TxtSalesID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & ", " & _
"" & Forms!frmTransferItem!TxtItemID & ", " & _
"" & Forms!frmTransfer!TxtOldTableNum & ", " & _
"" & Forms!frmTransferItem!TxtRightID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & "," & 1 & ", " & _
"#" & Date & "#, " & _
"#" & Time & "#)"
Loop
Click to expand...

Well, you don't need any code, nor any looping. You just need an Append
query
with the aid of a handy auxiliary table NUM, with one field N having
values
form 0 to some large number (65535 maybe):

INSERT INTO tblTransfers(TransferID + N + 1, TransferCheckID,
TransferFromID,
TransferItem, TransferTableFrom, TransferTableTo, TransferToID,
TransferToQty,
TransferTime)
SELECT TransferID, SalesID, OldServerID, txtItmeID, txtOldTableNum,
txtRightID, txtOldServerID, 1, Now()
FROM <the recordsource of frmTransfer>, Num
WHERE tblTransfers.TransferID = DMax("[TransferID]", "tblTransfers")
AND N < Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Forms!frmTransfer!TxtLeftID), 0)

Not sure about frmTransferItem vs. frmTransfer - do you have both forms
open??

John W. Vinson [MVP]
Click to expand...
 
Thanks for the advice
What does delimited do? It sounds like something that I should have been
doing and didn't. Any help appreciated
Thanks
DS
strive4peace said:
Hi DS,

firstly, you need to delimit non-numeric fields

text is delimited with ' or "
dates are delimited with #

for example
"" & Forms!frmTransferItem!TxtItemID & ", "
should probably be
"'" & Forms!frmTransferItem!TxtItemID & "', "

1 is not a variable, it is a literal value
so this:
& 1 & ", "
should be this:
& "1, "

also, why are you not storing TransferDate and TransferTime together?

TransferDateTime --> "#" & Now & "#"


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I have to move X number of records from one table to another table so
this is a 2 part question. First I need to run the loop the number of
times that TxtCount equals. Second I need to insert from the
CheckDetails table to the Transfer table, however they have different
fields. How can I do this?
Any help appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("CDItemID", "tblCheckDetails", "CDCheckID = " &
Me.TxtLeftID), 0)

Do Until Me.TxtCount
Me.TxtTransferID = Nz(DMax("[TransferID]", "tblTransfers"), 0) + 1
CurrentDb.Execute "INSERT Into tblTransfers(TransferID,TransferCheckID, "
& _
"TransferFromID,TransferItem,TransferTableFrom,TransferTableTo, " & _
"TransferToID,TransferQTY,TransferDate,TransferTime) " & _
"VALUES(" & Forms!frmTransferItem!TxtTransferID & ", " & _
"" & Forms!frmTransfer!TxtSalesID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & ", " & _
"" & Forms!frmTransferItem!TxtItemID & ", " & _
"" & Forms!frmTransfer!TxtOldTableNum & ", " & _
"" & Forms!frmTransferItem!TxtRightID & ", " & _
"" & Forms!frmTransfer!TxtOldServerID & "," & 1 & ", " & _
"#" & Date & "#, " & _
"#" & Time & "#)"
Loop
Click to expand...
Click to expand...
 
Back
Top