No data on form

[Gismo]

Hi All,

Is it possible to display a message when button is clicked to open form and there is no data to display in form?

 

[arnelgp]

On the click event if the button use Dcount on the table/query that the form is bound to. If zeri show the msg and dont open the form.

 

[Gismo]

The form is have the button located does not have any bound tables or queries. I have a macro opening a form but I need it to display a message if the user does not have access to open the form. the form that the button opens is linked to the users access table. any other suggestions?

 

[arnelgp]

Same thing on the click of the button chk if user has access to open the form. Use vba.

 

[isladogs]

You could use a dlookup based on your user access table. Something like

if nz(DLookup(......),"")="" then msgbox ....

But a better method is to either disable or hide the button for users without access rights

 

[Gismo]

do you perhaps have a code I could use to check for access before the form opens and gives a message if no access, please

 

[Gismo]

thank you ridders

 

[Gismo]

How will I hide the button if no access?

 

[isladogs]

In the form load event, use a dlookup based on the user access table as in my previous answer.
If the criterion isn't met, then
Me.mybuttonname.visible=false
Or
Me.mybuttonname.enabled=false

 

[Gismo]

I have tried below code which I had to add to previous code for hiding buttons.
after a combobox has been updated, only then will 4 of the buttons show. but now I only want 1 button to show related to the access granted to the user. this code seems to compare all 4 buttons with and "and statement" and not an Or statement.
so all buttons must be hidden before the update. after the update only the button related to user access must display.

now all buttons show if I have access to all and if I remove access to one, non of the buttons show

Private Sub WPNo_AfterUpdate()
If Me.WPNo > "0" Then
display all 4 buttons
If Me.Technician = "-1" Then
it technician has access, display technician button and hide other 3 buttons
If Me.Inspector = "-1" Then
if inspector has access, only display inspector button and hide other 3 buttons
If Me.AME1 = "-1" Then
If Me.DualInspector = "-1" Then
Me.TE.Visible = True
Me.CE.Visible = True
Me.DI.Visible = True
Me.AME.Visible = True

Else
Me.TE.Visible = False
Me.CE.Visible = False
Me.DI.Visible = False
Me.AME.Visible = False
End If
End If
End If
End If
End If
End Sub

 

[arnelgp]

if you only want to show one button:

Me.TE.Visible = False
Me.CE.Visible = False
Me.DI.Visible = False
Me.AME.Visible = False

If me.WPNO > "0" then
	Me.TE.Visible = (Me.Technician="-1")
	Me.CE.Visible = (Me.Inspector="-1")
	Me.DI.Visible = (Me.DualInspector="-1")
	Me.AME.Visible = (Me.Ame1="-1")
End If

 

[isladogs]

Private Sub WPNo_AfterUpdate()
If Me.WPNo > "0" Then
display all 4 buttons
If Me.Technician = "-1" Then
it technician has access, display technician button and hide other 3 buttons
If Me.Inspector = "-1" Then
if inspector has access, only display inspector button and hide other 3 buttons
If Me.AME1 = "-1" Then
If Me.DualInspector = "-1" Then
Me.TE.Visible = True
Me.CE.Visible = True
Me.DI.Visible = True
Me.AME.Visible = True

Else
Me.TE.Visible = False
Me.CE.Visible = False
Me.DI.Visible = False
Me.AME.Visible = False
End If
End If
End If
End If
End If
End Sub

I will answer this in several steps:

1, Check your datatypes
If, as seems logical, WPNo is a number then it should be

If Me.WPNo > 0 Then

If Technician, Inspector etc are Yes/No fields which is also logical then each line should be like this

Me.Technician = True

2. This would be more efficient

Private Sub WPNo_AfterUpdate()

Me.TE.Visible = False
Me.CE.Visible = False
Me.DI.Visible = False
Me.AME.Visible = False

If Me.WPNo > 0 Then
    If Me.Technician = True Then Me.TE.Visible = True
    If Me.Inspector = True Then Me.CE.Visible = True
    If Me.DualInspector = True Then Me.DI.Visible = True
    If Me.AMEFieldName = True Then Me.AME.Visible = True
End If
End Sub

If you prefer each line separately use If...ElseIf ...End if

3. Better still, just set the visible state according to the field value

Private Sub WPNo_AfterUpdate()

Me.TE.Visible = False
Me.CE.Visible = False
Me.DI.Visible = False
Me.AME.Visible = False

If Me.WPNo > 0 Then
    Me.TE.Visible = Me.Technician
    Me.CE.Visible = Me.Inspector 
    Me.DI.Visible = If Me.DualInspector
    Me.AME.Visible If Me.AMEFieldName
End If

End Sub

4.You could set the visible state of the 4 buttons to false in the form design so this becomes

Private Sub WPNo_AfterUpdate()

If Me.WPNo > 0 Then
    Me.TE.Visible = Me.Technician
    Me.CE.Visible = Me.Inspector 
    Me.DI.Visible = If Me.DualInspector
    Me.AME.Visible If Me.AMEFieldName
End If

End Sub

5. Finally, you could replace all 4 fields (Technician/Inspector etc) with a text field Job Role with values T,I,DI,AME and use

Private Sub WPNo_AfterUpdate()

If Me.WPNo > 0 Then

    Select case JobRole

    Case "T"
    Me.Technician.Visible = True

    Case "I"
    Me.Inspector.Visible = True

    Case "DI"
    ....

    Case "AME"
   ...
  
    End Select

End If

End Sub

 

[Gismo]

works perfect, thank you

 

[isladogs]

Out of interest, which solution did you use?
One of mine or arnel's?

 

[Gismo]

Arnel's was the easiest but tnx

 

[isladogs]

It was easiest but that doesn't fix any of the issues I mentioned in my post.
I would recommend you do so.