sorting records programmatically

[Faberman]

In my app., the user can use a form to insert a record into a table.
The table holds line data for quotations.
i.e.

QuoteNo, SubQuoteNo, LineNo, etc., etc., etc.

When the new line is added, all the line records whose LineNo is >= to the
new LineNo are re-assigned LineNo's. e.g. LineNo = LineNo + 1.

When the record is added, I would like to order the records by QuoteNo,
SubQuoteNo and LineNo so that when all the line data records are viewed (in a
different form), they essentially appear in LineNo order (first line1, next
line down line2, and so on).
Access adds the new line at the end of the relevant line data records.


I have been trying something on the lines of :

DoCmd.RunSQL ("SELECT * From QLine WHERE (((QLine.QuoteNo) =
[Forms]![frmqlineaddition]![QNo]) And ((QLine.SubQuoteNo) =
[Forms]![frmqlineaddition]![SQNo]))ORDER BY QLine.QuoteNo DESC,
QLine.SubQuoteNo DESC, QLine.LineNo DESC;")

At the moment, I get -

Run-time error 2342

A RunSQL action requires an argument consisting of an SQL statement.

For starters, I was under the impression that I was using an SQL statement
as an argument.
And, secondly, is there a better way to do this?

Thanks.

 

[KJGinNC]

You don't need the ()

DoCmd.RunSQL "SELECT * blah blah blah"

 

[chris.nebinger]

This still won't give you the results that you want. DoCmd.RunSQL is
meant for action queries, ie INSERT, UPDATE, or DELETE.

A table itself is just a list of records. The order really isn't
important. You can have your form sort by Quote, SubQuote, and
LineNumber. If the form is open when a new record is added, it must be
..requery'd in order for the new record to show in the right place.

Chris Nebinger

 

[KJGinNC]

Chris - you are so right. I get obsessed with syntax because I struggle with
it!!

For my own curiosity what event would work best? Lost Focus? After Update?

 

[Faberman]

Thanks for coming back on this.

I see how the query method would not work.

The form in which I would like to records sorted has a Record Source or QLine
i.e. the underlying table.

I have set the Order By property of the form to :
QLine.QuoteNo, QLine.SubQuoteNo, QLine.LineNo

to no avail.

I have changed the recordsource to a query (similar to the one in my
original post.
Again, no joy.

What am I missing? It's got to be simple.

 

[Faberman]

Ok. I find myself replying to my own post.
And yes. It is simple.

In the OnLoad event of the form I entered :

Me.OrderBy = "QLine.LineNo ASC"

That's it.
The records displayed are ordered correctly. (And reordered correctly after
the insertion and LineNo changes).

 

[Ricoy-Chicago]

I am sorry to bump in, but I have a problem similar using the Orderby.
I have a form that is based on a query, this query asks (using "Like") for
the parameters, [Last Name] and [First Name], then sorts the data by [Last
Name], [First Name] and the queried records are displayed on a columnar form.
No problem here.

The users sometimes want so sort the data by

, [Last Name], [First
Name] or by [Contact Date], [last Name], [First Name]. [ContacDate] and
[Code] are included in the query and form.

I created a button above the column to sort. For example the "sort" button
is above the [Code] heading. Now I am stuck. to code the command button, I
tried to use:

Me.OrderBy= "[Code]", "[Last Name]", "[First Name]"
Me.OrderBy= "Code","Last Name","First Name"
Me.OrderBy= "Code","[Last Name]","[First Name]"

None of them work

Thank you for any help.

 

[Faberman]

Hi,

I found a way of doing it in my situation by :

Me.OrderBy = "QLine.LineNo ASC"

Telling it which table and which column to sort on seems to do the trick.
Hope this helps in your situation.

I am sorry to bump in, but I have a problem similar using the Orderby.
I have a form that is based on a query, this query asks (using "Like") for
the parameters, [Last Name] and [First Name], then sorts the data by [Last
Name], [First Name] and the queried records are displayed on a columnar form.
No problem here.

The users sometimes want so sort the data by

, [Last Name], [First
Name] or by [Contact Date], [last Name], [First Name]. [ContacDate] and
[Code] are included in the query and form.

I created a button above the column to sort. For example the "sort" button
is above the [Code] heading. Now I am stuck. to code the command button, I
tried to use:

Me.OrderBy= "[Code]", "[Last Name]", "[First Name]"
Me.OrderBy= "Code","Last Name","First Name"
Me.OrderBy= "Code","[Last Name]","[First Name]"

None of them work

Thank you for any help.


[QUOTE="Faberman"]
In my app., the user can use a form to insert a record into a table.
The table holds line data for quotations.
i.e.

QuoteNo, SubQuoteNo, LineNo, etc., etc., etc.

When the new line is added, all the line records whose LineNo is >= to the
new LineNo are re-assigned LineNo's. e.g. LineNo = LineNo + 1.

When the record is added, I would like to order the records by QuoteNo,
SubQuoteNo and LineNo so that when all the line data records are viewed (in a
different form), they essentially appear in LineNo order (first line1, next
line down line2, and so on).
Access adds the new line at the end of the relevant line data records.


I have been trying something on the lines of :

DoCmd.RunSQL ("SELECT * From QLine WHERE (((QLine.QuoteNo) =
[Forms]![frmqlineaddition]![QNo]) And ((QLine.SubQuoteNo) =
[Forms]![frmqlineaddition]![SQNo]))ORDER BY QLine.QuoteNo DESC,
QLine.SubQuoteNo DESC, QLine.LineNo DESC;")

At the moment, I get -

Run-time error 2342

A RunSQL action requires an argument consisting of an SQL statement.

For starters, I was under the impression that I was using an SQL statement
as an argument.
And, secondly, is there a better way to do this?

Thanks.[/QUOTE][/QUOTE]

 

[Ricoy-Chicago]

Me.Orderby = "Code"

works, but when I added the other two fields I got an error! When I type

Me.Orderby = "Code", "Last Name", "First Name"

On the VB editor I got the "compile error, expected End of statement"

I changed the Commas to "and" and I don't get an error (On the VB Editor)
but when I click on the command button I got a "type mismach" error. Argghh...
Thanx for your help!

Hi,

I found a way of doing it in my situation by :

Me.OrderBy = "QLine.LineNo ASC"

Telling it which table and which column to sort on seems to do the trick.
Hope this helps in your situation.

I am sorry to bump in, but I have a problem similar using the Orderby.
I have a form that is based on a query, this query asks (using "Like") for
the parameters, [Last Name] and [First Name], then sorts the data by [Last
Name], [First Name] and the queried records are displayed on a columnar form.
No problem here.

The users sometimes want so sort the data by

, [Last Name], [First
Name] or by [Contact Date], [last Name], [First Name]. [ContacDate] and
[Code] are included in the query and form.

I created a button above the column to sort. For example the "sort" button
is above the [Code] heading. Now I am stuck. to code the command button, I
tried to use:

Me.OrderBy= "[Code]", "[Last Name]", "[First Name]"
Me.OrderBy= "Code","Last Name","First Name"
Me.OrderBy= "Code","[Last Name]","[First Name]"

None of them work

Thank you for any help.


[QUOTE="Faberman"]
In my app., the user can use a form to insert a record into a table.
The table holds line data for quotations.
i.e.

QuoteNo, SubQuoteNo, LineNo, etc., etc., etc.

When the new line is added, all the line records whose LineNo is >= to the
new LineNo are re-assigned LineNo's. e.g. LineNo = LineNo + 1.

When the record is added, I would like to order the records by QuoteNo,
SubQuoteNo and LineNo so that when all the line data records are viewed (in a
different form), they essentially appear in LineNo order (first line1, next
line down line2, and so on).
Access adds the new line at the end of the relevant line data records.


I have been trying something on the lines of :

DoCmd.RunSQL ("SELECT * From QLine WHERE (((QLine.QuoteNo) =
[Forms]![frmqlineaddition]![QNo]) And ((QLine.SubQuoteNo) =
[Forms]![frmqlineaddition]![SQNo]))ORDER BY QLine.QuoteNo DESC,
QLine.SubQuoteNo DESC, QLine.LineNo DESC;")

At the moment, I get -

Run-time error 2342

A RunSQL action requires an argument consisting of an SQL statement.

For starters, I was under the impression that I was using an SQL statement
as an argument.
And, secondly, is there a better way to do this?

Thanks.[/QUOTE][/QUOTE][/QUOTE]

 

[Ricoy-Chicago]

I feel sooo stupid, the correct coding is:

Me.OrderBy = "code, Last name, First name"

Thank you!

Me.Orderby = "Code"

works, but when I added the other two fields I got an error! When I type

Me.Orderby = "Code", "Last Name", "First Name"

On the VB editor I got the "compile error, expected End of statement"

I changed the Commas to "and" and I don't get an error (On the VB Editor)
but when I click on the command button I got a "type mismach" error. Argghh...
Thanx for your help!

Hi,

I found a way of doing it in my situation by :

Me.OrderBy = "QLine.LineNo ASC"

Telling it which table and which column to sort on seems to do the trick.
Hope this helps in your situation.

I am sorry to bump in, but I have a problem similar using the Orderby.
I have a form that is based on a query, this query asks (using "Like") for
the parameters, [Last Name] and [First Name], then sorts the data by [Last
Name], [First Name] and the queried records are displayed on a columnar form.
No problem here.

The users sometimes want so sort the data by

, [Last Name], [First
Name] or by [Contact Date], [last Name], [First Name]. [ContacDate] and
[Code] are included in the query and form.

I created a button above the column to sort. For example the "sort" button
is above the [Code] heading. Now I am stuck. to code the command button, I
tried to use:

Me.OrderBy= "[Code]", "[Last Name]", "[First Name]"
Me.OrderBy= "Code","Last Name","First Name"
Me.OrderBy= "Code","[Last Name]","[First Name]"

None of them work

Thank you for any help.


:

In my app., the user can use a form to insert a record into a table.
The table holds line data for quotations.
i.e.

QuoteNo, SubQuoteNo, LineNo, etc., etc., etc.

When the new line is added, all the line records whose LineNo is >= to the
new LineNo are re-assigned LineNo's. e.g. LineNo = LineNo + 1.

When the record is added, I would like to order the records by QuoteNo,
SubQuoteNo and LineNo so that when all the line data records are viewed (in a
different form), they essentially appear in LineNo order (first line1, next
line down line2, and so on).
Access adds the new line at the end of the relevant line data records.


I have been trying something on the lines of :

DoCmd.RunSQL ("SELECT * From QLine WHERE (((QLine.QuoteNo) =
[Forms]![frmqlineaddition]![QNo]) And ((QLine.SubQuoteNo) =
[Forms]![frmqlineaddition]![SQNo]))ORDER BY QLine.QuoteNo DESC,
QLine.SubQuoteNo DESC, QLine.LineNo DESC;")

At the moment, I get -

Run-time error 2342

A RunSQL action requires an argument consisting of an SQL statement.

For starters, I was under the impression that I was using an SQL statement
as an argument.
And, secondly, is there a better way to do this?

Thanks.[/QUOTE][/QUOTE][/QUOTE]

 

[Marshall Barton]

I feel sooo stupid, the correct coding is:

Me.OrderBy = "code, Last name, First name"

Actually, if you really have a space (or other
non-alphanumeric character) in a field name, you should
enclose the names in square brackets:

Me.OrderBy = "code, [Last name], [First name]"

I don't use the OrderBy property so I don't know if it might
be more forgiving that just about everything else in Access,
but putting them in won't cause any trouble. Actually, it's
a good idea to avoid those characters altogether. If you
change the field names to LastName and FirstName, you won't
have to worry about all this.

 

[Ricoy-Chicago]

It does work without the " ", but you are correct, I added the [ ], thank you
for your suggestion. I have about 7 different end-user forms that use the
[First Name], [Last name] fields, I am afraid to delete the spaces because I
do not know if the end-users forms will work.

I feel sooo stupid, the correct coding is:

Me.OrderBy = "code, Last name, First name"

Actually, if you really have a space (or other
non-alphanumeric character) in a field name, you should
enclose the names in square brackets:

Me.OrderBy = "code, [Last name], [First name]"

I don't use the OrderBy property so I don't know if it might
be more forgiving that just about everything else in Access,
but putting them in won't cause any trouble. Actually, it's
a good idea to avoid those characters altogether. If you
change the field names to LastName and FirstName, you won't
have to worry about all this.

 

[chris.nebinger]

Ricoy,

You have just found out why it is recommended that you NEVER use spaces
in table/field names. I think the best syntax would be:

Me.OrderBy = "code,[Last Name],[First Name]"

Anytime you have a space in the field name, you need to enclose it in
[]'s. No space, no bracket!


Chris Nebinger