sorting a table with a query 1

[irethedo]

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)

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

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

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[irethedo]

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?

 

[dhookom]

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

 

[irethedo]

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:

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

 

[dhookom]

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

 

[irethedo]

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

 

[dhookom]

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

 

[irethedo]

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

 

[dhookom]

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:

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

 

[irethedo]

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 )

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

 

[dhookom]

Try this:

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

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

 

[irethedo]

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

 

[irethedo]

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

 

[dhookom]

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

 

[irethedo]

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?

 

[dhookom]

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

 

[irethedo]

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