Help with a filter when opening a form

J

John Ortt

I am trying to create a drill-down query. I have an order summary sheet
which sums all the orders for a particular customer by month. When the user
double clicks on a field I would like to open the detail report for the
record they have clicked on. The problem is the form I am using is already
used from another report so I would rather use code to create the link
rather than put it in the form or the query.

The following code is opening the form but it is not applying the filter.

If anybody has any ideas why I would be very grateful.

Thankyou,
John

--------------------------------------------------------

Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"
DoCmd.Openform stDocName, , stLinkCriteria
RecordSource = "VendorMarginsDDQuery"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and " &
stLinkCriteria3 & " and " & stLinkCriteria4

End Function
 
K

Katrina

From looking at the code below, the criteria is not being applied because it
is not being set until AFTER you open the form.

Try this
Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and " &
stLinkCriteria3 & " and " & stLinkCriteria4

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria
'RecordSource = "VendorMarginsDDQuery"
Click to expand...
End Function
Click to expand...
If this does not work, make sure that the query VendorMarginsDDQuery
contains at least all of thefields that are in the original recordsource for
the form.



HTH

Kat

John Ortt said:
I am trying to create a drill-down query. I have an order summary sheet
which sums all the orders for a particular customer by month. When the user
double clicks on a field I would like to open the detail report for the
record they have clicked on. The problem is the form I am using is already
used from another report so I would rather use code to create the link
rather than put it in the form or the query.

The following code is opening the form but it is not applying the filter.

If anybody has any ideas why I would be very grateful.

Thankyou,
John

--------------------------------------------------------

Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"
DoCmd.Openform stDocName, , stLinkCriteria
RecordSource = "VendorMarginsDDQuery"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and " &
stLinkCriteria3 & " and " & stLinkCriteria4

End Function
Click to expand...
 
J

John Ortt

Thanks for the Help Kat, much appreciated,


I have just tried the adjustment you suggested and the code us coming up
with an error on the following line:

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

I did check the VendorMarginsDDQuery and I couldn't see any missing fields.

I will keep trying but if you can spot any errors in the code i pasted back
into the database I would be very grateful.


Thankyou,

John

_________________

Revised Code:
_________________

Function Link()



Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and "
& stLinkCriteria3 & " and " & stLinkCriteria4
DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria
'RecordSource = "VendorMarginsDDQuery"


End Function

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Katrina said:
From looking at the code below, the criteria is not being applied because it
is not being set until AFTER you open the form.

Try this
Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and
Click to expand...
"
&
stLinkCriteria3 & " and " & stLinkCriteria4

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria
'RecordSource = "VendorMarginsDDQuery"
Click to expand...
End Function
Click to expand...
If this does not work, make sure that the query VendorMarginsDDQuery
contains at least all of thefields that are in the original recordsource for
the form.



HTH

Kat

John Ortt said:
I am trying to create a drill-down query. I have an order summary sheet
which sums all the orders for a particular customer by month. When the user
double clicks on a field I would like to open the detail report for the
record they have clicked on. The problem is the form I am using is already
used from another report so I would rather use code to create the link
rather than put it in the form or the query.

The following code is opening the form but it is not applying the filter.

If anybody has any ideas why I would be very grateful.

Thankyou,
John

--------------------------------------------------------

Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"
DoCmd.Openform stDocName, , stLinkCriteria
RecordSource = "VendorMarginsDDQuery"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and
Click to expand...
"
&
stLinkCriteria3 & " and " & stLinkCriteria4

End Function
Click to expand...
Click to expand...
 
K

Katrina

Things to check for the code to work as you have it
1. Fields
Me![TYPE]
Me![IPT]
Me![CUSTOMER_2]
Me![SD Forecast]
must all contain data of some sort.
2. If any of these fields are numeric - do not use the "'" text qualifier
for the numeric fields

3. Make sure that all of the fields used in the criteria string are included
in the recordset and filter for the form

If you have checked all of that, try commenting out parts of your criteria
to see if you can determine where the problem is.

I would open first without using criteria all, just to make sure that it is
not a form problem.


HTH
Kat




John Ortt said:
Thanks for the Help Kat, much appreciated,


I have just tried the adjustment you suggested and the code us coming up
with an error on the following line:

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria

I did check the VendorMarginsDDQuery and I couldn't see any missing fields.

I will keep trying but if you can spot any errors in the code i pasted back
into the database I would be very grateful.


Thankyou,

John

_________________

Revised Code:
_________________

Function Link()



Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & " and "
& stLinkCriteria3 & " and " & stLinkCriteria4
DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria
'RecordSource = "VendorMarginsDDQuery"


End Function

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Katrina said:
From looking at the code below, the criteria is not being applied
Click to expand...
because
it
is not being set until AFTER you open the form.

Try this
Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & "
Click to expand...
Click to expand...
and
"
&
stLinkCriteria3 & " and " & stLinkCriteria4

DoCmd.Openform stDocName, "VendorMarginsDDQuery", stLinkCriteria
'RecordSource = "VendorMarginsDDQuery"
Click to expand...
End Function
Click to expand...
If this does not work, make sure that the query VendorMarginsDDQuery
contains at least all of thefields that are in the original recordsource for
the form.



HTH

Kat

I am trying to create a drill-down query. I have an order summary sheet
which sums all the orders for a particular customer by month. When
Click to expand...
the
user
double clicks on a field I would like to open the detail report for the
record they have clicked on. The problem is the form I am using is already
used from another report so I would rather use code to create the link
rather than put it in the form or the query.

The following code is opening the form but it is not applying the filter.

If anybody has any ideas why I would be very grateful.

Thankyou,
John

--------------------------------------------------------

Private Sub TYPE_DblClick(Cancel As Integer)

Link

End Sub


Function Link()

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String

stDocName = "VendorExpectedMarginsReport"
DoCmd.Openform stDocName, , stLinkCriteria
RecordSource = "VendorMarginsDDQuery"

stLinkCriteria1 = "[TYPE]=" & "'" & Me![TYPE] & "'"
stLinkCriteria2 = "[IPT]=" & "'" & Me![IPT] & "'"
stLinkCriteria3 = "[CUSTOMER_2]=" & "'" & Me![CUSTOMER_2] & "'"
stLinkCriteria4 = "[SD Forecast]=" & "'" & Me![SD Forecast] & "'"

stLinkCriteria = stLinkCriteria1 & " and " & stLinkCriteria2 & "
Click to expand...
Click to expand...
and
"
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with DoCmd.OpenForm Command 4
My VB to open a form isn't working 5
Access Access DLookup Function – more than 1 criteria 0
Type Mismatch 2
Enter Parameter Value Error 4
Opening forms with same ID no 2
open form multiple criteria 3
stLinkCriteria 2

Top