Ask Experts Questions for FREE Help !
Ask
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #1

    Aug 19, 2014, 05:18 AM
    Filter Combo box
    At the login form, I used DoCmd.ApplyFilter , "Department = 'Finance'"
    To restrict user view on the main form so that if the ID and password
    Match with the Finance department, the form will only preview the data
    From of Finace department's staff. I also put the combo box,FindNextRecord
    And FindPreviousRecord to make it become more user friendly. However, the
    Combo box doesn't filter the name and show the list of every staff from
    Different department. Is there is any way to filter the combo box so that
    It will only show the staff name from the Finance department


    Note: Every details of each staff are recorded in a single table.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Aug 19, 2014, 05:42 AM
    Not exactly clear at what you are doing. But to filter a combobox, you have to adjust the RowSource of the combo. So the WHERE clause in the RowSource would have to filter for the Finance Department.

    By the Way. I would not be storing the Department name. I would have a table of Departments with a code number for each. And that's what I would store. You can still have the user select from the Department name bu setting the width of the first column (the code) to 0.

    You might also find my blog on Login Security with VBA to be helpful.
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #3

    Aug 19, 2014, 06:23 AM
    Hello Scott, Thanks for your reply. I will briefly explain about my database project. Okay, here we go. Firstly, my first homepage is a login form.

    Private Sub Command17_Click()
    Text1.SetFocus
    If Text1 = "finance" And Text9 = "finance@199" Then
    MsgBox "Login succeed", vbInformation, "Finance Page"
    DoCmd.Close
    DoCmd.OpenForm "Finance Page"

    ElseIf Text1 = "hrm" And Text9 = "hrm@9" Then
    MsgBox "Login succeed", vbInformation, "HRM Page"
    DoCmd.Close
    DoCmd.OpenForm "HRM Page"



    Okay, let say we enter the ID and Password for Finance Department. Then, it will open the Finance Page. Here is another vba code

    Private Sub Command0_Click()
    DoCmd.OpenForm "PDP Database"
    DoCmd.ApplyFilter , "Department = 'Finance'"
    End Sub

    The "PDP Database" will be opened and it will only show the information for the Finance Department's staff. The reason I used DoCmd.ApplyFilter is because my "PDP Database" form is originated from a table which consist of every staff in my company. Therefore I used this command to limit the future user of this database. Basically the future user of this database will be the heads of department of every department and it is strictly prohibited to let them know about other's department information.

    Okay so lets get to the combo box thing. The reason I want to put the combo box is because my boss want the "PDP Database" form to become more user friendly so that by having the combo box is more easier to navigate the information from one staff to other within the same department. But the problem is, once I tested the database using the Finance's ID and password, the "PDP Database" form is perfectly filtered and it only show the details of Finance's staff. However the COMBO BOX (I used the staff name from the same table as the row source) still showing other staff from different department. So, what's your recommendation to filter the combo box. Is there is any way such as Me.Filter or DoCmd.Filter?

    By the way, I am really new in Microsoft access. Start using on Mac 2014.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Aug 19, 2014, 06:42 AM
    OK, a little more understandable. But I told you how to filter the combo. Your combobox should be based on a query as its RowSource, you need to add a WHERE clause to that query to filter for the department.

    I understand you are new to this, but I think you have made things more complex than need be.

    It looks to me like users are not logging in individually but by department, am I understanding that correctly?

    Also what does Text9 represent? (btw you should use descriptive names for your objects, it will help you (and others) follow the code.). That is command17 for?

    Also, you really should take a look at my blog (Creating Login Security using Access VBA | ScottGem's space). I think you will find it very helpful in your project.
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #5

    Aug 19, 2014, 07:09 AM
    Text 1 is for username and Text9 is for password. The RowSource of the ComboBox is SELECT [Full Database].[Name] FROM [Full Database]. Full Database is my 'master' table and basically it have every details of each of staff including their ID, Name, Department,Position, Section , etc.

    Yeah it seems that I have made thing become more complicated. Thanks for your link. I think I started to know a little bit about the Login Security in access
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Aug 19, 2014, 08:17 AM
    Ok, See if you had named the controls descriptively (i.e. txtUser and txtPassword) I wouldn't have had to ask. But you have some other issues here. First, having spaces in object names is not a good idea and can com back to haunt you. Name is a reserved word in Access and shouldn't be used for an object name. People names should be broken out into their components (first, last etc). You can always concatenate the names back together into a fullname.

    So here's how I would do it. So the RowSource of the combo should look like this:

    SELECT UserID, UserName FROM FullDatabase WHERE Department = Forms!formname!controlname;

    Forms!formname!controlname should point to a control on a form where you are storing the Department of the current user. In that way, the combo will be filtered only for those employees in that department.

    But I really think you should look at my blog and modify your app to use the techniques described.
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #7

    Aug 20, 2014, 05:16 AM
    Thanks Scott! Your blog is great. It is really helpful. Thanks again!
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #8

    Aug 20, 2014, 05:17 AM
    Glad to help.
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #9

    Aug 21, 2014, 09:18 PM
    Hello scott, one more question. Is there is any way to create a value for for a text box? Let say if someone fill in some words in the box, the value will become 1. And if the box is empty, the value is 0. Is it possible?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #10

    Aug 22, 2014, 02:58 AM
    Sure. The IsNull Function will return a -1 (True) if empty and 0 (False) if filled in. So you could use that. If you detail more about what you are trying to do, I can be more specific.
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #11

    Aug 22, 2014, 05:56 AM
    Lets assume that I have three Yes-No combo boxes. And I want Yes = 1 and No = 0. I named my first combo box as Combo1, second combo box as Combo2 and third combo box as Combo. These Yes-No combo boxes will represent whether an employee attends a particular training. So, I want Yes = 1 and No = 0. So, I created percentagebox1, percentagebox2 and percentagebox3 to represent the Yes-No value to 1 and 0 respectively. Below is my code and at the first glance I think I am in the right track.

    Private Sub Combo1_AfterUpdate()
    Call TextSource
    End Sub

    Private Sub Combo2_AfterUpdate()
    Call TextSource
    End Sub

    Private Sub Combo3_AfterUpdate()
    Call TextSource
    End Sub

    Private Sub TextSource()
    If Me!Combo3 = "Yes" Then
    Me!percentagebox3 = " 1"
    ElseIf Me!Combo3 = "No" Then
    Me!percentagebox3 = " 0"
    End If

    If Me!Combo2 = "Yes" Then
    Me.percentagebox2 = " 1"
    ElseIf Me!Combo2 = "No" Then
    Me!percentagebox2 = " 0"
    End If

    If Me!Combo1 = "Yes" Then
    Me!percentagebox1 = " 1"
    ElseIf Me!Combo1 = "No" Then
    Me!percentagebox1 = " 0"
    End If
    End Sub

    However, I need to count the total number of training attended by an employee and therefore, I created a text box and its control source is =([percentagebox1]+[percentagebox2]+[percentagebox3]).

    So, I tested my code by selecting all Yes for those three combo boxes and I am expecting the value will be 3 as Yes = 1. However, the value in the text box showed something like this. 1 1 1. Is there is any way to tackle this problem?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #12

    Aug 22, 2014, 06:35 AM
    The reason it showed as 1 1 1 is because you are assigned Text values (Me!percentagebox1 = " 1") instead of numeric values. But you are making this much more complicated than it needs to be, Plus there are problems with your structure.

    The correct structure here is to have a child table:

    tblEmpTraining
    EmpTrainingID (PK Autonumber)
    EmpID (FK)
    TrainingID (FK)
    CompletedDate.

    This also means a table of training like so:

    tblTraining
    TrainingID (PK Autonumber)
    TainingTitle
    TrainingDescription

    You enter the data using a subform where you select the Training title from a combobox and enter the date completed. You can then get a total of training for an employee by using a Dcount to count the number of records for an employee.

    Also, you should name your objects more descriptively. Using Combo1, Combo2 doesn't tell you what the objects are for.
    yaqimy's Avatar
    yaqimy Posts: 7, Reputation: 1
    New Member
     
    #13

    Aug 22, 2014, 07:04 AM
    Thanks Scott. Great solution! Thanks also for the previous solution for my login form problem. Learn a lot from your blog. Just read your latest post. My deepest condolences to you in your brother's passing.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Dynamic filling of second combo box on selection of first combo box topic [ 0 Answers ]

I want to make multiple combo boxes in a jsp page, when I select first combo box value then according to selected value in first combo box ,second combo box will be populated from the mysql database.ex.if I am selecteing state from first combo box then according to selected state in second box...

Mulitple Combo Box [ 3 Answers ]

I have seen before in a database a certain combo box. I am using MS Access 2000 and Visual Basic. This combo box had 5 choices, all these choices had a control source to a field called Initiatves; so when a user clicked a choice it was a new record in the initiative field in the tblMain. The...

Multiple Combo Box [ 2 Answers ]

I have seen before in a database a certain combo box. I am using MS Access 2000 and Visual Basic. This combo box had 5 choices, all these choices had a control source to a field called Initiatves; so when a user clicked a choice it was a new record in the initiative field in the tblMain. The...

Combo box/list box/drop down box [ 1 Answers ]

I have a question for you in regards to a list box in a query. I have a form that I am using and want to give the users some options on what years they want to pull data from. So I was thinking about creating a list box or drop down box that would display the available years, when they highlighted...

Combo box script [ 2 Answers ]

I was wondering if anyone has or knows where I could get a script for combo boxes that alows me to go about 6 levels in but select earlier if needed and also boxes only appears if previous box has been used.


View more questions Search