Appending a Query

[fabby1]

Hi

I have a query that is used in different screens

What I want is to change the criteria using the following code

Private Sub cboUser_AfterUpdate()

Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset


Set qdf = CurrentDb.QueryDefs("MainDetailsQuery")
Set dbs_Current = CurrentDb


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"
            G_AStaffNo = "*"
            G_Director = "*"
            G_Region = Me.cboUser.Column(4)
            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_MASTER1.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()))"
            If G_AStaffNo = "100473" Then
               qdf.SQL = qdf.SQL & " AND Left([LOCATION],3) NOT Like ""BES"";"
            ElseIf G_AStaffNo = "100467" Then
               qdf.SQL = qdf.SQL & "AND left([LOCATION],3) Like ""BES"";"
            End If

        Case "BADM"
            G_AStaffNo = Me.cboUser.Column(2)
            G_Director = "*"
            G_Region = "*"
        End Select


Me.txtPassword.Visible = True
Me.txtPassword.SetFocus
End Sub

The problem Im having is that is DOESN'T qrite the chnages back into the query

how can I do this

Thanks

Phil

 

[PHV]

Have you tried CurrentDb.QueryDefs.Refresh ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244