Can I Use If In A Query?

[CharlesWhiteman]

My Db will be used by different companies plus a main company.

The main Table is called TblPrimaryData.
The Table will include enquiries and customers
Each company will enter their own enquiries and their own customers

Users view records in a form based on a query, based on TblPrimaryData

Is it possible to:

1) The main company can see all records
2) Company 2 can see only their enquiries and customers
3) Company 3 can see only their own enquiries and customers
4) Company 4 can only see customers

I'm wondering whether this can be done in the QBE window in the query that the main form is based upon?

Many thanks for any advice

 

[Galaxiom]

In queries use the Immediate If

IIF({condition}, {true value}, {false value})

 

[Galaxiom]

Well I answered the title but the question is another matter.

What you are trying to do should be done with a relationship between the user and the records.
Customers and Enquiries tables will have a company field.
The users table will have a company field.
A join between the current user's record in the User table and the company in the Customer or Enquiry table will limit the records returned.

However you want to give different rights to some users so use a join table.
This will allocate the type of records a user from a particualr group can access. There are a couple of ways it can be done.

Ideally, the join table will have multiple records with fields for UserCompany, RecordCompany and RecordType. RecordType would be normalised with a numerical code for the type of record, Customer or Enquiry (and no doubt later Report etc etc).

Main company users will have a record for each RecordCompany and RecordType so they can access all of them. Other will be allocated appropriately.

Your queries will be joined each way from the join table on the Company fields with a condition:
WHERE JoinTable.RecordType = {somecode that represents the type of query}.

Alternatively the different types of records can be allocated in separte fields of the join table. However this requires a new field for each new type of record. By using a RecordType field in the join table its structure needs never be adjusted.

 

[MStef]

Look at "DemomMainCompDataA2000.mdb" (attachment, zip).
Open Form1Main and try. Look at VBA.

 

[CharlesWhiteman]

Thanks for all your replies. Am now looking at them. Will post back accordingly. Many thanks.