VBA question confused

[Afrosheen via AccessMonster.com]

I have a form set up with a recordset. I'm also using a filter.

Me.Filter = "position IN ('custody','sgt','lieutenant','captain', 'other')
and shift IN ('a-days','day shift','afternoon shift')"
Me.FilterOn = True

I'm trying to get an order by the last name field. I read Allen Brown's page
about setting the order. I've also read other pages on this board that
basically says it can't be done. So, I wrote an SQL that would give me what I
want.

strSelect = "SELECT * " & _
" FROM [tbl_Roster] " & _
" WHERE [position] IN ('custody','sgt','lieutenant',
'captain', 'other') " & _
" AND [shift] IN ('a-days','day shift','afternoon shift') "
& _
" And [archive] = False" & _
" Order by [locat1], [lname]; "
Me.Form.RecordSource = strSelect

I've also read the if you have a record set you shouldn't use a SQL statement.


I just wanted to know which is the right way?

Pt 2 Can the SQL be broke up with a If statement in between like:

strSelect = "SELECT * " & _
" FROM [tbl_Roster] " & _
" WHERE [position] IN ('custody','sgt','lieutenant',
'captain', 'other') " & _
if a = 1 then
" AND [shift] IN ('a-days','day shift','afternoon
shift') " & _
else
" AND [shift] IN ('a-days','day shift') " & _

end if
" And [archive] = False" & _
" Order by [locat1], [lname]; "

I hope this is not a second post.
Thanks for reading this. I really appreciate the help.

 

[Douglas J. Steele]

There's nothing intrinsically wrong with using an SQL statement rather than
a query as the RecordSource.

To generate the SQL statement, you'd need to replace your code with

strSelect = "SELECT * " & _
" FROM [tbl_Roster] " & _
" WHERE [position] IN ('custody','sgt','lieutenant','captain', 'other') "
If a = 1 Then
strSelect = strSelect & " AND [shift] IN ('a-days','day shift','afternoon
shift') " & _
Else
strSelect = strSelect & " AND [shift] IN ('a-days','day shift') " & _
End If
strSelect = strSelect & " And [archive] = False" & _
" Order by [locat1], [lname]; "

Note that it is possible to replace the SQL of a saved query:

Dim qdfSaved As DAO.QueryDef

Set qdfSaved = CurrentDb.QueryDefs("NameOfQuery")
qdfSaved.SQL = strSelect

 

[Afrosheen via AccessMonster.com]

Gee Doug that was fast.

I'll check it out. Thanks.

There's nothing intrinsically wrong with using an SQL statement rather than
a query as the RecordSource.

To generate the SQL statement, you'd need to replace your code with

strSelect = "SELECT * " & _
" FROM [tbl_Roster] " & _
" WHERE [position] IN ('custody','sgt','lieutenant','captain', 'other') "
If a = 1 Then
strSelect = strSelect & " AND [shift] IN ('a-days','day shift','afternoon
shift') " & _
Else
strSelect = strSelect & " AND [shift] IN ('a-days','day shift') " & _
End If
strSelect = strSelect & " And [archive] = False" & _
" Order by [locat1], [lname]; "

Note that it is possible to replace the SQL of a saved query:

Dim qdfSaved As DAO.QueryDef

Set qdfSaved = CurrentDb.QueryDefs("NameOfQuery")
qdfSaved.SQL = strSelect

I have a form set up with a recordset. I'm also using a filter.

[quoted text clipped - 43 lines]

I hope this is not a second post.
Thanks for reading this. I really appreciate the help.

 

[Afrosheen via AccessMonster.com]

Thanks again and again for your help. It works perfectly. I really, really
appreciate the help.

There's nothing intrinsically wrong with using an SQL statement rather than
a query as the RecordSource.

To generate the SQL statement, you'd need to replace your code with

strSelect = "SELECT * " & _
" FROM [tbl_Roster] " & _
" WHERE [position] IN ('custody','sgt','lieutenant','captain', 'other') "
If a = 1 Then
strSelect = strSelect & " AND [shift] IN ('a-days','day shift','afternoon
shift') " & _
Else
strSelect = strSelect & " AND [shift] IN ('a-days','day shift') " & _
End If
strSelect = strSelect & " And [archive] = False" & _
" Order by [locat1], [lname]; "

Note that it is possible to replace the SQL of a saved query:

Dim qdfSaved As DAO.QueryDef

Set qdfSaved = CurrentDb.QueryDefs("NameOfQuery")
qdfSaved.SQL = strSelect

I have a form set up with a recordset. I'm also using a filter.

[quoted text clipped - 43 lines]

I hope this is not a second post.
Thanks for reading this. I really appreciate the help.