VBA question confused

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.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1

0
Afrosheen
5/3/2010 4:21:21 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
639 Views

Similar Articles

[PageSpeed] 43

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

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Afrosheen via AccessMonster.com" <u46942@uwe> wrote in message 
news:a7756e15a2fc6@uwe...
>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.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1
> 


0
Douglas
5/3/2010 4:33:13 PM
Gee Doug that was fast.

I'll check it out. Thanks.



Douglas J. Steele wrote:
>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.

-- 
Message posted via http://www.accessmonster.com

0
Afrosheen
5/3/2010 4:48:32 PM
Thanks again and again for your help. It works perfectly. I really, really
appreciate the help.





Douglas J. Steele wrote:
>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.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1

0
Afrosheen
5/3/2010 5:11:54 PM
Reply:

Similar Artilces:

Saving Question
Why does Excel sometimes ask me if I want to save my spreadhseet even though I haven't made any changes? For example, I just brought it up, made no changes, did nothing, and when I close it out I'm asked if I want to save. Never understood that. Some functions are "volatile" and force a recalculation upon opening the workbook. These commands include TODAY(), NOW(), AREAS() and some others I can't think of right now. Once the workbook is recalced, Excel assumes that it has changed, even if you didn't really change anything. -- Cordially, Chip Pearson Microsoft...

Tab control question about updates
Searched but couldn't see this discussed. I have a form with 5 tabs. Data can be loaded three ways, manually entered, imported from a text file, and imported from a Word template. On one of the tabs (the second) when the user enters information such as a social sec. number (SSN) that matches a file already in the system, a flag goes off and alerts the user. Works fine. On the import functions, the data is loaded and appears correctly, but when the user moves to the second tab where the SSN number appears, it does not set off the flag when the page opens. I can make it happen with a c...

outlook 2007 cached mode question
With the cached mode a copy of the mailbox is stored locally. Does this mean that a user can go to another user's cached copy and open/read it? Each of my users has to be a local administrator because of a records management system we use, so each person's "private" folders are mapped to a shared network drive where security settings restrict access. If a local administrator would have access to the cached copies of mailboxes I would have to manually change the storage location. If I disable the cached mode what, if any, functionality do I lose? Bill Bill <trai...

Balancing Question
As posted before, I never got my July/August statement from my bank. I know my statement is up to date, and when I tried to balance it with september, it left me with 200 bucks in bank charges as a descrepency because the starting/ending balance was different. My bank charges me 5.00 for each statement I need because they don't offer it on their webpage. My question is this: Do I have any other option to fix my account/balancing problem besides paying 10 dollars for old statements? Would reinstalling money and starting a new money file with just my September purchases/withdrawls, and ba...

Reference column question
Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is when I do my formula which is a subtraction for each column,I want to use a refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to each column how can I get to use f1 as reference for that column G1 for that column.Thanx If I understand correctly, try putting a $ in front of the row reference. Also, sum is not necessary for what you are doing. =F$1-D3 as you copy that down it becomes =F$1-D4 copied across to the ri...

Keeping track of which listbox has focus: Best approach question
Access 2007 working in compatibility mode with a 2003 database. I have three list boxes on a tab page. I also have buttons that operate based on which list box has focus. So my approach has been to keep track of which list box has focus by having the following kind of code in the On Got Focus function of each list box: Private Sub lbx_suspendedIndefinitely_manageMyTasks_GotFocus() If Not (IsNull(Me.lbx_suspendedIndefinitely_manageMyTasks)) Then Set suspendedListBoxWithFocus = Me.lbx_suspendedIndefinitely_manageMyTasks End If End Sub in which suspendedListBoxW...

Outlook 2007 question
I installed outlook 2007 and now I try to customise it but I notice that the Tools/Opions and Tools/Customize menus are disabled. Why and how to enable them? thanks I notice now that the problem is more severe. I cannot reply to messages, save is not working. It seems that most of the functionality is disabled. I can receive emails but cannot answer them. Any clue? this is very bothering. Thanks! "YLY" wrote: > I installed outlook 2007 and now I try to customise it but I notice that the > Tools/Opions and Tools/Customize menus are disabled. Why and how to enable > t...

Question #10
If possible... How do I get excel to change the color of a "grid" if I enter say specfic letter or word in it. Example. 800 columns by 800 rows All columns and rows are small as possible Each letter would mean something, and I would need the grid with tha letter to be a specifc color. To allow easy viewing of the information V = red R = grey H = blue So instead of filling in each color per grid myself, I would get exce to auto-fill Any help would be greatly appreciat -- dirtytongu ----------------------------------------------------------------------- dirtytongue's Profi...

2 Formatting Questions
Date Fields: Can we, in the CRM forms, format dates to read into the “15 Aug 2005” format? This is possible in reports, but by default, the forms use only numbers for dates, but in any format. Currency: Any ideas of how to track multiple currencies in the same application - specifically USD and Canadian? I know a Microsoft CRM installation currently supports one language. But aside from using a picklist to specify "USD", "CAN", "MEX", or other I'm concerned about how their forecasting. -- Carroll Little Vis.align, LLC 610-692-3290 x3326 www.visalign.c...

Macro or VBA
I am dumb, but would like to automatically trim down reports that we get from our IT dept. We have to delete redundant columns (A,B,C,D,E,G,H,J,K)from each report. In other words, the only data we want to view is in F,I,J,P. Can this done more effciently with a macro or VBA/ Could someone give me an example of either so that I could work from and learn? Thank-You start a new workbook open one of those report workbooks record a macro into that new workbook (not the report workbook) when you delete the columns you don't want. stop recording back to that new workbook save it as a nice...

passing non-field value into vba
I need to pass the line item sequence number (lnitmseq) from the sales item detail entry window into vba. This isnt a visible field in the window, so i cannot "add field to vba" using the TOOLS --> customize toolbar window. Anyone have any idea how this can be done? Using the Modifier you can make the field visible, add it to VBA and then remove the modified window or make the field invisible again. While in the Window Layout mode, make sure that the Invisible fields are shown (Layout >> Show Invisible Fields) and then change the Visual Property, Visible = true and mov...

VBA date question
Why isn't this working? I have a form which has a text box for an attribute of type DATE. This attribute, foo, is normally null when the form is loaded. I tried setting the default value for the text box to "Date()" but it doesn't show up (i'm thinking it's because I'm actually running a join query to generate the record for the form so the form never gets the opportunity to display the default value for foo since it thinks it's supposed to be null, the result from the query) So I added some code like this: Private Sub Form_Open(Cancel As Integer) If (I...

create a report using vba to select the names for the report
Hi, I need to create a report of selected Customers, I have gone as far as creating a string with customer names in it. Whats next? sample = Selected_Customer_Names= [ABC], [test], [New], [A New One] ...... and so on. Is this correct for the report and/or how do i open the report with the above only listed? -- Message posted via http://www.accessmonster.com On Wed, 28 Apr 2010 03:47:34 GMT, "trevorC via AccessMonster.com" <u44860@uwe> wrote: >Hi, >I need to create a report of selected Customers, I have gone as far as >creating a string with ...

Access 2007 Rich Text Question 04-28-07
Hi All In the plan text version of a Memo field you can easily add a date by pressing Ctrl+Colon which makes life easy for the end user. However, if the Memo field is set to the Rich Text Property this no longer works. Or am I missing something? Tom gave me a part answer last week but I cannot see a way of inserting a date on a Rich Text Memo field by using keystrokes Thanks ...

A Question about CTreeCntrl
I'm writing an application from an example that uses a CTreeCtrl. I'm don't understand the usage of the InsertItem methods and its parameters. HTREEITEM InsertItem( LPTVINSERTSTRUCT lpInsertStruct ); HTREEITEM InsertItem( UINT nMask, LPCTSTR lpszItem, int nImage, int nSelectedImage, UINT nState, UINT nStateMask, LPARAM lParam, HTREEITEM hParent, HTREEITEM hInsertAfter ); HTREEITEM InsertItem( LPCTSTR lpszItem, HTREEITEM hParent = TVI_ROOT, HTREEITEM hInsertAfter = TVI_LAST ); HTREEITEM InsertItem( LPCTSTR lpszItem, int nImage, int nS...

Vlookup question #8
Hi- I have a spreadsheet that looks at individuals and the amount they paid within a certain year for healthcare expenditures. Based upon this level of expenditure, I would like Excel to spit out the Category that the individual falls into. Here is what the table for looking up the category looks like: A B C Category Min Max 00 $0 $20 01 $20 $165 Thus, if the expenditure is $17, they would fall into category 00. How do I do this in Excel? I know its probably an easy VLOOKUP function but I'm not sure how to do the VLOOKUP function when telling Excel th...

RPC/HTTP Question
I have two questions about implementing RPC over HTTP: I need to setup about 4 users to work from home over broadband..... I am considering the use of RPC/HTTP but have some questions about it. 1) Can RPC/HTTP be implemented on a single Exchange 2003 server? 2) Once implemented, will the data be downloaded on the user's remote desktops (i.e, PF,? Thank you , Rafael Rafael wrote: > I have two questions about implementing RPC over HTTP: > > I need to setup about 4 users to work from home over broadband..... I > am considering the use of RPC/HTTP but have some questions ab...

HR Reason Code and VBA
When changing pay code rates there is a dialog that pops up allowing the user to enter an effective date and reason code. Is there a way to get to this dialog box in VBA? This particular dialog does not seem to trigger BeforeModalDialog or AfterModalDialog. TIA, Jim That's because it's not a modal dialog. You know this because there is a drop list on it and modal dialog only have Yes/No/cancel, or OK or such. It might be modal however meaning that you have to use the hotkey to add it and the fields to vba. Control F-11 and Shift - F11 I believe - check your menu before the w...

my question did not post
I've posted a question that didn't show up in the excel community so I posted it again. Although I did find both when I clicked on my name and recent posts by user...what am I doing wrong? Where will this on go... :) Rene, These newsgroups will die in a few days. We will all meet again at http://social.answers.microsoft.com/Forums/en-US/category/officeexcel best wishes -- Bernard Liengme Microsoft Excel MVP "Rene" <Rene@discussions.microsoft.com> wrote in message news:D606C0F8-CBB8-46A8-818D-4151615980A9@microsoft.com... > I've posted a ques...

RAID 1 Question on Dell SC 1420 Poweredge
I have a RAID 1 array on a Poweredge SC 1420 that recently had on drive fail. The controller is an onboard CERC SATA 1.5/2s. I have read that it is possible to upgrade to a different controller using the same disks without loosing data providing the new controller is of the same family since the RAID configuration is stored on the HD. What controllers would be considered "of the same family" as the CERC SATA 1.5/2s? I would like to use the current functional drive along with a new larger drive and after the new drive has been rebuilt replace the smaller drive with a new...

Set print width to page width in VBA
IF I set the width, e.g. ActiveWorksheet.PageSetup..FitToPagesWide = 1 It automatically makes it 1 page tall, not good on long sheets, how do I set it to scale 1 page wide and however many pages tall? -- A)bort, R)etry, I)nfluence with large hammer. I recorded a macro when I did it manually. These are the lines that I'd keep: With ActiveSheet.PageSetup .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With Trevor Best wrote: > > IF I set the width, e.g. > > ActiveWorksheet.PageSetup..FitToPagesWide = 1 > > It automat...

VBA
Hi, I am currently writing a database and what i want it to do is when one particular field is filled in eg. Cylinder, then it will pick up only "cylinder" items. I have tried this code below but it doesnt seem to work. Could someone please help me. Private Sub Job_Installable_AfterUpdate() 'If Job_Installable.[Column](1) = "Cylinder" Then ' 'Forms!OrderForm.ctlSubForm.Form!Controls.NewData ' 'Forms! ' [tblJob_Consumables SubForm]![Job_Consumable Consumable ID].ControlSource = "qlkpConsumableCylinder" 'Else...

Question on PDF format
Using Publisher 2007, VISTA Utlimate. When I create a PDF file to send a 16 page newsletter (11 x 17) through an e-mail attachment, the pages display in the same order that they print out (page 16 is on the lefthand side and page 1 is on the righthand side, etc.). I've been trying to find a way for the PDF file to display the pages in order so that the person I'm sending it to can see the pages in order, just as if they were holding the hard copy of this newsletter in their hand. Is this possible in PDF? Thanks! Joy Change the booklet setup to a regular page setup, 11 x 8.5. -- ...

Category question
If you lend some money to a friend, what is this best way to categorise and record this transaction in Money? Would it be categorised as Income when the money is paid back to you? Say Irregular Income? What if they pay you some interest? Thank you. Create an account called Loans Due Me. When you lend the money, it's a transfer to that account. Record who it is with a memo. If they pay you back, withdraw it from the account as transfer. If they pay you interest, record it as split with the reimbursed amount transferred from the Loans account and an income category for the int...

Newbie Registry question.
I'm using MFC and i want to save data to the registry just before my application shuts down. What is the best way of doing that? I do not seem to be able to find a registry class in MFC. Can anyone help? I would welcome any code example assuming the data is an ip address written from an edit box. Thanks in advance JB look at the functions: CWinApp::WriteProfileInt CWinApp::WriteProfileString and the variable: CWinApp::m_pszRegistryKey in the help, that should get you going. "bredal Jensen" <bred.jen@mimosa.com> wrote in message news:OzwA2$teEHA.3028@TK2MSFTNGP12.phx...