filtering out informations on the form

[misdirection]

Hi,

I am a new member here and I would like to welcome myself here !

Anyways, to the point, I am having a hard time implementing some important features in Microsoft Access. I already created a form and all of the information is populated on the table. However, I am making a database for two branches, and I don't want both of the branches to see each other's information even if both are sharing the same database.

How do I filter out the information on the form? From my understanding, I will have to use the filter option on the property menu. So I will have to click "form" and then click "filter" and then I just enter the branch name that needs to be filtered out?

Filter on the property box -> "employee.Branch = 'McDonald'" did I do it correctly?

I understand the WHERE clause -> WHERE employee.Branch = "McDonald" (means only the employees from McDonald can see the form, not Pizzahut). Are you catching my drift? Please tell me how to filter out the information so only certain people can view information on the form, thank you.

I am not a expert on Microsoft Access but I do have vast of knowledge in mySQL by using DML and DDL statements; I am kind of upset on why the place I am working for doesn't use mySQL, or oracle for any database purpose. Sigh.. oh well.

 

[georgedwilkinson]

The way you handle this in Access is identical to the way both Oracle and MySQL recommend in their documentation. Use a view/query/sql statement that filters (via the where clause) the recordset based on the user's access level to the data. It's all about designing your tables to accomodate this, though, in all 3 cases.

However, I'm not sure that using Access' "filter" functionality is the right approach. You'll want to limit user's access directly in the recordset (via a view or query), just like you would using Java, Oracle Forms, or PHP.

Since this is not DML or DDL (rather, it is structured query language, aka "SQL"), I am assuming you haven't used straight "select" functionality. You'll need to understand how to do that to create a recordset.

Let us know if you need more help.

 

[misdirection]

I do know how to use the SELECT statement:

SELECT firstName,MiddleName,LastName
FROM tblEmployee
WHERE firstName = "Bob";

I took Introduction to Database and Relational Modeling at RIT (Information Technology).

I managed to filter out the information that a USER should see. On the property menu, I click "form" and then on the filter field, I typed in Branch = McDonald without using the quotes, and I turned on 'FilterOnLoad' and it works wonderfully, although she/he will have to manually input the branch name everytime she opens the form. I asked my boss regarding the security issue and she told me not to worry about it.

 

[georgedwilkinson]

I'm glad you got it working.

 

[misdirection]

I need some help..

SELECT tblResturant.FirstName, tblResturant.LastName FROM tblResturant
WHERE Branch = "McDonald" AND BranchAssigned= "PizzaHut"

This is just an example. I am trying to execute that query and then put that the information on the form without just showing the results. Is there a way to do that or do I have to do that programmaically via Visual Basic (rather not to.. but if I have to then ok...).

Let me know, thanks!

 

[misdirection]

nevermind, I got it. Thanks.

 

[georgedwilkinson]

nevermind, I got it. Thanks.

Excellent!

 

[misdirection]

One more thing

What is the SQL statement for negaation operator? NOT IN or what? I am not sure which negation operator M$ access use.

 

[misdirection]

!= or <> doesn't work - Microsoft Access sees <> as parameter.

 

[georgedwilkinson]

<> is equivalent to "not equal". I imagine you have a syntax problem if you cannot use it.

"Not" is a boolean operator that changes true to false and false to true.

Not (1 = 1)

would return "false".

Not (0 = 1)

would return "true".

"not in" is used just before a recordset (i.e. a list or a select statement). It is the boolean opposite of "in".

myvariable not in ('McDonald', 'Pizzahut')

would return true if myvariable was not equal to one of the two choices.