Insert Record and Change Sequential Number Field

[m stroup]

I have a form which writes to a temporary table with an autonumber key
(flightID) and a field (flightnum) I want to use for sequential numbering.

After entering the date field on the form I want to give the record a
flightnum which is sequential by date/time of flight.

My thought is After_date_update,
if [txtboxDate] < DLast([date], "tblFlights"), then
Sort tblFlights by date
Delete flightnum field
Create new flight num field
Run a do loop incrementing flightnums.
else
insert record.

Seems like a lot of work and i don't know the code to sort/delet/create but
this seems like the right logic.

Any suggestions?

 

[Klatuu]

This is not a viable solution for your problem. It would slow the user down
every time you need to reorder flights. Since this is a temporary table, why
not create flight numbers when you add these records to the permanent table?

In reality, the concept needs work. What you are wanting to do will be
difficult to use and not easy to code to keep it correct. Also, a couple of
other issues.
The DLast would not be the correct function to use. DLast returns the last
occurance of the value, not the minimum or maximum value and in either case
will not necessarily be what you want.
Using [date] as a name is not good. Date is a reserved word in Access and
will cause incorrect values to be returned.
You don't delete a field then add it back to a table, you just replace the
values in the field.
An autonumber in a temporary table is useless.

 

[m stroup]

Thanks, I was very tentative about the whole idea - I should trust my gut.
I have flights happening on site which are entered in order, so
autonumbering is not a problem here in the permanent table. It is the
occasional flight which occurs off-site that is entered after the fact and
will be "out of order" in the permanent table. But I need to have all
flights sequentially numbered.

I was going to use DLast with the idea that the records would be sorted
after each entry and the last would have been the "latest" flight. I
understand what you are saying though.

I am changing my Date field as we speak. Thanks for the advice.

Pax,
Mary
--
Teach me to fish! Thanks for the help.
Pax, M

This is not a viable solution for your problem. It would slow the user down
every time you need to reorder flights. Since this is a temporary table, why
not create flight numbers when you add these records to the permanent table?

In reality, the concept needs work. What you are wanting to do will be
difficult to use and not easy to code to keep it correct. Also, a couple of
other issues.
The DLast would not be the correct function to use. DLast returns the last
occurance of the value, not the minimum or maximum value and in either case
will not necessarily be what you want.
Using [date] as a name is not good. Date is a reserved word in Access and
will cause incorrect values to be returned.
You don't delete a field then add it back to a table, you just replace the
values in the field.
An autonumber in a temporary table is useless.
--
Dave Hargis, Microsoft Access MVP

I have a form which writes to a temporary table with an autonumber key
(flightID) and a field (flightnum) I want to use for sequential numbering.

After entering the date field on the form I want to give the record a
flightnum which is sequential by date/time of flight.

My thought is After_date_update,
if [txtboxDate] < DLast([date], "tblFlights"), then
Sort tblFlights by date
Delete flightnum field
Create new flight num field
Run a do loop incrementing flightnums.
else
insert record.

Seems like a lot of work and i don't know the code to sort/delet/create but
this seems like the right logic.

Any suggestions?

 

[Klatuu]

If you have the date and time of each flight and the flights are to be
ordered by a date/time, then the actual order in the table is of no
consequence. Just order your outputs (forms, reports, etc) by that date/time.
--
Dave Hargis, Microsoft Access MVP

Thanks, I was very tentative about the whole idea - I should trust my gut.
I have flights happening on site which are entered in order, so
autonumbering is not a problem here in the permanent table. It is the
occasional flight which occurs off-site that is entered after the fact and
will be "out of order" in the permanent table. But I need to have all
flights sequentially numbered.

I was going to use DLast with the idea that the records would be sorted
after each entry and the last would have been the "latest" flight. I
understand what you are saying though.

I am changing my Date field as we speak. Thanks for the advice.

Pax,
Mary
--
Teach me to fish! Thanks for the help.
Pax, M

This is not a viable solution for your problem. It would slow the user down
every time you need to reorder flights. Since this is a temporary table, why
not create flight numbers when you add these records to the permanent table?

In reality, the concept needs work. What you are wanting to do will be
difficult to use and not easy to code to keep it correct. Also, a couple of
other issues.
The DLast would not be the correct function to use. DLast returns the last
occurance of the value, not the minimum or maximum value and in either case
will not necessarily be what you want.
Using [date] as a name is not good. Date is a reserved word in Access and
will cause incorrect values to be returned.
You don't delete a field then add it back to a table, you just replace the
values in the field.
An autonumber in a temporary table is useless.
--
Dave Hargis, Microsoft Access MVP

I have a form which writes to a temporary table with an autonumber key
(flightID) and a field (flightnum) I want to use for sequential numbering.

After entering the date field on the form I want to give the record a
flightnum which is sequential by date/time of flight.

My thought is After_date_update,
if [txtboxDate] < DLast([date], "tblFlights"), then
Sort tblFlights by date
Delete flightnum field
Create new flight num field
Run a do loop incrementing flightnums.
else
insert record.

Seems like a lot of work and i don't know the code to sort/delet/create but
this seems like the right logic.

Any suggestions?