Sorting on 2 fields

C

Charlie

I got a table with

Sample number
plus size
minus size

The table can contain the same sample numbers with different sample size
ranges and there will be a lot off different sample numbers… ie 111222 with
size range 3 - 5 , 111222 with size 5 - 7 and 111333 with size 7 - 10 etc

I have written the following query to sort the records by sample number and
plus sample size. So it firstly groups all sample numbers together and sorts
the sizes based on the plus size field, output becomes

sample number plus size
111222 size 5
111222 size 9
111333 size 3
111333 size 7 etc

SELECT [Inbound Samples].[Sample Number], [Inbound Samples].[Stockpile
Number], [Sizing Samples].[Last Update], [Sizing Samples].[Minus Size],
[Sizing Samples].[Plus Size], [Sizing Samples].Result
FROM [Inbound Samples] INNER JOIN [Sizing Samples] ON [Inbound
Samples].[Sample Number] = [Sizing Samples].[Sample Number]
ORDER BY [Inbound Samples].[Sample Number], [Sizing Samples].[Plus Size];

I then create a datasheet view form based on the query.

The sort is correct for the initial first time (sorted by sample no and
sample size) however when i sort within the dataform (right click a data
column and sort) it is only then sorted by that sample number or sample size
and loses the settings off sorting by both. Once it is sorted in this manner,
It asks the user if you want to save the form layout..If yes is selected the
sorting within the query itself is lost.

My question is the best way to achieve this subgroup sorting without
creating too many buttons etc.. If I can somehow turn off the prompt asking
for the view to be saved this will do the trick...or is there some
functionality I can put into access forms to do subgroup sorting?

thanks






I am trying to sort the output by sample number and plus size
 
G

Gary Walter

Charlie said:
I got a table with

Sample number
plus size
minus size

The table can contain the same sample numbers with different sample size
ranges and there will be a lot off different sample numbers. ie 111222
with
size range 3 - 5 , 111222 with size 5 - 7 and 111333 with size 7 - 10 etc

I have written the following query to sort the records by sample number
and
plus sample size. So it firstly groups all sample numbers together and
sorts
the sizes based on the plus size field, output becomes

sample number plus size
111222 size 5
111222 size 9
111333 size 3
111333 size 7 etc

SELECT [Inbound Samples].[Sample Number], [Inbound Samples].[Stockpile
Number], [Sizing Samples].[Last Update], [Sizing Samples].[Minus Size],
[Sizing Samples].[Plus Size], [Sizing Samples].Result
FROM [Inbound Samples] INNER JOIN [Sizing Samples] ON [Inbound
Samples].[Sample Number] = [Sizing Samples].[Sample Number]
ORDER BY [Inbound Samples].[Sample Number], [Sizing Samples].[Plus Size];

I then create a datasheet view form based on the query.

The sort is correct for the initial first time (sorted by sample no and
sample size) however when i sort within the dataform (right click a data
column and sort) it is only then sorted by that sample number or sample
size
and loses the settings off sorting by both. Once it is sorted in this
manner,
It asks the user if you want to save the form layout..If yes is selected
the
sorting within the query itself is lost.

My question is the best way to achieve this subgroup sorting without
creating too many buttons etc.. If I can somehow turn off the prompt
asking
for the view to be saved this will do the trick...or is there some
functionality I can put into access forms to do subgroup sorting?
Click to expand...
Hi Charlie,

I have used 2 methods for datasheet view forms.

1) add an option group in form header
name it "optSortBy"
add field names as options

|--Sort By------------------|
| |
| o Sample Number |
| o Plus Size |
|----------------------------|

in afterupdate event of optSortBy,
change ORDER BY clause of form
recordsource

Private Sub optSortBy_AfterUpdate()
On Error GoTo Err_optSortBy_AfterUpdate
Dim strSQL As String
Dim strOrderBy As String
Dim intPos As Integer

strSQL = "SELECT I.[Sample Number], " _
& "I.[Stockpile Number], S.[Last Update], " _
& "S.[Minus Size], S.[Plus Size], S.Result " _
& "FROM [Inbound Samples] AS I " _
& "INNER JOIN " _
& "[Sizing Samples] AS S " _
& "ON I.[Sample Number] = S.[Sample Number] "

If Me!optSortBy = 1 Then
strOrderBy= "ORDER BY I.[Sample Number], S.[Plus Size];"
Else
strOrderBy= "ORDER BY S.[Plus Size], I.[Sample Number];"
End If

strSQL = strSQL & strOrderBy

Me.RecordSource = strSQL
Me.Requery

Exit_optSortBy_AfterUpdate:
Exit Sub

Err_optSortBy_AfterUpdate:
MsgBox Err.Description
Resume Exit_optSortBy_AfterUpdate

End Sub

2) change the recordsource SQL in click event
of labels over your fields
 
C

Charlie

Gary thanks for your help. I am trying to put the radio buttons in the
dataform view, It is in the Form Header in design mode, but is not showing up
when the datasheet is run. I checked the attributes and visible is turned on.

Also How would I pass the sql to a subform as this is an alternative method
I can use. ie I have a form with the subform displaying the datasheet.

thanks

Gary Walter said:
I got a table with

Sample number
plus size
minus size

The table can contain the same sample numbers with different sample size
ranges and there will be a lot off different sample numbers. ie 111222
with
size range 3 - 5 , 111222 with size 5 - 7 and 111333 with size 7 - 10 etc

I have written the following query to sort the records by sample number
and
plus sample size. So it firstly groups all sample numbers together and
sorts
the sizes based on the plus size field, output becomes

sample number plus size
111222 size 5
111222 size 9
111333 size 3
111333 size 7 etc

SELECT [Inbound Samples].[Sample Number], [Inbound Samples].[Stockpile
Number], [Sizing Samples].[Last Update], [Sizing Samples].[Minus Size],
[Sizing Samples].[Plus Size], [Sizing Samples].Result
FROM [Inbound Samples] INNER JOIN [Sizing Samples] ON [Inbound
Samples].[Sample Number] = [Sizing Samples].[Sample Number]
ORDER BY [Inbound Samples].[Sample Number], [Sizing Samples].[Plus Size];

I then create a datasheet view form based on the query.

The sort is correct for the initial first time (sorted by sample no and
sample size) however when i sort within the dataform (right click a data
column and sort) it is only then sorted by that sample number or sample
size
and loses the settings off sorting by both. Once it is sorted in this
manner,
It asks the user if you want to save the form layout..If yes is selected
the
sorting within the query itself is lost.

My question is the best way to achieve this subgroup sorting without
creating too many buttons etc.. If I can somehow turn off the prompt
asking
for the view to be saved this will do the trick...or is there some
functionality I can put into access forms to do subgroup sorting?
Click to expand...
Hi Charlie,

I have used 2 methods for datasheet view forms.

1) add an option group in form header
name it "optSortBy"
add field names as options

|--Sort By------------------|
| |
| o Sample Number |
| o Plus Size |
|----------------------------|

in afterupdate event of optSortBy,
change ORDER BY clause of form
recordsource

Private Sub optSortBy_AfterUpdate()
On Error GoTo Err_optSortBy_AfterUpdate
Dim strSQL As String
Dim strOrderBy As String
Dim intPos As Integer

strSQL = "SELECT I.[Sample Number], " _
& "I.[Stockpile Number], S.[Last Update], " _
& "S.[Minus Size], S.[Plus Size], S.Result " _
& "FROM [Inbound Samples] AS I " _
& "INNER JOIN " _
& "[Sizing Samples] AS S " _
& "ON I.[Sample Number] = S.[Sample Number] "

If Me!optSortBy = 1 Then
strOrderBy= "ORDER BY I.[Sample Number], S.[Plus Size];"
Else
strOrderBy= "ORDER BY S.[Plus Size], I.[Sample Number];"
End If

strSQL = strSQL & strOrderBy

Me.RecordSource = strSQL
Me.Requery

Exit_optSortBy_AfterUpdate:
Exit Sub

Err_optSortBy_AfterUpdate:
MsgBox Err.Description
Resume Exit_optSortBy_AfterUpdate

End Sub

2) change the recordsource SQL in click event
of labels over your fields
Click to expand...
 
G

Gary Walter

I'm sorry Charlie...my bad.

I read "datasheet" and thought
"continuous form." If I ever needed
a "datasheet-like" form, I have always
designed a continuous form that "looked
like" a datasheet.

That way I *can* put controls in the form
header.

So...what to do....

1) If you want to put a datasheet
as a subform in a main form, you
want to know how to change the
recordsource of the subform:

When you add a subform (say "frmSubxxx")
to a main form (say "frmMain"), that subform
exists in a *subform control* which may or may
not have the same name as "frmSubxxx."

If it is the same name (check Properties of
your subform control), then the short answer
for code executed in the main form:

Forms!frmMain!frmSubxxx.Form.RecordSource = strSQL
or
Me!frmSubxxx.Form.RecordSource = strSQL

the "Forms!frmMain!frmSubxxx"
(or "Me!frmSubxxx") gets us to
the subform *control*.

Within that control is a Form (the "actual subform")
which has a RecordSource.

Another way of looking at it

===============
Dim ctl As Control
Dim frm As Form

Set ctl = Me!frmSubxxx
Set frm = ctl.Form

frm.RecordSource = strSQL
=================

So you checked the Properties
of your subform you put on your
main form and you see:

Name : frmSubxxx
Source Object: frmSubxxx

using whatever is in *Name*
(of control) line, your event
on the main form might look like
(aircode, untested)

Private Sub optSortBy_AfterUpdate()
On Error GoTo Err_optSortBy_AfterUpdate
Dim strSQL As String
Dim strOrderBy As String
Dim intPos As Integer
Dim ctl As Access.Control
Dim frm As Access.Form

'use Name of subform control here!
Set ctl = Me!frmSubxxx
Set frm = ctl.Form

strSQL = "SELECT I.[Sample Number], " _
& "I.[Stockpile Number], S.[Last Update], " _
& "S.[Minus Size], S.[Plus Size], S.Result " _
& "FROM [Inbound Samples] AS I " _
& "INNER JOIN " _
& "[Sizing Samples] AS S " _
& "ON I.[Sample Number] = S.[Sample Number] "

If Me!optSortBy = 1 Then
strOrderBy= "ORDER BY I.[Sample Number], S.[Plus Size];"
Else
strOrderBy= "ORDER BY S.[Plus Size], I.[Sample Number];"
End If

strSQL = strSQL & strOrderBy

'update recordsource of subform
frm.RecordSource = strSQL
frm.Requery

Exit_optSortBy_AfterUpdate:
Set ctl = Nothing
Set frm = Nothing
Exit Sub

Err_optSortBy_AfterUpdate:
MsgBox Err.Description
Resume Exit_optSortBy_AfterUpdate

End Sub

2) there are advantages to using a
continuous form that looks "like"
a datasheet. Here be a few I can
think up at the moment:

a) you can use combobox controls
for some of the fields, so users can
only change a field value to "exact"
value (no mistypes)

b) your field controls have events like
"After_Update" where you can put
some code

c) you get a form header where, at
the least, you can put a close-form
command button so you can close
the form "gracefully."

There are probably more, but....

good luck,

gary




"Charlie"wrote:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Random selection of records based on specifc value 5
Lottery 6
Multiple counts, same field, different values 8
Make table from table 3
query to get top player 8
data query 1
Sorting data 3
Hiding report section headers and footers as needed with code 6

Top