problems with docmd.openreport

[humer]

Hello everybody.
I made next function that open a report with a condition that is made from a combo box in a form what is attached with a checkbox. If the checkbox is valid then for the criteria is attached a additional condition.
The problem is the next: if there is more that one condition then its apear an message box that contains the value of condition. How can I change the function without having troubles with messageboxes ?

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

Dim stLinkCriteria1 As String

Dim Msg As String

stLinkCriteria1 = "[Fin_Program_ID]=" & Me![Fin_Program_ID]

If (Me![c2]) Then
stLinkCriteria1 = stLinkCriteria1 & " and [Project_Domain]=[" & [Project_Domain].Value & "]"
End If

If (Me![c3]) Then
stLinkCriteria1 = stLinkCriteria1 & " and [Project_Category]=[" & Me!Project_Category.Value & "]"
End If

If (Me![c4]) Then
stLinkCriteria1 = stLinkCriteria1 & " and [Project State]=[" & Me![Project State].Section & "]"
End If


DoCmd.OpenReport "Projects_full", acPreview, , stLinkCriteria1

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.description
Resume Exit_Command116_Click

End Sub

Another question for the same report: can I change the visibility of some fields in the report from VBA in the same function ???

 

[Mile-O]

Hello everybody.
I made next function that open a report with a condition that is made from a combo box in a form what is attached with a checkbox. If the checkbox is valid then for the criteria is attached a additional condition.
The problem is the next: if there is more that one condition then its apear an message box that contains the value of condition. How can I change the function without having troubles with messageboxes ?

• What are the conditions?
• Are the fields in the criteria used in the report you are opening?
• By "checkbox is valid" do you mean True?
• Does the Message Box say "Enter Parameter Value"?

You need to give as much information as possible. As it's hard to give an answer as your question currently stands. Also, the snippet of code is not enough - it's best advised to paste the whole subroutine here.



[EDIT] - Now half my questions are no longer required.

 

[Mile-O]

I see your problem - you are enclosing values within square brackets which is forcing the report to believe they are field's or controls.

Based on the assumption that your fields are text:

Private Sub Command116_Click()
    On Error GoTo Err_Command116_Click
    
    Dim stLinkCriteria As String
    
    stLinkCriteria = "[Fin_Program_ID] = " & Me.[Fin_Program_ID]
    
    If Me.[C2] Then
        stLinkCriteria = "[Project_Domain] = """ & Me.[Project_Domain] & """"
    End If
    
    If Me.[C3] Then
        stLinkCriteria = stLinkCriteria & " AND [Project_Category] = """ & Me.[Project_Category] & """"
    End If
    
    If Me.[c4] Then
        stLinkCriteria = stLinkCriteria & " AND [Project State] = """ & Me.[Project State] & """"
    End If
    
    DoCmd.OpenReport "Projects_full", acPreview, , stLinkCriteria
    
Exit_Command116_Click:
    Exit Sub
    
Err_Command116_Click:
    MsgBox Err.Description
    Resume Exit_Command116_Click

End Sub

In other places I'd advise you to do a search on naming conventions and early/late binding, also.

 

[humer]

Re: Re: problems with docmd.openreport

• What are the conditions?
• Are the fields in the criteria used in the report you are opening?
• By "checkbox is valid" do you mean True?
• Does the Message Box say "Enter Parameter Value"?

You need to give as much information as possible. As it's hard to give an answer as your question currently stands. Also, the snippet of code is not enough - it's best advised to paste the whole subroutine here.



[EDIT] - Now half my questions are no longer required.

1. if the checkbox is selected then apend the criteria
2. yes, there are this fields
3. valid = true
4. the message of the checkbox is the criteria that is selected
exemple: I selected from the combo cb2 Criteria1 and I has valiadate the checkbox c2:
[Fin_Program_ID]=1 and [Project_Domain]=[Criteria1]
and the messagebox
title: Enter Parameter Value
text is: Criteria1

 

[Mile-O]

Re: Re: Re: problems with docmd.openreport

I selected from the combo cb2 Criteria1 and I has valiadate the checkbox c2:
[Fin_Program_ID]=1 and [Project_Domain]=[Criteria1]

[Fin_Program_ID]=1 AND [Project_Domain] = """ & [cb2] & """"

 

[humer]

thanks you very much
that realy works
I have one more question: can I change the visibility of some fields in the report in the same function ???

 

[Mile-O]

Yes.

Me.TextBox1.Visible = False
Me.TextBox2.Visible = False

Simple as that....

 

[humer]

A new problem: I want to change the visibility of some fields in the report from the same form. I made some checkboxes in the form for selection of the fields that I need in the report. By default the fields from the report are not visible.

Here is the code that change the visibility, but it doesn't work

If (Forms![Export]![state].Value) Then
Report.[Projects_full].[Project State].Visible = True
End If

at end is an problem: a messagebox:
title: Microsoft Office Access
Message: Object required