re sequencing consecutive number field after delete or addition to form or datasheet?

[jc1234]

I'm sorry, I should have been clearer. It is really quite simple. The form has a RecordSource on the Data tab of the property sheet. Instead of pointing to the table, you point to the Query. You're Done.

Ok cool. I changed the record source to the query and then had to change the control source on a few fields to the table. Everything works fine now. One last question (I promise). If I close out of the form and re-open all fields are set in the order that I want them, then I just press the button and it renumbers and gets rid of any duplicates or out of sequence numbers. How come it requires that I close out of the form before the rows are ordered by date and doesnt update on a save, is it that the query cant run on an open form?

[RuralGuy]

It should keep the rows ordered by date if you have set that in the query. How about posting the SQL for the query here so I can see it?

[jc1234]

It should keep the rows ordered by date if you have set that in the query. How about posting the SQL for the query here so I can see it?

SELECT [Schedule Status].*, [Schedule Status].[Scheduled Completion], [Schedule Status].[DAC & DSP Name], [Schedule Status].[Deliverable Description]
FROM [Schedule Status]
ORDER BY [Schedule Status].[Scheduled Completion], [Schedule Status].[DAC & DSP Name], [Schedule Status].[Deliverable Description];


I just checked and if I close and reopen the query without saving the form it does update, but neither the form or query will update when open, at least not visibly to me.

[RuralGuy]

Let's put in a Requery instead of the .MoveFirst and see what happens.

Private Sub cmdRenumber_Click()
Dim Sequence As Long
With Me.Recordset
   .MoveFirst
   Do While Not .EOF
      Sequence = Sequence + 1
      .Edit
      !RecNum = Sequence
      .Update
      .MoveNext
   Loop
   Me.Requery
End With
End Sub

[RuralGuy]

BTW, SELECT [Schedule Status].* will select *all* of the fields so you are actually selecting three of the fields twice with:

SELECT [Schedule Status].*, [Schedule Status].[Scheduled Completion], [Schedule Status].[DAC & DSP Name], [Schedule Status].[Deliverable Description]

[jc1234]

The new function does reorder the records according to the query, but they remain out of number sequence.

If I dont select *, then a number of columns in the form show as #NAME?

Is it possible to requery before changing the sequence? I tried to change the function, but put it into a continual loop..oops.

[RuralGuy]

Try this:

Private Sub cmdRenumber_Click()
Dim Sequence As Long
Me.Requery '-- Start by refreshing the recordset
With Me.Recordset
   .MoveFirst
   Do While Not .EOF
      Sequence = Sequence + 1
      .Edit
      !RecNum = Sequence
      .Update
      .MoveNext
   Loop
   Me.Requery
End With
End Sub

[jc1234]

Try this:

Private Sub cmdRenumber_Click()
Dim Sequence As Long
Me.Requery '-- Start by refreshing the recordset
With Me.Recordset
   .MoveFirst
   Do While Not .EOF
      Sequence = Sequence + 1
      .Edit
      !RecNum = Sequence
      .Update
      .MoveNext
   Loop
   Me.Requery
End With
End Sub

That did it Thanks again.

[RuralGuy]

Glad we could help.