Form reminders for specific user only

[Koloss]

Hello.
In my database, I have a form of reminder.
Query collect all the information about the unfinished business at the base and at the start-up of the base shows form reminders.
The base is splitet into front and back end, and are located on the server. Each user is assigned rights what can be done in the database.
The problem where I am "stuck" is to make the form reminder that can be seen only by those users whose jobs are not completed and not all users of the database. Each user uses his computer with your username and password. I tried to connect to form reminder with this information, but I failed.
I searched all the forums but have not found such an example.
Does anyone know how to overcome this problem ?

[June7]

Need to identify the users. Either hard code the user privileges or create a users table with defined privileges. Identify the user when the db is opened either by login procedure or pull username from the network login. Open forms in mode appropriate to the identified user.

However, be aware that holding down the shift key will override many optional database settings and bypass code execution on open which means the db is wide open to anyone. F11 will also expand/collapse the navigation pane. Unless you want to publish the db as an executable.

Login and user privileges are common topics. Search forum or web.

What did you try and why did it not work, what happened - error message, wrong results, nothing?

[Koloss]

Thank you very much for your prompt response.
Copy of the front-end should be placed on any client computer…. I know, but as the base of the "test" work and need often make corrections in it, it is still on the server. When all updates are complete, everyone will get a copy of front-end base.

Query collects all the unfinished business from the base. Basically from only one table, based on the callback expiration date.

On load of Main form i have:


Dim intStore As Integer
intStore = DCount("[Naziv_plana_u_izradi]", "[tblUslovi_detalj]", "[Dat_podsetnik] <= Now() AND [sa_liste] =0")
If intStore = 0 Then
Exit Sub
Else
If MsgBox("Ima " & intStore & " uslova koji misu stigli" & _
vbCrLf & vbCrLf & "Hocete li da ih pogledate odmah?", _
vbYesNo, "Uslovi koji kasne...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmReminders", acNormal
Else
Exit Sub
End If
End If


[sa_liste] is check box, which when clicked removes the job from the list.

Username and password for the database user is different from the user names and passwords for logging into the IT system.
And i have table: tbl_AssignUsersToGroups, tbl_UserAccess, tbl_ObjectControls, tbl_Groups, tbl_ControlsToDisable which used to allocate rights to users..

I made a mistake at the start. Table tbl_UserAccess have column : first, last, user name and password. In the table with unfinished business I have full name (first and last along the same column ) related to unfinished business.
I try to connect that names on couple way with query. In the startup procedure, I try to compare [LoginName] with the set of [UserName] returned by the query. For a query the criteria I try

WHERE [LoginName] IN ( (SELECT [UserName] FROM qry_UnfinishedBusiness) );

next i try

SELECT * FROM tblUslovi_detalj WHERE userID=CurrentUser AND Completed=False etc, etc...

but I get an error message “The SQL statement could not be executed because it contains ambiguous outer join.” or ” trying to open a table that is already open”…
I really stuck…

Just to mention ... It's much easier to make a form with combobox filter with users names. And subform with the list of unfinished business. I ask this question because I want to learn something new. And because I do not like to give up easily from the problem.
Thanks to everyone who will try to solve the problem.

[MarvinM]

Koloss,

This is not hopeless. I think we can get you where you need to be.
The first thing we need is a way to connect the [UserName] from you list of UnfinishedBusiness to the [LoginName] in your UserAccess table. Are these two fields in sync? Are they the same data type and are the names spelled exactly the same in both?

Next, you want to know if the [LoginName] exists in the domain of [UserName] in the results of your UnfinishedBusiness query. Right?

I would go with some code like this:

If DCount("[UserName]", "qry_UnfinishedBusiness", "[UserName] = '" & [LoginName] &"'")>0 Then
  MsgBox("You have Unfinished Business to complete.", vbExclamation, "Notice")
End If

This of course depends on [LoginName] being a string you can use here. If it's not, declare a string variable and set it =[LoginName] and use that here. I hope this is helpful. If it doesn't give you the exact solution, maybe it will get you going in the right direction.

Marvin M

[June7]

Without the entire code procedure to analyze, can't really advise. I wouldn't store names in Unfinished Business, just user ID.

There are many ways to approach this situation.

I don't implement a username/password for the database different from the network login. I presume if user logged into network they are a valid db user. I grab the network username with:

Environ("USERNAME") - yes, type it EXACTLY as shown.

I do have a 'login' procedure for new users. This establishes them in the Users table and they never see the login again.

I have MainMenu form bound to Users table. This form opens filtered to the user record and it never closes. This makes the user info available for reference anywhere in the db, including their privileges category.

[Koloss]

Thank you very much guys. As Marvin said, you sent me in the right direction.

[MarvinM]

Koloss,

Glad to help. You know how to thank us is by clicking on the little sheriff badge in the lower left corner of our posts. We don't do this for money; we do it for "reputation points". Your click just might be the one that gets me a second green box under my name. Woohoo!

Note that June has a great point there about using the network login. I always do this whenever possible just so my users don't have to remember another password. (Big complaint around here. )

Much appreciated,
Marvin M