Log-in form help please

[uk_martin]

Hi all

I am preparing to try something new to me, but which is probably "old hat" to many others here, so please have a little patience.



I've found this tutorial for creating a simple log-in form - http://www.databasedev.co.uk/login.html

What I need to know is wheter this form, when created from the tutorial, will create some kind of record within Access that can be used, so that when the logged on user creates a new data record, their username can form part of an automated record reference. At the moment, a unique reference number is created with every record added to the database, but what I want for the future is for reports to be able to show the reference number in the "username\unique reference number" format.

If it can be done from this log-in form then great, and please let me know how to link the username to the data table and/or report.

If not, can this be modified, or is there a better alternative log-in form out there more suitable to what I need?

Thanks in advance.

Martin

[pbaldy]

The sample code stores the logged-in user in a public variable named "lngMyEmpID". You can use that value in later code to place the value in your user field. Another option would be to hide the login form rather than close it and simply refer to the textbox containing the user in the default value property of your data entry form.

[uk_martin]

Thanks very much for your reply. Glad that there seems to be a simple way forward (or two)

Cheers

Martin

[pbaldy]

No problem, and welcome to the site by the way.

[uk_martin]

Thank you

As a matter of interest, what's the simplest way for the value that the "IngMyEmpID" contains to be inserted into the data table, in a column of its own, so that all the records created by that logged in user in their session will be attributed to them in the future?

I'll be adding a column into the data table called "EmpID" and the "IngMyEmpID" is the 4th item in a module called "Post Database" ("Public lngMyEmpID As Long")

The reason I want to record the values in the table is so that there is a permanent record of the operator who inputted the data, and not so that a report picks up the ID of another logged in person, running off a report.

Thanks again in advance.

Martin

[pbaldy]

Simplest would probably be

to hide the login form rather than close it and simply refer to the textbox containing the user in the default value property of your data entry form.

You can't refer to a variable outside of VBA, so you'd either have to place the value there in VBA code or create a function that returned the variable value and refer to that in the default value property.

[uk_martin]

The database that I "inherited" had a macro that closed the log-in screen. I did start by keeping what was working, etc, so it looks like I'll have to change the Macro around. Problem is, that whilst "Close" is a command that is in the design view action options, "Hide" isn't.

Being a Macro novice, which of the options should I use to do the hiding of the login form?

Thanks

Martin

[smithse]

Hi uk_martin,

I do exactly (or similar anyway), and I use a table in the database which consists of the users and their "permission" levels. In this table is a column which stores the NTUsername which I grab from a function to return the currently logged on user. I store this in a variable which is then later accessible. In actual fact, I have come acorss a number of instances in which Access forgets the values of global or public variables, so have a function which checks the value of that variable, and if it is not there, then query the database again.
You could store all the details in a table, but I decided not to given the number of times by application is querying the "CurrentLoggedOnUser" property.

This of course is helped by the fact that each user has a separate account on the computer.

If you want any more help then shout.

[uk_martin]

Hi Smithse

I started by creating a login form using the tutorial quoted above. It meant that I had to create a "tblEmployees" table (per the tutorial) however I added a column for the users Full Names as well. Everyone has the same permissions, so permissions aren't an issue in my case.

The login form has a combo box where a users initials can be selected. What I need to be able to do now is when the "go" button is pressed, is for the login form to "hide" but the value of the users initials (and eventually their full name as well) need to be added to any new records created in the main data table, in the appropriate columns in that table.

I'm looking at tutorials etc to see how to use stuff from one form in another, and lets see if I can do this by myself (lol), however this thing about hiding the login form, is puzzling me, as I expected "hide" to be an option in the macro builder, the same as "close" was, however, it's not there.

[smithse]

Hi uk_martin,

Well, here's my view on in:
I don't use hidden forms very much, and prefer to use Public or "global" variables as some people term them.

In this case, you might have a module called "CurrentUser" for example.

That might have a variable called "CurrentLoggedOnUserID" e.g. Public CurrentLoggedOnUserID as string

Then, upon clicking my CommandButton, I'd set the value of the variable named above, to the value of the combo box, so something like

CurrentLoggedOnUserID = me.cboSelectUserInitials (where "cboSelectUserInitials" id the name of the combobox option)

The variable "CurrentLoggedOnUserID" should be accessible from VBA or any queries.

Sorry, I can't help too much with macros, as I am a VBA man rather than a macro man LOL!

Oh, and my apologies if I didn't understand what you are talking about, as I have not seen the sample or tutorial DB.

[uk_martin]

Thanks smithse

Lets see if I'm adapting what you are saying correctly then...

The Tutorial (link above) suggests putting "Public lngMyEmpID As Long" into "a module" - so I've created a module called "CurrentUser" as you've suggested, and pasted what the Tutorial suggested. Will it make a difference if it's "as string" or "as long"? I can try the other alternative if needs be. Just wondering, that's all.

By "Command Button" I presume you mean the "Add New Record" button on the main form?

The underlying code for the "Add New Record" button is:

Private Sub new_record_Click()
On Error GoTo Err_new_record_Click


DoCmd.GoToRecord , , acNewRec

Exit_new_record_Click:
Exit Sub

Err_new_record_Click:
MsgBox Err.Description
Resume Exit_new_record_Click

End Sub

So do I add "Public lngMyEmpID = me.cboEmployee" (adapted from your suggestion to fit in with my asset naming) after the "Private Sub new_record_Click()" line?

Thanks

Martin

[smithse]

uk_martin,

It appears, (again, reading the code, and not trying out the example) that every time a user logs onto the database, a record is created in a table. Is this what you would want? I am not too sure or convinced that it is.
What version of Access are you using?
The form that you are using in this case would be an 'unbound' form, not linked to a table, and the VBA code doing all the work for you.
Let me know what version, and I can post an example.

Just very quickly in my little brain, the logic that I would be building would be (and this is upon clicking the command button)

1. Check that the username field contains something
2. Check that the password field contains something
3. Perform a check that the password matches the stored one
4. If it does, then store the value of the 'logged on user' in the Public variable
4a. If not, either give the user another chance, or kick them out of the app.

[uk_martin]

Hi Smithse

Thanks again for the reply. What I want is for the main data table to have two additional columns, one to permanently store the initials of the person who created any new records in there, and one to contain that person's full name. This data will later be used for invoice reference numbers and signature blocks in letters, generated as reports in Access.

The method of ading the initials to the data table will be by means of pulling it off the login form and into the table, at the same time as the "Add new record" button is pressed in the main data input form.

Embarrasingly, I still use Access 2003

I can't say that I understand how the "Public lngMyEmpID As Long" bit of a module works. I haven't created a table for the user log-ins to be stored as such. Whether Access creates a temporary table for every session, I don't know. I'm too new to all this to understand the behind the scenes workings I'm afraid.

Basically, I have two relevant tables:
"Post" for the main data
"tblEmployees" with the user names and initials.

...and two relevant forms:
"Login Form" for the logging in of the users
"Post" for the main data input.

There's a module called "Current User", and a Macro called "Close Login Form" too.

Hopefully that spells out what I've got and where I want to go with it.

Thanks in advance for offering to help out.

Cheers

Martin

[smithse]

Hi Martin,

Not a problem. Consider an option though: You want to store two additional columns, when in fact, you only need to store one. This is the fundamental design of a relational database.

Once you store the initials of the 'editor' of the record, in that records, you have also stored their full name, their department, phone number or whatever other information you might have in the tblEmployees table. How? Easy... Their employeeID is stored in the row, and joined by a query or relationship. Also, I would use a 'unique' value to store that in the "Post" table. Instead of initials, I would use their employee number, a value that will never change. Initials can ;-)

Let me knock up a sample.. (hope it works for you, as I am using 2010). uk_martin.zipuk_martin.zip

[pbaldy]

The database that I "inherited" had a macro that closed the log-in screen. I did start by keeping what was working, etc, so it looks like I'll have to change the Macro around. Problem is, that whilst "Close" is a command that is in the design view action options, "Hide" isn't.

Being a Macro novice, which of the options should I use to do the hiding of the login form?

Thanks

Martin

If you go back in this direction, I think it's SetValue (I'm not at a computer right now). You'd set the Visible property of the form to False.