define an action if no records exist

[stillnew2vb]

Greetings gurus,

Could you help please?

I have a form which loads on Access startup. If a record exists in the form then an action is performed but how do you define an action if there are no records?

The code I have got is

If Me.UserName <> "" Then
DoCmd.Maximize
MsgBox "New Support Request" & Chr$(13) & Chr$(13) & "Double-click the username to view", vbOKOnly
ElseIf Not exist Then
DoCmd.Minimize
MsgBox "no requests", vbOKOnly
End If

When there are no records in the form I get an error message run time error 2427 You entered an expression that has no value coming from If Me.UserName... I understand why it is happening but what is the method of getting the code to run if there are no records?

Thank you for your time

 

[MarkK]

You can check for nulls here ....

If Nz(Me.UserName, "") <> "" Then
  DoCmd.Maximize
  MsgBox _
    "New Support Request" & vbcr & vbcr & _
    "Double-click the username to view", vbOKOnly
Else
  DoCmd.Minimize
  MsgBox "no requests", vbOKOnly
End If

Check out the Nz() function, which can help you handle nulls.

 

[ghudson]

You can also count the records in the form on open...

    If Me.RecordsetClone.RecordCount = 0 Then

 

[stillnew2vb]

Thank you lagbolt & ghudon.

Those both work very nicely.

 

[ions]

Lagbolt

I use

If IsNull(Me.UserName) then

as opposed to...

If Nz(Me.UserName, "") <> "" Then


I can see how NZ would be good in a Query, similar to IIF but isn't IsNull preffered in VBA? Or perhaps it's just a style difference

Thanks

 

[boblarson]

Lagbolt

I use

If IsNull(Me.UserName) then

as opposed to...

If Nz(Me.UserName, "") <> "" Then


I can see how NZ would be good in a Query, similar to IIF but isn't IsNull preffered in VBA? Or perhaps it's just a style difference

Thanks

Ions -

If Nz(Me.UserName, "") <> "" Then

is better than

If IsNull(Me.UserName) then

because the one with NZ handles empty strings as well as nulls where yours does not.