Solved

*?*FILTER TABLE on 'Sub Form_Open' Event-VBA Code*?*

Posted on 2001-08-06
15
662 Views
Last Modified: 2012-05-04
I need to apply a TABLE/RECORDSET FILTER to

Me.Batch_AmtPaid = DSum("[PY_TTL]", "qryBATCH_OPEN_TTL")

Function during the Sub Form_Load Event.  The 'Filter'  Value(PAY_PER)  is determined by the PAY_PER value in the last record/row  of the Pay_BatchOpen  Table in the 'Sub Form_Open' Event?

I either get a Type Mismatch Err in the OpenRecordset when trying to execute the Code or the Nothing is executed - ie the FILTER is not implemented.

This would be analogous to a PARAMETER SELECT QUERY, except instead of the User entering the specific value when the Query is executed, it is automatically loaded from a VBA Code Variable.


SPECIFIC QUESTION:  How is a Filter property automatically  implemented from VBA Event Code and what does the Code look like, ie a specific example or detail?

=======================================================
EVENT CODE(Sub Form_Open)

Dim Db As Database
Set Db = CurrentDb()

Dim rsTblPay_BatchOpen As Recordset
Set rsTblPay_BatchOpen = Db.OpenRecordset("tblPay_BatchOpen", dbOpenDynaset)

Dim PP_Filter As Variant

rsTblPay_BatchOpen.MoveLast
PP_Filter = rsTblPay_BatchOpen!PAY_PER

'Me.Filter = PP_Filter

Dim rsTblTime_Cards As Recordset
Set rsTblTime_Cards = Db.OpenRecordset("Select * From tblTime_Cards Where PAY_PER =  ' PP_Filter' ", dbOpenDynaset)

0
Comment
Question by:mhotto
  • 11
  • 4
15 Comments
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Let me guess - you are using Access 2000 with MS DAO 3.6 Object Library checked in References.

The Type Mismatch error is occuring because you are Dimming a "Recordset" instead of a "DAO.Recordset".

Try this...

****************************


Dim Db As Database
Set Db = CurrentDb()

Dim rsTblPay_BatchOpen As DAO.Recordset
Set rsTblPay_BatchOpen = Db.OpenRecordset("tblPay_BatchOpen", dbOpenDynaset)

Dim PP_Filter As Variant

rsTblPay_BatchOpen.MoveLast
PP_Filter = rsTblPay_BatchOpen!PAY_PER

'Me.Filter = PP_Filter

Dim rsTblTime_Cards As DAO.Recordset
Set rsTblTime_Cards = Db.OpenRecordset("Select * From tblTime_Cards Where PAY_PER =  ' PP_Filter' ", dbOpenDynaset)

0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Hmmm, I'm a little lost...

qryBATCH_OPEN_TTL

What is that, the Form's RecordSource?

In that case you will want to redesign the SQL for the Query before the Form loads.

Try this...

*********************************************************
Private Sub Form_Open(Cancel As Integer)

          Dim strSQL As String

          strSQL = "Select [tblTime_Cards].* From [tblTime_Cards] Where PAY_PER =  '" &  PP_Filter & "'"

          CurrentDb.QueryDefs("qryBATCH_OPEN_TTL").SQL = strSQL

End Sub
*********************************************************

This should work.

I will put spaces in between the single and double quotes.

...Where PAY_PER =  '  " &  PP_Filter & "  '  "
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Here is the whole Open event.

*******************************************************

Dim Db As Database
Set Db = CurrentDb()

Dim rsTblPay_BatchOpen As DAO.Recordset
Set rsTblPay_BatchOpen = Db.OpenRecordset("tblPay_BatchOpen", dbOpenDynaset)

Dim PP_Filter As Variant

rsTblPay_BatchOpen.MoveLast
PP_Filter = rsTblPay_BatchOpen!PAY_PER

'Me.Filter = PP_Filter

Dim rsTblTime_Cards As DAO.Recordset
Set rsTblTime_Cards = Db.OpenRecordset("Select * From tblTime_Cards Where PAY_PER =  ' PP_Filter' ",
dbOpenDynaset)


--------

Private Sub Form_Open(Cancel As Integer)

          Dim strSQL As String
          Dim rsTblPay_BatchOpen As DAO.Recordset
          Set rsTblPay_BatchOpen = CurrentDb.OpenRecordset("tblPay_BatchOpen", dbOpenDynaset)
          Dim PP_Filter As Variant

          rsTblPay_BatchOpen.MoveLast
          PP_Filter = rsTblPay_BatchOpen!PAY_PER
 
         strSQL = "Select [tblTime_Cards].* From [tblTime_Cards] "
          strSQL = strSQL & "Where PAY_PER = '" &  PP_Filter & "'"
         CurrentDb.QueryDefs("qryBATCH_OPEN_TTL").SQL = strSQL
          rsTblPay_BatchOpen.Close
          Set rsTblPay_BatchOpen = Nothing

End Sub


*************************************************
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Oops, forgot to delete the scrap.

Let me repost that

********************************************************

Here is the whole Open event.

Private Sub Form_Open(Cancel As Integer)

         Dim strSQL As String
         Dim rsTblPay_BatchOpen As DAO.Recordset
         Set rsTblPay_BatchOpen = CurrentDb.OpenRecordset("tblPay_BatchOpen", dbOpenDynaset)
         Dim PP_Filter As Variant

         rsTblPay_BatchOpen.MoveLast
         PP_Filter = rsTblPay_BatchOpen!PAY_PER

        strSQL = "Select [tblTime_Cards].* From [tblTime_Cards] "
         strSQL = strSQL & "Where PAY_PER = '" &  PP_Filter & "'"
        CurrentDb.QueryDefs("qryBATCH_OPEN_TTL").SQL = strSQL
         rsTblPay_BatchOpen.Close
         Set rsTblPay_BatchOpen = Nothing

End Sub


*************************************************

(trying to talk on the phone while typing - sorry)
0
 

Author Comment

by:mhotto
Comment Utility
Mac,

Just  got into Work, though I did glance @ your solution  briefly last evening.  I will work on implementation, shortly.  The initial review of your solution is what I was hoping for. As a Note, for reference purposes - I am developing this application using ACCESS97 on a WIN 98 OS platform. (Production Ver will  reside on NT platform).

Thanks for your help, I'm sure I'll be getting back shortly.

Thanks

Mike
0
 

Author Comment

by:mhotto
Comment Utility
Mac,
 
Your Code in the Form Open Sub worked as advertised

However, in the Form Load Sub

this Function (which as been executing)

 Me.Batch_AmtPaid = DSum("[PY_TTL]", "qryBATCH_OPEN_TTL")

now produces this==> ErrMsg

RUN TIME Err: 64479  - The Expression you entered as a QUERY Parameter produced this Error: The Object doesn't contain the Automation Object  'PP_FILTER'.

I think it is becasuse I need to include some of the SQL CODE in the QUERYDEF in the Form Open Sub in the DSum Function?

Anyway, if you can help me fix that, the 150 pts are yours. Since the whole purpose of this Code is to get an Aggregate Sum Function for a Selected Pay Period.

Appreciate your help a lot.  Thanks Mike
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Hmmm, looks like Access isn't sure what's wrong and is spitting up an Automation error again.

What kind of field is tblTime_Cards!PAY_PER?

If it's a Date, then you need #s arount the Variant
strSQL = strSQL & "Where PAY_PER = #" &  PP_Filter & "#"
       
If it's a Number, then you don't need any quote
strSQL = strSQL & "Where PAY_PER = " &  PP_Filter

Are you sure it is the line
Me.Batch_AmtPaid = DSum("[PY_TTL]", "qryBATCH_OPEN_TTL")
that is breaking?  I would expect that in the Open event, not the Load.

Try this, using # or ' as applicable (see above)

Me.Batch_AmtPaid = DSum("[PY_TTL]", "[tblTime_Cards]", "PAY_PER = '" & PP_Filter & "'")

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:MacRena
Comment Utility
I'll put spaces in between the single-quote and the double-quote...

DSum("[PY_TTL]", "[tblTime_Cards]", "PAY_PER = ' " & PP_Filter & " ' ")
0
 

Author Comment

by:mhotto
Comment Utility
Mac,

The 'PP_FILTER'  variable  is a INTEGER - PAY PERIOD#: 1..26

PROGRAM SPECS:

OPEN FORM, CHECK LAST RECORD in FORM assoc  DATA TABLE(PAY PER).

 IF RECORD COMPLETE Then
      LOAD  NEW RECORD(PAY PER)  in FORM  Else LOAD LAST RECORD with SUMMED TABLE  DETAIL(TIME CARDS)  in Form txtCntrl  'BATCH_AMTPD'.

That is why the DSUM Function is in the LOAD EVENT, not the OPEN Event.  
Also, I want  the DSUM Function executed when ever the 'PAY PER' Form is Active.  Since the User must always 'EXIT' the application from the Batch Screen or Open the 'Detail' Form from the 'PAY PER' form.

I'll test your latest changes and get back to you.  Also, it's hard reading(old geezer eyes) to differentiate & Match Up  where the "   and the '  are when they are contigous.  So a Space btwn them helps.  However, I figured out the proper sequence by looking at the Strng  in Debug.

Thanks

Mike
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
No quotes are used for Integers!  Neither single or double

Me!Batch_AmtPaid = DSum("[PY_TTL]", "[tblTime_Cards]", "PAY_PER = " & PP_Filter)

Another thing you will need to do is Dim PP_Filter public for the whole Form to see (right below the line that says "Option Compare Database" at the top of the Code Page).  That way it is set in the Open event, but retains it's scope and value when the Load event executes.
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
(Of course when you Dim PP_Filter at the top as Public, you will not need to Dim it in the Open event)
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Hold it, forget that.
Taking the DSum from the tblTime_Cards will get ALL pay periods, not just the last one.


Well, then we need to keep the line
Me.Batch_AmtPaid = DSum("[PY_TTL]", "qryBATCH_OPEN_TTL")
and figure out why Access is breaking on that line - so let's step back....

You're sure it is breaking in the Load and not the Open?
0
 
LVL 4

Accepted Solution

by:
MacRena earned 150 total points
Comment Utility
So the Open event strSQL has to read...

strSQL = strSQL & "Where PAY_PER = " &  PP_Filter

There are no single quotes around PP_Filter


Here is the whole Open event (rewritten w/o the 's.

*****************************************************
Private Sub Form_Open(Cancel As Integer)

        Dim strSQL As String
        Dim rsTblPay_BatchOpen As DAO.Recordset
        Set rsTblPay_BatchOpen = CurrentDb.OpenRecordset("tblPay_BatchOpen", dbOpenDynaset)
        Dim PP_Filter As Variant

        rsTblPay_BatchOpen.MoveLast
        PP_Filter = rsTblPay_BatchOpen!PAY_PER

       strSQL = "Select [tblTime_Cards].* From [tblTime_Cards] "
        strSQL = strSQL & "Where PAY_PER = " &  PP_Filter
       CurrentDb.QueryDefs("qryBATCH_OPEN_TTL").SQL = strSQL
        rsTblPay_BatchOpen.Close
        Set rsTblPay_BatchOpen = Nothing

End Sub
0
 

Author Comment

by:mhotto
Comment Utility
Mac,  Thanks for your help.  Your solution was prompt, met the parameters of the problem, and worked.  Your timeliness and followup were excellent.  I was able to follow your Code and understand the details. Hope you are available again if I have another question.  Good Work.  Thank You Mike Otto
0
 
LVL 4

Expert Comment

by:MacRena
Comment Utility
Good!  Very glad to help.
I'm in and out, but so are dozens of other experts.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now