open a form with conditions (1 Viewer)

[scallebe]

Good evening (for me it is here in Belgium) developers

I would like to open a form in 2 ways : as a User or as a Admin.

I have a table with all the users where the Usertype field has a possible selection User or Admin. (from another table)

When the Usertype is "User" the form should go open and show only the records he/she added. When the Usertype is "Admin" the form should go open and show all the records.

I have some code but I already understand it's not that simple.

Private Sub Form_Load()

If [tabelCorrespondenten.Usertype] = ("Admin") Then

DoCmd.OpenForm "FormPP", acNormal

Else

DoCmd.OpenForm "FormPP", , , "Tarifeerder = GetWinUser()"
DoCmd.GoToRecord , , acNewRec

End If

End Sub

I already find out I have to do some 'Recordset' but there is where it stops for me.

Is there someone who can help me with this issue?

Thanks

Greetz

Pascal

 

[isladogs]

Consider using the OpenArgs property to define how the form is opened

 

[scallebe]

isladogs,

Thanks for your reply.

I have some basic in vba and I tried this but it didn't work

Private Sub Form_open()

If Me.OpenArgs() = "Admin" Then
DoCmd.OpenForm "FormPP", acNormal

Else

DoCmd.OpenForm "FormPP", , , "Tarifeerder = GetWinUser()"
DoCmd.GoToRecord , , acNewRec

End If

End Sub

Can you help me a little further please

Thanks

Greetz

Pascal

 

[isladogs]

Hi
That's not correct.
You need to define the open args values first.
In use there should be no () after me,openargs
I would use the Form load event not form open

I'm just about to sign off for tonight.
There are many webpages explaining open args.
For example, see if this link helps https://support.office.com/en-us/article/OpenArgs-Property-C8616938-CD58-4B1A-84CF-3D043F59E13B

 

[deletedT]

Does this works?

Private Sub Form_open()

If Me.OpenArgs() = "Admin" Then
DoCmd.OpenForm "FormPP", acNormal

Else

DoCmd.OpenForm "FormPP", , , "Tarifeerder = " & GetWinUser()
DoCmd.GoToRecord , , acNewRec

End If

End Sub

 

[pbaldy]

Since the user is presumably a text value, perhaps:

DoCmd.OpenForm "FormPP", , , "Tarifeerder = '" & GetWinUser() & "'"

 

[arnelgp]

where are you opening the FormPP? is it from the Login screen or Welcome screen?

 

[scallebe]

Hi everybody, thank you so much for your reply...

Tera's code doesn't work
pbaldy : the way you open the form also doesn't work


These codes works seperetly perfect:

Shows all records :

Private Sub Form_load(Cancel As Integer)

DoCmd.OpenForm "FormPP", acNormal
DoCmd.GoToRecord , , acNewRec

End Sub

Shows only the records created by the user (it can be a 'user' or an 'admin') :

Private Sub Form_load(Cancel As Integer)

DoCmd.OpenForm "FormPP", , , "Tarifeerder = GetWinUser()"
DoCmd.GoToRecord , , acNewRec

End Sub

I think, to combine the 2 ways, access needs to find out first if the user on his/her local machine is a 'user' or an 'admin' witch can be found out in the table 'TabelCorrespondenten' and then open the form one way or the other way.

My research in google learned me that I have to do that with a OpenRecordSet… or a DLOOKUP... True?? (but how I don't know ) and then use the if - then - else argument...

Thanks

Greetz

Pascal

 

[scallebe]

arnelgp,

Thanks for your reply.

The form can be used after a login. it's connected on a table 'TabelPP'. It's one of the many forms in my db and opens by a switchboard.

Greetz

Pascal

 

[arnelgp]

form Load or Open is not appropriate to put the code.
you should put it after the login is validated.

If Me.OpenArgs() = "Admin" Then
DoCmd.OpenForm "FormPP", acNormal

Else

DoCmd.OpenForm "FormPP", , , "Tarifeerder = '" & GetWinUser() & "'"
docmd.GoToRecord acDataForm,"FormPP",acNewRec

End If

add also code to the BeforeInsert Event of "FormPP"

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!Tarifeerder  = GetWinUser()
End Sub

 

[isladogs]

As I said originally, if using OpenArgs, you need to define what the OpenArgs means before opening the form.
My suggestion is that it is based on UserType.
So I would create a function GetUserType based on user login details

Function GetUserType()
GetUserType = DLookup("UserType", "TabelCorrespondten", use filter based on login)
End Function

The filter criteria would check the user login on the login form. Is that the Tarifeeerder and GetWinUser check?

Next in your calling form, use that in the OpenArgs

If GetUserType="Admin" Then
  DoCmd.OpenForm "FormPP", , , , , ,GetUserType
Else
  DoCmd.OpenForm "FormPP", , , "Tarifeerder = '" & GetWinUser & "'", , ,GetUserType
End If

The above assumes that Tarifeerder is a text field

Finally in FormPP itself, do something like this in Form_Load

If Not Me.OpenArgs="Admin" Then DoCmd.GoToRecord , , acNewRec

 

[scallebe]

arnelgp,

Thanks for reply.

The form doesn't have to open after Login. After Login, the Switboard forms open. FormPP is just one of many…
I think your solution is not an option here... forgive me if I'm wrong...

isladogs,

Also thanks for reply.

I got this one so far :

Function GetUserType()
GetUserType = DLookup("UserType", "TabelCorrespondenten", "UserLogin = GetWinUser()")
End Function

and

Private Sub Form_Load()
If Not Me.OpenArgs="Admin" Then DoCmd.GoToRecord , , acNewRec
End Sub

Is this correct?

Where do I put this code

If GetUserType="Admin" Then
  DoCmd.OpenForm "FormPP", , , , , ,GetUserType
Else
  DoCmd.OpenForm "FormPP", , , "Tarifeerder = '" & GetWinUser & "'", , ,GetUserType
End If

To all the developers :

I'm a VBA student... (beginner) I try to understand your solutions and try to figger out what they do. Sometimes I can follow but sometimes :banghead::banghead:

So thank you all for your help. Thank god there Forums like this…

Greetz

Pascal

 

[scallebe]

arnelgp,
FormPP doesn't have to open after Login. After Login a Switchboard Items Form opens. FormPP is just one of the forms in my DB. Also The Login & Password Form is not connected to a table, so the input is not saved.

I can not put your solution in the Login Form If that's what you mean. I think…

isladogs,

This is what I gor so far :

Function GetUserType()
GetUserType = DLookup("UserType", "TabelCorrespondenten", "UserLogin = GetWinUser()")
End Function

and

Private Sub Form_Load()
If Not Me.OpenArgs="Admin" Then DoCmd.GoToRecord , , acNewRec
End Sub

Correct so far?

Where do I put the "If GetUserType…. code"?

I want to thank all of your for your help here, I'm just a VBA student (beginner ) and I try to learn form this. Thank God there are Forums like this

Greetz

Pascal

 

[pbaldy]

Post 12 was moderated, I'm posting to trigger email notifications.

 

[Pat Hartman]

Typically, when a user logs in, I store certain security info about him in the login form and just hide the form when I open the switchboard so the form is always available for reference throughout the app. Depending on someone's security settings, I may allow add/change/delete but that check is done at the point where a user tries to take the action. So, in the current event, if this is a new record, I check to see if the user is allowed to create new records. If he isn't, I undo the change, cancel the event and present a message.

In your case, if the only difference is what records a person can see, modify the query.

Select ...
From ..
Where IIf(Forms!frmLogin!UserType = "Admin", True, IIf(CreatedBy = Forms!frmLogin!UserID, True, False) = True

 

[isladogs]

Assuming user login is a text string you need text delimiters
Place the function code in a standard module

Function GetUserType()
GetUserType = DLookup("UserType", "TabelCorrespondenten", "UserLogin = '" & GetWinUser & "'")
End Function

The If...Else...End If code to open FormPP needs to go on the appropriate button click event in your login form

 

[scallebe]

Hi everybody,

I don't know what happend with post #12. I clicked submit reply and then something strange happend and brought me back to the loginform. Back to the thread I couldn't find my reply so I started again…

After Pat Hartman's reply I started thinking and came up with the following solution :

First I set my FormLogin "vissible = False" instead of closing it.
I also find out in de FormLogin that there was a code to lookup the UserType (for later use after login). So I used that code in my FormPP :

Private Sub Form_Load()
Dim Userlevel As Integer
    Userlevel = DLookup("UserType", "TabelCorrespondenten", "UserLogin = '" & Forms!FormLogin.txtUserName.Value & "'")
        If Userlevel = 1 Then ' for admin
        DoCmd.OpenForm "FormPP", acNormal
        Else
        DoCmd.OpenForm "FormPP", , , "Tarifeerder = GetWinUser()"
        DoCmd.GoToRecord , , acNewRec
        End If
End Sub

So far it works for me.

Later I will look into the "OpenArg" solution. It's a new itme I have to learn.

Thank you all so much for the proposed solutions. I'm sure I will come back later for this Thread or another one.

Greetz

Pascal