Changing the Rank Order of Records

[ddmcn]

I am trying to find a way to "insert" a record into a table that will not put the new record at the end of the table but insert it into the middle of the table. For example, let's say that I am keeping track of officers of a trade association. The association has a president, a number of vice-presidents, and a number of secretaries. The list might look like:

President John Williams
Vice President Tomas Dojaric
Vice President Lillian Berget
Secretary Robert Bruce
Secretary Kim Jeung Tao

Now... I want to add a new officer...who happens to be a Vice President. If I insert his/her record it will appear at the bottom of the list. But I want it to appear at the point in the list where the other Vice Presidents are. I have thought about a button to move the record up in the list...but then I don't know how to re-number the existing records.

If I haven't explained this too well, let me know.

TIA

Dennis

 

[Dennisk]

no can do.
Rows are inserted at the end of a table and you use queries to order your records. Only input data via a form.

 

[Rich]

Add a ranking order for officers, better still use a lookup table to enter officers title and rank

 

[ddmcn]

no can do.
Rows are inserted at the end of a table and you use queries to order your records. Only input data via a form.

Thanks, Dennis. Of course, you're right.

The table has a column with the org id and another column with a sequence number. The form is organized first by org id then by the sequence number. What I would need to do is change the sequence numbers so that the new record fell into the right order within a particular organization. Does that make sense?

I really don't care that the record is at the end of the table...I know that is the case...I just need it to appear in the form and a subsequent report in the proper order.

 

[The_Doc_Man]

This is an example of thinking ahead.

If you are going to want to control the order of appearance of a field in some entity such as a report, have a field in the table that lets you SPECIFY the order. Then use a query that sorts on that field. End of discussion.

 

[Rich]

I could have sworn that's what I'd already suggested

 

[MStCyr]

I am trying to find a way to "insert" a record into a table that will not put the new record at the end of the table but insert it into the middle of the table. For example, let's say that I am keeping track of officers of a trade association. The association has a president, a number of vice-presidents, and a number of secretaries. The list might look like:

President John Williams
Vice President Tomas Dojaric
Vice President Lillian Berget
Secretary Robert Bruce
Secretary Kim Jeung Tao

Now... I want to add a new officer...who happens to be a Vice President. If I insert his/her record it will appear at the bottom of the list. But I want it to appear at the point in the list where the other Vice Presidents are. I have thought about a button to move the record up in the list...but then I don't know how to re-number the existing records.

If I haven't explained this too well, let me know.

TIA

Dennis

Good morning

Please find attached the MoveRecord.zip file which contains an MDB file of the same name.

The app allows you to rank the listed items, allowing you to place the new item at the required location using the Up/Down buttons on the right of the form.

Best regards

Maurice

 

[ddmcn]

Maurice, thanks for your reply. I am running this code in an ADO database and it seems to stall on some of the statements. I am having some amount of trouble re-writing it to suit the ADO environment. Any hints?

TIA

 

[MStCyr]

Maurice, thanks for your reply. I am running this code in an ADO database and it seems to stall on some of the statements. I am having some amount of trouble re-writing it to suit the ADO environment. Any hints?

TIA

Hi Dennis

Can you send me what you have and I'll take a look at it

Best regards

Maurice

 

[MStCyr]

Hi Dennis

I'm not sure what you've tried so far, but the general expression below should help you get going on this...

Example:.......

Function fGetFldNamesADO()
Dim strStoreSQL As String
Dim i As Integer

strStoreSQL = "SELECT * FROM tblCustomers"

Dim adoCon As New ADODB.Connection
Dim adoRst As New ADODB.Recordset

Set adoCon = CurrentProject.Connection

adoRst.Open strStoreSQL, adoCon

' RS.Fields is the collection of fields associated with the recordset...
' The count given is correct, but since the fields are numbered
' starting at zero, we have to subtract one to get the maximal field number:
For i = 0 To adoRst.Fields.Count - 1
' Naturally, each element in the Fields collection is
' an ADODB.Field object. And the Field object has various
' properties, including Name:-
MsgBox "Field Number >> " & i & " Field Name >>> " & adoRst.Fields (i).Name
Next

adoRst.Close
adoCon.Close

'Usage: open the immediate window (CTRL G) type this in fGetFldNamesADO and press enter

End Function 'fGetFldNamesADO

Best regards

Maurice