Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA code to apply a filter that is loaded from a query during the Form's open event property

Posted on 2009-05-14
26
Medium Priority
?
418 Views
Last Modified: 2013-11-28
Could someone please guide me on the VBA syntax to load a query that then filters records when my form opens?  Right now I have to double click the form to open and then manually load this query from the "Records" menu in the toolbar and then apply this query as the filter to my form.  I am basically trying to automate this task so that when the form opens, the query is auto. loaded and applied as the filter.  Thank you.
0
Comment
Question by:sxxgupta
  • 18
  • 8
26 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24385301
did you try using the query as the Record Source of your form?

private sub form_open(cancel as integer)
me.recordsource="nameOfquery"

end sub
0
 

Author Comment

by:sxxgupta
ID: 24385479
I'll give it a try.
0
 

Author Comment

by:sxxgupta
ID: 24385531
I only get one blank record.  The reason why I would like to filter using the query is that the query and the form each are linked to one master table.  Only until the records are filtered on the form, using the query, I then enter additional data in the fields, on the form, that are linked to the same table.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24385569
can you post the sql of the query
0
 

Author Comment

by:sxxgupta
ID: 24385606
SELECT *
FROM iitdata
WHERE (((iitdata.NMDF) Is Null) AND ((iitdata.IITDate) Between [Forms]![z filter].[BeginDate] And [Forms]![z filter].[EndDate]) AND ((iitdata.LIFECORPartNo) Like [Forms]![z filter].[PartNumber] & "*" And (iitdata.LIFECORPartNo) Not Like "20A*" And (iitdata.LIFECORPartNo) Not Like "20B*") AND ((iitdata.SupplierName) Like [Forms]![z filter].[SupplierName] & "*") AND ((iitdata.QtyRecd)>0) AND ((iitdata.QtyInsp)>=0) AND ((iitdata.QtyAcpt)>=0) AND ((iitdata.QtyRej)>0)) OR (((iitdata.NMDF) Is Null) AND ((iitdata.IITDate) Between [Forms]![z filter].[BeginDate] And [Forms]![z filter].[EndDate]) AND ((iitdata.LIFECORPartNo) Like [Forms]![z filter].[PartNumber] & "*" And (iitdata.LIFECORPartNo) Not Like "20A*" And (iitdata.LIFECORPartNo) Not Like "20B*") AND ((iitdata.SupplierName) Like [Forms]![z filter].[SupplierName] & "*") AND ((iitdata.QtyRecd)>0) AND ((iitdata.QtyInsp)>=0) AND ((iitdata.QtyAcpt)>=0) AND ((iitdata.QtyRej)=0) AND ((iitdata.AdjQtyRej) Is Null)) OR (((iitdata.NMDF) Is Null Or (iitdata.NMDF) Is Not Null) AND ((iitdata.IITDate) Between [Forms]![z filter].[BeginDate] And [Forms]![z filter].[EndDate]) AND ((iitdata.LIFECORPartNo) Like [Forms]![z filter].[PartNumber] & "*" And (iitdata.LIFECORPartNo) Not Like "20A*" And (iitdata.LIFECORPartNo) Not Like "20B*") AND ((iitdata.SupplierName) Like [Forms]![z filter].[SupplierName] & "*") AND ((iitdata.QtyRecd)>0 And ((iitdata.QtyRecd)<>[QtyAcpt] Or (iitdata.QtyRecd)<>[InvQty])) AND ((iitdata.QtyInsp)>=0) AND ((iitdata.QtyAcpt)=0 Or (iitdata.QtyAcpt)<=[QtyRecd]) AND ((iitdata.QtyRej)=0) AND ((iitdata.AdjQtyRej)=0) AND ((iitdata.InvQty)>0)) OR (((iitdata.NMDF) Is Null Or (iitdata.NMDF) Is Not Null) AND ((iitdata.IITDate) Between [Forms]![z filter].[BeginDate] And [Forms]![z filter].[EndDate]) AND ((iitdata.LIFECORPartNo) Like [Forms]![z filter].[PartNumber] & "*" And (iitdata.LIFECORPartNo) Not Like "20A*" And (iitdata.LIFECORPartNo) Not Like "20B*") AND ((iitdata.SupplierName) Like [Forms]![z filter].[SupplierName] & "*") AND ((iitdata.QtyRecd)>0 And ((iitdata.QtyRecd)<([QtyAcpt]+[QtyRej]) Or (iitdata.QtyRecd)>([InvQty]+[QtyRej]))) AND ((iitdata.QtyRej)>=0) AND ((iitdata.InvQty)>0 And (iitdata.InvQty)<>[QtyAcpt])) OR (((iitdata.NMDF) Is Null) AND ((iitdata.IITDate) Between [Forms]![z filter].[BeginDate] And [Forms]![z filter].[EndDate]) AND ((iitdata.LIFECORPartNo) Like "20A*" Or (iitdata.LIFECORPartNo) Like "20B*") AND ((iitdata.SupplierName) Like [Forms]![z filter].[SupplierName] & "*") AND ((iitdata.QtyRecd)>0) AND ((iitdata.QtyRej)<>0) AND ((iitdata.InvQty)<>([QtyAcpt]-1))) OR (((iitdata.NMDF) Is Null) AND ((iitdata.IITDate) Between [Forms]![z filter].[BeginDate] And [Forms]![z filter].[EndDate]) AND ((iitdata.LIFECORPartNo) Like "20A*" Or (iitdata.LIFECORPartNo) Like "20B*") AND ((iitdata.SupplierName) Like [Forms]![z filter].[SupplierName] & "*") AND ((iitdata.QtyRecd)>0) AND ((iitdata.QtyInsp)>=0) AND ((iitdata.QtyAcpt)>=0) AND ((iitdata.AdjQtyRej) Is Null))
ORDER BY iitdata.IITReportNo;
I was thinking
docmd. load query perhaps
docmd. apply filter perhaps (where the filter is the name of the query?)

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24385745
and what is the name of the form that you are trying to filter?
0
 

Author Comment

by:sxxgupta
ID: 24386127
The form is called "z supplier qty adjustment"
The query is called "z supplier qty adjustment filter"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24386733
to summarize

Form [z filter] is open, correct?
is it on this form, [z filter] that you open the form  "z supplier qty adjustment"  via a command button click?

0
 

Author Comment

by:sxxgupta
ID: 24386919
yes.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24387057
try doing something like this in the click event of the button

private sub Button_Click()
dim sFilter as string,ssql as string
dim qd as dao.queryDef
set qd=currentdb.querydef("z supplier qty adjustment filter")

ssql=qd.sql
ssql=left(ssql,Instr(ssql,"Order By")-1)

sFilter=trim(mid(ssql,instr(ssql,"Where")+6))

'for testing
debug.print sFilter

qd.close
end sub


look in the immediate window and see what was  printed, we'll see if we have the correct filter







0
 

Author Comment

by:sxxgupta
ID: 24387251
sorry, I get a compile error when I click the button.  Error in on the "dim qd as dao.queryDef" program line "user-defined type not defined."
0
 

Author Comment

by:sxxgupta
ID: 24387420
I set my object library and corrected the line "set qd=currentdb.querydef("z supplier qty adjustment filter")" to set qd=currentdb.querydefs("z supplier qty adjustment filter").  querydefs was missing a s.  Everything appears to run now, but I don't see an "immediate window".
0
 

Author Comment

by:sxxgupta
ID: 24387434
sorry.  There is an immediate window.
0
 

Author Comment

by:sxxgupta
ID: 24387439
The filter seems to be there.  I will try it and let you know.
0
 

Author Comment

by:sxxgupta
ID: 24387613
Hi capricorn:
This is exactly what I have:

Private Sub Form_Open(Cancel As Integer)
Dim sFilter As String, ssql As String
Dim qd As DAO.QueryDef
DoCmd.Maximize

Set qd = CurrentDb.QueryDefs("z supplier qty adjustment filter")
ssql = qd.sql
ssql = Left(ssql, InStr(ssql, "Order By") - 1)
sFilter = Trim(Mid(ssql, InStr(ssql, "Where") + 6))
'for testing
    Debug.Print sFilter
    qd.Close
End Sub
Everything appears to work without any compile errors.  The filter appears in the immediate window, but when I view the form, I still get one "blank" record.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24387654
we havent applied the filter yet to the form...

lets try this

Private Sub Form_Open(Cancel As Integer)
Dim sFilter As String, ssql As String
Dim qd As DAO.QueryDef
DoCmd.Maximize

Set qd = CurrentDb.QueryDefs("z supplier qty adjustment filter")
ssql = qd.sql
ssql = Left(ssql, InStr(ssql, "Order By") - 1)
sFilter = Trim(Mid(ssql, InStr(ssql, "Where") + 6))
'for testing
'   Debug.Print sFilter
   qd.Close

me.filter=sfilter
me.filteron=true
End Sub
0
 

Author Comment

by:sxxgupta
ID: 24387895
Yes.  Sorry about jumping ahead.  Use the code above to apply the filter and now get this:
Runtime error 2176 "The setting for this property is too long"
Debug points to the code line:     Me.filter = sFilter
0
 

Author Comment

by:sxxgupta
ID: 24387910
I am assuming I have to "suppress" this error with code............
0
 

Author Comment

by:sxxgupta
ID: 24394725
Good morning Capricorn:
It seems that your solution is almost there.  I keep getting the
Runtime error 2176 "The setting for this property is too long"
Debug points to the code line:     Me.filter = sFilter
0
 

Author Comment

by:sxxgupta
ID: 24397100
Anyone???
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24397428
try this, see if this eliminate the error

Set qd = CurrentDb.QueryDefs("z supplier qty adjustment filter")

'change this line from the code that you are using
 'ssql = qd.sql
'to this line

 ssql = replace(qd.sql,"iitdata.","")
0
 

Author Comment

by:sxxgupta
ID: 24397697
I will try it right now.
0
 

Author Comment

by:sxxgupta
ID: 24397729
So, this is what I have now per your instructions.

Private Sub Form_Open(Cancel As Integer)
Dim sFilter As String, ssql As String
Dim qd As DAO.QueryDef
DoCmd.Maximize
Set qd = CurrentDb.QueryDefs("z supplier qty adjustment filter")
ssql = Replace(qd.SQL, "iitdata.", "")
ssql = Left(ssql, InStr(ssql, "Order By") - 1)
sFilter = Trim(Mid(ssql, InStr(ssql, "Where") + 6))
'for testing
    'Debug.Print sFilter
'qd.Close
Me.filter = sFilter
Me.FilterOn = True
End Sub
Same Runtime error 2176 "The setting for this property is too long"
Debug points to the code line:     Me.filter = sFilter
 
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 24397866
can't you not just use the query "z supplier qty adjustment filter" as the recordsource of the form "z supplier qty adjustment"?
then open the form "z supplier qty adjustment" from form [z filter] after setting all the variables.
0
 

Author Comment

by:sxxgupta
ID: 24397979
will try
0
 

Author Closing Comment

by:sxxgupta
ID: 31581469
Sometimes the easiest solutions are the best solutions.  Thank you for your persistence over these two days.  I do appreciate your time.  I thought that you could not "write" to a table using a form that uses a query as its recordsource.  I guess in this case you can because my query and my form that I want to write data into is based on the same table and is not linked to any other table....? Correct?
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

885 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