Syntax error prevents hiding control on report? 1

[capndave]

I am a true beginner. On open I want the report (rptIssuePrepImpl) to not display PhyName control when PhyName = "_O.R. Materiels Mgr. (Stock)"

This is where I am and I get syntax error. My deadline is approaching fast.

Private Sub Report_Open(Cancel As Integer)

If [Me.PhyName] = "_O.R. Materiels Mgr. (Stock)" Then
[Me.PhyName.Visible] = False

ElseIf [Me.PhyName] <> "_O.R. Materiels Mgr. (Stock)" Then

[Me.PhyName.Visible] = True

End If


End Sub

 

[SgtJarrow]

This line:

ElseIf [Me.PhyName] <> "_O.R. Materiels Mgr. (Stock)" Then

should probably only be

Else

That's it...nothing else. This is because you have already tested for the value in the If opening line...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[capndave]

I did change ElseIF to Else but the error is on this line

[Me.PhyName] <> "_O.R. Materiels Mgr. (Stock)" Then

 

[capndave]

This is where I am stuck now. I get the runtime error "MS Access can not find the field "|" referred to in your expression. The If line is yellow in the debug screen.

Private Sub Report_Open(Cancel As Integer)

If [Me.PhyName] = "_O.R. Materiels Mgr. (Stock)" Then
[Me.PhyName.Visible] = False

Else
[Me.PhyName.Visible] = True

End If


End Sub

 

[SgtJarrow]

Alright....let's back up a couple steps and make sure you learn why. This is much better than me just giving an answer.

In Access, the "Me" is a shortcut to the current object, usually a form or report. When referring to a control on that object, you can use Me.controlname or me!controlname or even Me("controlname"). There are reasons for each one, but I won't delve into that. You refer to a property form or report with Me.property and for controls on the form or report with Me.controlname.property or Me!controlname.property or even Me("controlname").property The square brackets ([]) are only necessary if a portion of the declaration has a space in it...so if you had a control called "first name" you would use Me.[first name] or Me![first name] or Me("first name"). Note the last one, using the paranthesis, does not need the square brackets. This is becuase the double quotes define the name. The square brackets act as a marker for Access to know the full name of something containing spaces.

When using If statements, you need to check one instance for validity.
So if you want to do something if there is a match:

If something = something Then
' Do something
End If

If you want to have an answer or code if the value does not match the condition:

If something = something Then
' Do something
Else
' Do something else
End If

If you need to check for more than one condition, I personally prefer Select statements:

Select Case something
Case something: Do something
Case something else: Do something else
Case another something: Do another something
Case Else: Do this is no match was found in the cases
End Select

I suggest you take a close look at the help files for each of these when you need them until you become comfortable with them.
All that aside, I think the following should work for you:

Private Sub Report_Open(Cancel As Integer)

' First let's check if the PhyName field is equal to
' _O.R. Materiels Mgr. (Stock)
If Me.PhyName = "_O.R. Materiels Mgr. (Stock)" Then
    ' Since the PhyName field IS EQUAL to the checked value
    ' Let's hide the field
    Me.PhyName.Visible = False
Else
    ' Let's make the PhyName field visible, since it is not
    ' equal to the checked value
    Me.PhyName.Visible = True
End If

End Sub

If you have more questions or issues, please let me know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[capndave]

Robert,

Thanks so much for taking the time to educate me. Iam teaching myself and some things boil down to trial and error. However, the event still hangs up on the first line with what you gave:

If Me.PhyName = "_O.R. Materiels Mgr. (Stock)" Then

 

[SgtJarrow]

hmmmm...Make sure that that control actually does exist on the report and is named that. The control may be called something like Text1 and have a control source of PhyName. You need to use the NAME of the CONTROL.

It might also be good to try to use the bang format in this case....bang means the ! format.

So:

Private Sub Report_Open(Cancel As Integer)

' First let's check if the PhyName field is equal to
' _O.R. Materiels Mgr. (Stock)
If Me![PhyName] = "_O.R. Materiels Mgr. (Stock)" Then
    ' Since the PhyName field IS EQUAL to the checked value
    ' Let's hide the field
    Me![PhyName].Visible = False
Else
    ' Let's make the PhyName field visible, since it is not
    ' equal to the checked value
    Me![PhyName].Visible = True
End If

End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[capndave]

Robert,

I am still getting runtime error "you entered and expression that has no value"

Does it matter that "PhyName" is actually stored in the table as the "PhyCode". The report is based on a query in which the table Order_Log ( which stores PhyCode) is in a relationship with the lookup table (Phycode, PhyName) to display the PhyName on the report.

Thanks again for all your help. I am on my own as there is no one I know that has more Access knowledge than me.

 

[SgtJarrow]

I am always glad to help out.....

What the field in the table is called doesn't matter. What we need to determine is what the CONTROL on the report is called. If you have the report open in design view, and right click on the control that displays the value and select properties - you should get the properties window. (You probably already knew how to get there...just making sure). The property we want to look at is NAME.

Whatever is listed in the NAME property is what should be between the brackets of the Me![here] in my last example.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer