Openreport action

  • Thread starter Thread starter Kathy R.
  • Start date Start date
K

Kathy R.

I am working on a form that uses an option gorup and combo
box to select records for printing labels. i found some
sample code that i used to make this work, however i don't
really understand why you need the two criteria "crit"
and "orig" in the openreport method.. it seems redundant,
like just one of them would be adequate. SelectOrigin is
the combo box with list of groups and OriginID is the
primary key in the origins table. Can someone please
explain? please see code below. thanks so much in
advance...

Dim orig As Long, Crit, ErrMsg As String
'Contact_Mailing_Labels
'
If Me.PrintLabelsFor = 2 Then
If Not IsNull(Me.SelectOrigin) Then
orig = Me.SelectOrigin
Crit = "[OriginID] = " & orig 'build criteria to
apply
DoCmd.OpenReport "Contact_Mailing_Labels",
acViewPreview, , Crit

Else ' user didn't select an origin, ask them
to do so
ErrMsg = "Please select an origin"
MsgBox ErrMsg
End If
Else
DoCmd.OpenReport "Contact_Mailing_Labels",
acViewPreview
End If
 
Kathy,

In the example, Crit and orig are not two criteria, they are variables
(although Crit has not been declared as to type, which is poor). And
then the structure of the code is that one variable (Crit) includes
the value of the other variable (orig) in its definition, and then it
is used as the criteria in the Where condition of the OpenReport
method. This to me seems unnecessarily involuted for no apparent
reason, and is the kind of code that Access's wizards come up with. I
personally see no reason not to do it like this...

If Me.PrintLabelsFor = 2 Then
If Not IsNull(Me.SelectOrigin) Then
DoCmd.OpenReport "Contact_Mailing_Labels", acViewPreview, , _
"[OriginID]=" & Me.SelectOrigin
Else ' user didn't select an origin, ask them to do so
MsgBox "Please select an origin"
End If
Else
DoCmd.OpenReport "Contact_Mailing_Labels", acViewPreview
End If

- Steve Schapel, Microsoft Access MVP
 
Hi Steve,

Thanks so much for the prompt reply. The code you posted
is definitely more straightforward and easier to follow
than what I had come up with. However, I do have one more
question (you'll really know I'm a newbie with this one!) -
- in the first DoCmd.OpenRport statement, why do you
include the [OriginID] field in addition to the
Me.SelectOrigin control value... i guess I'm not clear why
the OriginID is necessary. If a value (an origin name) has
been selected in the combo box, isn't this sufficient
criteria for the report? Thanks again!

Kathy


-----Original Message-----
Kathy,

In the example, Crit and orig are not two criteria, they are variables
(although Crit has not been declared as to type, which is poor). And
then the structure of the code is that one variable (Crit) includes
the value of the other variable (orig) in its definition, and then it
is used as the criteria in the Where condition of the OpenReport
method. This to me seems unnecessarily involuted for no apparent
reason, and is the kind of code that Access's wizards come up with. I
personally see no reason not to do it like this...

If Me.PrintLabelsFor = 2 Then
If Not IsNull(Me.SelectOrigin) Then
DoCmd.OpenReport "Contact_Mailing_Labels", acViewPreview, , _
"[OriginID]=" & Me.SelectOrigin
Else ' user didn't select an origin, ask them to do so
MsgBox "Please select an origin"
End If
Else
DoCmd.OpenReport "Contact_Mailing_Labels", acViewPreview
End If

- Steve Schapel, Microsoft Access MVP


I am working on a form that uses an option gorup and combo
box to select records for printing labels. i found some
sample code that i used to make this work, however i don't
really understand why you need the two criteria "crit"
and "orig" in the openreport method.. it seems redundant,
like just one of them would be adequate. SelectOrigin is
the combo box with list of groups and OriginID is the
primary key in the origins table. Can someone please
explain? please see code below. thanks so much in
advance...

Dim orig As Long, Crit, ErrMsg As String
'Contact_Mailing_Labels
'
If Me.PrintLabelsFor = 2 Then
If Not IsNull(Me.SelectOrigin) Then
orig = Me.SelectOrigin
Crit = "[OriginID] = " & orig 'build criteria to
apply
DoCmd.OpenReport "Contact_Mailing_Labels",
acViewPreview, , Crit

Else ' user didn't select an origin, ask them
to do so
ErrMsg = "Please select an origin"
MsgBox ErrMsg
End If
Else
DoCmd.OpenReport "Contact_Mailing_Labels",
acViewPreview
End If
Click to expand...

.
Click to expand...
 
I was looking at the syntax structure for the WHERE clause
in online Help and I think i understand now... Is
'"[OriginID]=" & Me.SelectOrigin 'in conformance with
[fieldname]=Forms![formname]![controlname on form]? and
then, my question, again, goes back to why the OriginID
field and not OriginName? Hope I'm not too high-
maintenance!!
thanks.

kr





-----Original Message-----
Kathy,

In the example, Crit and orig are not two criteria, they are variables
(although Crit has not been declared as to type, which
Click to expand...
is
poor). And
then the structure of the code is that one variable (Crit) includes
the value of the other variable (orig) in its
Click to expand...
definition,
and then it
is used as the criteria in the Where condition of the OpenReport
method. This to me seems unnecessarily involuted for no apparent
reason, and is the kind of code that Access's wizards come up with. I
personally see no reason not to do it like this...

If Me.PrintLabelsFor = 2 Then
If Not IsNull(Me.SelectOrigin) Then
DoCmd.OpenReport "Contact_Mailing_Labels", acViewPreview, , _

Else ' user didn't select an origin, ask them to do so
MsgBox "Please select an origin"
End If
Else
DoCmd.OpenReport "Contact_Mailing_Labels", acViewPreview
End If

- Steve Schapel, Microsoft Access MVP


I am working on a form that uses an option gorup and combo
box to select records for printing labels. i found some
sample code that i used to make this work, however i don't
really understand why you need the two criteria "crit"
and "orig" in the openreport method.. it seems redundant,
like just one of them would be adequate. SelectOrigin is
the combo box with list of groups and OriginID is the
primary key in the origins table. Can someone please
explain? please see code below. thanks so much in
advance...

Dim orig As Long, Crit, ErrMsg As String
'Contact_Mailing_Labels
'
If Me.PrintLabelsFor = 2 Then
If Not IsNull(Me.SelectOrigin) Then
orig = Me.SelectOrigin
Crit = "[OriginID] = " & orig 'build criteria to
apply
DoCmd.OpenReport "Contact_Mailing_Labels",
acViewPreview, , Crit

Else ' user didn't select an origin, ask them
to do so
ErrMsg = "Please select an origin"
MsgBox ErrMsg
End If
Else
DoCmd.OpenReport "Contact_Mailing_Labels",
acViewPreview
End If
Click to expand...

.
Click to expand...
.
Click to expand...
 
I was looking at the syntax structure for the WHERE clause
in online Help and I think i understand now... Is
'"[OriginID]=" & Me.SelectOrigin 'in conformance with
[fieldname]=Forms![formname]![controlname on form]? and
then, my question, again, goes back to why the OriginID
field and not OriginName? Hope I'm not too high-
maintenance!!
thanks.
Click to expand...

Don't confuse the names of Fields in a table, with the name of
Controls on a form. A Field and a Control are two different objects,
with their own names, properties, and uses.

The expression

"[OriginID] = " & Me.SelectOrigin

is valid, if you have a table field named OriginID and you wish to
search that table using the value selected by the user in a Form
control named SelectOrigin.

I suspect that your confusion between OriginID and OriginName is due
to Microsoft's misdesigned, misleading, and very obnoxious Lookup
misfeature. Is OriginName a Lookup field in your table? If so, IT IS
NOT REALLY THERE. A Lookup field is just that - it *conceals* the
actual contents of your table, OriginID, behind a looked-up
OriginName. When you're writing SQL (or doing almost anything beyond
the very basics) you must work with the actual field stored in your
table - the ID - rather than the looked-up value. This is the prime
reason (among many) why I dislike the Lookup field type!
 
Kathy,

You have just about answered your own question. Here's the bit you
are missing... SelectOrigin is the name of the combobox, and its
bound column is obviously the OriginID field of whatever the
combobox's RowSource is. And then your Report also has an OriginID
field. And you are asking Access to match these up for you.
Translating the Where condition clause of your OpenReport method. i.e.
"[OriginID]=" & Me.SelectOrigin
into English will be something like:
"Print the report only for the record where the value of the OriginID
field on the report is the same as the value of the SelectOrigin
combobox".

Hope that helps. Please let me know if I have missed the point of
your question!

- Steve Schapel, Microsoft Access MVP
 
Steve,

Thank you SO much for your detailed explanation (and
patience). I am fairly new to Access (as a developer) and
the other issue is I have not worked on this (or any)
project in a while and quite frankly, had forgotten about
the OriginID field being in the report! (slaps forehead).
Anyway, your English translation helped immensely and I
think i can move on now. This forum is a wonderful
resource - I'm so glad I discovered it! Thanks again.

KR
 
Hi John,

Thanks for your $.02 on my question about openreport and
Where condition syntax. The OriginName field is an actual
field in the Origin table along with OriginID; it is not a
lookup field as you suspected. I'm sort of familiar with
lookup fields, however, now armed with your valuable
input, I'll steer clear of using them!!

The (sad) truth about my confusion is that besides being
inexperienced with Access, I let too much time lapse
between actual work sessions, so I seem to have to relearn
much of it each time! Anyway, thanks for you help, i do
appreciate it.

Kathy

-----Original Message-----
I was looking at the syntax structure for the WHERE clause
in online Help and I think i understand now... Is
'"[OriginID]=" & Me.SelectOrigin 'in conformance with
[fieldname]=Forms![formname]![controlname on form]? and
then, my question, again, goes back to why the OriginID
field and not OriginName? Hope I'm not too high-
maintenance!!
thanks.
Click to expand...

Don't confuse the names of Fields in a table, with the name of
Controls on a form. A Field and a Control are two different objects,
with their own names, properties, and uses.

The expression

"[OriginID] = " & Me.SelectOrigin

is valid, if you have a table field named OriginID and you wish to
search that table using the value selected by the user in a Form
control named SelectOrigin.

I suspect that your confusion between OriginID and OriginName is due
to Microsoft's misdesigned, misleading, and very obnoxious Lookup
misfeature. Is OriginName a Lookup field in your table? If so, IT IS
NOT REALLY THERE. A Lookup field is just that - it *conceals* the
actual contents of your table, OriginID, behind a looked- up
OriginName. When you're writing SQL (or doing almost anything beyond
the very basics) you must work with the actual field stored in your
table - the ID - rather than the looked-up value. This is the prime
reason (among many) why I dislike the Lookup field type!


.
Click to expand...
 
Back
Top