Code for Increments!

[voskouee]

i have the following code...

Sub Update_Line()

Dim strSQL As String
Dim rsSOBP As DAO.Recordset
Dim a As Integer

Set rsSOBP = CurrentDb.OpenRecordset("SELECT SOBP FROM Countries;")

Do While Not rsSOBP.EOF
a = a + 10
strSQL = "UPDATE " & rsSOBP!SOBP & " SET Line = " & a & ";"
CurrentDb.Execute strSQL
rsSOBP.MoveNext
Loop

rsSOBP.Close
Set rsSOBP = Nothing

End Sub

this is giving me numbering of all the lines in all the tables i have..

Tables 1
10
10
10
10

Table 2

20
20
20
20

i wants to have

Tables 1
10
20
30
40

Table 2
10
20
30

How can i change my code to make this work?

Please i ve been trying for days now to get this right...

 

[RuralGuy]

Try:

Set rsSOBP = CurrentDb.OpenRecordset("SELECT SOBP FROM Countries;")

With rsSOBP
Do While Not .EOF
   a = a + 10
   .Edit
   !Line = a
   .Update
   .MoveNext
Loop

.Close
End With

 

[voskouee]

ok.. instead of writing code for all the tables.. is there a way to write a code for all the tables...

this is what i have for the two tables... the name of the tables are listed in Countries tables in the field SOBP.... please help

Sub UpdateLine1()

Dim rsSOBP As DAO.Recordset
Dim strsql As String
Dim a As Variant

Set rsSOBP = CurrentDb.OpenRecordset("SELECT Line FROM RN00144GE3;")

With rsSOBP
Do While Not .EOF
a = a + 10
.Edit
!Line = a
.Update
.MoveNext
Loop

.Close
End With

End Sub

Sub UpdateLine2()

Dim rsSOBP As DAO.Recordset
Dim strsql As String
Dim a As Variant

Set rsSOBP = CurrentDb.OpenRecordset("SELECT Line FROM RN00144GE4;")

With rsSOBP
Do While Not .EOF
a = a + 10
.Edit
!Line = a
.Update
.MoveNext
Loop

.Close
End With

End Sub

 

[RuralGuy]

Is this what you had in mind?

Private Sub YourSub()

Dim rsSOBP As DAO.Recordset
Dim rsLine As DAO.RecordSet
Dim LineNumber As Integer

Set rsSOBP = CurrentDb.OpenRecordset("SELECT SOBP FROM Countries;")

With rsSOBP
   Do While Not .EOF
      Set rsLine = CurrentDb.OpenRecordset(!SOBP,dbOpenDynaset)
      LineNumber = 0
      With rsLine
         Do While Not .EOF
            LineNumber = LineNumber + 10
            .Edit
            !Line = LineNumber
            .Update
            .MoveNext
         Loop
         .Close
      End With
      Set rsLine = Nothing
      .MoveNext
   Loop
   .Close
End With
Set rsSOBP = Nothing
End Sub

If it is then you need error handling in this sub! This is pure <<< AIR CODE >>> so there could be problems.

 

[voskouee]

This is exactly what i want! Thank you so much i appreciate the help!

i will study the code so i can figure out the steps and use it later on..

Cheers!

 

[RuralGuy]

You're welcome. Post back if it does not work.

 

[voskouee]

Different Question

Since you know more than me.. one more question..

I am about to finish my first Access database..

Most of the functions are done be seperate Modules that i wrote.

i need to execute these modules one by one after the click of one button.

is there a way? or do i need to add them all in one in the click event?

is there macro that can execute all of them and then add the macro to the button?

thanks in advance

 

[RuralGuy]

Just call them one after the other in the Click event of your button. Use the Code Builder to give you the stub for your event.

 

[voskouee]

Help with this code

Since you were helpfull yesterday.. could i get your idea on this one?

thank u so much in advace..

http://www.access-programmers.co.uk/forums/showthread.php?t=123452