Wrapper Function 1

[fabby1]

Hi

Im using a wrapper function in a query to hopefully choose between two different events.

I set a global variable using

Private Sub cboUser_AfterUpdate()

G_Surname = Me.cboUser.Column(0)
G_Forename = Me.cboUser.Column(1)
G_AStaffNo = Me.cboUser.Column(2)
G_Password = Me.cboUser.Column(3)
G_Region = Me.cboUser.Column(4)
G_Regiontxt = Me.cboUser.Column(5)
G_Director = Me.cboUser.Column(6)
G_Location = Me.cboUser.Column(7)

Select Case G_Level
        Case "DIRC"
            G_AStaffNo = "*"
            G_Region = "*"
            G_Director = Me.cboUser.Column(6)
        Case "BMAN"
            If G_AStaffNo = "100473" Then
               G_Location = "Like BES"
            ElseIf G_AStaffNo = "100467" Then
               G_Location = "Not Like BES"
            End If
            G_AStaffNo = "*"
            G_Director = "*"
            G_Region = Me.cboUser.Column(4)
        Case "BADM"
            G_AStaffNo = Me.cboUser.Column(2)
            G_Director = "*"
            G_Region = "*"
        End Select
 
Me.txtPassword.Visible = True
Me.txtPassword.SetFocus
End Sub

What it does is this.

If the user is a BMAN (Branch Manager) and the user is eather one of two users I want to set a wrapper function to show certain values.

So I set G_LOCATION here, this works OK.

I then have a query

SELECT dbo_MASTER1.STATUS, Left([LOCATION],3) AS LOC, dbo_MASTER1.FIRSTNAME, dbo_MASTER1.STAFFNO, dbo_MASTER1.SURNAME
FROM ((dbo_MASTER1 LEFT JOIN dbo_EXITINTERVIEW ON dbo_MASTER1.STAFFNO = dbo_EXITINTERVIEW.STAFFNO) LEFT JOIN dbo_CURRENTPAY ON dbo_MASTER1.STAFFNO = dbo_CURRENTPAY.STAFFNO) LEFT JOIN dbo_CONTACTS ON dbo_MASTER1.STAFFNO = dbo_CONTACTS.STAFFNO
WHERE (((dbo_MASTER1.STATUS)="Active") AND ((Left([LOCATION],3))=Location()));

That calls the following wrapper function LOCATION()

This is LOCATION()

Public Function LOCATION() As String
   LOCATION = G_Location
End Function

What I want this to do is set the query to either use LIKE or NOT LIKE, can this be done this way ?

 

[fabby1]

HI Can anybody help me

Im desperate

 

[mordja]

fabby1,

Im assuming that you have built the query through the query design menu. As you are deciding elements of the sql string using VBA you might as well do the query execution using ADO or DAO. Something like


If G_AStaffNo = "100473" Then
SQLStr = SQLStr & "Where Location Like BES"
ElseIf G_AStaffNo = "100467" Then
SQLStr = SQLStr & "Where Location Not Like BES"
End If


However if you want to keep the query as a query object rather than something you can create on the fly, you could modify its SQL string using something similar to the above and then set it below.
A QueryDef object has an SQL string attribute which you could modify.

DAO Something like

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("YourQueryName")
qdf.SQL = SQLStr

Alternatively ADO

Dim cmd As ADODB.Command
Dim cat As New ADOX.Catalog

cat.ActiveConnection = CurrentDb.Connection

Set cmd = cat.Procedures("YourQuery").Command
cmd.CommandText = SQLStr
Set cat.Procedures("YourQuery").Command = cmd



Hope this helps

MOrdja

 

[fabby1]

MOrdja

Thanks for the post.

Yes I have built a query in design view & would prefer to keep that as I have several screens that use the same query.

I'm not sure how to use what you have suiggested in my problem.

If G_AStaffNo = "100473" Then
SQLStr = SQLStr & "Where Location Like BES"
ElseIf G_AStaffNo = "100467" Then
SQLStr = SQLStr & "Where Location Not Like BES"
End If

I understand what you are doing here, how do I use that in my query or wrapper function

Sorry for being a donkey

PHil

 

[mordja]

fabby1,

Start by opening your querydef.


Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryName")


Where "YourQueryName" is the name of your query.
Next build your queryDef sql string from scratch excluding the where clause part involving location.


qdf.SQL = "SELECT blah blah blah " _
& "blah blah" _
& "WHERE ((dbo_MASTER1.STATUS)=""Active"") AND " _


Now in your case statement


If G_AStaffNo = "100473" Then
qdf.sql = qdf.sql & "Where Location Like ""BES"";"
ElseIf G_AStaffNo = "100467" Then
qdf.sql = qdf.sql & "Where Location Not Like ""BES"";"
End If


Now the underlying SQL of your query will have been modified. For the above to work you need to to add the Microsoft Data Access Objects Library (Tools - References)

Last do

Set qdf = Nothing


Mordja

 

[fabby1]

Thanks

I think I've got that now, thanks for you patience

Cheers

Phil

 

[fabby1]

Mordja

Thought I had it.

I'm OK with the first part it's this bit Im struggling with

Now the underlying SQL of your query will have been modified. For the above to work you need to to add the Microsoft Data Access Objects Library (Tools - References)

Last do

Set qdf = Nothing

Sorry about this.

Phil

 

[mordja]

In the VBA editor window, go to tools - References and select(tick) Microsoft DAO 3.6 Object Library.

Set qdf = Nothing

just gets rid of the QueryDef object. Include that line of code and the end of your after update function.

You can test if it has worked by opening the query in the query design window and selecting the SQL view.

Mordja

 

[fabby1]

Mordja

Nearly there I understand what you are trying to do now, Only problem I have it when I try and append the string to the SQL it comes back with

   error '3142'

   Character found after end of SQL statement

this is the current Query

?qdf.SQL
SELECT dbo_MASTER1.STATUS, dbo_MASTER1.STAFFNO, dbo_MASTER1.TITLE, dbo_MASTER1.SURNAME, dbo_MASTER1.FIRSTNAME, dbo_MASTER1.KNOWNAS, dbo_MASTER1.ADDRESS1, dbo_MASTER1.ADDRESS2, dbo_MASTER1.ADDRESS3, dbo_MASTER1.ADDRESS4, dbo_MASTER1.ADDRESS5, dbo_MASTER1.POSTCODE, dbo_MASTER1.HOMETELEPHONE, dbo_MASTER1.WORKTELEPHONE, dbo_MASTER1.MOBILETELEPHONE, dbo_MASTER1.GENDER, dbo_MASTER1.ETHNIC, dbo_MASTER1.ACCOUNT, dbo_MASTER1.REGION, dbo_MASTER1.DEPARTMENT, dbo_MASTER1.REPORTSTO, dbo_MASTER1.JOBTITLE, dbo_MASTER1.CURRENTJOBCLASS, dbo_CURRENTPAY.PAY, dbo_CURRENTPAY.PAYPERIOD, dbo_MASTER1.RATE1, dbo_MASTER1.RATE2, dbo_MASTER1.RATE3, dbo_MASTER1.RATE4, dbo_MASTER1.RATE5, dbo_MASTER1.RATE6, dbo_MASTER1.RATE7, dbo_MASTER1.RATEBO, dbo_MASTER1.DATEOFJOIN, dbo_MASTER1.CONTINUOUSDOJ, dbo_MASTER1.CONTINUOUSLENGTHOFSERVICE, dbo_MASTER1.DOB, dbo_MASTER1.WORKTIME, dbo_MASTER1.CONTRACTTYPE, dbo_MASTER1.NOTICEPERIOD, dbo_MASTER1.RETIREMENT, dbo_MASTER1.NINUMBER, dbo_MASTER1.LIFECOVER, dbo_MASTER1.PENSION, dbo_MASTER1.PENCON, dbo_MAS
TER1.PENCONER, dbo_MASTER1.HCSCHEME, dbo_MASTER1.PROVIDER, dbo_MASTER1.REGNUMBER, dbo_MASTER1.MODELVEH, dbo_MASTER1.MAKEVEH, dbo_MASTER1.CARALLOW, dbo_MASTER1.CARALLDATE, dbo_EXITINTERVIEW.DATELEAVE, dbo_EXITINTERVIEW.LEAVECATEGORY, dbo_EXITINTERVIEW.LEAVEREASON, dbo_EXITINTERVIEW.APPLOAN, dbo_EXITINTERVIEW.TRAINLOAN, dbo_EXITINTERVIEW.FLOATPAY, dbo_EXITINTERVIEW.KEYHOLD, dbo_EXITINTERVIEW.LAPTOP, dbo_EXITINTERVIEW.LAPSER, dbo_EXITINTERVIEW.MOBILE, dbo_EXITINTERVIEW.CAMERA, dbo_CONTACTS.ADDRESS, dbo_CONTACTS.CONTACTTYPE, dbo_CONTACTS.DAYTELEPHONE, dbo_CONTACTS.DAYTELEPHONE1, dbo_CONTACTS.EVENINGTELEPHONE, dbo_CONTACTS.NAME, dbo_CONTACTS.NAME1, dbo_CONTACTS.RELATIONSHIP, Left([BRANCHADMIN],6) AS Admin, dbo_MASTER1.DC, dbo_MASTER1.REGCODE
FROM ((dbo_MASTER1 LEFT JOIN dbo_EXITINTERVIEW ON dbo_MASTER1.STAFFNO=dbo_EXITINTERVIEW.STAFFNO) LEFT JOIN dbo_CURRENTPAY ON dbo_MASTER1.STAFFNO=dbo_CURRENTPAY.STAFFNO) LEFT JOIN dbo_CONTACTS ON dbo_MASTER1.STAFFNO=dbo_CONTACTS.STAFFNO
WHERE (((dbo_MASTER1.STATUS)="Active") AND ((Left([BRANCHADMIN],6)) Like BranchAdmin()) AND ((dbo_MASTER1.DC) Like Director()) AND ((dbo_MASTER1.REGCODE) Like BranchManager()));

heres the select statement

Select Case G_Level
        Case "DIRC"
            G_AStaffNo = "*"
            G_Region = "*"
            G_Director = Me.cboUser.Column(6)
        Case "BMAN"
            If G_AStaffNo = "100473" Then
               qdf.SQL = qdf.SQL & "AND LOCATION Like ""BES"";"
            ElseIf G_AStaffNo = "100467" Then
               qdf.SQL = qdf.SQL & "AND LOCATION Not Like ""BES"";"
            End If

If I try and re-create it as your suggested it say that it has multiple joins

   error '3296'
   Join expression not supported

I think I just need to take the ; from the end of the current stored procedure is there away of doing this

Thanks

Phil

 

[fabby1]

Mordja

Got that sorted


One last thing, I think I need to append the changed query back to the query definitions how to I do this

Phil

 

[mordja]

Phil,

If you are doing as I said, then by deafault your are changing the SQL of the query definition.

Why dont you do a simple test for yourself to see how it all works. Create a query with nothing in it through the query design window and save it say as Temp.

Create a button on a form with the code

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("Temp")
qdf.SQL = "SELECT * From MyTable"
Set qdf = Nothing


In the above substitute MyTable with the name of any table in your DB. Click on the button and run the code.

Now go and have a look at Temp, even run it.

Mordja