Change subform record order

  • Thread starter Thread starter Sammie
  • Start date Start date
S

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?
 
Sammie

Try this:

Forms!MyForm!MyChild.Form.OrderBy = "MyControl"
Forms!MyForm!MyChild.Form.FilterOn = True
 
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.
 
Sammie said:
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.
Click to expand...


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
 
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.
 
Sammie said:
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."
Click to expand...


I forgot to mention that Temp should be replaced by
tblVendorContacts.
 
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?
 
Sammie said:
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?
Click to expand...


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.
 
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


Marshall Barton said:
Sammie said:
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?
Click to expand...


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.
Click to expand...
 
Sammie said:
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
Click to expand...


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] & """" _
 
Back
Top