Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Question For: Will_Scarlet7 -- your query you wrote last night for me

Will

Yesterday you wrote this Query for me, and I wanted to talk to you about it... let me know when you get here!

Comment from will_scarlet7
Date: 04/11/2005 03:08PM PDT
 Comment  


ssilvi84,
I'm calling it quits for the night...

Here is a query that you might find useful in building your ComboBox:
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
SELECT tbl_Users.UserName, tbl_Reports.RptName, IsBitFlagSet([tbl_RptPermissions].[Permissions],[tbl_Reports].[ReportBit]) AS HasPermission, tbl_Reports.PKID
FROM tbl_Reports, tbl_RptPermissions INNER JOIN tbl_Users ON tbl_RptPermissions.UserID = tbl_Users.PKID
WHERE (((IsBitFlagSet([tbl_RptPermissions].[Permissions],[tbl_Reports].[ReportBit]))=-1))
ORDER BY tbl_Users.UserName;
▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓

    It uses a InnerJoin to link the tbl_Users and tbl_RptPermissions, and then a Cartesian join to link in th tbl_Reports, it then calls the "IsBitFlagSet" that I posted above to calculate a true or false field for each person and each report.

Have a good night!

Sam
0
ssilvi84
Asked:
ssilvi84
  • 26
  • 20
1 Solution
 
will_scarlet7Commented:
Here now... Anyone else is free to join in as well right?
0
 
ssilvi84Author Commented:
Of course Anyone is free --- I'll change topic name now that I have you on board!

To start:

Like I said via e-mail, Queries are not my strongsuit.  You have a slew of ▓ 's in that query.. what are they, what are they for, and what do I do with them?

0
 
ssilvi84Author Commented:
Can't change -- but yes anyone is welcome
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
will_scarlet7Commented:
Sorry about those... They are not part of the query. I put a line of ASCI characters before and after the query string to differentiate it from the rest of the post and
they were each converted to ▓ by EE...
0
 
ssilvi84Author Commented:
yeah I was very confused by that.. okay onto the query

Do you want me to use that as the rowsource for my combo?  I'm assuming yes.

I moved IsBitFlagSet function to my new form, and threw that query into my cmb box.  It returns with "Undefined function 'IsBitFlagSet' in expression"
0
 
will_scarlet7Commented:
Try moving the "IsBitFlagSet" function out of the form and into a seperate module, that way you can call it from anywhere and not have to duplicate it in your forms.

Then build the query first as a stand-alone query to see how it looks and if it returns something that could work for you in your form.
0
 
ssilvi84Author Commented:
Sam -

I just moved it into a seperate Module... can't believe I didn't think of that (why duplicate it 193024832 times in every form I'll need it? *sigh*)

I ran the Query in the cmb Box, and it returned in column 1 the username and column 2 the report... it shows every report for every username.  Great work.  

Now... I'm currently working out of a 1 user database.  When I combine all of my components into the central DB they want me to create, it's going to read the username (Environ function) automatically.  Given a username, how do I filter that cbo?  I'm guessing it will go in my query.. FILTER BY perhaps?
0
 
will_scarlet7Commented:
If you have a function in a module similar to this:

Function CurrUser() As String
    CurrUser = Environ("UserName")
End Function

You can call that as an added criteria in your query like this:

SELECT tbl_Users.UserName, tbl_Reports.RptName, IsBitFlagSet([tbl_RptPermissions].[Permissions],[tbl_Reports].[ReportBit]) AS HasPermission, tbl_Reports.PKID
FROM tbl_Reports, tbl_RptPermissions INNER JOIN tbl_Users ON tbl_RptPermissions.UserID = tbl_Users.PKID
WHERE (((tbl_Users.UserName)=CurrUser()) AND ((IsBitFlagSet([tbl_RptPermissions].[Permissions],[tbl_Reports].[ReportBit]))=-1))
ORDER BY tbl_Users.UserName;

*** Note the "WHERE (((tbl_Users.UserName)=CurrUser()) AND..."
0
 
ssilvi84Author Commented:
that's exactly what the function does :)

Thanks... I'll put that in and see what happens

note* I created a cbo box that references the username like we did in our last form.  I'll change the code accordingly...
0
 
ssilvi84Author Commented:
Okay same problem as before.. when I throw this in and call CurrUser, it bugs out if I have the function in form code... if I throw it in module, how do i ref my cbo box?
0
 
will_scarlet7Commented:
I think that in order for the query to find a function it must be a "Public Function" in an external module. Also you should try entering the function in the criteria with a
opening and closing parenthesis "CurrUser()" so that it is recognised as a function call...
0
 
ssilvi84Author Commented:
In the meantime, I just used the Environ("UserName") because I am logged in, and it just so happens I added my LAN ID here at work to my tbl_Users yesterday.

I'm able to populate my rowsource correctly, but when I try to select one of the items in my cbo, it doesn't allow me to select anything other then the first item in the list...

Any ideas?
0
 
ssilvi84Author Commented:
Okay so going back to that... if I call a Public Function in an external module, how do I reference the value of my combo box?
0
 
will_scarlet7Commented:
What happens when you select any of the records in your ComboBox?
0
 
ssilvi84Author Commented:
I guess in the end it really is a moot point.  I changed a username to the LAN Id for a coworker and then opened the dbase on her machine.. once done, when I opened that form it showed me the reports I assigned to her using frmPermissionSet.  I'm only asking for my own personal knowledge.. how to access properties of a control from an external module..
0
 
will_scarlet7Commented:
>> Okay so going back to that... if I call a Public Function in an external module, how do I reference the value of my combo box?

I'm not entirely clear on this... What value are you trying to refference when calling "CurrUser()"?
0
 
ssilvi84Author Commented:
I've no problem selecting an item in the username cbo box.. just the reports one.  
0
 
ssilvi84Author Commented:
the value of the UserName combo box... if I select a record in that cbo, I want to filter the other one by it...
0
 
will_scarlet7Commented:
   There are several ways to access Controls on a form from Functions inside of modules. If it is always the same form and control you can
use "Forms!YourFormName!YourControl"

    For example, you have a form called "frmSetPermissions" with a ComboBox called "cboUserName" and you want to read the value
into a variable in a function:

    myVariable = Forms!frmSetPermissions!cboUserName
0
 
ssilvi84Author Commented:
I tried that

Public Function CurrUser() As String
    CurrUser = Forms!frmReportsByUser!Combo2
End Function

Error 2186: This property isn't available in design view...
0
 
will_scarlet7Commented:
>> I've no problem selecting an item in the username cbo box.. just the reports one.

Does it show multiple items in the drop-down? What happens when you make a selection? When I first a test for to try the query I did not set the column widths
so it only showed the user name (column 1) in the combo whenever I made a selection. After I set the column widths to 0";2" it seemed to display properly...
0
 
will_scarlet7Commented:
>> Error 2186: This property isn't available in design view...

Sounds like you are trying to run the function while the form "frmReportsByUser" is in design view, and since it cannot read the value until the form is initialised
it is choking. Try switching the form to "Form View" (Normal View) and see if the function works...
0
 
ssilvi84Author Commented:
yes, it shows multiple items in the drop-down.  The first report queried for my LAN ID is Gen Rpt.. When I try and select anything else, the drop down closes, but Gen Rpt doesn't change at all.

I did the same thing in regards to the columns.  I was seeing only user name, i changed to 2 columns and set widths to  0";2" respectively.. It displays properly, but I cannot select anything else..  It is enabled, but not locked..
0
 
ssilvi84Author Commented:
The error occurs when switching from design to form...

When I open directly in form, it doesn't bug, but i get the following error

"Invalid Use of Null"

I've seen this before, and fixed it, but I don't know what to do in this particular case.. am i just missing something easy?
0
 
ssilvi84Author Commented:
Hey pal~

I'm going to run to the grocery store and pick up some gatorade for me at the office (might grab lunch too).. i'll be back inside a half an hour... Only reason i'm leaving now is cuz i'm stuck on this damn drop down!
0
 
ssilvi84Author Commented:
k i'm back
0
 
will_scarlet7Commented:
I'm taking off for a bit, but I think this might solve the issue.

    Function CurrUser() As String
        If SysCmd(acSysCmdGetObjectState, acForm, "frmReportsByUser") = 3 _
            And Not IsNull(Forms!frmReportsByUser!Combo2) Then
            CurrUser = Forms!Forms!frmReportsByUser!Combo2
        Else
            CurrUser = Environ("UserName")
        End If
    End Function

This checks first to see if the form is open and has a valu in the UserName Combo Box, and if not returns "". You then would need to add
an "After Update" even to your User Name ComboBox to requery the Reports Combo to match the current User. Something like this:

    Private Sub Combo2_AfterUpdate()
        Me!cboReports.Requery
    End Sub

0
 
ssilvi84Author Commented:
It still only runs with Environ("Username")

What I did was to send the value of the cbo box by using .Column(1) to my CurrUser function, i.e. CurrUser(str As String).  I was able to successfully pull the value into my function, but when I ran the query, it said "invalid # of arguments".. I understand that, because when i call the function in my query, I'm not sending it the data it needs.  Any way to do this via the query?

Scott
0
 
ssilvi84Author Commented:
Like I said, question is really irrelevent because I will just tie it to Environ("Username") when the DB is live.

I'm still curious about why I cannot select what I need...

I will award you 500 pts and an A though for helping me with the query.. it wasn't part of my question yesterday, so you've more then earned them...
0
 
will_scarlet7Commented:
Sorry... I think I used the wrong Object State in the code above. Try this:

Function CurrUser() As String
    If SysCmd(acSysCmdGetObjectState, acForm, "Form2") = 1 _
        And Not IsNull(Forms!Form2!cboUserName) Then
        CurrUser = Forms!Form2!cboUserName.Column(1)
    Else
        CurrUser = Environ("UserName")
    End If
End Function

(State 1 should be when the form is Open)
0
 
will_scarlet7Commented:
Correction...
State 1 should be when the form is Open & any changes have been saved. State 3 is when changes have been made, but not yet saved.
0
 
ssilvi84Author Commented:
Even when I do that, it still jumps straight to the Else statement
0
 
will_scarlet7Commented:
Try putting a breakpoint at the "If" line, then when the code pauses type the following into the "Imediate" window:

? SysCmd(acSysCmdGetObjectState, acForm, "Form2")

And see what the return is...
0
 
ssilvi84Author Commented:
0

0
 
will_scarlet7Commented:
Oops... The form name is my test form "Form2" which is likely to never evaluate to 1 on your computer. My bad...
You need to modify all the lines bely that I marked with '<<-- to reflect your form's name and combo boxes...

Function CurrUser() As String
    If SysCmd(acSysCmdGetObjectState, acForm, "Form2") = 1 _  '<<-- Replace "Form2"
        And Not IsNull(Forms!Form2!cboUserName) Then                '<<-- Replace "Form2" & "cboUserName"
        CurrUser = Forms!Form2!cboUserName.Column(1)              '<<-- Replace "Form2" & "cboUserName.Column(1)"
    Else
        CurrUser = Environ("UserName")
    End If
End Function
0
 
ssilvi84Author Commented:
Any way for me to create another thread to give you some more points?

I believe the phrase "You're the man" is getting overkill
0
 
ssilvi84Author Commented:
By the way... jump over to:

http://www.experts-exchange.com/Databases/MS_Access/Q_21386020.html

For some more points.. directly relates to what we worked on earlier
0
 
will_scarlet7Commented:
You could open another thread to give points for the "CurrUser" function, but you might want to check with the Comunity Support TA to see
if it is Kosher or not. No worries if not. Just glad to finaly get it working for you!

God bless!

Sam
0
 
ssilvi84Author Commented:
You too sam..

How do i check with comm support TA?
0
 
will_scarlet7Commented:
I gotta go out for an hour, but if it is not solved when I get back I will upload my test DB and see if that can help.
0
 
ssilvi84Author Commented:
alright its waiting for you :) No one took the bait
0
 
will_scarlet7Commented:
>> How do i check with comm support TA?
Post a question here:
    http://www.experts-exchange.com/Community_Support/

I also modified the code for both the CurrUser and the Combo2 After Update events to make them more reliable. I can post the new code here if you are interested...
0
 
ssilvi84Author Commented:
You mean, make something more reliable? lol yeah I think so...

I'll contact Comm support now..
0
 
will_scarlet7Commented:
This method I believe will be more reliable in determining if the form is actually loaded or not (don't forget to mdify the code for your Form's
and Control's names):

    Function CurrUser() As String
        If CurrentProject.AllForms("Form2").IsLoaded Then               '<<-- Replace "Form2"
            If CurrentProject.AllForms("Form2").CurrentView = 1 _     '<<-- Replace "Form2"
            And Not IsNull(Forms!Form2!cboUserName) Then             '<<-- Replace "Form2" & cboUserName
                CurrUser = Forms!Form2!cboUserName.Column(1)       '<<-- Replace "Form2" & cboUserName.Column(1)
            Else
                CurrUser = Environ("UserName")
            End If
        Else
            CurrUser = Environ("UserName")
        End If
    End Function
0
 
ssilvi84Author Commented:
thanks...

I contacted Comm for you, waiting to hear back.  God Bless to you too, thanks again for everything you did!

<note> my boss LOVES me =) </note>
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>By the way... jump over to ... some more points..
Asking will_scarlet 'What VBA code causes Access to make a sound' works to..

<inside joke>

wb Will
0
 
will_scarlet7Commented:
;-)

Hi Jim,
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 26
  • 20
Tackle projects and never again get stuck behind a technical roadblock.
Join Now