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

[RuralGuy]

We'll put a CommandButton on your form if you want that can Renumber the table. Everyone can see it and use it or we can put it behind a password. What do you want to do? We could also put it behind a DoubleClick of some other control on your form. Have you given this any thought as to what you want?

[jc1234]

We'll put a CommandButton on your form if you want that can Renumber the table. Everyone can see it and use it or we can put it behind a password. What do you want to do? We could also put it behind a DoubleClick of some other control on your form. Have you given this any thought as to what you want?

A command button would be fine, I dont think there is a need to password protect it at this point.

I am still a at a loss on how I would get the ID numbers to reset though. I do understand the sql query, as I have had past experience with that. VBA on the other hand seems way above my head.

[RuralGuy]

You are using ac2007 so it will probably want to create a Macro but I prefer code. use the CommandButton wizard to create a new button on your form and then we'll change what the button does so we do not care what the initial functions is, just pick and record function. Then open the property sheet for the button to the event tab and tell me what is in the Click event.

[jc1234]

Private Sub Command44_Click()

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

ID = Nz(DMax("ID", "Schedule Status")) + 1

End Sub



and yes, I know that any DBA or programmer would shoot me if they saw spaces in fields or table names, but it had been so long since Ive worked with a table, that I forgot. If I need to eliminate spaces inf fields or tables, I can do that.

[RuralGuy]

Excellent! You shoud get in the habit of naming your controls something useful like "cmdRenumber". I'm working on the code in a test db I have. I'll post back when it is done.

[jc1234]

Excellent! You shoud get in the habit of naming your controls something useful like "cmdRenumber". I'm working on the code in a test db I have. I'll post back when it is done.

I cant thank you enough, really! I'll rename it whatever you use as a name. Is there a book you could recommend that would make a good reference for someone of my skill level?

[RuralGuy]

Your botton code should look something like:

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
   .MoveFirst
End With
End Sub

...replacing RecNum with the name of your field. Let me know how it works.

[RuralGuy]

Is there a book you could recommend that would make a good reference for someone of my skill level?

Crystal has some good free videos that might get you started. There are lots of good books and tons of free lessons on the net.

[jc1234]

Your botton code should look something like:

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
   .MoveFirst
End With
End Sub

...replacing RecNum with the name of your field. Let me know how it works.

IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thank you so much!!!! you have no concept of how much time and pain you just saved. THANK YOU!

[RuralGuy]

You are very welcome. Your form should be bound to that query you created so the query will do the sorting for you. Are you ready to follow the link in my sig to mark this thread as Solved?

[jc1234]

You are very welcome. Your form should be bound to that query you created so the query will do the sorting for you. Are you ready to follow the link in my sig to mark this thread as Solved?

Done! I'll make sure the form is bound to the query. Eventually, I'll figure out a way to take the button function you made and have it run the query, sort, and re-number in one click. I'll start to figure that out when I have more free time in a few weeks, right now I have to start working on more data entry and less functionality enhancements.

Thanks again!

[RuralGuy]

Because the query does the sorting for you and when you bind it to your form all of the records will remain sorted. The button you have already does *all* of the things you mentioned. Have fun.

[TG_W]

I'm late coming back to the party (and RuralGuy smashed the problem), but I was also going to suggest you make the field a 4 or 5 digit code and call it "ActivityID" or "ActivityKey" in the hopes that people would not feel that it needed to be in order or dwell on skipped numbers. Also, making the font smaller helps .

[jc1234]

Ive been surfing the net and trying different things for a few hours, but I cant figure out how to bind the form to the query I created. I can see that the query depends on the table and only the table, but nothing depends on the query.

If the form is dependent on the table and the query is dependent on the table, does that mean that they are bound?

[RuralGuy]

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.