Sanjay
asked on
VBA code to apply a filter that is loaded from a query during the Form's open event property
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.
ASKER
I'll give it a try.
ASKER
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.
can you post the sql of the query
ASKER
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)<>[QtyAc pt] Or (iitdata.QtyRecd)<>[InvQty ])) AND ((iitdata.QtyInsp)>=0) AND ((iitdata.QtyAcpt)=0 Or (iitdata.QtyAcpt)<=[QtyRec d]) 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)<([QtyAc pt]+[QtyRe j]) 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)<>([QtyAc pt]-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?)
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)<>[QtyAc
ORDER BY iitdata.IITReportNo;
I was thinking
docmd. load query perhaps
docmd. apply filter perhaps (where the filter is the name of the query?)
and what is the name of the form that you are trying to filter?
ASKER
The form is called "z supplier qty adjustment"
The query is called "z supplier qty adjustment filter"
The query is called "z supplier qty adjustment filter"
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?
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?
ASKER
yes.
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,inst r(ssql,"Wh ere")+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
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,
sFilter=trim(mid(ssql,inst
'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
ASKER
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."
ASKER
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".
ASKER
sorry. There is an immediate window.
ASKER
The filter seems to be there. I will try it and let you know.
ASKER
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.
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.
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
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
ASKER
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
Runtime error 2176 "The setting for this property is too long"
Debug points to the code line: Me.filter = sFilter
ASKER
I am assuming I have to "suppress" this error with code............
ASKER
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
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
ASKER
Anyone???
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.", "")
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.",
ASKER
I will try it right now.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
will try
ASKER
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?
private sub form_open(cancel as integer)
me.recordsource="nameOfque
end sub