Print the record in the form

[Guest]

I have read everything that I could find about this (Even went to Allen
Browne's website) and it is still not working. Here is my code:


Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stWhere As String

stWhere = "[Subgroup]= """ & Me.[Subgroup] & """"
DoCmd.OpenReport "Tripwire 2", acPreview, , strWhere

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

Payor Subgroup is one of three primary keys on the form. I have a table for
Regional Manager, Supervisor, Payor List, Payor Analysis. The tables have a
one to many relationship starting at the Regional manager level and cascading
down as listed. In my source query, I pull from all tables. The Subgroup
field used comes from the Payor List table, where it is the primary key.

I can get the report to Print all records, but not just the record in the
form. Could the other primary keys be getting in the way or am I doing
something else wrong?

 

[Allen Browne]

Cortney, your code uses
stWhere
twice, and then applies the filter:
strWhere
Correct the spelling, and see if this fixes the problem. It does not matter
which name you use, as long as it is consistent.

To help catch this kind of error, add this line to the very top of your
module:
Option Explicit
Access will then pick up on your spelling error.

If that does not solve the problem, you say:
Payor Subgroup is one of three primary keys on the form

Does this mean that the form gets its record from the [Payor List] table,
and the primary key of this table is a combination of 3 fields? If so, the
strWhere string will need to contain 3 parts, supplying values for all 3
fields. If you have trouble doing that, please post:
- the names of the 3 fields, and
- their data type (i.e. in table design, is the type Number, or Text, or
....)

 

[Guest]

It didn't work but thanks for letting me know. The way the tables are set up
is I have a Regional Manager Table (Regional is PK), then I have the
Supervisor Table (Sup is PK) Regional is foreign key, then I have a Payer
List (Subgroup is the PK) Sup is the foreign key, the last table is Payer
Analysis (No PK but Subgroup is the foreign key). The form comes from a
query that pulls from all four tables.

If I had them set up as subdatasheets they would cascade from Regional to
Sup to Payer List to Payer Analysis. The form pulls up Payers by Regional or
Sup depending on the choice the user makes. I can print all Payers by either
Regional or Sup but not just one. I am stumped. I have tried every
different way posted on the site to try to get it to work. I think it might
be the setup. Hopefully you can help!!!!

By the way, they are all text fields.

Cortney, your code uses
stWhere
twice, and then applies the filter:
strWhere
Correct the spelling, and see if this fixes the problem. It does not matter
which name you use, as long as it is consistent.

To help catch this kind of error, add this line to the very top of your
module:
Option Explicit
Access will then pick up on your spelling error.

If that does not solve the problem, you say:
Payor Subgroup is one of three primary keys on the form

Does this mean that the form gets its record from the [Payor List] table,
and the primary key of this table is a combination of 3 fields? If so, the
strWhere string will need to contain 3 parts, supplying values for all 3
fields. If you have trouble doing that, please post:
- the names of the 3 fields, and
- their data type (i.e. in table design, is the type Number, or Text, or
....)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have read everything that I could find about this (Even went to Allen
Browne's website) and it is still not working. Here is my code:


Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stWhere As String

stWhere = "[Subgroup]= """ & Me.[Subgroup] & """"
DoCmd.OpenReport "Tripwire 2", acPreview, , strWhere

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

Payor Subgroup is one of three primary keys on the form. I have a table
for
Regional Manager, Supervisor, Payor List, Payor Analysis. The tables have
a
one to many relationship starting at the Regional manager level and
cascading
down as listed. In my source query, I pull from all tables. The Subgroup
field used comes from the Payor List table, where it is the primary key.

I can get the report to Print all records, but not just the record in the
form. Could the other primary keys be getting in the way or am I doing
something else wrong?

 

[Allen Browne]

Okay, you have these 4 tables, related one-to-many:

[Regional Manager] table:
Regional Text primary key
...

Supervisor table:
Sup Text primary key
Regional Text relates to [Regional Manager].Regional
...

[Payer List] table:
Subgroup Text primary key
Sup Text relates to Supervisor.Sup
...

[Payer Analysis] table:
Subgroup Text relates to [Payer List].Subgroup

I assume your table has a record from the [Payer Analysis] table, and you
are trying to restrict the report to print just this one record. Can you add
a primary key field to this table? Without a primary key, there is no way to
identify which record you want to print.

Once you have a primary key field to refer to, you can use it in strWhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

It didn't work but thanks for letting me know. The way the tables are set
up
is I have a Regional Manager Table (Regional is PK), then I have the
Supervisor Table (Sup is PK) Regional is foreign key, then I have a Payer
List (Subgroup is the PK) Sup is the foreign key, the last table is Payer
Analysis (No PK but Subgroup is the foreign key). The form comes from a
query that pulls from all four tables.

If I had them set up as subdatasheets they would cascade from Regional to
Sup to Payer List to Payer Analysis. The form pulls up Payers by Regional
or
Sup depending on the choice the user makes. I can print all Payers by
either
Regional or Sup but not just one. I am stumped. I have tried every
different way posted on the site to try to get it to work. I think it
might
be the setup. Hopefully you can help!!!!

By the way, they are all text fields.

Cortney, your code uses
stWhere
twice, and then applies the filter:
strWhere
Correct the spelling, and see if this fixes the problem. It does not
matter
which name you use, as long as it is consistent.

To help catch this kind of error, add this line to the very top of your
module:
Option Explicit
Access will then pick up on your spelling error.

If that does not solve the problem, you say:
Payor Subgroup is one of three primary keys on the form

Does this mean that the form gets its record from the [Payor List] table,
and the primary key of this table is a combination of 3 fields? If so,
the
strWhere string will need to contain 3 parts, supplying values for all 3
fields. If you have trouble doing that, please post:
- the names of the 3 fields, and
- their data type (i.e. in table design, is the type Number, or Text,
or
....)

I have read everything that I could find about this (Even went to Allen
Browne's website) and it is still not working. Here is my code:


Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stWhere As String

stWhere = "[Subgroup]= """ & Me.[Subgroup] & """"
DoCmd.OpenReport "Tripwire 2", acPreview, , strWhere

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

Payor Subgroup is one of three primary keys on the form. I have a
table
for
Regional Manager, Supervisor, Payor List, Payor Analysis. The tables
have
a
one to many relationship starting at the Regional manager level and
cascading
down as listed. In my source query, I pull from all tables. The
Subgroup
field used comes from the Payor List table, where it is the primary
key.

I can get the report to Print all records, but not just the record in
the
form. Could the other primary keys be getting in the way or am I doing
something else wrong?

 

[Guest]

Allen Browne you are a genius. I create an auto number field for the last
table and made it the primary key. Once I went off of that, it worked like a
charm. Thanks a bunch for your help!!

Okay, you have these 4 tables, related one-to-many:

[Regional Manager] table:
Regional Text primary key
...

Supervisor table:
Sup Text primary key
Regional Text relates to [Regional Manager].Regional
...

[Payer List] table:
Subgroup Text primary key
Sup Text relates to Supervisor.Sup
...

[Payer Analysis] table:
Subgroup Text relates to [Payer List].Subgroup

I assume your table has a record from the [Payer Analysis] table, and you
are trying to restrict the report to print just this one record. Can you add
a primary key field to this table? Without a primary key, there is no way to
identify which record you want to print.

Once you have a primary key field to refer to, you can use it in strWhere.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

It didn't work but thanks for letting me know. The way the tables are set
up
is I have a Regional Manager Table (Regional is PK), then I have the
Supervisor Table (Sup is PK) Regional is foreign key, then I have a Payer
List (Subgroup is the PK) Sup is the foreign key, the last table is Payer
Analysis (No PK but Subgroup is the foreign key). The form comes from a
query that pulls from all four tables.

If I had them set up as subdatasheets they would cascade from Regional to
Sup to Payer List to Payer Analysis. The form pulls up Payers by Regional
or
Sup depending on the choice the user makes. I can print all Payers by
either
Regional or Sup but not just one. I am stumped. I have tried every
different way posted on the site to try to get it to work. I think it
might
be the setup. Hopefully you can help!!!!

By the way, they are all text fields.

Cortney, your code uses
stWhere
twice, and then applies the filter:
strWhere
Correct the spelling, and see if this fixes the problem. It does not
matter
which name you use, as long as it is consistent.

To help catch this kind of error, add this line to the very top of your
module:
Option Explicit
Access will then pick up on your spelling error.

If that does not solve the problem, you say:
Payor Subgroup is one of three primary keys on the form

Does this mean that the form gets its record from the [Payor List] table,
and the primary key of this table is a combination of 3 fields? If so,
the
strWhere string will need to contain 3 parts, supplying values for all 3
fields. If you have trouble doing that, please post:
- the names of the 3 fields, and
- their data type (i.e. in table design, is the type Number, or Text,
or
....)

I have read everything that I could find about this (Even went to Allen
Browne's website) and it is still not working. Here is my code:


Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stWhere As String

stWhere = "[Subgroup]= """ & Me.[Subgroup] & """"
DoCmd.OpenReport "Tripwire 2", acPreview, , strWhere

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

Payor Subgroup is one of three primary keys on the form. I have a
table
for
Regional Manager, Supervisor, Payor List, Payor Analysis. The tables
have
a
one to many relationship starting at the Regional manager level and
cascading
down as listed. In my source query, I pull from all tables. The
Subgroup
field used comes from the Payor List table, where it is the primary
key.

I can get the report to Print all records, but not just the record in
the
form. Could the other primary keys be getting in the way or am I doing
something else wrong?