Steve Schapel said:Tom,
You could have a field in the table which is used in the form's
undewrlying query for the ordering of the data on the form, which I
assume you are referring to a continuous view form. You could then
write code on the Click event of the 'up' and 'down' command buttons to
exchange this SortOrder value of the current record with that of the
previous/next record. There are a number of approaches that could be
taken to this. Here's one possible idea (air code), using MoveDown as
the example, and assuming the records also have a unique identifier
called ID...
Dim OneBelow As Long
Dim NextID As Long
OneBelow = DMin("[SortOrder]", "MyQuery", "[SortOrder] > " & Me.SortOrder)
NextID = DLookup("[ID]","MyQuery","[SortOrder]=" & OneBelow
CurrentDb.Execute "UPDATE MyQuery SET SortOrder=" & Me.SortOrder & _
" WHERE [ID]=" & NextID
Me.SortOrder = OneBelow
Me.Requery
--
Steve Schapel, Microsoft Access MVP
Tom said:Is there a way to move a record up or down within a Form. In other words to
manually sort records. Could I create a form with command buttons to either
move the record up or down?
code, but I am not clear on one thing one thing. "My query" is the query that
pulls up the set of records that I want to reoder within....correct?
Also..... I have some code that will renumber those chosen records. So if
this code does not specify a sort order, will it then assign the renumbering
to the records in the order in which they are found in the database (the
manually set order)? As long as no sort is applied after manually setting the
order.
:
Tom,
You could have a field in the table which is used in the form's
undewrlying query for the ordering of the data on the form, which I
assume you are referring to a continuous view form. You could then
write code on the Click event of the 'up' and 'down' command buttons to
exchange this SortOrder value of the current record with that of the
previous/next record. There are a number of approaches that could be
taken to this. Here's one possible idea (air code), using MoveDown as
the example, and assuming the records also have a unique identifier
called ID...
Dim OneBelow As Long
Dim NextID As Long
OneBelow = DMin("[SortOrder]", "MyQuery", "[SortOrder] > " & Me.SortOrder)
NextID = DLookup("[ID]","MyQuery","[SortOrder]=" & OneBelow
CurrentDb.Execute "UPDATE MyQuery SET SortOrder=" & Me.SortOrder & _
" WHERE [ID]=" & NextID
Me.SortOrder = OneBelow
Me.Requery
--
Steve Schapel, Microsoft Access MVP
Tom said:Is there a way to move a record up or down within a Form. In other words to
manually sort records. Could I create a form with command buttons to either
move the record up or down?
Steve Schapel said:Tom,
Tom L wrote:
....code, but I am not clear on one thing one thing. "My query" is the query that
pulls up the set of records that I want to reoder within....correct?
Correct. In other words, the query that the form is based on.
Also..... I have some code that will renumber those chosen records. So if
this code does not specify a sort order, will it then assign the renumbering
to the records in the order in which they are found in the database (the
manually set order)? As long as no sort is applied after manually setting the
order.
I am not sure what you mean by "renumber those chosen records". But
whatever else you do with the data should not be relevant to what I am
suggesting. My idea involves the addition of a new field to the table
which can be used as the basis of the sort order. In other words, the
query that the form is based on will show a sorting in this field. The
data in this field itself will probably not be meaningful in any other
way, and you probably don't want to show it on the form... just put a
control on the form with its Visible property set to No. This field
will need to have data entered into it initially, according to whatever
is the "starting point" sort order. How you go about this will depend,
among other things, on the number of existing records in the table. You
could add the new field initially as an Autonumber field, which will
mean values will be assigned to all existing records, and then change
the data type to Number to allow editing of the value as per your
Up/Down buttons.
--
Steve Schapel, Microsoft Access MVP
:
Tom,
You could have a field in the table which is used in the form's
undewrlying query for the ordering of the data on the form, which I
assume you are referring to a continuous view form. You could then
write code on the Click event of the 'up' and 'down' command buttons to
exchange this SortOrder value of the current record with that of the
previous/next record. There are a number of approaches that could be
taken to this. Here's one possible idea (air code), using MoveDown as
the example, and assuming the records also have a unique identifier
called ID...
Dim OneBelow As Long
Dim NextID As Long
OneBelow = DMin("[SortOrder]", "MyQuery", "[SortOrder] > " & Me.SortOrder)
NextID = DLookup("[ID]","MyQuery","[SortOrder]=" & OneBelow
CurrentDb.Execute "UPDATE MyQuery SET SortOrder=" & Me.SortOrder & _
" WHERE [ID]=" & NextID
Me.SortOrder = OneBelow
Me.Requery
--
Steve Schapel, Microsoft Access MVP
Tom L wrote:
Is there a way to move a record up or down within a Form. In other words to
manually sort records. Could I create a form with command buttons to either
move the record up or down?
Tom L said:I already had an extra field that was already used for sorting, so thats done.
Steve Schapel said:Tom,
Tom L wrote:
....code, but I am not clear on one thing one thing. "My query" is the query that
pulls up the set of records that I want to reoder within....correct?
Correct. In other words, the query that the form is based on.
Also..... I have some code that will renumber those chosen records. So if
this code does not specify a sort order, will it then assign the renumbering
to the records in the order in which they are found in the database (the
manually set order)? As long as no sort is applied after manually setting the
order.
I am not sure what you mean by "renumber those chosen records". But
whatever else you do with the data should not be relevant to what I am
suggesting. My idea involves the addition of a new field to the table
which can be used as the basis of the sort order. In other words, the
query that the form is based on will show a sorting in this field. The
data in this field itself will probably not be meaningful in any other
way, and you probably don't want to show it on the form... just put a
control on the form with its Visible property set to No. This field
will need to have data entered into it initially, according to whatever
is the "starting point" sort order. How you go about this will depend,
among other things, on the number of existing records in the table. You
could add the new field initially as an Autonumber field, which will
mean values will be assigned to all existing records, and then change
the data type to Number to allow editing of the value as per your
Up/Down buttons.
--
Steve Schapel, Microsoft Access MVP
:
Tom,
You could have a field in the table which is used in the form's
undewrlying query for the ordering of the data on the form, which I
assume you are referring to a continuous view form. You could then
write code on the Click event of the 'up' and 'down' command buttons to
exchange this SortOrder value of the current record with that of the
previous/next record. There are a number of approaches that could be
taken to this. Here's one possible idea (air code), using MoveDown as
the example, and assuming the records also have a unique identifier
called ID...
Dim OneBelow As Long
Dim NextID As Long
OneBelow = DMin("[SortOrder]", "MyQuery", "[SortOrder] > " & Me.SortOrder)
NextID = DLookup("[ID]","MyQuery","[SortOrder]=" & OneBelow
CurrentDb.Execute "UPDATE MyQuery SET SortOrder=" & Me.SortOrder & _
" WHERE [ID]=" & NextID
Me.SortOrder = OneBelow
Me.Requery
--
Steve Schapel, Microsoft Access MVP
Tom L wrote:
Is there a way to move a record up or down within a Form. In other words to
manually sort records. Could I create a form with command buttons to either
move the record up or down?
Steve Schapel said:Tom,
Are The Order and ID fields both number data type?
--
Steve Schapel, Microsoft Access MVP
Tom said:"SortOrder" is the field I am sorting by. Correct?? Mine = "Order"
I am getting a syntax error in the UPDATE statement:
CurrentDb.Execute "UPDATE MoveRecord SET Order=" & Me.Order & _
"WHERE [ID]=" & NextID
I could not find the error.
Entire procedure:
Private Sub Command17_Click()
Dim OneBelow As Long
Dim NextID As Long
OneBelow = DMin("[Order]", "MoveRecord", "[Order] > " & Me.Order)
NextID = DLookup("[ID]", "MoveRecord", "[Order]=" & OneBelow)
CurrentDb.Execute "UPDATE MoveRecord SET Order=" & Me.Order & _
"WHERE [ID]=" & NextID
Me.Order = OneBelow
Me.Requery
End Sub
Steve Schapel said:Tom,
Ok, I think I've got it... "Order" is a Reserved Word in Access.
Probably you shouldn't use it as a field name at all, but the code
should work if you use []s like this...
Dim OneBelow As Long
Dim NextID As Long
OneBelow = DMin("[Order]", "MoveRecord", "[Order] > " & Me.Order)
NextID = DLookup("[ID]", "MoveRecord", "[Order]=" & OneBelow)
CurrentDb.Execute "UPDATE MoveRecord SET [Order]=" & Me.[Order] & _
" WHERE [ID]=" & NextID
Me.[Order] = OneBelow
Me.Requery
P.S. Don't forget the space before WHERE!
--
Steve Schapel, Microsoft Access MVP
Tom said:Steve,
I had deleted it during debugging, same error with or without space. I
repasted your code to make sure, and then used Find/Replace to add my fields.
Same error. Must be something we are missing, maybe due to other code on the
form? I've eliminated most everything else to debug. Or the underlying query,
but all that in it is a sort.