nested FOR NEXT????

H

Henro

I have a couple of tables that I need to preserve. So I copied those
tables 4 times. At the moment new data is being added I empty table 5,
copy the contents of 4 to 5, I empty 4, copy contents of 3 to 4 and so on.

The code that does that;
<code>

Dim tbl1 As String
Dim tbl2 As String
Dim SelectTblNr As Integer
Dim tblNoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String


tbl1 = "tblKopHenro"
tbl2 = "tblKopAuto"


For tblNoNew = 5 To 2 Step -1
tblNoOld = tblNoNew - 1
DeleteString = "DELETE " & tbl1 & tblNoNew & ".* FROM " & tbl1
& tblNoNew & ";"
InsertString = "INSERT INTO " & tbl1 & tblNoNew & " SELECT " &
tbl1 & tblNoOld & ".* FROM " & tbl1 & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next

For tblNoNew = 5 To 2 Step -1
tblNoOld = tblNoNew - 1
DeleteString = "DELETE " & tbl2 & tblNoNew & ".* FROM " & tbl2
& tblNoNew & ";"
InsertString = "INSERT INTO " & tbl2 & tblNoNew & " SELECT " &
tbl2 & tblNoOld & ".* FROM " & tbl2 & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next

</code>



But as you see, I have to do that for every table I want to preserve
like that , that will be at last 7 tables, probably more so I need to
use the code from FOR... Next for every tablename, every time changing
the tbl number (tbl1, tbl2, tbl3 and so on).
Now I thought: if I would provide a list of tablenames and use a FOR...
Next to add the numbers to `tbl` I can do with only a few lines of code.
So I made it like this:


<code>

Dim tbl1 As String
Dim tbl2 As String
Dim SelectTblNr As Integer
Dim tblnoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String
Dim Tabelnr As Integer
Dim Tabelnm As string

For Tabelnr = 1 to 2
Tabelnm = "tbl" & Tabelnr

tbl1 = "tblKopHenro"
tbl2 = "tblKopAuto"

For tblnoNew = 5 To 2 Step -1
tblNoOld = tblnoNew - 1
DeleteString = "DELETE " & Tabelnm & tblnoNew & ".* FROM " &
Tabelnm & tblnoNew & ";"
InsertString = "INSERT INTO " & Tabelnm & tblnoNew & " SELECT "
& Tabelnm & tblNoOld & ".* FROM " & Tabelnm & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next
Next


</code>


I figured I would use the code above but `Tabelnm` should resolve to
tbl1 which should resolve to "tblKopHenro".
Well, that last thing is not happening! If I halt the code then the
variable 'Tabelnm' is 'tbl1' and not 'tblKopHenro'
How can I do this?

and another thing: I succeeded in finding the tablenames of the tables
to copy using a query:

SELECT Left([Name],Len([Name])-1) AS SelectedTables, MSysObjects.Type
FROM MSysObjects
GROUP BY Left([Name],Len([Name])-1), MSysObjects.Type
HAVING (((Left([Name],Len([Name])-1)) Like "tblkop*") AND
((MSysObjects.Type)=1));


Could that query somehow be used as the source of the list of tables?
(eg tbl1 = [the first out of this query] etc etc)

TIA for thinking!
 
D

Douglas J Steele

For question 1, use an array.

Dim tbl(1 To 2) As String
Dim SelectTblNr As Integer
Dim tblnoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String
Dim Tabelnr As Integer
Dim Tabelnm As string

tbl(1) = "tblKopHenro"
tbl(2) = "tblKopAuto"

For Tabelnr = 1 to 2
Tabelnm = tbl(Tabelnr)

For tblnoNew = 5 To 2 Step -1
tblNoOld = tblnoNew - 1
DeleteString = "DELETE " & Tabelnm & tblnoNew & ".* FROM " &
Tabelnm & tblnoNew & ";"
InsertString = "INSERT INTO " & Tabelnm & tblnoNew & " SELECT "
& Tabelnm & tblNoOld & ".* FROM " & Tabelnm & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next
Next

For question 2, use a recordset:



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Henro said:
I have a couple of tables that I need to preserve. So I copied those
tables 4 times. At the moment new data is being added I empty table 5,
copy the contents of 4 to 5, I empty 4, copy contents of 3 to 4 and so on.

The code that does that;
<code>

Dim tbl1 As String
Dim tbl2 As String
Dim SelectTblNr As Integer
Dim tblNoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String


tbl1 = "tblKopHenro"
tbl2 = "tblKopAuto"


For tblNoNew = 5 To 2 Step -1
tblNoOld = tblNoNew - 1
DeleteString = "DELETE " & tbl1 & tblNoNew & ".* FROM " & tbl1
& tblNoNew & ";"
InsertString = "INSERT INTO " & tbl1 & tblNoNew & " SELECT " &
tbl1 & tblNoOld & ".* FROM " & tbl1 & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next

For tblNoNew = 5 To 2 Step -1
tblNoOld = tblNoNew - 1
DeleteString = "DELETE " & tbl2 & tblNoNew & ".* FROM " & tbl2
& tblNoNew & ";"
InsertString = "INSERT INTO " & tbl2 & tblNoNew & " SELECT " &
tbl2 & tblNoOld & ".* FROM " & tbl2 & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next

</code>



But as you see, I have to do that for every table I want to preserve
like that , that will be at last 7 tables, probably more so I need to
use the code from FOR... Next for every tablename, every time changing
the tbl number (tbl1, tbl2, tbl3 and so on).
Now I thought: if I would provide a list of tablenames and use a FOR...
Next to add the numbers to `tbl` I can do with only a few lines of code.
So I made it like this:


<code>

Dim tbl1 As String
Dim tbl2 As String
Dim SelectTblNr As Integer
Dim tblnoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String
Dim Tabelnr As Integer
Dim Tabelnm As string

For Tabelnr = 1 to 2
Tabelnm = "tbl" & Tabelnr

tbl1 = "tblKopHenro"
tbl2 = "tblKopAuto"

For tblnoNew = 5 To 2 Step -1
tblNoOld = tblnoNew - 1
DeleteString = "DELETE " & Tabelnm & tblnoNew & ".* FROM " &
Tabelnm & tblnoNew & ";"
InsertString = "INSERT INTO " & Tabelnm & tblnoNew & " SELECT "
& Tabelnm & tblNoOld & ".* FROM " & Tabelnm & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next
Next


</code>


I figured I would use the code above but `Tabelnm` should resolve to
tbl1 which should resolve to "tblKopHenro".
Well, that last thing is not happening! If I halt the code then the
variable 'Tabelnm' is 'tbl1' and not 'tblKopHenro'
How can I do this?

and another thing: I succeeded in finding the tablenames of the tables
to copy using a query:

SELECT Left([Name],Len([Name])-1) AS SelectedTables, MSysObjects.Type
FROM MSysObjects
GROUP BY Left([Name],Len([Name])-1), MSysObjects.Type
HAVING (((Left([Name],Len([Name])-1)) Like "tblkop*") AND
((MSysObjects.Type)=1));


Could that query somehow be used as the source of the list of tables?
(eg tbl1 = [the first out of this query] etc etc)

TIA for thinking!
Click to expand...
 
H

Henro

Douglas, the code is running very sweet now.
Thank you.

I would like to finetune it.

I understand that I can populate the array tbl(1 to 2) with the names
provided by the query

SELECT Left([Name],Len([Name])-1) AS SelectedTables
FROM MSysObjects
GROUP BY Left([Name],Len([Name])-1)
HAVING (((Left([Name],Len([Name])-1)) Like "tblkop*"));


using a recordset?? I would not know how to do that.... :(

And as I see it would the array be limited to the number that I provide
in tbl(1 to 2) ?

What if I would count somehow the number of results returned by the
query and use that in the array?
eg: tbl(1 to X) where X is the recordcount?
Then somehow populate the array using the values found in SelectedTables?

Is that an idea? How could something like that be done?

Thnx for thinking!

Henro

Douglas J Steele schreef:
 
H

Henro

Douglas, the code is running very sweet now.
Thank you.

I would like to finetune it.

I understand that I can populate the array tbl(1 to 2) with the names
provided by the query

SELECT Left([Name],Len([Name])-1) AS SelectedTables
FROM MSysObjects
GROUP BY Left([Name],Len([Name])-1)
HAVING (((Left([Name],Len([Name])-1)) Like "tblkop*"));


using a recordset?? I would not know how to do that.... :(

And as I see it would the array be limited to the number that I provide
in tbl(1 to 2) ?

What if I would count somehow the number of results returned by the
query and use that in the array?
eg: tbl(1 to X) where X is the recordcount?
Then somehow populate the array using the values found in SelectedTables?

Is that an idea? How could something like that be done?

Thnx for thinking!

Henro

Douglas J Steele schreef:
 
D

Douglas J Steele

As I started to say, you need to use a recordset. I use DAO:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim SelectTblNr As Integer
Dim tblnoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String
Dim Tabelnr As Integer
Dim Tabelnm As string

strSQL = "SELECT Left([Name],Len([Name])-1) " & _
"AS SelectedTables FROM MSysObjects " & _
"GROUP BY Left([Name],Len([Name])-1) " & _
"HAVING (((Left([Name],Len([Name])-1)) Like 'tblkop*'))"

Set dbCurr = CurrentDB()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
Tabelnm = rsCurr!SelectedTables

For tblnoNew = 5 To 2 Step -1
tblNoOld = tblnoNew - 1
DeleteString = "DELETE " & Tabelnm & tblnoNew & _
".* FROM " & Tabelnm & tblnoNew
InsertString = "INSERT INTO " & Tabelnm & tblnoNew & _
" SELECT " & Tabelnm & tblNoOld & ".* FROM " & _
Tabelnm & tblNoOld

dbCurr.Execute DeleteString, dbFailOnError
dbCurr.Execute InsertString, dbFailOnError
Next
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing


Note that I replaced your RunSQL statements with the Execute method of the
Database object. That has the advantage that it doesn't pop up the "You're
about to delete n records..." messages, and it trap errors that might occur
(assuming you have error trapping in your code)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Henro said:
Douglas, the code is running very sweet now.
Thank you.

I would like to finetune it.

I understand that I can populate the array tbl(1 to 2) with the names
provided by the query

SELECT Left([Name],Len([Name])-1) AS SelectedTables
FROM MSysObjects
GROUP BY Left([Name],Len([Name])-1)
HAVING (((Left([Name],Len([Name])-1)) Like "tblkop*"));


using a recordset?? I would not know how to do that.... :(

And as I see it would the array be limited to the number that I provide
in tbl(1 to 2) ?

What if I would count somehow the number of results returned by the
query and use that in the array?
eg: tbl(1 to X) where X is the recordcount?
Then somehow populate the array using the values found in SelectedTables?

Is that an idea? How could something like that be done?

Thnx for thinking!

Henro

Douglas J Steele schreef:
For question 1, use an array.

Dim tbl(1 To 2) As String
Dim SelectTblNr As Integer
Dim tblnoNew As Integer
Dim tblNoOld As Integer
Dim DeleteString As String
Dim InsertString As String
Dim Tabelnr As Integer
Dim Tabelnm As string

tbl(1) = "tblKopHenro"
tbl(2) = "tblKopAuto"

For Tabelnr = 1 to 2
Tabelnm = tbl(Tabelnr)

For tblnoNew = 5 To 2 Step -1
tblNoOld = tblnoNew - 1
DeleteString = "DELETE " & Tabelnm & tblnoNew & ".* FROM " &
Tabelnm & tblnoNew & ";"
InsertString = "INSERT INTO " & Tabelnm & tblnoNew & " SELECT "
& Tabelnm & tblNoOld & ".* FROM " & Tabelnm & tblNoOld & ";"
DoCmd.RunSQL DeleteString
DoCmd.RunSQL InsertString
Next
Next

For question 2, use a recordset:
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sequence for tickets 2
Cannot delete tables from code 2
problems adding a second table to a dataset 4
Select Records based another table... 2
PowerPoint Shuffle (swap) objects on PPT slides with the same name only 0
VBA Multiple Line handling with Split() 1
adding a clumn to every table access2003 1
Transaction Processing 1

Top