variable for query criteria - using more than one 1

[fredk]

I am trying to set the criteria of the query using code - I am ok until I have to use more than one result - For example, if I want the criteria to be only one name I am ok - However, if I want an "all" field I am running into problems - For example:

This code works all but the Case All - Because the criteria is not correct when entered in the query - How do I correct this so the query reads it as "Dascit" or "TRA" etc ?????

Thank you very much!!!!

Select Case lngOptionGroupValue
Case Dascit:
strTest = "Dascit"
Case TRABenefits:
strTest = "TRA"
Case MidMonmouth:
strTest = "Mid"
Case FotekWalsh:
strTest = "Fot"
Case DirectSouth:
strTest = "Direct"
Case All:
strTest= "Dasict or TRA or Mid or Fot or Direct "
End Select

 

[JeremyNYC]

I'm a little confused. Are DirectSouth, etc constants you've defined? Assuming that's the case...

I'm guessing that you're building the sql statement in code, rather than supplying a parameter for a parameter query, or else I would expect it to work.

If you're building it in code, you can't do:
SELECT Yadda FROM tblWhatever WHERE Yadda = 'OneThing' OR 'AnotherThing'

Instead, you have to do
SELECT Yadda FROM tblWhatever WHERE Yadda = 'OneThing' OR Yadda = 'AnotherThing'

This means that you'll have to make your Case All like this:
strTest= "Dasict' OR Yadda = 'TRA' OR Yadda = 'Mid' OR Yadda = 'Fot' OR Yadda = 'Direct"

Note that I've not included single quotes at the start or end of this because I'm assuming you've got them in the place where you put strTest into the rest of the Sql statement. Although this will work, it would probably be easier to read if you put the single quotes in all of the items in the case/select statement and took them out of the place wehre you build the big SQL.

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[fredk]

Thanks for helping me Jeremy - Please bear with me as I am learning!

Actually, what I am trying get this function to do is insert text into an unbound field on a form (to use as query criteria)- There is an option group on the form with each name. In the function, I assign each name (ie direct) to an option. When the function runs it passes the criteria back to the form and the query uses the criteria

That field is then used in my query. The only part that does not work is when I try to have more than one field - for example, if I want to show all of the criteria (I cant leave it null because there are more than just the 5 here)

Actually, I took this example from Solutions - In Solutions, they did run SQL instead of using as query criteia - They used the sql as recordset for a sub-form - I am trying to run a report - Can I proceed using sql to run the report? If so, how do assign the sql to the reports recordsource?

I hope that makes sense - Thanks so much for helping me!!!!

Fred

 

[CVigil]

fredk, you asked, "[...] Can I proceed using sql to run the report? If so, how do I assign the sql to the reports recordsource?" You can programmatically alter the data displayed by a report.

You have a few options; I'll call them 1, 2, and 3. For options 1 and 2, you'll set the report's RecordSource in DesignView, and include any WHERE conditions that won't change. Then when you run the report, you'll add the WHERE conditions that do change from run to run (more on how in a minute). Options 1 and 2 assume that you will be running the report from Visual BASIC. For option 3, it won't matter if you set the RecordSource ahead of time. You'll set the RecordSource in the report's Report_Open event, using VB to read controls on your form. Option 3 assumes that you will have your form open when you start the report.

I'll give you the basic ideas, and for now, leave it to you to plug in your table names, control names, variables, and all that good jazz. As always, when code examples don't read well in your browser, just copy and paste them into the VBA editor in Access, and they should look much better and more legible. Also, use the Option Explicit at the beginning of modules, and declare all of your variables .

OPTION 1:
Options 1 and 2 assume that you will be running the report from Visual BASIC. (This is easily adapted to a macro, though.) You open the report (in Preview View). Then you set the Filter property of the report. Then you *turn on* the Filter property by setting the FilterOn property to True. An example Sub:

Public Sub RunReport1()
    'Shows the report in preview
    '  -- you see *everything*!
    DoCmd.OpenReport "rptReport1", acViewPreview
    MsgBox ""  'just pauses, to *let* you see everything
    'filter shows only records where Field1 contains a "1"
    Reports![rptReport1].Filter = "(Field1 like '*1*')"
    Reports![rptReport1].FilterOn = True
    'Now you saw the data in Preview *change*!
    '  Only filtered data is seen!
End Sub

The filter property is a string containing a WHERE clause without the word "WHERE" (see above). It is added to any existing WHERE clause in the RecordSource with an AND, to determine what records will display. It is an addition to the base RecordSource. You can't change the RecordSource property itself, once the report is open, but you can do additional filtering. Neat, isn't it? The SQL you are building will provide you with the value for the Filter property.


OPTION 2:
Options 1 and 2 assume that you will be running the report from Visual BASIC. (This is easily adapted to a macro, though.) You can also add to the Filter property directly in the DoCmd.RunReport statement -- a single step, instead of 3, and no flash when the data changes. Syntax for the DoCmd.OpenReport method is quoted from the help file for the command:

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

The filtername argument specifies a query that adds filtering; I haven't used it myself; the wherecondition argument looks & acts just the way our setting for the Filter property of the report did, in Option 1. Here's a sample Sub:

Public Sub RunReport2()
    'carefully note the commas
    DoCmd.OpenReport "rptReport1", acViewPreview, , _
                     "(ID like '*1*')"
End Sub

Again, of course, *your* filtering condition will have been built from your form into a nice little string variable which you can use in the same way as the string *literal* above.


OPTION 3:
Ah, yes, there is an option 3. Option 3 assumes that you will (always) have your form open when(ever) you start the report. Here, you add some code to the report's Report_Open event routine, and use that to set the report's RecordSource property just before the report opens (which is when this event fires, *just before*). The code setting the RecordSource property will reference the form controls directly, and use them to build your WHERE clause. An example Sub:

Private Sub Report_Open(Cancel As Integer)
    Dim strTest as String
    'Constants assumed be declared globally

    Select Case Forms![YourFormName].lngOptionGroupValue
        Case Dascit:
             strTest = "([MyField] = 'Dascit')"
            Case TRABenefits:
             strTest = "([MyField] = 'TRA')"
            Case MidMonmouth:
              strTest = "([MyField] = 'Mid')"
            Case FotekWalsh:
              strTest = "([MyField] = 'Fot')"
            Case DirectSouth:
               strTest = "([MyField] = 'Direct')"
            Case All:
               strTest= "([MyField] = 'Dasict') or " & _
                        "([MyField] = 'TRA') or & _
                        "([MyField] = 'Mid') or & _
                        "([MyField] = 'Fot') or & _
                        "([MyField] = 'Direct')"
       End Select
    Me.RecordSource = _
       "SELECT [YourFieldsHere] " & _
       "FROM [YourTableOrQueryHere]" & _
       "WHERE " & strTest & _
       ";"
    'Now, when the data is displayed, the
    'value shown in the property sheet at
    'Design Time for RecordSource is
    'ignored (no longer there)!
    'The RecordSource now contains the
    'string set in this Sub.
    'MsgBox Me.RecordSource
End Sub

The report shows the data in the RecordSource defined in its Report_Open property now, and your form doesn't have to do any building. The important thing is, as I've said, for the form to open so that the Forms![YourFormName] reference does not return an error.

 

[fredk]

C Vigil - Thanks so much for helping me out on this - I really appreciate all of the time it took to respond in such a thorough manner!!!!

I am going to work through these and I will let you know - Again, I cant thank you enough!!!!

Fred

 

[CVigil]

Sure you can, fredk. You just did . -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers&quot

 

[fredk]

C Vigil - Thanks for your help on this (I got sidetracked so I am not getting a chance to try it until now)

Regarding your option #3 - I tried to run this and get the message "user defined type not defined" I think my problem is with the constants you said are assumed declared globally - What do you mean by that ? That is where I am lost -

Thanks very much!!!

Fred

 

[CVigil]

"'Constants assumed be declared globally"
Baaah! Bad English on my part, and I have no excuse, I'm afraid -- I'm just guilty of somehow leaving out a word or two. &quot;'Constants are assumed to be declared globally&quot; would have been better phrasing, and less confusing than what I *did* type <sheepish grin> .

General tip: Did the VB editor show you which line(s) caused the error? That should give you a clue about the source of your problem, and some more context for the error message.

Answering your specific problem: See the Select Case statement. That is where I use words that are not A) part of the VBA language, and are not B) declared as identifiers within the Sub. Those are undoubtedly the source of your problem; since you didn't catch my meaning about declaring the constants globally, I'm assuming you didn't declare them.

&quot;Identifiers&quot; can be variables, constants, object names -- words that identify *that to which they refer*. Each of the words following the keyword Case, used to declare a Case block within the Select Case statement, is either 1) a declared identifier or 2) going to cause errors . So Dascit and the other identifiers need to be declared, so that VB knows what the heck those words are and how to deal with them. My comment could have referred more specifically to these words, but was meant to indicate that the Sub's coding *assumes* that they are declared globally (outside any individual Sub or Function) as Constants with specific and appropriate values.

You have an option group on your form; option groups return a number value. When the user sees the controls inside the option group, with their labels, and chooses one, the option group return the value associated with the chosen control. That value can only be a number. So even if your label for a choice (control) reads &quot;Dascit&quot;, your option group can only return something like 0 or 1 or 4 or 199. In a sense, the option group is translating the label (word) that the user sees and chooses, into a number.

Your job in the code is to deal correctly with the number, to have the code take the correct action based on the number returned by the option group. If you want to use your word in the code (say, because you are more familiar with it than the number), then your job includes &quot;re-translating&quot; that number back into the word.

For instance,

Const Dascit=1

associates the identifier Dascit with the value 1. (Constants are very similar to variables, the main difference being that Constants' values can *not* be changed once assigned, and variables' *can*.) If the choice labeled &quot;Dascit&quot; on the form gets the option group to return the value 1, then this is possible:
1) User chooses option labeled &quot;Dascit&quot; on form.
2) Option group returns 1 as its value.
(&quot;translation&quot
3) Code checks option group's value, gets 1.
4) Code compares 1 to value of the constant Dascit;
*With the above declaration*, code finds that
1 *is* equal to Dascit and executes code in the
Case Dascit block.
(&quot;re-translation&quot
5) Note that user chose &quot;Dascit&quot; and the Case Dascit
code block is the one that ran!

Basically, you just have to make sure that the values returned by the user's choices in the option group match your constant declarations and the use of the constants in the Select Case statement.

Hope that's enough (of course), but if not, I'm still here .
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[fredk]

Thanks so much for your help - As always, you provide great feedback!

I follow you on the logic, what I am having a problem with is where to declare the constants?? I think at this point it would be best if I lay all my cards on the table even though I feel a little sheepish about it because my code may need help!!! Just note that I have about 6 users and each user is assigned about 5 or 6 brokers. I am trying to use one form and depending on the user, pull only their assigned brokers - Also I am trying to have each rep have an &quot;all&quot; option which would allow them to see the report with all 5 or 6 of only their brokers included.

Here goes....

First, on the form I have the following in the forms open event:

Dim strUser As String
'use getusername function to get windows user name
strUser = GetUserName
'depending on user, complete the form so only the sac's brokers appear
Select Case strUser
Case &quot;Fred&quot;
Me.Label26.Caption = &quot;Dascit&quot;
Me.Label28.Caption = &quot;TRA&quot;
Me.Label30.Caption = &quot;MId&quot;
Me.Label32.Caption = &quot;Fotek&quot;
Me.Label34.Caption = &quot;Walsh&quot;
Me.Label36.Caption = &quot;Direct-South&quot;
Me.Label38.Visible = False

Case &quot;Peggy&quot;
Me.Label26.Caption = &quot;Glenn&quot;
Me.Label28.Caption = &quot;Scott&quot;
End Select

This is incomplete but basically uses the users login to determine the field names on the form.

Then in the on click event of the button to run the report I have the following:

Private Sub cmdRunRpt_Click()
On Error GoTo Err_Command54_Click
Dim strSACName As String
Dim strUser As String
Dim lngOptionValue As Long
Dim lngX As Long
Dim strRestrict As String
Dim stDocName As String

'assign the username to struser
strUser = GetUserName

'assign user name and the word 'brokers' to strSACName
strSACName = strUser & &quot;Brokers&quot;

'assign the value of the option group to lngX
lngX = Forms!SACSpecific!SACBrokerRun.Value


strRestrict = Eval(strSACName & &quot;(&quot; & lngX & &quot&quot

'I was asigning the broker name to a field on the form and
'using this field as the criteria in the query - this worked
except when I wanted to see all the brokers for one rep
Me.BrokerCriteria = strRestrict

stDocName = &quot;rptBrokerDetail&quot;
DoCmd.OpenReport stDocName, acPreview

Exit_Command54_Click:
Exit Sub

Err_Command54_Click:
MsgBox Err.Description
Resume Exit_Command54_Click

In the open event of the report I added your criteria but was getting the error message for the following:
(Dim strTest As sting)



Private Sub Report_Open(Cancel As Integer)
Dim strTest As sting

Select Case Forms!
[SACSpecific].lngOptionGroupValue
Case DASCIT:
strTest = &quot;([broker] = 'dascit')&quot;
Case Fotek:
strTest = &quot;([broker] = 'Dascit') or&quot;
&quot;([broker] = 'walsh')&quot;
End Select

Me.RecordSource = _
&quot;SELECT [broker] &quot; & _
&quot;from [tbldata]&quot; & _
&quot;WHERE &quot; & strTest & _
&quot;;&quot;

I think I am still confused about where to declare the constants and how I link the constants to your code - ??

Hope I am not too far off although I know I am off - thanks so much!!!!!

Fred

 

[fredk]

One more thing - I was doing some reading last night and I am trying to plug along with this - I inserted the following code in the open event of the report

Private Sub Report_Open(Cancel As Integer)

Dim strtest As String
Select Case Forms!
[SACSpecific].lngOptionGroupValue
Case Dascit:
strtest = &quot;([broker] = 'dascit')&quot;
Case mid:
strtest = &quot;([broker] = 'mid monmouth financial')&quot;
End Select



Me.RecordSource = _
&quot;SELECT [broker] &quot; & _
&quot;From [tbldata]&quot; & _
&quot;Where &quot; & strtest & _
&quot;;&quot;
End Sub

And I declared the constants in a module - However, I am getting a compile error (type-declaration charachter does not match declared data type) on the line:

select case forms!

Thanks C Vigil!!!!

 

[CVigil]

First quick response:
&quot;declared globally (outside any individual Sub or Function)&quot;

This means, &quot;in a module, but before any line that starts a Sub or Function (like 'Private Sub XXX()' or Public Function TrueOrFalseFunction() as Boolean')&quot;.

Your Access options (Tools|Options) may or may not be set up to automatically include Option Explicit as the very first line of every newly-created module, whether that module is part of a form (a class module) or a stand-alone &quot;normal&quot; module. (Check help to see what that does.) They may or may not be.

I suggest, by the way, that they *should* be so set.

Any declarations that should be global should go right after that Option Explicit statement and any other Option statements. Option statements should be the very first things in a module, followed by any global declarations. The fact that the declaration lines (Dim, Const ...) are *not* in-between a

Sub...End Sub

or

Function...End Function

pair is what *makes* them global. Variables/constants declared *inside* a procedure (a sub or Function) can only be used inside that procedure. Variables/constants declared in a module, *outside* a procedure, can be used by **any** procedure in that module -- and by any procedure in any other module, if the module containing the declaration is not itself Private.

... now back to reading more...

==================

You wrote:
* * * * * * *
In the open event of the report I added your criteria but was getting the error message for the following:

(Dim strTest As sting)

Private Sub Report_Open(Cancel As Integer)
Dim strTest As sting

* * * * * * * *
&quot;Sting&quot; is a typo. It should be &quot;String&quot;. That might be in your code, or it might just be here in the retyping for your post. Just pointing it out, &quot;in case&quot;.

==================

You wrote:
* * * * * * * * *

Select Case Forms!
[SACSpecific].lngOptionGroupValue

[...]

And I declared the constants in a module - However, I am getting a compile error (type-declaration character does not match declared data type) on the line:

select case forms!

* * * * * * * * *

You have a line-break (a &quot;return&quot after the exclamation mark in that line you say gives the error, so the compiler reads it as a single line of code, *NOT* as part of the line following. That right there is your basic problem. Get rid of the return, joining the ! and the [, and your problem is fixed.

If you care about why, read this: VB takes *Forms* as an identifier (which it is, for the Forms collection), and the *!* as a type-declaration character which tells VBA that the identifier *Forms* is of a particular data type. I forget what data-type the exclamation mark does represent, but it does *not* indicate a collection. (For more understanding of what type-declaration characters are, check the Help. If you are writing any VB code, and the VB help is not installed on your PC, it should be.) A parallel example: If lngAnswer is declared as a Long (long integer), and you put lngAnswer$ somewhere in your code, you'll get an error because lngAnswer is a Long, but the $ when you use it in lngAnswer$ tells VB that lngAnswer is a string, which it is not. The conflict of information, of type-declaration, causes an error, because $ is the type-declaration character for strings; any variable name ending with a $ is supposed to be a string.

So, again, in your case,

Forms!

and

[SACSpecific].lngOptionGroupValue

are not on the same line. You *should* have

Forms![SACSpecific].lngOptionGroupValue

in your code.


==================

-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[fredk]

Thank you so much c vigil - I will get on that right away - I guess I am starting to get some of this because when I looked at the line &quot;select case forms!&quot; I was thinking that this was some type of referance that access used that I did not know about (duh) now I see that it belongs together with the bang -

Actually, this has been a good excercise because it is making me thing about the associations.

I appreciate all of your help in getting me straight on this, hopefully, this will do it -

Thanks again!!!!

Fred

 

[fredk]

CVigil - I am sorry to bother you again with this - I am applying what you told me - I put the constants under the option explicit in the module with the report events and when I ran the report it did not recognize the variable (for example, I was using &quot;dascit&quot

So, I moved the constants to the reports module under the option explicit section.

Now, when I run the report, I get a run time error 2465 - application-defined object defined error - the error is on the line:

Select Case forms![SACSpecific].lngOptionGroupValue
in the report open event.

Thanks again and sorry to be a pain!!!!

Fred

 

[CVigil]

You're not a bother; I can ignore you at any time <evil grin> !

Is your form open at the time the report runs? The Forms collection is the collection of all *OPEN* forms. Sometimes that's a pain, and just occasionally it is handy.

If it is open, do you have the name of your form and control typed correctly in the code? Since the Select and Case are spelled and used correctly, and since the error message says something about &quot;object&quot;, the error must relate to the form or control you reference in the offending line.

Also:
* * * * *
I put the constants under the option explicit in the module with the report events and when I ran the report it did not recognize the variable (for example, I was using &quot;dascit&quot

So, I moved the constants to the reports module under the option explicit section.
* * * * *
You seem to repeat yourself. You put the constants in a place, ran the report which didn't recognize the variable (constant?), and then put the constants in the place where they already were.

I'm not sure what you were trying to communicate to me. But maybe it is worth repeating that you should use *constants* for this, not variables, since their value should never change.

  Const Dascit=99

is not the same as

  Dim Dascit As Long

In particular, the first *sets the value of Dascit*.
The second declares Dascit, but doesn't assign a value. (Actually...it does, 0, but that's not the value you want for every one of your constants!) You have to explicitly assign/change the value of Dascit within a procedure if you declare it as a variable.

And also, you know not to put quotes around an identifier in code, right? I realise that putting quotes around it in the post doesn't mean you do it in code, but since the distinction between an identifier and a string that happens to consist of the same characters as those in the identifier's name has been relevant to our conversation, I have to make sure. I've been careful to avoid using quotes around an identifier in our discussion, for just that reason.


I'm not sure I got to the root of your question about the declarations or not, but I'll see what you have to say next; another post from me is already in the works, by the way, and has been since my first today. That will come through when it is finished. It does not change what you're doing so far.
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[CVigil]

[Your second post today came in while I was typing this, so I'm still putting this one in. I just wanted to note that it started a little bit ago, immediatley after my first post today. -- Editor]

This is in a post separate from the first one I put up today, because the point I'm going to make is completely separated from those of that post.

Now that I see what your project is, fredk, and what all you are trying to do, or how you're going about it, you may not want to use constants like Dascit and so forth.

From what I see now, Dascit is a broker associated with one user or more, but not associated with all users. Your option group controls remain the same, but the labels change. (See the code you posted near the top of your second-most-recent posting of Dec 2, 2002.) So Label26 sometimes reads &quot;Dascit&quot; and sometimes reads &quot;Glenn&quot; and sometimes (presumably) reads other things. And Label26 is by a control (CheckBox, RadioButton, whatever), which sets the option group's value when it is selected/checked. That control *always* returns the same value -- you *could* change the control's option group value, but I don't see you doing that in code, so it always returns the same value. Let's say that the value returned is 567 (just a number pulled from thin air; it won't matter if you aren't using it for math; could be 1, could be -8, but this time, it's 567 ).

The single Select Case statement above can indeed tell if lngOptionGroupValue is equal to 567. But what then, fredk? How does your code distinguish between 567 meaning &quot;Dascit&quot; and 567 meaning &quot;Glenn&quot; and 567 meaning, say, &quot;Bubba Claus&quot; (my imaginary example &quot;other&quot; broker)???

So you have to be able to answer that question with something other than &quot;it doesn't&quot; . Eventually, anyway.

There are multiple possible approaches, as there almost always are.

You could set the OptionValue properties of the various controls in the option group at the same time you set the Caption properties of their labels. So that first control's OptionValue would be 1 when the label is &quot;Dascit&quot; and the control means the broker Dascit, and the constant Dascit would be equal to 1. And ... That first control's OptionValue would be 16 when the label is &quot;Glenn&quot; and the control means the broker Glenn, and the constant Glenn would be equal to 16. And so forth. That would let you safely use the scheme we've already come up with pretty well.

OR... You could leave the Option Values alone and have Select Cases inside of each Case of the main one. You'd have something like this:

With Forms![SACSpecific]
    Select Case .lngOptionGroupValue 
        Case 1
            Select Case .Label26
                Case "Dascit"
                     'DO STUFF
                Case "Glenn"
                     'DO STUFF
                '...Other Cases...
                Case Else
                     'DO STUFF
            End Select 'Case .Label26
        Case 2
            Select Case .Label28
                Case "TRA"
                     'DO STUFF
                Case "Scott"
                     'DO STUFF
                '...Other Cases...
                Case Else
                     'DO STUFF
            End Select 'Case .Label26
        '...Other Cases...
        Case Else
            'Handle it as you like
    End Select
End With

... But with this solution, you'd have to have all of your possibilities hard-coded in multiple places, that is, explicitly typed out in the code. And any changes would require you to change your code and re-compile ... not the prettiest way to do it, though it will work.

OR ... another choice, and probably one you won't want to mess with at this moment, if you need to get your project just working ... You could set up a table that lists every broker for each user, with a unique number assigned to each. When a user opens the form, the form could read the list of applicable brokers from that table, and *on the fly*, add controls to the option group for each broker, and set the properties for each control and its label. Then the form code could use values from the table in the Select Case evaluation; perhaps the table would even just contain the criteria you'd use, but I'd need to think it out a bit more. This would be a somewhat advanced task, mostly just requiring careful thinking about logic and layout of the created controls on the form.

In any case, since your values are going to be changing depending on the user, you need to take that into acount in your coding. Do you actually *understand* why I suggested using constants, and how your Select Case code relates to your form design? Do you understand how the Select Case statement works?

--------

Another idea I had last night, and just remembered this morning: What about using a ListBox or ComboBox, instead of an option group with CheckBoxes or similar controls? The ListBox portion of either control could have columns with broker names or whatever choices you offer. Then the code, instead of using code that chooses among options (like Select Case or If Then statements), could just loop through selected items in the list, building directly with the strings that are the first column of the list, adding to the WHERE clause or filter criteria as selected items are &quot;looped through&quot;.

In fact, the list's RecordSource could have *everyone's* brokers/choices, and simply be further filtered when you know whih user is accessing the form.

This is an approach, a strategy, not the complete solution, obviously .

Until the next letter... -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[fredk]

C Vigil - Thanks so much! I am understanding what you are saying - (fyi, I mistyped regarding where I put the constants - I first put them in the forms module, that did not work so I moved them to the reports module)

Also, its funny you mentioned the list box option because I found an example of that in Northwind - however, my problem is that I need the user to be able to run one of their brokers independently or run the report with all their brokers included in the report. That is what got me involved in this good stuff (and I mean that seriously, I have learned a lot from the interaction!)

You suggest:

******
You could set the OptionValue properties of the various controls in the option group at the same time you set the Caption properties of their labels. So that first control's OptionValue would be 1 when the label is &quot;Dascit&quot; and the control means the broker Dascit, and the constant Dascit would be equal to 1. And ... That first control's OptionValue would be 16 when the label is &quot;Glenn&quot; and the control means the broker Glenn, and the constant Glenn would be equal to 16. And so forth. That would let you safely use the scheme we've already come up with pretty well.
******
I like this scheme and if possible would like to use this - I can add the code to set the values of the option buttons depending on the user. After that, I need to walk through the process.

I fixed the form problem - I was using &quot;lngOptionGroupValue&quot; instead of the actual name of my option group - I got the report to run, I just need to add all the additional fields that are on the report into the sql.

Just so I have this correct, I can declare the constants using the above method... I will just change the option button values depending on who is logged into the form and then place ALL of the constants in the report module under option explicit - Am I on the right path?

Again, I cannot thank you enough!!!!

 

[fredk]

C Vigil - I got this up and running thanks to all of your help!!! The only thing I have a question about is when I want to see more than one broker - for example, if I want to see both fotek and walsh - I tried:

Select Case forms![SACSpecific].sacbrokerrun
Case Dascit:
strtest = &quot;(tbldata.[broker] = 'dascit')&quot;
Case tra:
strtest = &quot;(tbldata.[broker] = 'tra benefits')&quot;
Case mid:
strtest = &quot;(tbldata.[broker] = 'mid monmouth financial')&quot;
Case fotekwalsh:
strtest = &quot;(tbldata.[broker] = 'fotek') or (tbldata.[broker] = 'fotek')&quot;
Case directsouth:
strtest = &quot;(tbldata.[broker] = 'direct-south')&quot;
End Select


but I only get foteks ????

Again, I can't thank you enough!!!!!!

Fred

 

[fredk]

I got it C Vigil (just created a query and changed to sql to get the syntax.

Again, thanks a million for getting me from there to here!!!!

 

[CVigil]

GREAT! YIPPEE!

It was pleasant to come in after your three successive posts, to see you'd worked right through it! (My e-mail client did tell me that auto-notices had come in, saying that another post had been added to the thread, but I've been busy with *my* work for a while , so I only got to check just now. Heh heh, you got it, m'boy!)

Now, just so that a future reader understands how you fixed your last problem (let's be polite and also realise that someone else is going to read and use this eventually) ...

You wrote:
* * * * * *
for example, if I want to see both fotek and walsh - I tried:

 '[...] code commented out by CVIGIL[...]
 Case fotekwalsh:
       strtest = "(tbldata.[broker] = 'fotek') or (tbldata.[broker] = 'fotek')"
 '[...] code commented out by CVIGIL[...]

but I only get foteks ????
* * * * * * *

Since you got things working, I presume that you spotted the problem in your code. Hopefully the future reader just did, too, in my snipped version of your code. &quot;IF the broker is fotek or the broker is fotek&quot;, THEN the broker can only be fotek, right? And by extension, the broker can not then be walsh.

I assume you just copied and pasted in your code and got a bit ahead of yourself in forgetting to change the name. Believe me, any developer worth half his (or her) salt has done this at some time or another. Sometimes we just need fresh eyes or someone else's to spot our typos or oversights.

Anecodal reverie time, skip it if you want...
I remember spending six hours reviewing a PASCAL program listing in college, pages and pages long, trying to find the problem. That problem was a semi-colon at the end of the *entire program* instead of a period. I forget now if it was compilation that went wonko, but gave me messages telling me to check the wrong part of the code -- which, golly, sure looked right to me! -- or if it compiled (perhaps with conditional errors), but the running was funny. After six hours, someone else lookig at the code, and finding the actual problem at the end of the listing -- *** several pages down from where the *compiler* asid the problem was *** -- was ... a miracle ... frustrating as hell ... a huge relief ... incentive to buy the guy pizza and blow off the rest of the night ! But the point is that a) having been pointed in the wrong direction, b) knowing my own code and intended logic, and c) spending a lot of time staring at the same pattern of shapes (the code), I was constitutionally incapable of seeing the problem in my own program. That it wasn't where I was looking was irrelevant to my current point; after a certain point, I wouldn't have seen it if it *was* there where I was looking. -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[fredk]

Your right, its a good idea to sum it up ... I also go though the old posts and find them very helpful!- Again thanks for all of your help and PATIENCE!



Fred