Ok I checked the properties of the form and found that was fine.
I also opened the form (frm_Batch_Job_Steps) and opened the immediate
window
typed the code as you had suggested. I noticed 2 things:
First, The code worked but only for the first record of the subform where
the on click event occurs (frm_Duplicate Jobs/subform tbl_Duplicate_Batch
Jobs Subform). So I have Duplicate Job A and Duplicate Job B it will
open
the new form (frm_Batch Job_Steps) with Duplicate Job A details even
though I
had the focus on duplicate Job B.
To prove this, in the immediate window I coded the following: msgbox
"([JobName] ='" & Forms![frm_Duplicate Jobs]![tbl_Duplicate_Batch Jobs
subform].Form![JOBNAME] & "') and ([JobCount]='" & Forms![frm_Duplicate
Jobs]![tbl_Duplicate_Batch Jobs subform].Form![JOBCOUNT] & "')". This
confirmed that the record of focus on was Duplicate Job B.
Secondly, I decided to comment out the .caption part of the code. so now
my
code is:
Function OpenAClient()
'Purpose: Open an independent instance of form frmClient.
Dim frm As Form
Dim criteria As String
'Open a new instance, show it, and set a caption.
Set frm = New Form_frm_Batch_Job_Steps
With frm
'.AllowFilters = True
.Filter = "([JobName] ='" & Forms![frm_Duplicate
Jobs]![tbl_Duplicate_Batch Jobs subform].Form![JOBNAME] & "') and
([JobCount]='" & Forms![frm_Duplicate Jobs]![tbl_Duplicate_Batch Jobs
subform].Form![JOBCOUNT] & "')"
'DoCmd.ApplyFilter , "([JobName] ='" & Forms![frm_Duplicate
Jobs]![tbl_Duplicate_Batch Jobs subform].Form![JOBNAME] & "') and
([JobCount]='" & Forms![frm_Duplicate Jobs]![tbl_Duplicate_Batch Jobs
subform].Form![JOBCOUNT] & "')"
'criteria = "([JobName] ='" & Forms![frm_Duplicate
Jobs]![tbl_Duplicate_Batch Jobs subform].Form![JOBNAME] & "') and
([JobCount]='" & Forms![frm_Duplicate Jobs]![tbl_Duplicate_Batch Jobs
subform].Form![JOBCOUNT] & "')"
'Debug.Print criteria
.FilterOn = True
.Visible = True
'.Caption = frm.Hwnd & ", opened " & Now()
End With
'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)
Set frm = Nothing
End Function
When I run the code in debug, the form correctly opens up with the name
of:
"Step JobStep 02: Select Batch Job Steps" which is the original name of
the
form prior to renaming the form to "frm_bath_job_steps". It also happens
to
be the name of the data source in my case. When I run the code as the user
would, the form's name is "window nbr" and opened on and the date time.
even though that code has been commented out. As a second thought, I
added
the .caption to be .caption = "FRED" and as previously shown... when I
run
the code in debug mode the form opens up as Fred but when I run it as a
user
it defaults to the window nbr and open on date and time. So somewhere on
my
form I have a setting set that will not all the form to be changed......
On
the other hand (how likely, I don't know) the form has been corrupted.
Allen Browne said:
As you say, the expression looks fine. Something else must be going on.
If this is A2007, try checking if you have the form's properties set like
this:
Filter On Load No
OrderBy On Load No
Data Entry No
Allow Filters Yes
Also, temporarily remove anything in the form's Open, Load, Activate, and
Timer events, as well as anything in the events of the first control to
get
focus.
You might also see if you can assign that filter after the form opens.
Open
the Immediate Window (Ctrl+G) and try entering:
Forms![WhateverItsCalled].Filter = "([Jobname...
Forms![WhateverItsCalled].FilterOn = True
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
message
So I tried the following suggestions, and again thanks for them.
Interestingly, I'm still stuck with the same issue. First of all the
debug.print output appears to look fine: ([JobName] ='FI0023 - REVENUE
CABS
351 DM') and ([JobCount]='12141800'). The JobCount is text not numeric
at
the table level. What is strange still is that , like last time,
when I
debug the code and step through it, the form opens up to the correct
data.
Yet when I click on the record as a user does, the form only opens to
the
first record in the Job step table. Again, thanks for your
assistnace.
:
If the filter is not working, it's probably not formed correctly.
Create a string variable, and Debug.Print the string to see what comes
out.
Add brackets around each of the 2 parts. Omit the quotes if the field
type
is numeric, e.g. JobCount looks like it might be a Number field.
Once you get the filter string right, it should work.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
message
Didn't realize this posting made it through.... thought it died.
Please
ignore my second posting.
Here is my code at this point:
----------
Function OpenAClient()
'Purpose: Open an independent instance of form frmClient.
Dim frm As Form
'Open a new instance, show it, and set a caption.
Set frm = New Form_frm_Batch_Job_Steps
With frm
.Filter = "[JobName] ='" & Forms![frm_Duplicate
Jobs]![tbl_Duplicate_Batch Jobs subform].Form![JOBNAME] & "' and
[JobCount]='" & Forms![frm_Duplicate Jobs]![tbl_Duplicate_Batch Jobs
subform].Form![JOBCOUNT] & "'"
.FilterOn = True
.Visible
.Caption = frm.Hwnd & ", opened " & Now()
End With
'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)
Set frm = Nothing
End Function
----------
The second form (frm_Batch_Job_Steps) does load but loads the first
record
of the table the form is based on. The curious thing is that when I
look
at
the advanced filter/sort option I can see the correct columns and
criteria
to
filter the data. I can also run through the code in debug mode and
the
the
code loads the form with the correct records populating the form
(frm_Batch_Job_Steps). It is only when I use the form as a user
will
that
the code not apply the filter. Probably something pretty simple...
:
I have a form that lists duplicate Batch Job Names (form is based
on a
table
that has job name and job nbr as primary key). I want to give the
user
the
ability to click on the batch job name and open a new form (Batch
Job
Steps)
that will show the job steps for that batch job and if needed click
on
the
duplicate job name and show the job steps. In other words: Batch
Job A
(12
Job Steps) & Batch Job A (16 Job Steps). Both of these records have
job
nbr
that form part of the primary key but is hidden from the user.) I
was
trying
to use the code below and filter the form on load to no avail....
Not
sure if
I'm making a mountain out of a mole hill. The issue is that I can
have
2+
batch jobs with the sam batch job Name.
Public clnClient As New Collection 'Instances of frmClient.
Function OpenAClient()
'Purpose: Open an independent instance of form frmClient.
Dim frm As Form
'Open a new instance, show it, and set a caption.
Set frm = New Form_frm_Batch_Job_Steps
frm.Visible = True
frm.Caption = frm.Hwnd & ", opened " & Now()
'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)
Set frm = Nothing
End Function
.
.
