How can I make a sign in form?

[Guest]

I would like to make a sign in page that they type their name in and hit
submit. Then a screen would pop up that says welcome so & so and you have
earned so many points (1 point for each time they sign in). I would
appreciate any suggestions. I have spent 4 days searching books, internet and
etc.
Thanks,
Jean

 

[Allen Browne]

To achieve this, you will need to programmatically create a record each time
the person signs in, and then DCount() the number of records the person has.

1. Create a table to hold a record of log-ins. Fields:
ID AutoNumber primary key
UserName Text
LogIn Date/Time
Save it with the name tblLogUser

2. Create an unbound form with 2 unbound text boxes named (say) txtSignIn
and txtSplash.

In the AfterUpdate event procedure of txtSignIn, you will need something
like this:

Private Sub txtSignIn_AfterUpdate()
Dim strUser As String
Dim strSql As String
Dim lngCount As Long

strUser = Nz(Trim(Me.txtSignIn), vbNullString)
If strUser = vbNullString Then
MsgBox "Please enter your name."
Else
strSql = "INSERT INTO tblLogUser ( UserName, LogIn ) " & _
"SELECT """ & strUser & """ AS UserName, " & _
"Now() AS LogIn;"
dbEngine(0)(0).Execute strSql, dbFailOnError

lngCount = DCount("*", "tblLogUser", _
"UserName = """ & strUser & """")
If lngCount = 1 Then
Me.txtSplash = "Your first time!"
Else
Me.txtSplash = "You have logged in " & lngCount & " times."
End If
End If
End Sub

A more comprehensive solution might also have a table of valid names, so the
user can select their name from a combo box instead of typing into a text
box.

 

[Guest]

Allen,

I've tried this one for my database and would like some more info.

After I created this table and form, how to I set it up to display as the
first page in other words as a log on page?

My form, after I entred a name, doesn't do anything it just keeps the name
but the splash box stays empty. Am I doing anything wrong?

 

[Allen Browne]

You can set the form as the Startup form under:
Tools | Startup.

Or, you might get a better result by creating a macro.
In the macro, choose the OpenForm action.
In the lower pane of macro design, choose your form, and in the last option
choose Dialog as the mode. This way the user will not be able to do anything
else until they deal with this form.

Did the form create a record in tblLogUser? You do need to press Enter after
entering the name.

If you cannot get it working, choose the line:
strUser = ...
and press F9. This creates a breakpoint. Now when the code runs (because you
typed something in the box), Access will show you the code. You can then
single-step through the code by pressing F8, and see what's goint on.

Once you get the form working, you will need to add code to close the form.
Just add this line (without changing anything):
DoCmd.Close acForm, Me.Name

 

[Guest]

Thanks it works!

You can set the form as the Startup form under:
Tools | Startup.

Or, you might get a better result by creating a macro.
In the macro, choose the OpenForm action.
In the lower pane of macro design, choose your form, and in the last option
choose Dialog as the mode. This way the user will not be able to do anything
else until they deal with this form.

Did the form create a record in tblLogUser? You do need to press Enter after
entering the name.

If you cannot get it working, choose the line:
strUser = ...
and press F9. This creates a breakpoint. Now when the code runs (because you
typed something in the box), Access will show you the code. You can then
single-step through the code by pressing F8, and see what's goint on.

Once you get the form working, you will need to add code to close the form.
Just add this line (without changing anything):
DoCmd.Close acForm, Me.Name

 

[Guest]

Thank you so much for your help, but i'm having a problem I typed everything
in but on this line:
lngCount = DCount("*", "tblLogUser", _
"UserName = """ & strUser & """)
I keep getting an error (compile error: expected: list separator or )? What
am I doing wrong?

Jean

 

[Guest]

Thank you, however when I typed in the line:
lngCount = DCount("*", "tblLogUser", _
"UserName = """ & strUser & """)
it highlights in red and gives a compile error: Expected: list separator or )
what can/should I do?
Thanks,
Jean

 

[Allen Browne]

Do you live in a country that uses something other than a comma as a list
separator? You could see how your computer is set up by going to the Windows
Control Panel | Regional Options, and see what your list separator is.

You can also try losing the line-continuation character (the underscore at
the end of the first line), and combining them into one line.

You might like to see if you have any references marked as "MISSING". From
the code window, choose References on the Tools menu. If there is a problem,
see:
http://allenbrowne.com/ser-38.html