add field value to another table base on ID when a button is click

[Guest]

I have a form named login that is based on a simple query to allow members to
login based a password. There is a button that is use to verify the
password. I would like to enter a value into another table to track the
dates the member has been there based on the members ID. I am at a loss on
how to get this started, whether I have to use an append query or what, or
the way to start setting it up. Any help would be greatly appreciated.

Thanks

 

[Guest]

What is the code you have now, when you verify the password?

In general I would use Append SQL to insert the values

Docmd.RunSql "INSERT INTO MycommentTable ( [UserId], [DateEntered]) VALUES
(" & UserIdVariable & ", Now())"

I assume that you know the UserId, so set it in the UserIdVariable.
=============================================
If the UserId field type is string, add a single quote before and after
Docmd.RunSql "INSERT INTO MycommentTable ( [UserId], [DateEntered]) VALUES
('" & UserIdVariable & "', Now())"

 

[Guest]

Here is the code for checking the password.


Private Sub Login_Click()
On Error GoTo Err_Login_Click

If Me.PassVerify = Me.Password Then
Dim MemName, MyDate, MyMonth, YourMonth, MyDay, MyYear
MyDate = Now() ' Assign a date.
MyMonth = month(MyDate) 'Determines current month number
YourMonth = MonthName(MyMonth) 'Determines current month name
MyDay = day(MyDate) 'Determines current day
MyYear = year(MyDate) 'Determines current year
MemName = Me.Username
Me.LoginOkLabel.Caption = MemName & " has attended class on " & YourMonth &
" " & MyDay & ", " & MyYear
Else
Me.LoginOkLabel.Caption = "Incorrect Password, please try again"
Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "The password you entered is incorrect," + Chr(13) + _
"Please try again" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Login Failed" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If

Me.PassVerify = Null
Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub

I am going to try to write in code based on what you recommended. Or will
this code interfer with the code you recommended

 

[Guest]

Try the code, if any problem, please post back

--
\\// Live Long and Prosper \\//
BS"D

Here is the code for checking the password.


Private Sub Login_Click()
On Error GoTo Err_Login_Click

If Me.PassVerify = Me.Password Then
Dim MemName, MyDate, MyMonth, YourMonth, MyDay, MyYear
MyDate = Now() ' Assign a date.
MyMonth = month(MyDate) 'Determines current month number
YourMonth = MonthName(MyMonth) 'Determines current month name
MyDay = day(MyDate) 'Determines current day
MyYear = year(MyDate) 'Determines current year
MemName = Me.Username
Me.LoginOkLabel.Caption = MemName & " has attended class on " & YourMonth &
" " & MyDay & ", " & MyYear
Else
Me.LoginOkLabel.Caption = "Incorrect Password, please try again"
Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "The password you entered is incorrect," + Chr(13) + _
"Please try again" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Login Failed" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If

Me.PassVerify = Null
Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub

I am going to try to write in code based on what you recommended. Or will
this code interfer with the code you recommended

What is the code you have now, when you verify the password?

In general I would use Append SQL to insert the values

Docmd.RunSql "INSERT INTO MycommentTable ( [UserId], [DateEntered]) VALUES
(" & UserIdVariable & ", Now())"

I assume that you know the UserId, so set it in the UserIdVariable.
=============================================
If the UserId field type is string, add a single quote before and after
Docmd.RunSql "INSERT INTO MycommentTable ( [UserId], [DateEntered]) VALUES
('" & UserIdVariable & "', Now())"

 

[Guest]

I tried the code and it works great but I have another question for you if
you do not mind. I have the ATTENDANCE table 2006 field set to allow no
duplicates. Whenever I click on the login button again it pops up a Dialog
box saying "Microsoft Office Access can't append all the records in the
append query." due to the fact I do not want to allow duplicate logins for
the day. I tried to use a On Error Goto but it still popups up that error
box and after I click No to not append the query, it pops up my msgbox. Is
there a way to eliminate this problem.

I also have turned off in Options on the Edit/Find tab under Confirm
category both "Record Changes' and "Action Queries"

Here is my Code for the button and thank you very much for your time, it is
very appreciated.

Private Sub Login_Click()
On Error GoTo Err_Login_Click

If Me.PassVerify = Me.Password Then
Dim MemName, MyDate, MyMonth, YourMonth, MyDay, MyYear, TodayDate

MyDate = Now() ' Assign a date.
MyMonth = month(MyDate) 'Determines current month number
YourMonth = MonthName(MyMonth) 'Determines current month name
MyDay = day(MyDate) 'Determines current day
MyYear = year(MyDate) 'Determines current year
MemName = Me.Username
TodayDate = MyMonth & "/" & MyDay & "/" & MyYear
Me.Date.Value = TodayDate
Me.LoginOkLabel.Caption = MemName & " has attended class on " & YourMonth &
" " & MyDay & ", " & MyYear
On Error GoTo AlreadyEntered
'If the UserId field type is string, add a single quote before and after
DoCmd.RunSQL "INSERT INTO Attendance ( [MemberID], [2006]) VALUES (" &
[MemberID] & ", [Date])"
SendKeys (n)
AlreadyEntered:
Dim EMsg, EStyle, ETitle, EHelp, ECtxt, EResponse
EMsg = "You have already logged in today," + Chr(13) + _
"Please click Ok" ' Define message.
EStyle = vbOKOnly + vbCritical ' Define buttons.
ETitle = "Already Logged In" ' Define title.
EHelp = "DEMO.HLP" ' Define Help file.
ECtxt = 1000 ' Define topic
' context.
' Display message.
EResponse = MsgBox(EMsg, EStyle, ETitle, EHelp, ECtxt)
Else
Me.LoginOkLabel.Caption = "Incorrect Password, please try again"
Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "The password you entered is incorrect," + Chr(13) + _
"Please try again" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Login Failed" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If


Me.PassVerify = Null

Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub

 

[Guest]

Sorry about the last post, my mistake, I can't have no dups on the field or
else no other member can login. I will have to have it disable the member
name from the list box or have it remember how has logged in. Thanks for the
help

 

[Guest]

Before and after the Insert SQL write
Docmd.SetWarnings False
' Your SQL
Docmd.SetWarnings True

This will remove the warnings

--
\\// Live Long and Prosper \\//
BS"D

I tried the code and it works great but I have another question for you if
you do not mind. I have the ATTENDANCE table 2006 field set to allow no
duplicates. Whenever I click on the login button again it pops up a Dialog
box saying "Microsoft Office Access can't append all the records in the
append query." due to the fact I do not want to allow duplicate logins for
the day. I tried to use a On Error Goto but it still popups up that error
box and after I click No to not append the query, it pops up my msgbox. Is
there a way to eliminate this problem.

I also have turned off in Options on the Edit/Find tab under Confirm
category both "Record Changes' and "Action Queries"

Here is my Code for the button and thank you very much for your time, it is
very appreciated.

Private Sub Login_Click()
On Error GoTo Err_Login_Click

If Me.PassVerify = Me.Password Then
Dim MemName, MyDate, MyMonth, YourMonth, MyDay, MyYear, TodayDate

MyDate = Now() ' Assign a date.
MyMonth = month(MyDate) 'Determines current month number
YourMonth = MonthName(MyMonth) 'Determines current month name
MyDay = day(MyDate) 'Determines current day
MyYear = year(MyDate) 'Determines current year
MemName = Me.Username
TodayDate = MyMonth & "/" & MyDay & "/" & MyYear
Me.Date.Value = TodayDate
Me.LoginOkLabel.Caption = MemName & " has attended class on " & YourMonth &
" " & MyDay & ", " & MyYear
On Error GoTo AlreadyEntered
'If the UserId field type is string, add a single quote before and after
DoCmd.RunSQL "INSERT INTO Attendance ( [MemberID], [2006]) VALUES (" &
[MemberID] & ", [Date])"
SendKeys (n)
AlreadyEntered:
Dim EMsg, EStyle, ETitle, EHelp, ECtxt, EResponse
EMsg = "You have already logged in today," + Chr(13) + _
"Please click Ok" ' Define message.
EStyle = vbOKOnly + vbCritical ' Define buttons.
ETitle = "Already Logged In" ' Define title.
EHelp = "DEMO.HLP" ' Define Help file.
ECtxt = 1000 ' Define topic
' context.
' Display message.
EResponse = MsgBox(EMsg, EStyle, ETitle, EHelp, ECtxt)
Else
Me.LoginOkLabel.Caption = "Incorrect Password, please try again"
Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "The password you entered is incorrect," + Chr(13) + _
"Please try again" ' Define message.
Style = vbOKOnly + vbCritical ' Define buttons.
Title = "Login Failed" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If


Me.PassVerify = Null

Exit_Login_Click:
Exit Sub

Err_Login_Click:
MsgBox Err.Description
Resume Exit_Login_Click

End Sub