Different Table

[Robeen]

You had a couple of problems in your Form/Code.



Do this:
1. Change the name of your Date Text Box to txtDate.
2. Change the name of your Time Text Box to txtTime.
3. [Optional] Change the Control Source of your txtTime to
= Format(Now(),"hh:nn:ss")
. . . this will give you just the Time portion of 'Now()' [if that is what you want].

Now, paste this code between your Private Sub Login_Click() and your End Sub:

 
Dim Date_Worked, Strt_Time As Date
 
Me.txtDate.SetFocus
Date_Worked = Me.txtDate
 
Me.txtTime.SetFocus
Strt_Time = Me.txtTime
 
StrSQL = "INSERT INTO Users (Date_Worked, Strt_Time) "
StrSQL = StrSQL & "VALUES (" & "#" & Date_Worked & "#" & ", " & "#" & Strt_Time & "#" & "); "
 
'MsgBox StrSQL
 
DoCmd.RunSQL StrSQL

I ran the above in your database on my machine.
It added a new record to your Users table with and ID of 1, a Date_Worked of 10/06/2011, and a Strt_Time of 3:15:19 PM.

I hope this helps.

[imintrouble]

Thank you so much Robeen. When I ran this code as a log-in, It correctly entered the right date and time. However the other information such as user_Name and EMail and the user type weren't changed. in fact those fields were left blank. Is there anyway to change it so that each time these people log on all of the information gets saved?

Either way, I'm just happy you got it to work. thanks again.

[Robeen]

Because of your first post in this thread:

How do I take the information garnered from the Date and Time boxes and, when I push a button, the two are saved to a different Table for recording those specific things.

All previous information, name/e-mail are to be saved to a user table seperatly. Thanks!

. . . I thought that you needed the Date & Time Inserted into one table and the other information into another table.

Do you need all the information on the form to get saved to the Users Table?

[imintrouble]

Yes I'm sorry for not being so precise before. Is there anyways I could do that? I'm assuming its a very simple change to the control button by puting in the same information as I did for the date and time, just with the email and username and user type.

[Robeen]

Do you want to try it yourself?

What you'll need to do is:
1. In the Insert statement in StrSQL:

 
"INSERT INTO Users (Date_Worked, Strt_Time) "

add all the field names in your Table that you will be inserting data into - in the correct order - separated by commas.

2.
In this part of the StrSQL:

StrSQL = StrSQL & "VALUES (" & "#" & Date_Worked & "#" & ", " & "#" & Strt_Time & "#" & "); "

Add all the values you will be adding to your table [must be in the same order as all the fields in the Insert Into list in the first line of StrSQL].

Adding String Values is different than adding Date values . . .

Let me know if you want me to give you the syntax.
I'll need to know which Table fields get data.

[imintrouble]

yeah, i tried simply changing everything as instructed, but then I got a run-time error '3075'

[Robeen]

It IS a llittle tricky. but once you get to do it a couple of times - it gets easier.

Try this:

NB: The Private Sub and End Sub are included in the following code.

 
Private Sub Login_Click()
Dim Date_Worked, Strt_Time As Date
Dim intID As Integer
Dim StrUser_Name, StrEmail, StrUser_Type As String
 
Me.txtDate.SetFocus
Date_Worked = Me.txtDate
 
Me.txtTime.SetFocus
Strt_Time = Me.txtTime
 
Me.ID.SetFocus
intID = Me.ID
 
Me.User_Name.SetFocus
StrUser_Name = Me.User_Name
Me.EMail.SetFocus
StrEmail = Me.EMail
 
Me.User_Type.SetFocus
StrUser_Type = Me.User_Type
StrSQL = "INSERT INTO Users (ID, User_Name, EMail, User_Type, Date_Worked, Strt_Time) "
StrSQL = StrSQL & "VALUES (" & intID & ", " & "'" & StrUser_Name & "'" & ", " & "'" & StrEmail & "'" & ", " & "'" & StrUser_Type & "'" & ", " & "#" & Date_Worked & "#" & ", " & "#" & Strt_Time & "#" & "); "
 
'MsgBox StrSQL
 
DoCmd.RunSQL StrSQL
End Sub

I hope this works for you!! Let me know.

[imintrouble]

After entering the code, I ran the login button and it said, Microsof Access can't append all the records in the append query.

[Robeen]

I just entered another record using the Login Form & it went in just fine.

I clicked the New Record button at the bottom of the form [>*] and when I tabbed off the ID field and entered a User Name - I automatically got the next ID number assigned in the ID text box on the Form . . .

Can you tell me the steps you are doing in trying to enter a new record?
Maybe I can figure why it is not working for you.

[Paul H]

Hey Robeen,

I think your suggest of using a Command Button will work for a very similar problem I am having. How to you keep the form from rolling over to a new record automatically and force the use of the comman button?

[Robeen]

Hi Paul H!

I am really busy right now - so the best thing for you will be if you post your question separately.

That way - more people will look at it and you will have a better chance of getting help!

All the best!!

[imintrouble]

Hey Robeen, I'm not sure what happened but I think I fixed it. I have it now to where when I "login" all of the information is saved to a table for recording the information, and into a query where the information will be evaluated. I can't thank you enough for the help you've given me.

Now then, with that being said, I was wondering if you had any ideas as to how I might go about creating a log out function. I've got the start time, now I only need a way to record the out time to be finished.

[Robeen]

Are you talking about adding the End_Time to the Users Table?
When do you want the End_Time to be added to the Table?

[imintrouble]

The ideal time would be when someone is finished working in the databse. That can be when they close out of the database and I think it might even be able to be done with an unload or an onclose event to state the end_time and record it to the users table. I have no idea how to do this however :P.

[Robeen]

If you're going to allow the person to leave the Login Form and go to other Forms in your application, you will have to store the ID that was entered when the 'Login' button was clicked.
That ID will have to be stored in a variable that doesn't get wiped out when different Forms are opened and Closed.

1. Right at the top of your code window put the Public intID_Public variable declaration:

 
Option Compare Database
Public intID_Public As Integer
Option Explicit

In the code for the Login button - put these two lines of code:

 
Me.ID.SetFocus
intID_Public = Me.ID

Identify what event will cause the End_Time to be written to the Users Table.

I have put the code behind a button called cmdUpdate.

Note: Since I have 'Public intID_Public As Integer' above the Update Sub routine -it will stay in memory after the Login code has completed.

 
Private Sub cmdUpdate_Click()
 
Dim StrSQL As String
 
StrSQL = "Update Users Set End_Time = #" & Now() & "# Where ID = " _
    & intID_Public & ";"
 
'MsgBox StrSQL
 
DoCmd.RunSQL StrSQL
 
End Sub

I tested this real quick & it works for me.

All the best!!