Still having problems

[Guest]

Hello,
I am still having problems getting this to work. My goal is I have a form
that has a drop down list of all associate id's that are in the data base.
After the individual associate id is selected then i click on the view report
command button that opens the report show just that individuals records. Here
is what I have so far for the command button event. I still can't get it to
work!

Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks, greg

 

[Al Campagna]

GMac,
You need to use a Where argument in the OpenReport method. Your combo (ex.
cboAssocID), on the form has that value.
And, assuming that AssocID is a unique key field... and the form remains open when the
report is run...
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID
Only the employee with that AssocID will be reported.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Al,
I tried that as well with no success I still get an outside error.
Any more help would be great!

GMac,
You need to use a Where argument in the OpenReport method. Your combo (ex.
cboAssocID), on the form has that value.
And, assuming that AssocID is a unique key field... and the form remains open when the
report is run...
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID
Only the employee with that AssocID will be reported.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,
I am still having problems getting this to work. My goal is I have a form
that has a drop down list of all associate id's that are in the data base.
After the individual associate id is selected then i click on the view report
command button that opens the report show just that individuals records. Here
is what I have so far for the command button event. I still can't get it to
work!

Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks, greg

 

[Al Campagna]

GMac,
What do you mean by "outside error"
What is your code? Cut and Paste it into your reply.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al,
I tried that as well with no success I still get an outside error.
Any more help would be great!

GMac,
You need to use a Where argument in the OpenReport method. Your combo (ex.
cboAssocID), on the form has that value.
And, assuming that AssocID is a unique key field... and the form remains open when
the
report is run...
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID
Only the employee with that AssocID will be reported.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,
I am still having problems getting this to work. My goal is I have a form
that has a drop down list of all associate id's that are in the data base.
After the individual associate id is selected then i click on the view report
command button that opens the report show just that individuals records. Here
is what I have so far for the command button event. I still can't get it to
work!

Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks, greg

 

[Guest]

Al,
Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID

I get a complile error that highlights the "1=1" and it says invalid outside
procedure.

GMac,
You need to use a Where argument in the OpenReport method. Your combo (ex.
cboAssocID), on the form has that value.
And, assuming that AssocID is a unique key field... and the form remains open when the
report is run...
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID
Only the employee with that AssocID will be reported.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,
I am still having problems getting this to work. My goal is I have a form
that has a drop down list of all associate id's that are in the data base.
After the individual associate id is selected then i click on the view report
command button that opens the report show just that individuals records. Here
is what I have so far for the command button event. I still can't get it to
work!

Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks, greg

 

[Al Campagna]

GMac,
Why are you using the "1=1" at all? It's meaningless besides not being a legitimate
expression, and it shouldn't be needed to open a report for a specific AssocID.
**What form event triggers this code? (you don't indicate this, and this code must occur
within an Event Procedure...(Important))
I think that may be your problem... you've just placed some code in the module...
outside of a legitimate procedure.

For example, say you click a button (cmdPrintMyReport) to run the report.
In the OnClick EventProcedure of cmdPrintMyReport, you should have this code...

Private Sub cmdPrintMyReport_Click()
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID
End Sub

This should be all you need to open a report based on the AssocID indicated on the
main form.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al,
Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"


I get a complile error that highlights the "1=1" and it says invalid outside
procedure.

GMac,
You need to use a Where argument in the OpenReport method. Your combo (ex.
cboAssocID), on the form has that value.
And, assuming that AssocID is a unique key field... and the form remains open when
the
report is run...
DoCmd.OpenReport "Program Outline", , , "[AssocID] = " & cboAssocID
Only the employee with that AssocID will be reported.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hello,
I am still having problems getting this to work. My goal is I have a form
that has a drop down list of all associate id's that are in the data base.
After the individual associate id is selected then i click on the view report
command button that opens the report show just that individuals records. Here
is what I have so far for the command button event. I still can't get it to
work!

Dim strWhere As String
Dim stDocument As String
strWhere = "1=1"
If Not IsNull(Me.cboAssocID) Then
strWhere = strWhere & " AND [Outline].[Associate ID] = " & Me.cboAssocID
End If
stDocument = "Program Outline"
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks, greg

 

[Rick Brandt]

GMac,
Why are you using the "1=1" at all? It's meaningless besides not
being a legitimate expression, and it shouldn't be needed to open a
report for a specific AssocID.

I suspect he might have copied some code he found somewhere. I actually use
that in places where I am building a dynamic WHERE clause in code. The
reasoning is that I test numerous form fields to see if it has an entry and
if it does I tack another piece onto the WHERE clause. By starting with
"WHERE 1 = 1 " I know that all added pieces can use "AND". Otherwise you
need a lot of extra testing to see if an added piece is the first piece (use
"WHERE") or a subsequent piece (use "AND").

You are correct that if he is only using a single criteria that the 1 = 1 is
completely unnecessary.

 

[Al Campagna]

Rick,
Hmmm... not sure if I fully understand that "1 = 1" part, and how that assists you,
but...
Sounds like you use it as some kind of a "trap"... or "fallout" (if all else fails)
expression, but I'm not quite I understand what it's telling you.
I will "cogitate" upon that...

With all the questions I had regarding what The OP was trying to do, I tried to stay on
target with "need to open report against one AssocID. So, in that regard, the 1=1 seemed
moot, and in the last piece of code the OP posted, he didn't insinuate that into the Where
at all anyway.
But... once the OP indicated the "outside procedure" error, I had to assume he had
placed the code outside a legitimate procedure, and consider that the major problem at
this point.

Thanks Rick,

Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Rick Brandt]

Rick,
Hmmm... not sure if I fully understand that "1 = 1" part, and how
that assists you, but...
Sounds like you use it as some kind of a "trap"... or "fallout" (if
all else fails) expression, but I'm not quite I understand what it's
telling you. I will "cogitate" upon that...

Consider a base SQL of...

SELECT *
FROM TableName
WHERE 1 = 1

I can now test multiple form entries to see if I should add more criteria to
the WHERE clause. If a form field has an entry I add a criteria for that
entry, otherwise I don't. As I am testing each entry and decide that I do
need to add a criteria I know it will always be a string beginning with "AND
" since the initial "WHERE" has already been added to the SQL. If I just
had...

SELECT *
FROM TableName

....Then every time I decide to add a criteria I would have to ask "Is this
the first criteria being added?" because if it is I need to use "WHERE". If
it's not the first criteria then I need to use "AND". Using "WHERE 1 = 1"
simply eliminates that decision from my code. One could just as easily use
a real criteria like...

SELECT *
FROM TableName
WHERE PrimaryKeyField Is Not Null

All that really matters is that the initial WHERE be a test that is always
true. By using a literal like "1 = 1" I am assuming that the query engine
only has to evaluate it one time so it is a bit more efficient than
performing a more "real" test.

 

[Guest]

Rick,
All is correct. I got help on this matter a couple days ago, but I don't
think I explained what I wanted to happen in great detail. The deal is, I
have a form that has a combo box with all associate ID's in the database.
When the user selects the associate ID they want to view, they then need to
click the view report icon that is at the bottom of the form. Upon button
click, I want just that associates report to open. Can't figure it out.
Sorry, It's been 8 yrs since college and I haven't used Access all that much
since. Most of it comes back, but not enough to help me!
Thanks,

 

[Al Campagna]

Rick,
Thanks for that explanantion. I'm pretty sure I can see what you're doing now.
One "never fails" criteria associate with the Where, and all other criteria are Ands.
Neat trick!
Thank you for that reply...
--
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."