Change subform record order

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sammie - 31 Jan 2008 03:46 GMT

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?

Signature

Thanks.
Sammie Access 2003

Reply to this Message

Rod Behr - 31 Jan 2008 09:49 GMT

Sammie

Try this:

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

> I want the user to be able to re-order the records on a subform (ideally)
> with a click, or very few key-strokes.
[quoted text clipped - 7 lines]
>
> Cananyone help?

Reply to this Message

Sammie - 31 Jan 2008 15:40 GMT

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.

Signature

Thanks.
Sammie Access 2003

> Sammie
>
[quoted text clipped - 14 lines]
> >
> > Cananyone help?

Reply to this Message

Marshall Barton - 31 Jan 2008 23:10 GMT

>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Sammie - 01 Feb 2008 22:40 GMT

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.

Signature

Thanks.
Sammie Access 2003

> >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
[quoted text clipped - 23 lines]
>
> Change SeqNo to the name of your priority text box

Reply to this Message

Marshall Barton - 02 Feb 2008 13:29 GMT

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Sammie - 02 Feb 2008 16:37 GMT

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?

Signature

Thanks.
Sammie Access 2003

> >I tried your code and I get the following error message:
> >"Run-time error '3078'
[quoted text clipped - 3 lines]
> I forgot to mention that Temp should be replaced by
> tblVendorContacts.

Reply to this Message

Marshall Barton - 02 Feb 2008 17:46 GMT

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Sammie - 02 Feb 2008 21:02 GMT

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?

Signature

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
[quoted text clipped - 14 lines]
> propose an approach without knowing how to select the
> records that need to be renumbered.

Reply to this Message

Marshall Barton - 02 Feb 2008 21:30 GMT

>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
[quoted text clipped - 24 lines]
>
>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] & """" _

Signature

Marsh
MVP [MS Access]

Reply to this Message