printing one record on a report from a command button in a form

[Guest]

Hello

I am using the following code to try to get a report to show a single record
by clicking a command button in a sub form (perhaps it doesnt work because
its a sub form not a form?) The mainform holds the primary key and subform
displays the data (I also have 2 other sub forms nested in the subform!)

My other complication is that the primary key is a combination of 2 fields,
however, before I even get to this part, when I enter a different field name
in the RunID part of the code which is unique, the report previews, but for
all my records.

So I have 2 problems I guess
1 - Why is it showing all records and not just one?
2 - is there a code that I can borrow for when the RunID needs to be a
primary key made up of 2 fields?

Thanks a lot scripting dudes


Dim strDocName As String
Dim strWhere As String
strDocName = "myreport"
strWhere = "[RunID]=" & Me!RunID
DoCmd.OpenReport strDocName, acPreview, strWhere

 

[Guest]

Hi Jenny,

If you have both of the fields that make up the primary key on the main
form, then you can build the WHERE clause as follows:

strWhere = "[FIRSTFIELD]=" & Me.Parent.FIRSTCONTROL & " AND [SECONDFIELD]="
& Me.Parent.SECONDCONTROL

You'll need to replace the text in CAPS with the name of the fields that
make up the primary key, and the controls on the main form that display the
fields.

Hope this helps,

Stuart

 

[Guest]

The first problem is you have a syntax error. The Where condition is the 4th
argument, not the 3rd.
DoCmd.OpenReport strDocName, acPreview, strWhere
Should be
DoCmd.OpenReport strDocName, acPreview, ,strWhere

If you want to filter on two fields, then you need to specify both in strWhere
strWhere = "[FirstField] = '" & Me.txtOne & "' And [NextField] = '" &
Me.txtTwo & "'"

 

[Guest]

Hi Stuart

Many thanks for your help. This certainly answers part 2 of my question.
Great! However I am still getting all my records showing rather than just
one. My control names are the same as the field names so the code you gave
me looks like this

strWhere = "[proposalnumber]=" & Me.Parent.proposalnumber & " AND
[eventnumber]=" & Me.Parent.eventnumber

Does that look ok to you?!

I wonder if it is something to do with the query behind the report which
makes all the records show?

I have to go now but will look into this more next week.

Many thanks again for your help and happy weekend.

Jenny

Hi Jenny,

If you have both of the fields that make up the primary key on the main
form, then you can build the WHERE clause as follows:

strWhere = "[FIRSTFIELD]=" & Me.Parent.FIRSTCONTROL & " AND [SECONDFIELD]="
& Me.Parent.SECONDCONTROL

You'll need to replace the text in CAPS with the name of the fields that
make up the primary key, and the controls on the main form that display the
fields.

Hope this helps,

Stuart

Hello

I am using the following code to try to get a report to show a single record
by clicking a command button in a sub form (perhaps it doesnt work because
its a sub form not a form?) The mainform holds the primary key and subform
displays the data (I also have 2 other sub forms nested in the subform!)

My other complication is that the primary key is a combination of 2 fields,
however, before I even get to this part, when I enter a different field name
in the RunID part of the code which is unique, the report previews, but for
all my records.

So I have 2 problems I guess
1 - Why is it showing all records and not just one?
2 - is there a code that I can borrow for when the RunID needs to be a
primary key made up of 2 fields?

Thanks a lot scripting dudes


Dim strDocName As String
Dim strWhere As String
strDocName = "myreport"
strWhere = "[RunID]=" & Me!RunID
DoCmd.OpenReport strDocName, acPreview, strWhere

 

[Guest]

Yup - that looks fine. It will be your query that's at fault. You probably
want to create another query based on your original query. This new query
will need to group the records together - have a search in the help for GROUP
(it's an SQL statement) as this might help you understand how to filter the
data you want in your report.

Off myself - have a happy weekend too.

Cheers,

Stuart

Hi Stuart

Many thanks for your help. This certainly answers part 2 of my question.
Great! However I am still getting all my records showing rather than just
one. My control names are the same as the field names so the code you gave
me looks like this

strWhere = "[proposalnumber]=" & Me.Parent.proposalnumber & " AND
[eventnumber]=" & Me.Parent.eventnumber

Does that look ok to you?!

I wonder if it is something to do with the query behind the report which
makes all the records show?

I have to go now but will look into this more next week.

Many thanks again for your help and happy weekend.

Jenny

Hi Jenny,

If you have both of the fields that make up the primary key on the main
form, then you can build the WHERE clause as follows:

strWhere = "[FIRSTFIELD]=" & Me.Parent.FIRSTCONTROL & " AND [SECONDFIELD]="
& Me.Parent.SECONDCONTROL

You'll need to replace the text in CAPS with the name of the fields that
make up the primary key, and the controls on the main form that display the
fields.

Hope this helps,

Stuart

Hello

I am using the following code to try to get a report to show a single record
by clicking a command button in a sub form (perhaps it doesnt work because
its a sub form not a form?) The mainform holds the primary key and subform
displays the data (I also have 2 other sub forms nested in the subform!)

My other complication is that the primary key is a combination of 2 fields,
however, before I even get to this part, when I enter a different field name
in the RunID part of the code which is unique, the report previews, but for
all my records.

So I have 2 problems I guess
1 - Why is it showing all records and not just one?
2 - is there a code that I can borrow for when the RunID needs to be a
primary key made up of 2 fields?

Thanks a lot scripting dudes


Dim strDocName As String
Dim strWhere As String
strDocName = "myreport"
strWhere = "[RunID]=" & Me!RunID
DoCmd.OpenReport strDocName, acPreview, strWhere

 

[Guest]

Hi Klatuu

Many thanks for your help. I have followed your advice but am still stuck
on one thing (sorry if its v basic - I fear it is!) I can't figure out what
I am supposed to type where you have written "Me.txtOne" and "Me.txtTwo".
Obviously it's not the field value, if I type the field name here it still
returns the report for all records.

Please help!
Thank you

The first problem is you have a syntax error. The Where condition is the 4th
argument, not the 3rd.
DoCmd.OpenReport strDocName, acPreview, strWhere
Should be
DoCmd.OpenReport strDocName, acPreview, ,strWhere

If you want to filter on two fields, then you need to specify both in strWhere
strWhere = "[FirstField] = '" & Me.txtOne & "' And [NextField] = '" &
Me.txtTwo & "'"

Hello

I am using the following code to try to get a report to show a single record
by clicking a command button in a sub form (perhaps it doesnt work because
its a sub form not a form?) The mainform holds the primary key and subform
displays the data (I also have 2 other sub forms nested in the subform!)

My other complication is that the primary key is a combination of 2 fields,
however, before I even get to this part, when I enter a different field name
in the RunID part of the code which is unique, the report previews, but for
all my records.

So I have 2 problems I guess
1 - Why is it showing all records and not just one?
2 - is there a code that I can borrow for when the RunID needs to be a
primary key made up of 2 fields?

Thanks a lot scripting dudes


Dim strDocName As String
Dim strWhere As String
strDocName = "myreport"
strWhere = "[RunID]=" & Me!RunID
DoCmd.OpenReport strDocName, acPreview, strWhere

 

[Guest]

Thanks have got there now!
Yippee!

Hi Klatuu

Many thanks for your help. I have followed your advice but am still stuck
on one thing (sorry if its v basic - I fear it is!) I can't figure out what
I am supposed to type where you have written "Me.txtOne" and "Me.txtTwo".
Obviously it's not the field value, if I type the field name here it still
returns the report for all records.

Please help!
Thank you

The first problem is you have a syntax error. The Where condition is the 4th
argument, not the 3rd.
DoCmd.OpenReport strDocName, acPreview, strWhere
Should be
DoCmd.OpenReport strDocName, acPreview, ,strWhere

If you want to filter on two fields, then you need to specify both in strWhere
strWhere = "[FirstField] = '" & Me.txtOne & "' And [NextField] = '" &
Me.txtTwo & "'"

Hello

I am using the following code to try to get a report to show a single record
by clicking a command button in a sub form (perhaps it doesnt work because
its a sub form not a form?) The mainform holds the primary key and subform
displays the data (I also have 2 other sub forms nested in the subform!)

My other complication is that the primary key is a combination of 2 fields,
however, before I even get to this part, when I enter a different field name
in the RunID part of the code which is unique, the report previews, but for
all my records.

So I have 2 problems I guess
1 - Why is it showing all records and not just one?
2 - is there a code that I can borrow for when the RunID needs to be a
primary key made up of 2 fields?

Thanks a lot scripting dudes


Dim strDocName As String
Dim strWhere As String
strDocName = "myreport"
strWhere = "[RunID]=" & Me!RunID
DoCmd.OpenReport strDocName, acPreview, strWhere