code reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

can any1 pls help me.

i have created a combo box and listed all of the letters (reports) that we
send to clients, which until now i have created a cmd button for each
individual report with the linked criteria in a macro.

i though it better to list them, i have coded as follows but when i select a
report it does not filter for the specific client (record) but creates a
letter for each and every client.

what am i doing wrong!!!!

Dim StrCLtr1 As String
Dim strCltr2 As String
Dim strCLtr3 As String
Dim StrCLtr4 As String
Dim strCltr5 As String
Dim strCLtr6 As String
Dim strCLtr7 As String
Dim strLinkCriteria As String

StrCLtr1 = "rptNewBusiness"
strCltr2 = "rptReturnDocs"
strCLtr3 = "rptAcceptanceTerms"
StrCLtr4 = "Print Completion Letter"
stLinkCriteria = "[forms]![case recordsfrm]![casedetailstbl.case ref]="
& Me![case ref]

If Me.cmbSelectLetter = "Initial Contact" Then
DoCmd.OpenReport StrCLtr1, acViewPreview, "rptNewBusiness", , ,
stLinkCriteria
End If

If Me.cmbSelectLetter = "Return Documents" Then
DoCmd.OpenReport strCltr2, acViewPreview, "rptReturnDocs"
DoCmd.OpenReport strCltr2, , , stLinkCriteria
End If

If Me.cmbSelectLetter = "Acceptance Terms " Then
DoCmd.OpenReport strCLtr3, acViewPreview, "rptAcceptanceTerms"
DoCmd.OpenReport strCLtr3, , , stLinkCriteria
End If

If Me.cmbSelectLetter = "Print Completion Letter " Then
DoCmd.OpenReport StrCLtr4, acViewPreview, "rptAcceptanceTerms"
DoCmd.OpenReport StrCLtr4, , , stLinkCriteria
End If

thanks for you help..
 
Here is your problem:

stLinkCriteria = "[forms]![case recordsfrm]![casedetailstbl.case ref]="
& Me![case ref]

Try this instead (notice the apostraphe I put before and after the
criteria in the expression):

stLinkCriteria = "[case ref]='" & YourOpenFormName![case ref] &"'"

Substitute "YourOpenFormName" with the actual name of your form.
 
hi tom,

thnaks for your help, this has not worked for me as an error 'Object
Required' is stated. my form is called 'case recordsfrm' and i have input
this as 'case_recordsfrm'. but it cant seem to recogise it.

any further suggestions.

thanks richard
 
I think you probably have a field in your report name [Case ref] and you
want the report filter based on the value in your combo box. If this is the
case then use something like:
stLinkCriteria = "[case ref]=" & Me![case ref]
if [Case Ref] is text, use:
stLinkCriteria = "[case ref]=""" & Me![case ref] & """"
 
If you are getting that error, then you are referencing a control that
isn't there. Is the object [case ref] output in your report(s) when you
print? The next thing is your form. Is the [case ref] a field in your
form?

If you want, you can go into design mode, and email me a screen shot of
your form. To do this, open the form in design mode, then type
Alt+Print screen. This will copy a picture of your screen to the
clipboard, and then you can paste this picutre into paint and email it
to me.
 
hi tom,

the case ref is on each report, and is also an autonumber field on the form
case recordsfrm.

i will e-mail screen shot if you can let me have your e-mail address. mine
is (e-mail address removed).

thanks

richard
 
Richard,

Copy and paste this over what you already have:

Dim strCLtr1 As String
Dim strCLtr2 As String
Dim strCLtr3 As String
Dim strCLtr4 As String
Dim strLinkCriteria As String

strCLtr1 = "rptNewBusiness"
strCLtr2 = "rptReturnDocs"
strCLtr3 = "rptAcceptanceTerms"
strCLtr4 = "Print Completion Letter" 'This is a naming inconsistency.
stLinkCriteria = "[case ref]='" & Me![case ref] & "'"

Select Case cmbSelectLetter

Case Is = "Initial Contact"
DoCmd.OpenReport strCLtr1, acViewPreview, , stLinkCriteria

Case Is = "Return Documents"
DoCmd.OpenReport strCLtr2, acViewPreview, , stLinkCriteria

Case Is = "Acceptance Terms"
DoCmd.OpenReport strCLtr3, acViewPreview, , stLinkCriteria

Case Is = "Print Completion Letter"
DoCmd.OpenReport strCLtr4, acViewPreview, , stLinkCriteria

Case Else:

End Select

This should get you the desired results.
 
Back
Top