Attempt to Create Report form Based on Criteria is not working 4

[RobotMush]

I have a long list of clients with their Data as a subform. What I am trying to do it to get only the Client with their Data to be the only thing that prints. What I have created in my form is a "Print Preview" button that brings up the Report. The code is...

Private Sub cmdPrevRpt_Click()
On Error GoTo Err_cmdPrevRpt_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmClientDetail"
stLinkCriteria = "[strClnt#]=" & "'" & Me![strClnt#] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLnkCriteria

Exit_cmdPrevRpt_Click:
Exit Sub

Err_cmdPrevRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrevRpt_Click

End Sub

However, when I run it I get the entire list and not the single client that I want. What do I need to do to modify the code to achive what I want to do.

Thank you for all your help and suggestions.

RobotMush (Technical User) Self Taught

 

[fneily]

A couple of questions: Your report is called frmClientDetail? Or should it be rptClientDetail?
Is strClnt# a numeric or string? # implies numeric.
I would use a query prompt with a reference to the form's [strClnt#].
So create a query connecting the main table to the related table. You should have a one-to-many relationship. Bring down the fields you want into the grid. Then under strClnt on the criteria line you'd place Forms![YourFormName]![strClnt]
Now use the query as the RecordSource for your report.
So when they click print, the query will run and pick off the main record and all related records which will be the source of the report.

 

[RobotMush]

I'm not quiet following your insturctions the "frmClientDetail" is the name of that I gave the report and it was picked up as such by the Button Wizard.
I agree it should be "rptClientDetail"
strClient# is a text string. the "#" is shorter than "Num#" Was working on matching space to data fields when I created it.
I have two queries that make the Form and Subform I am using to look at the Client Data and also go to the Report to preview it before printing. One holds the Client Name the other holds the Data to that client. They are joined by "tblClient_ID" (one) to "tblData_ID" (Many)
Also the report is based on the same two queries as the Form.

I hope this clears things up a bit. And thank you for your help.

RobotMush (Technical User) Self Taught

 

[TheAceMan1]

How are ya RobotMush . . .

Things are a little vague here, but you should be using [blue]tblClient_ID[/blue] as your criteria:

[blue]   stLinkCriteria = "[tblClient_ID] = '" & Me![tblClient_ID] & "'"[/blue]

If tblClient_ID is numeric remove the single quotes . . .

See Ya! . . . . . .

 

[RobotMush]

My appologies for not making myself clear. I have tried your suggestion and the report comes up but still lists all clients and their data rather than just the client and data I am wanting.

Thank you for your help

RobotMush (Technical User) Self Taught

 

[LaurieHamlin]

I'm also not sure I'm following what you're doing, but I've had success with this method. First filter the form. Then in the report create a text box which refers to the value showing in the field in the form. I had to do this for a database when I couldn't print the form due to a known bug.
Hope that helps.

 

[Pampers]

Hi

(1) It is better not to use # as in strClnt# (reserved);
beter would be something like ClientID. If so, should it not be ClientID? (without str)?

(2) also there is a typo in your code:
DoCmd.OpenReport stDocName, acPreview, , stLnkCriteria
stLnkCriteria, must be stLinkCriteria with an i

(3) no need for "'" since it is a number

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmClientDetail"
stLinkCriteria = "ClientID=" & Me.ClientID 
DoCmd.OpenForm strDocName, acViewPreview, , stLinkCriteria

Pampers
Just let it go...

 

[TheAceMan1]

RobotMush . . .

You may also have a [blue]referencing[/blue] problem depending where the button is.

If button is on mainform:

[blue]   stLinkCriteria = "[tblClient_ID] = " & Me![[purple][b]tblClient_ID[/b][/purple]][/blue]

If button is on subform:

[blue]   stLinkCriteria = "[tblClient_ID] = " & Me![[purple][b]tblData_ID[/b][/purple]][/blue]

See Ya! . . . . . .

 

[RobotMush]

Thanks to all of you, specially to Pampers for spelling it out for me and also to Aceman, always great to get info from you.

Will see how the suggestions work and let y'all know what the results are.

RobotMush (Technical User) Self Taught

 

[RobotMush]

Pampers;
In answer to your questions…
(1)It is better not to use # as in strClnt# (reserved);
beter would be something like ClientID. If so, should it not be ClientID” (without str)?
If strClnt# a reserved variable, I have not had any problems with it. However,
in using the “str” in front of the variable that signifies to me when I come back to work
on the program at a later date that the variable is a text string. I do agree that I probably
need to go back and rename the base field name removing the “#” from it.

(2) also there is a typo in your code:
DoCmd.OpenReport stDocName, acPreview,,stLnkCriteria
stLnkCritera, must be stLinkCriteria with an i
Thank you, I have noticed that as well and made the correction. Will have to keep a
Sharper eye out on the program before posting it to here.

(3)no need for “’”since it is a number
Actual it is a text string as it has numbers and letters in the Client Number field.

LaurieHamlin:
In response to your helpful comment…
“I’m also not sure I’m following what you’re doing, but I’ve had success with this
method. First filter the form. Then in the report create a text box which refers to the
value showing in the field in the form. I had to do this for a database when I couldn’t
print the form due to a known bug.
Hope that helps.”
Your comment does help and I believe that I have done perhaps half of that. What I
have is a form that lets me search for the client. I then have 4 buttons that direct me
to the 4 different tables I have for the client (these were created over the years and
I am working on getting all the tables into one) This brings up a filtered form that
Displays the client’s name and number with a subform for the data that pertains to
that client. From this filtered form I have a preview print button that is supposed to
bring up the report and display only the client that was shown in the filtered form.
My problem is that it brings up the report but displays All the clients with All their
Data.
As a side note, the Form/Sub form and Report are all generated by the same Client-qry
And Data-qry.

AceMan1:
The button is in the main form. And while using the [tblClient_ID] I would prefer to
Use the [strClntID] field string as we have some business clients that have changed their
Company’s name and makes it easier to keep those clients together.

Now what has happened when I have implemented the above suggestions is…

Pampers suggestion of changing the [strClnt#] field in the Table Object.
I changed the Field name from [strClnt#] to [strClntID] in the Table Object I had to go
through all the queries, forms and change out the fields as well as recreate the Reports. Took
some time but was able to get everything back up and running as it had before I did the
changeover. I have already taken care of the type, the new program is below.

LaurieHamlin
I did create a text box in the Report that tied to the filtered form that I am using to call up the report.
The result of this is that the report comes up with every client on record with proper Client ID Number
As well as the text box showing the Client ID Number that I want to show.
That is, every client is now showing the same Client ID Number that I had Filtered in the form.

AceMan1
As a last resort I will use the tblClient_ID which is the record number, but I would prefer to use the
Client ID Number as I said above.

Here is the Program to call up the report as it stands now. (06-16-05 at 02:55 pm *1455 24hr time*)
Private Sub cmdPrevRpt_Click()
On Error GoTo Err_cmdPrevRpt_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptClientDetail"
stLinkCriteria = "[strClntID] = " & Me![strClntID] & ""
DoCmd.OpenReport stDocName, acPreview, stLinkCriteria

Exit_cmdPrevRpt_Click:
Exit Sub

Err_cmdPrevRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrevRpt_Click

End Sub

 

[PHV]

DoCmd.OpenReport stDocName, ac[!]View[/!]Preview[!], [/!], stLinkCriteria

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobotMush]

Thank you for the suggestion PHV. I have tried it as
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
and it still displays all the clients
When I tried it with the extra comma as in
DoCmd.OpenReport stDocName, acViewPreview,,stLinkCriteria
I got an Enter Parameter Value Error and asked to enter the
Me!strClntID
field.
Still that is progress forward but still have the stumbling block in the way.
Thank you for your help
RobotMush (Technical User) Self Taught

 

[PHV]

How about this ?
stLinkCriteria = "strClntID=" & Me!strClntID

Provided strClntID is the name of a populated control in the same form as the cmdPrevRpt button.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobotMush]

SUCCESS!!!!!
Thanks to PHV for giving the final piece of the matrix.
Here is the final Program result...
Private Sub cmdPrevRpt_Click()
On Error GoTo Err_cmdPrevRpt_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptClientDetail"
stLinkCriteria = "[strClntID] =" & "'" & Me![strClntID] & "'"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Exit_cmdPrevRpt_Click:
Exit Sub

Err_cmdPrevRpt_Click:
MsgBox Err.Description
Resume Exit_cmdPrevRpt_Click

End Sub

Thanks again to PHV, AceMan1, Pampers, and LaurieHamlin. Each suggestion and helpful hint allowed me to rethink what and how things were being done. I thank you all for your help.

RobotMush (Technical User) Self Taught