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.

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.

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.

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 (http://scottgem.wordpress.com/2010/01/12/creating-login-security-using-access-vba/)). I think you will find it very helpful in your project.

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

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.

Thanks Scott! Your blog is great. It is really helpful. Thanks again!

Glad to help.

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?

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.

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?

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.

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.