Query/Form Good Practice

[PhillipsAndrew]

Here's one,

I have two related tables in my database. They contain the names of departments, who is in those departments and whether they have the relevant authorisation to approve orders etc..
The database deals with orders, so in several places I need to check if the person logged into the database belongs to the correct department and has the relevant authorisation.

I have no problem designing queries on this, using information from the form as the criteria eg [Forms]![Choose Bid List]![Directorate] etc..

My question is this:
Since I need this information on several forms, how do I avoid having lots of different queries to match each form?
Is there a way that I can have just one query and dynamically change the form name used in its criteria?

OR:
Shoud I re-write the query in a module, so not have a query at all but use Recordsets etc. to 'manually' extract the info I need. This way I have reusable code, able to be called from anywhere in the database.

Which way is best?

Thanks
P

 

[Mile-O]

Is there a way that I can have just one query and dynamically change the form name used in its criteria?

Create a public variable called, for example, strMyForm.

i.e.

Public strMyForm As String

On each form's OnLoad event, assign the form's name to the string variable.

Private Sub Form_Load()
    strMyForm = Me.Name
End Sub

In a module, put this public funtion:

Public Function GetFormName() As String
    If ((IsNull(strMyForm)) Or (strMyForm = vbNullstring)) Then Exit Function
    GetFormName = strMyForm
End Function

In the query, where you have the form's allowed field, put the criteria:

GetFormName()

 

[PhillipsAndrew]

Thanks... I should have thought of that!

Is that considered better technique than coding everything?

Thanks
P

 

[Mile-O]

I don't know but I would rarely code and execute a query - it adds to database bloat.

At least this way you have a pre-built query...



...and its quick and simple.