Use UserId to login to DB 1

[DavidDrotts]

I am building a database to track a process that requires three levels of participation, the imputer, work center manager, and one office manager (i.e. imputer inputs data…manager approves input and sends to office manager for final approval) The office manager is responsible for 6 work centers. The first two levels (imputer and work center manager) are only allowed to view information regarding their respective work centers. To accomplish this I have built three forms, one for each level, that pull its information from a query that gathers the data only relevant to that particular work center. This is my vision…upon db startup user sees a splash screen that retrieves their computer logon identify and uses that information to open only the form that that particular person has access to. (This will negate having to manage/use a password security methodology.) To achieve this I have created a table that holds each user and their respective user ID (UserID) and I am using the following (see below) code to retrieve the logon information of the user. Question…how do I connect the dots? I can make the splash screen show the user id (text1), but how do I turn that into opening the form that is for that particular user?

Thanks in advance fro your wisdom AND time!

David

Option Explicit
'windows-defined type
Private Type SYSTEM_INFO
dwOemID As Long
End Type

Private Declare Sub GetSystemInfo Lib "kernel32" (lpSystemInfo As SYSTEM_INFO)
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long




Private Type ComputerUserInfo
UserName As String
ComputerName As String
End Type


Private Sub Command0_Click()

Dim CUI As ComputerUserInfo


GetUserInfo CUI

'show the results
DisplayResults CUI


End Sub

Private Sub GetUserInfo(CUI As ComputerUserInfo)

Dim r&, ret$

'get the user's name
ret$ = Space$(256)
r& = GetUserName(ret$, Len(ret$))
CUI.UserName = StripNull$(ret$)

'get the computers's name
ret$ = Space$(256)
r& = GetComputerName(ret$, Len(ret$))
CUI.ComputerName = StripNull$(ret$)

End Sub


Private Sub DisplayResults(CUI As ComputerUserInfo)
'fill in the labels


' Label1 = "User Name :"
' Label1 = "Computer Name :"

'show the info
'Computer User Info and Windows Registry Info
text1 = CUI.UserName
Text2 = CUI.ComputerName

End Sub




Private Function StripNull$(item$)

On Local Error Resume Next
StripNull$ = Left$(item$, InStr(item$, Chr$(0)) - 1)

End Function


Private Sub Form_Load()
Dim CUI As ComputerUserInfo

DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdRefresh
GetUserInfo CUI

'show the results
DisplayResults CUI


End Sub

Private Sub Form_Timer()
On Error GoTo Err_Timer_Click

Text10 = Now()
Text16 = "Active"
DoCmd.RunCommand acCmdRefresh

Err_Timer_Click:
Text10 = Now()
Text16 = "Active"
DoCmd.RunCommand acCmdRefresh
Exit Sub

End Sub

 

[RickSpr]

Warning: Trying to avoid user logon usually compromises security.

If you proceed in this vein, any user with a little Access knowledge can simply create a new database, link in your tables, and read the data you're trying to hide. Any security implementation based on VBA code will have this flaw.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DavidDrotts]

Hi Rick,

Security isn't a great concern. The information isn't sensitive and the only reason why I am going this route is that I am trying to avoid a complicated switchboard process that directs the right user to the right form.

Thanks for your post!
David

 

[RickSpr]

Ok. Then why not store the relevant form name in the table with the user information, and retrieve it to open the form?

DLookup function to retrieve the table name.
DoCmd.OpenForm to open the form.

This seems rudimentary, something you should already know if you're designing a multiuser solution. Am I missing something?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DavidDrotts]

Hi Rick,

Feeling a little stupid here…

I have never played with the Dlookup function before ( I have only been building applications for 4 months and my knowledge comes from interlibrary loan books!) so I didn’t even think of going down that road. Thanks for pointing it out!

BUT…I am still confused. I have built the Dlookup formula that drops the name of the form that matches the current user into the splash screen. You suggested “DoCmd.OpenForm to open the form.” How do I programmatically open the form? The field on the splash screen that holds the name of the form to be opened is FormAccess.

What would the syntax look like???

Thanks again for your time…you have been a big help!

David

 

[RickSpr]

I had been wondering why you were using type indicator suffixes in your Dim statement (r[red]&[/red], ret[red]$[/red]). They're very archaic, dating back to GW-Basic for MS-DOS days. Those must be some pretty old books you've been studying, and I can see you wouldn't get a good grip on Access from them.

You need to study some more up-to-date examples. The Northwind sample database supplied with Access, with liberal reference to the Help files, is a fair start. An aftermarket Access book, such as Access 2000 Developer's Handbook or the like, is even better if you can obtain it. (There are different editions for different versions of Access.)

Instead of storing the looked up form name "into the splash screen" (which I understand to mean that you're setting an unbound text box control?), you should store it in a Public variable that you've declared in a standard module. For example,
Code in standard module Module1:
Public strMainForm As String

Code in the splash screen's module:
strMainForm = DLookup(...)
DoCmd.OpenForm strMainForm



Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DavidDrotts]

Hi Rick,

I guess you can say I am struggling, but I am having a blast along the way.

I came up with the following solution to passing the Form name to the docmd.formopen.
It’s not very inventive and I am having problems with my stLinkCriteria statement, I keep getting “Enter parameter value" when my form opens. If I click okay through the prompts my form opens up perfectly and only the records of the OPR are displayed.

My form is bound to a query and also has subform bound to a table. Of course the “enter parameter value” errors are firing on each of the query statements. My guess is that the form is loading and it’s not seeing any data….am I close? Or is it a simple syntax problem?

Text6 is the form to be opened
Text0 is the person responsible for completing the job. (There could be numerous jobs requiring completion, each with their own “OPR”. Doing this not only opens the form to the right work center but it also only shows the records that apply to the individual that logged in.)

Thanks again for sticking with me!

David

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = Me![Text6]

stLinkCriteria = "[OPR]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

 

[RickSpr]

Apparently, you've got more of a grip on Access than I had surmised. I apologize if you felt talked down to.

You are getting the prompt for parameters because something in your query's definition doesn't exist when the query is opened. Look for a misspelled name. If you can't find it, open the query in SQL View, and copy and paste the SQL statement here. Also, tell me what parameter you are being prompted for.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DavidDrotts]

When I open my form normally everything works fine. It is only when the form is opened via VBA do I get the “Enter parameter value"
This is the query that it is firing on…
SELECT DISTINCT CPOemail.[Supervisors Email], CPOOPR2qry.OPR, CPOemail.Last FROM CPOemail INNER JOIN CPOOPR2qry ON CPOemail.Last=CPOOPR2qry.OPR;

This query comes from an unbound field on the subform which is used to find the supervisor of the OPR’s e-mail address. Once the OPR is finished inputing their information they hit an e-mail button which generates a report summarizing the changes that OPR did and emails it to the supervisor (which was found from this unbound text box). (this way nobody has to manually plug an e-mail address in)

 

[RickSpr]

[purple]It is only when the form is opened via VBA do I get the “Enter parameter value"[/purple]
This suggests that the problem is in your stLinkCriteria. That should be the only difference when opening the form with VBA code. However, I don't see anything wrong with how you're building stLinkCriteria.

What parameter are you being prompted for? The parameter name appears above the text box on the prompting dialog.

I didn't understand what you mean in the last paragraph. Did you mean the parameter value comes from an unbound text box? How does that text box get filled in? What gets put into it?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DavidDrotts]

My apologizes, I didn’t expound upon the field enough and mistakenly called it a text box. In fact, it is a list box and the SQL statement that is firing comes from the row source of this field. This one statement cause three “Enter parameter value" prompts, and it looks like this…

First prompt…
Shows the first portion of the statement :
SELECT DISTINCT CPOemail.[Supervisors Email]

Second prompt…
Shows the middle portion of the statement:
CPOOPR2qry.OPR

Third prompt…
Shows the last portion of the statement:
CPOemail.Last FROM CPOemail INNER JOIN CPOOPR2qry ON CPOemail.Last=CPOOPR2qry.OPR

In its entirety the SQL statement looks like this:
SELECT DISTINCT CPOemail.[Supervisors Email], CPOOPR2qry.OPR, CPOemail.Last FROM CPOemail INNER JOIN CPOOPR2qry ON CPOemail.Last=CPOOPR2qry.OPR;

Again, the form does open fine normally, its just when I use VBA do I get these prompts.

The purpose of this statement is to find the supervisors email address of the OPR, because after the OPR updates their information they will click a submit button that will send the information to the supervisor. The code behind the submit button will look for the supervisors e-mail address from this field (address) and place it in the “send To” of the e-mail.

Submit button code:

Private Sub Mail_Click()
On Error GoTo Err_Mail_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Me.address.Requery


If IsNull(Me.address.Column(0, 0)) Then
MsgBox "There is no email address.", , "Email Address Needed"
Else
DoCmd.SendObject acReport, "CPOOPRrpt2", "Rich Text Format", Me.address.Column(0, 0), "", "", "Inspection Tracking System", "The following Forms require review.", True, ""
End If


Exit_Mail_Click:
Exit Sub

Err_Mail_Click:
Err.Clear
Resume Exit_Mail_Click

End Sub



Thanks again for your input! I am really learning a lot during these posts.

David

 

[RickSpr]

The prompts you are getting are the parts of the query separated by commas. It's as if the entire query were being interpreted as a list of columns. I think you are somehow inserting or submitting the SQL statement where what is needed is a list of columns.

Please try to use more precise terminology. You refer to "this field (address)" in your post, but you had not been talking about any field prior to that. It took me considerable studying to realize that 'address' is the name of the list box control you were discussing. Fields and controls are different things. I realize your imprecision is due to being somewhat new to Access, and I'm not chiding you for it. I'm just encouraging you to deepen your understanding of Access. It will not only help us communicate better, but it will give you a better mental model of what's happening in your application, which makes it easier for you to figure out what's wrong.

It also would help if you would refer to forms, controls, etc. by their actual names, instead of by descriptive references. Even though the names may be arbitrary and meaningless to me, they have the advantage that they uniquely identify an object. I only just realized that the form which builds stLinkCriteria is not the same form that is opening when you get the parameter prompts. If you had referred to the forms by name, rather than as just "the form", I would have had the picture from the start. Likewise with "the list box".

Getting back now to the query being interpreted as a list of columns: Where is this SQL statement? You said it was the RowSource of the list box control (that would be the 'address.RowSource' property, if I'm not mistaken), but I don't see how opening the form via VBA would have any influence on the execution of this query while loading 'address' at form open time. You're not doing something in the Form_Open procedure to change address.RowSource are you?

Regarding your Submit_Click procedure, I note that you always refer to a fixed row in the 'address' list box (Column(0,0)), rather than the selected row (Column(0)). Is there only ever one row in the list box? I don't see how your row source could limit the result to one row. Is there any other VBA code that refers to 'address'? Please explain.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[DavidDrotts]

Hi Rick,

You are right I need to straighten out my terminology.

To clarify, “Address” is an unbound control on the sub-form “CPOStatus”, which is on the form “CPOOPRfrm”. The SQL Staement is, as you surmised, in the row source proerty. On db open the splash screen will have the hidden unbound control “text6” (Dlookup compares user to the form the user should have access to and populates with the name of the form). After 10 seconds, splash screen closes (executes VBA which looks for the value of text6 and opens relevant form) and opens CPOOPRfrm, or one of 18 forms that will open after the splash screen closes, will open filtered with the data pertinent to the user.

I guess I have just enough knowledge to be dangerous!

A case in point (as if you needed another one!) is that I found where I inappropriately used that SQL statement in another location…

I had plugged in the SQL statement into the “Order By” property of the CBOStatus form. I don’t know why I did this. All I can say in the last two weeks I have done A LOT of trial and error. This is my second application and is the first time that I needed to be slightly concerned with security, carried at all about grouping data, and that I needed to e-mail reports out of the system. On a side note, my first application was a web based survey system based off of the SERVQUAL survey methodology. All it did was collect responses and spit out the results in a ready to brief PowerPoint show. The most code that application used was a long If LEN statement which made sure the participants answered all the questions prior to submitting the form.

ANYWAY, after clearing the Order By property everything is working great!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

User logs on…user is sent to their form (OPROpenFC,OPROpenCC, or OPROpenFC is an example of one work center (CPO)) which has been filtered so that only their records are viewable…gotta love it! Now I just have to pretty everything up and I will be able to out brief!


To answer your question about why I used Me.address.Column(0, 0), instead of just one 0. I just did…and I don’t have a good reason either! I thought I needed to clarify it with the additional 0, but after changing it around to just one “0” it still works fine. I think you just saved from a having a few errors in the future!



Thanks again for your help! I don’t know how much time I would have stumbled on this problem!


Take Care,
David Drotts

 

[RickSpr]

About the Me.address.Column(0, 0), though, let me elaborate. With the second 0 in there, you were always taking the value of the first column in the first row. That means it didn't matter WHAT row was selected by the user. If there was only one row in the list box anyway, it wouldn't make any difference, but I didn't see how the list box's row source query would limit it to one row.

With the second 0 omitted, you are taking the value of the first column in the selected row. (An equivalent way of doing this is Me.address.Column(0, Me.address.ListIndex).)

So in other words, unless you had just one row in the list box (and if so, why use a list box at all?), your original code did not work, except when the first row in the list box was selected.

Your terminology in this post was much clearer and easier to understand. Thank you.

One more thing: Lots of people talk about "fields" when they mean "controls". It's very common, and ordinarily I understand what they mean--the control bound to that field. But you were talking about an unbound control, which has no field at all, so I really wasn't sure what you meant.

Glad you got it working.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein