INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

sorting a table with a query

sorting a table with a query

(OP)
I have a continuous form that has a table as its record source but I would like to set up
a combo box on my main form to sort the table by part number, by quantity or by line number...

Here is what the query that sorts the table by Part number looks like:
(I have omitted the other two queries)

CODE

SELECT * FROM PC_frm_tbl ORDER BY PC_frm_tbl.[Part_No]; 

but it only displays the table when I use the following:

CODE

Dim str1 As String
Dim qry As String
 DoCmd.SetWarnings False     ' squelch all update table qry messages

str1 = Me.Comb

If str1 = "By Quantity" Then
   qry = "SortPCbyQty_qry"
   DoCmd.OpenQuery qry, acViewNormal, acReadOnly
End If

If str1 = "By Part_Num" Then
   qry = "SortPCbyPart_qry"
   DoCmd.OpenQuery qry, acViewNormal, acReadOnly
End If

If str1 = "By Line #" Then
   qry = "SortPCbyLine_qry"
   DoCmd.OpenQuery qry, acViewNormal, acReadOnly
End If

PCList_frm.Requery

 DoCmd.SetWarnings True     ' unsquelch all update table qry messages 

How do I get it to actually sort and update the table?
Thanks

RE: sorting a table with a query

Hi,

Your request makes no sense.

The definition of a table is an unordered relation.

Order to a table comes only as a report through a query.

Other than via a query report, a table remains completely unordered.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: sorting a table with a query

(OP)

Quote:

Hi,

Your request makes no sense.

The definition of a table is an unordered relation.

Order to a table comes only as a report through a query.

Other than via a query report, a table remains completely unordered.
Skip,

So there isn't a way to use a query to change how an already populated table
is sorted?

RE: sorting a table with a query

Forms have an "order by" property that can be set at run time. You can also create an SQL statement on the fly it set the form's record source to the statement.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)

Quote:

dhookom (Programmer) 10 Sep 17 18:49
Forms have an "order by" property that can be set at run time. You can also create an SQL statement on the fly it set the form's record source to the statement.

Awesome tip... That is what I was looking for but I am still not getting this to work.

Does the "Me.OrderBy=" field name supposed to be the form field name or the table field name
of the record source of the form field that I want to sort on?
I have tried both but am still missing something

Here is my new code:

CODE -->

Private Sub Comb_AfterUpdate()
Select Case Me.Comb

   Case "By Quantity"
      Me.OrderBy = "QtyLeft"   ' Sort by qty.
      Me.OrderByOn = True      ' Apply the sort order.

   Case "By Part_Num"
      Me.OrderBy = "type"     ' Sort by part number.
      Me.OrderByOn = True    ' Apply the sort order.

   Case ""By Line #" 
      Me.OrderBy = "Line Number"   ' Sort by line number.
      Me.OrderByOn = True                ' Apply the sort order.

End Select

End Sub 

Thanks again

RE: sorting a table with a query

Does your code compile with the extra double quote?

You should order by the field name.

Did you perform any debugging?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)

Quote:

Does your code compile with the extra double quote?

You should order by the field name.

Did you perform any debugging?

The double quote must have been a typo when I pasted the code

Debugging seems to work.. nothing get snagged up but the sort does not happen

I have tried the following and none of these work

Me.OrderBy = "QtyLeft" ' Record source is PC_frm_tbl.QtyLeft
Me.OrderBy = "Qty" ' Field on continuous form is Me.Qty

Me.OrderBy = "type" ' Record source is PC_frm_tbl.QtyLeft
Me.OrderBy = "PNum" ' Field on continuous form is Me.PNum

Me.OrderBy = "Line" ' Record source is PC_frm_tbl.Line
Me.OrderBy = "Line Number" ' Field on continuous form is Me.Line Number

=================
I think the problem is that I have the combo box on the main form and I am attempting to sort the continuous form so
maybe me.Orderby is not correct but when I use the continuous form name of PC_frm_tbl.Orderby = xxxx
then I get a run time error 424 object required

RE: sorting a table with a query

Is your data in a subform? The code I provided didn't account for using a subform.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)

Quote (Is your data in a subform? The code I provided didn't account for using a subform. )


Yes, my data in a subform and my combo box is on the form.
There are no fields that are shared between the main form and the subform hence nothing is
linked between the two.

Will the OrderByOn work with the subform?

Thanks

RE: sorting a table with a query

When you ask questions please try to be more descriptive about forms and subforms. These are very critical to providing an answer. The name of your subform control is also significant.

You need to apply the OrderBy to the Form object of the subform control. Probably something like:

CODE --> vba

Me.[subform control name].Form.OrderBy = "Field Name From the Recordsource of the Subform" 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)
Sorry about that Duane
I thought that I had mentioned that in my initial post but just reread what I posted
and noticed that I forgot to mention that... My bad

My continuous subform control name is PCList_frm so I set my code as follows but am still not seeing
any sorting on the subform. ( I commented out the Me.PCList_frm.OrderBy = True line as I wasn't sure if it was needed but it doesn't seem to matter )

CODE -->

Private Sub Comb_AfterUpdate()
Select Case Me.Comb

   Case "By Quantity"
      Me.PCList_frm.Form.OrderBy = "QtyLeft"   ' Sort by qty.
'      Me.PCList_frm.OrderBy = True   ' Apply the sort order.

   Case "By Part_Num"
      Me.PCList_frm.Form.OrderBy = "type"   ' Sort by part number.
 '     Me.PCList_frm.OrderBy = True   ' Apply the sort order.

   Case "By Line #"
      Me.PCList_frm.Form.OrderBy = "Line"   ' Sort by line number.
 '     Me.PCList_frm.OrderBy = True   ' Apply the sort order.

End Select

' PC_frm_tbl.Repaint

End Sub 

Thanks for your help and patience with me

RE: sorting a table with a query

Try this:

CODE --> vba

Private Sub Comb_AfterUpdate()
   Select Case Me.Comb
      Case "By Quantity"
          Me.PCList_frm.Form.OrderBy = "QtyLeft"   ' Sort by qty.
      Case "By Part_Num"
         Me.PCList_frm.Form.OrderBy = "type"       ' Sort by part number.
      Case "By Line #"
         Me.PCList_frm.Form.OrderBy = "Line"       ' Sort by line number.
   End Select
   Me.PCList_frm.Form.OrderByOn = True
End Sub 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)
Thank you Duane... That works great and sorry again for the missing info earlier

RE: sorting a table with a query

(OP)
There is one peculiar thing that I have noticed regarding the Line number sorting of these subforms...

Regarding the Me.LicenseList_frm.Form.OrderBy = "Line"
The Line field (in the record source table field) is a number but because it is displayed in a text box, it is sorted as
a text field and not numerically sorted. Access does not have a numeric box does it?

In other words, instead of being sorted like 1, 2, 3, 4, 5, 11, 12, this field is sorted like 1, 11, 12, 2, 3, 4, 5...

Another observation is that the Me.LicenseList_frm.Form.OrderBy = "QtyLeft"

The QtyLeft field (in the record source table field) is also number displayed in a text box but this sorts
numerically (like 1, 2, 3, 4, 5, 11, 12)

The only difference that I can see is that the QtyLeft field on the form is not locked but the Line field on the form
(mentioned above) is locked so that the user can not change this field.

Is this difference in sorting (between these two fields) caused by one text box being locked and the other not being locked?

Is there a way around this with the Me.LicenseList_frm.Form.OrderBy = "Line" so the records displayed on the continuous subform
are sorted numerically by Line number or am I missing something else?

Thanks again

RE: sorting a table with a query

You can't sort by a text box, only a field in the record source. When you view the form's record source in datasheet, is the column aligned to the left or right?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)

Quote:

You can't sort by a text box, only a field in the record source. When you view the form's record source in datasheet, is the column aligned to the left or right?

RE: sorting a table with a query

irethedo,
Did you intend to post something in your reply? I only see the quote from my previous post.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: sorting a table with a query

(OP)

Quote:

You can't sort by a text box, only a field in the record source. When you view the form's record source in datasheet, is the column aligned to the left or right?

Thanks Duane-

I had these set to General but while looking at these tables, I discovered that the field that was not sorting was set to a text type in
the record source table instead of number and when I changed this it started working correctly.

It would have took me longer to find it had it not been for your last post.

Thanks again

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close