Record number 2

[spliterman]

I was wondering if it is possible to change the number of a selected record. I have a form that displays a query of a scheduling database and want to be able to change the sequence that items are ran. For example, I would change record 3 to record 4, Once I know if this is possible and how, then I could wite logic to do want I want.

 

[hkaing79]

Are you trying to insert a record in a particular position?

So let's say you have
1, A
2, B
3, D

But you want to insert C between B and D? so it becomes
1, A
2, B
3, C
4, D

 

[willir]

number of a selected record

Are you talking about the AutoNumber value assigned a record? A record is the fifth one entered but ID number displays 6.

 

[mph1]

If the number you are trying to change has a Data Type
in the table set to AutoNumber and acts as an ID number, then you won't be able to change the number to do what you are trying to accompolish. Look at the FAQ's to see what an autonumber' purpose is to be. If sequencial numbering is important, such as that used for an Invoice number, then you will have to create your own numbering system.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[payback]

Create a separate field for the sequencing data. When new records are added, have this field automatically filled with a default. Sort the query on this field.

When you change this number (by form/subform), loop through each record returned in your query except the record you have just changed and change the sequence field value accordingly.

If you have an autonumber field as the key for each record, it can be used to exclude the current record for that latter update.

 

[spliterman]

Thanks everyone!
Let me try to explain a little better. Is there some way to "catch" the number of a record on a form and use it as a veriable.. This number is not included in a field, but is the number of the record from top to bottom on screen. The same number that would show on the record selector at the bottom left of the screen to let you know which record you are currently working on. The form in question is a form that views the production schedule at my company. The user enters a start and end date and then te form shows the schedule for that time frame(the first one to run showing at the top and running down in the order to run.)I would like for the scheduler to work with this form and be able to change the run order. This is why I was thinking about using a button to move a record up or down the list and reorder it. Hope this explains more. Thanks for everything!

 

[willir]

Is there some way to "catch" the number of a record on a form and use it as a veriable...same number that would show on the record selector

As I feared.
In a way, you are trying to use the primary key as a way of "counting" records. The primary key is to uniquely identify each record. With a database, you may wish to display records in any order that is appropriate. For example, display invoices by InvoiceNumber. But you may also want to display the invoices largest to smallest, or oldest unpaid to current. You use the Order By clause to control the order of the reocrds. Bottom line is that the primary key should only be used to identify each record. I suspect many of us hide the "numbered" field.

Why does AutoNumber not count records?
Well, first, if you start creating a record, and then cancel out, the autonumber will have advanced. Or if you delete a bunch of old records, do you renumber the autonumber? It may be considered a large waste of time for something often considered cosmetic.

When do you need a sequential number?
For control and audit purposes. Cheque numbers for example.

...Moving on

I would like for the scheduler to work with this form and be able to change the run order.

The way I handle something like this is to use a SortOrder number. For example, questions in a survey or check list. If I want to move a question up or down, I change the SortOrder number. I then use the Order By clause to ensure the questions appear in the order I want.

Example:[tt]
QuestionID SortOrder Question
1 1100 What is your name?
2 1500 Enter your address.
3 1400 How old are you?
4 1600 What is your level of education?
5 1200 What is your gender?
6 1300 Are you married?
[/tt]

Although the question about gender is record #6, it will the second question in the survey because 1200 follows 1100 and preceeds 1300.

Now, if I have another question to add in the middle...
[tt]
7 1250 How many kids do you have?
[/tt]

The question can be inserted within the others because I left a gap between SortOrder numbers even though the record is entered much later.

I surmise you can use a similar approach with your issue.

Richard