Subform won't sort properly

[Gina Whipp]

Hello All,

Not sure why this won't work and I'm hoping someone can steer me in the
right direction. The code located below is on the main form; at the bottom
is the recordsource for the subform which won't open following the OrderBy.
It opens sorting another field located on the form. Can someone explain why
it won't follow my 'ORDER BY tblGroceryItems.giItem' on open of the main
form. Thanks, in advance.

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim FieldValue As String, Tmp As Variant

' Initialize SELECT statement.
MySQL = "SELECT * FROM tblGroceryItems WHERE "
MyCriteria = "" & "ORDER BY tblGroceryItems.giItem"
FieldValue = [txtlookfor]

'Create criteria for WHERE clause.
If FieldValue <> "" Then
MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) & FieldValue
& Chr(42) & Chr(39)) & "ORDER BY tblGroceryItems.giItem"
Else
MyCriteria = "True ORDER BY tblGroceryItems.giItem"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Set RecordSource property of Form.
Forms![frmMakeList].[sfrList].Form.RecordSource = MyRecordSource
Forms![frmMakeList].[sfrList].SetFocus

 

[Ken Snell [MVP]]

Assuming that this is a copy/paste of your real code:

 

[Ken Snell [MVP]]

Assuming that this is a copy/paste of your real code:

If FieldValue <> "" Then
MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) & FieldValue
& Chr(42) & Chr(39)) & "ORDER BY tblGroceryItems.giItem"
Else
MyCriteria = "True ORDER BY tblGroceryItems.giItem"
End If


You don't have a space in front of the ORDER word in the second line of
code. That line should be

MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) & FieldValue
& Chr(42) & Chr(39)) & " ORDER BY tblGroceryItems.giItem"


Same is true for your line of code higher up:

MyCriteria = "" & "ORDER BY tblGroceryItems.giItem"

this should be

MyCriteria = "" & " ORDER BY tblGroceryItems.giItem"

 

[Gina Whipp]

Ken,

Yes that is the copy/paste of my real code. Great catch but that still does
not cause the subform to alpha by the 'giItem'. Any other ideas?

Thanks,
Gina

Assuming that this is a copy/paste of your real code:

If FieldValue <> "" Then
MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) &
FieldValue
& Chr(42) & Chr(39)) & "ORDER BY tblGroceryItems.giItem"
Else
MyCriteria = "True ORDER BY tblGroceryItems.giItem"
End If


You don't have a space in front of the ORDER word in the second line of
code. That line should be

MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) &
FieldValue
& Chr(42) & Chr(39)) & " ORDER BY tblGroceryItems.giItem"


Same is true for your line of code higher up:

MyCriteria = "" & "ORDER BY tblGroceryItems.giItem"

this should be

MyCriteria = "" & " ORDER BY tblGroceryItems.giItem"


--

Ken Snell
<MS ACCESS MVP>

Hello All,

Not sure why this won't work and I'm hoping someone can steer me in the
right direction. The code located below is on the main form; at the
bottom is the recordsource for the subform which won't open following the
OrderBy. It opens sorting another field located on the form. Can someone
explain why it won't follow my 'ORDER BY tblGroceryItems.giItem' on open
of the main form. Thanks, in advance.

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim FieldValue As String, Tmp As Variant

' Initialize SELECT statement.
MySQL = "SELECT * FROM tblGroceryItems WHERE "
MyCriteria = "" & "ORDER BY tblGroceryItems.giItem"
FieldValue = [txtlookfor]

'Create criteria for WHERE clause.
If FieldValue <> "" Then
MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) &
FieldValue & Chr(42) & Chr(39)) & "ORDER BY tblGroceryItems.giItem"
Else
MyCriteria = "True ORDER BY tblGroceryItems.giItem"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Set RecordSource property of Form.
Forms![frmMakeList].[sfrList].Form.RecordSource = MyRecordSource
Forms![frmMakeList].[sfrList].SetFocus

 

[Gina Whipp]

I got it! Thanks Ken for making me think... If the code is correct then I
missed something... like duh, setting the RecordSource On Open!

 

[Ken Snell [MVP]]

It's the simple things.....

Glad it's working.
--

Ken Snell
<MS ACCESS MVP>

I got it! Thanks Ken for making me think... If the code is correct then I
missed something... like duh, setting the RecordSource On Open!

Hello All,

Not sure why this won't work and I'm hoping someone can steer me in the
right direction. The code located below is on the main form; at the
bottom is the recordsource for the subform which won't open following the
OrderBy. It opens sorting another field located on the form. Can someone
explain why it won't follow my 'ORDER BY tblGroceryItems.giItem' on open
of the main form. Thanks, in advance.

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim FieldValue As String, Tmp As Variant

' Initialize SELECT statement.
MySQL = "SELECT * FROM tblGroceryItems WHERE "
MyCriteria = "" & "ORDER BY tblGroceryItems.giItem"
FieldValue = [txtlookfor]

'Create criteria for WHERE clause.
If FieldValue <> "" Then
MyCriteria = ("[giItem]" & " Like " & Chr(39) & Chr(42) &
FieldValue & Chr(42) & Chr(39)) & "ORDER BY tblGroceryItems.giItem"
Else
MyCriteria = "True ORDER BY tblGroceryItems.giItem"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Set RecordSource property of Form.
Forms![frmMakeList].[sfrList].Form.RecordSource = MyRecordSource
Forms![frmMakeList].[sfrList].SetFocus