Change subform record order

[Sammie]

I want the user to be able to re-order the records on a subform (ideally)
with a click, or very few key-strokes.

I have a number field called priority on a subform bound to
tblVendorContacts. I want an easy way for the user to change the priority on
any given record on the subform to 1 (top priority), and to automatically
re-number the rest of the records (so you don't end up with two priority
1's). I would also like to automatically re-sort the records in priority
order.

Cananyone help?

 

[Rod Behr]

Sammie

Try this:

Forms!MyForm!MyChild.Form.OrderBy = "MyControl"
Forms!MyForm!MyChild.Form.FilterOn = True

 

[Sammie]

My subform automatically sorts on priority already. What I am looking for is
an easy way to change the priority and automatically renumber the list at the
same time. For example, I want to change priority 4 to priority 1, and
renumber the previous priority 1 to priority 2, 2 to 3 and 3 to 4.

 

[Marshall Barton]

My subform automatically sorts on priority already. What I am looking for is
an easy way to change the priority and automatically renumber the list at the
same time. For example, I want to change priority 4 to priority 1, and
renumber the previous priority 1 to priority 2, 2 to 3 and 3 to 4.

Here's some code I used to do that:

Private Sub SeqNo_AfterUpdate()
Dim db As DAO.Database

Set db = CurrentDb()

If Me.SeqNo < Me.SeqNo.OldValue Then
db.Execute "UPDATE Temp As T SET SeqNo = SeqNo + 1 " _
& "WHERE SeqNo Between " _
& Me.SeqNo & " And " & Me.SeqNo.OldValue - 1
ElseIf Me.SeqNo > Me.SeqNo.OldValue Then
db.Execute "UPDATE Temp As T SET SeqNo = SeqNo - 1 " _
& "WHERE SeqNo Between " _
& Me.SeqNo.OldValue + 1 & " And " & Me.SeqNo
Else
Me.SeqNo.Undo ' don't allow null or ??
End If

End Sub

Change SeqNo to the name of your priority text box

 

[Sammie]

I tried your code and I get the following error message:
"Run-time error '3078'
The Microsoft Jet database engine cannot find the input table or query
'Temp'. Make sure it exists and that its name is spelled correctly."

How does 'Temp' get created?
Really appreciate your help.

 

[Marshall Barton]

I tried your code and I get the following error message:
"Run-time error '3078'
The Microsoft Jet database engine cannot find the input table or query
'Temp'. Make sure it exists and that its name is spelled correctly."

I forgot to mention that Temp should be replaced by
tblVendorContacts.

 

[Sammie]

Thanks for that - it works. The problem is that it changes the priority
value for all records in the table. I want it to only change the records on
the subform (only change records related to the active record in the main
form). Can this be done?

 

[Marshall Barton]

Thanks for that - it works. The problem is that it changes the priority
value for all records in the table. I want it to only change the records on
the subform (only change records related to the active record in the main
form). Can this be done?

That can be done, but how depends on how the records to be
renumnered can be identified.

If they all have a specific "group" identifier field, then
it's just a matter of adding that field to the update
queries' WHERE clause:

"WHERE [group field] = " & Me.[group field] _
& " And SeqNo Between " _

If that condiition is not a rock solid rule, then I couldn't
propose an approach without knowing how to select the
records that need to be renumbered.

 

[Sammie]

The main form field vendorID equals the vendorID on the subform. So I tried
the code below on the after update property of the priority field, but it
returns the following:

"Run-time error '3061': Too few parameters. Expected 1."

Here's my code:

Private Sub Priority_AfterUpdate()
Dim db As DAO.Database

Set db = CurrentDb()

If Me.Priority < Me.Priority.OldValue Then
db.Execute "UPDATE tblvendorcontacts As T SET Priority = Priority + 1 " _
& "WHERE [vendorID] = " & Me.[VendorID] _
& " And Priority Between " _
& Me.Priority & " And " & Me.Priority.OldValue - 1
ElseIf Me.Priority > Me.Priority.OldValue Then
db.Execute "UPDATE tblvendorcontacts As T SET Priority = Priority - 1 " _
& "WHERE [vendorID] = " & Me.[VendorID] _
& " And Priority Between " _
& Me.Priority.OldValue + 1 & " And " & Me.Priority
Else
Me.Priority.UnDo ' don't allow null or ??
End If

End Sub

What am I doing wrong?
--
Thanks.
Sammie Access 2003

Thanks for that - it works. The problem is that it changes the priority
value for all records in the table. I want it to only change the records on
the subform (only change records related to the active record in the main
form). Can this be done?

That can be done, but how depends on how the records to be
renumnered can be identified.

If they all have a specific "group" identifier field, then
it's just a matter of adding that field to the update
queries' WHERE clause:

"WHERE [group field] = " & Me.[group field] _
& " And SeqNo Between " _

If that condiition is not a rock solid rule, then I couldn't
propose an approach without knowing how to select the
records that need to be renumbered.

 

[Marshall Barton]

The main form field vendorID equals the vendorID on the subform. So I tried
the code below on the after update property of the priority field, but it
returns the following:

"Run-time error '3061': Too few parameters. Expected 1."

Here's my code:

Private Sub Priority_AfterUpdate()
Dim db As DAO.Database

Set db = CurrentDb()

If Me.Priority < Me.Priority.OldValue Then
db.Execute "UPDATE tblvendorcontacts As T SET Priority = Priority + 1 " _
& "WHERE [vendorID] = " & Me.[VendorID] _
& " And Priority Between " _
& Me.Priority & " And " & Me.Priority.OldValue - 1
ElseIf Me.Priority > Me.Priority.OldValue Then
db.Execute "UPDATE tblvendorcontacts As T SET Priority = Priority - 1 " _
& "WHERE [vendorID] = " & Me.[VendorID] _
& " And Priority Between " _
& Me.Priority.OldValue + 1 & " And " & Me.Priority
Else
Me.Priority.UnDo ' don't allow null or ??
End If

End Sub

That probably means the the VendorID field in the table is a
Text field, If that is indeed the case, then it should be:

& "WHERE [vendorID] = """ & Me.[VendorID] & """" _