?
Solved

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

Posted on 2001-08-06
15
Medium Priority
?
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
15 Comments
 
LVL 4

Expert Comment

by:MacRena
ID: 6357815
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
ID: 6357832
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
ID: 6357845
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:MacRena
ID: 6357847
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
ID: 6359770
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
ID: 6360162
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
ID: 6360271
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
 
LVL 4

Expert Comment

by:MacRena
ID: 6360279
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
ID: 6360502
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
ID: 6360534
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
ID: 6360551
(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
ID: 6360568
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 600 total points
ID: 6360595
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
ID: 6368766
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
ID: 6368789
Good!  Very glad to help.
I'm in and out, but so are dozens of other experts.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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