Update query - how to renumber a field in sequence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create an up date query that renumbers one field in about 50
records. I need to renumber them in sequence, in even numbers starting at 0.
I need help with the "Update to:" coding. Anyone now how to write this code.

Thanks.
 
Tom said:
I am trying to create an up date query that renumbers one field in about 50
records. I need to renumber them in sequence, in even numbers starting at 0.
I need help with the "Update to:" coding. Anyone now how to write this code.

Thanks.
Click to expand...


I couldn't get an update query to do what you wanted.

Have you tried a subroutine to do the renumbering?

Try this air code:

Make sure a reference is set for DAO.
You can put this in the click event of a button or call it from a module..
Change the fields and table names to your names.....

'------------------------------
Option Compare Database
Option Explicit


Public Sub Renumber()
Dim strSQL As String, i As Integer
Dim rs As DAO.Recordset


strSQL = "SELECT Table1.field1, Table1.field2 FROM Table1 ORDER BY
Table1.field2;"

Set rs = CurrentDb.OpenRecordset(strSQL)

i = 0

With rs
Do While Not .EOF
.edit
![field1] = i
.Update
i = i + 2
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing

End Sub
'------------------------------

HTH
 
Thanks SteveS,

I don't normally right code this is just a small project for a rather large
home office. So I am in way over my head...but willing to learn. Any help is
greatly appreciated.

The code works fine. I need to apply the renumbering to a certain set of
records that are selected through a form with a ComboBox and a query. Can
anyone show me how to apply the renumber to the result of my query. The query
works fine and the renumbering works fine, I just need to apply the
Renumbering to the result of the query.

Here is the code for the Event Procedure which calls up the query:

Option Compare Database

Private Sub cboCabinet_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "renumberform1"
End Sub

Private Sub cmdEnter_Click()

If IsNull(cboCabinet) Then
MsgBox "You must choose a Cabinet."
Exit Sub
End If

DoCmd.OpenQuery "renumberquery1", acViewNormal, acEdit
DoCmd.Close acForm, "renumberform1"

End Sub




Private Sub Form_Load()

End Sub

Here is the Renumber code I am using:

Sub RenumberField()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * FROM [Folder Labels] ORDER BY [Order]"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Order] = i
rst.Update
rst.MoveNext
i = i + 2
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub


SteveS said:
Tom said:
I am trying to create an up date query that renumbers one field in about 50
records. I need to renumber them in sequence, in even numbers starting at 0.
I need help with the "Update to:" coding. Anyone now how to write this code.

Thanks.
Click to expand...


I couldn't get an update query to do what you wanted.

Have you tried a subroutine to do the renumbering?

Try this air code:

Make sure a reference is set for DAO.
You can put this in the click event of a button or call it from a module..
Change the fields and table names to your names.....

'------------------------------
Option Compare Database
Option Explicit


Public Sub Renumber()
Dim strSQL As String, i As Integer
Dim rs As DAO.Recordset


strSQL = "SELECT Table1.field1, Table1.field2 FROM Table1 ORDER BY
Table1.field2;"

Set rs = CurrentDb.OpenRecordset(strSQL)

i = 0

With rs
Do While Not .EOF
.edit
![field1] = i
.Update
i = i + 2
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing

End Sub
'------------------------------

HTH
Click to expand...
 
I got it figured out, thanks.

Tom L said:
Thanks SteveS,

I don't normally right code this is just a small project for a rather large
home office. So I am in way over my head...but willing to learn. Any help is
greatly appreciated.

The code works fine. I need to apply the renumbering to a certain set of
records that are selected through a form with a ComboBox and a query. Can
anyone show me how to apply the renumber to the result of my query. The query
works fine and the renumbering works fine, I just need to apply the
Renumbering to the result of the query.

Here is the code for the Event Procedure which calls up the query:

Option Compare Database

Private Sub cboCabinet_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "renumberform1"
End Sub

Private Sub cmdEnter_Click()

If IsNull(cboCabinet) Then
MsgBox "You must choose a Cabinet."
Exit Sub
End If

DoCmd.OpenQuery "renumberquery1", acViewNormal, acEdit
DoCmd.Close acForm, "renumberform1"

End Sub




Private Sub Form_Load()

End Sub

Here is the Renumber code I am using:

Sub RenumberField()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * FROM [Folder Labels] ORDER BY [Order]"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Order] = i
rst.Update
rst.MoveNext
i = i + 2
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub


SteveS said:
Tom said:
I am trying to create an up date query that renumbers one field in about 50
records. I need to renumber them in sequence, in even numbers starting at 0.
I need help with the "Update to:" coding. Anyone now how to write this code.

Thanks.
Click to expand...


I couldn't get an update query to do what you wanted.

Have you tried a subroutine to do the renumbering?

Try this air code:

Make sure a reference is set for DAO.
You can put this in the click event of a button or call it from a module..
Change the fields and table names to your names.....

'------------------------------
Option Compare Database
Option Explicit


Public Sub Renumber()
Dim strSQL As String, i As Integer
Dim rs As DAO.Recordset


strSQL = "SELECT Table1.field1, Table1.field2 FROM Table1 ORDER BY
Table1.field2;"

Set rs = CurrentDb.OpenRecordset(strSQL)

i = 0

With rs
Do While Not .EOF
.edit
![field1] = i
.Update
i = i + 2
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing

End Sub
'------------------------------

HTH
Click to expand...
Click to expand...
 
Back
Top