MS Access 2010 Login Form
Hello,
I have developed a login form for our users to login to a time and expense form. I have 3 unbound text fields. 1) User Name 2) Password 3) Employee ID. It seems to work but the issue I'm having is, I can enter any number into the employee id text box (rather than the actual employee id), and my code seems to be picking up the user id and password to allow entry to the form (although it still comes up with the correct form for the user), What I'm looking for is to also validate the employee id in the login screen. See my code below.
Any help would be greatly appreciated.
Private Sub cmdLogin_Click()
'---- Combobox validation ----
'UserName
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
MsgBox "You must provide a Username.", vbInformation
Me.txtUserName.SetFocus
Exit Sub
End If
'Password
If Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
MsgBox "You must provide a Password.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'EmployeeID
If Me.txtEmployeeID = "" Or IsNull(Me.txtEmployeeID) Then
MsgBox "You must provide your EmployeeID.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'-------------------------
'---- Validate login
'Login Fail
If DCount("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "'") <> 1 Then
MsgBox "Invalid Login", vbExclamation
Exit Sub
'Login Success
Else
'Load the variables
lngpubEmpID = DLookup("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
strpubSecLev = DLookup("SecurityLevel", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
'Admin (Full) Access
If strpubSecLev = "Admin" Then
'Open the form, showing all records
DoCmd.OpenForm "Copy Of Time Cards"
'User (specific) Access
ElseIf strpubSecLev = "User" Then
'Open the form, filtering for the loged in Employee's ID
DoCmd.OpenForm "Copy Of Time Cards", , , "EmployeeID=" & lngpubEmpID
End If
End If
'Close this form
DoCmd.Close acForm, "frmLogin"
End Sub
I have developed a login form for our users to login to a time and expense form. I have 3 unbound text fields. 1) User Name 2) Password 3) Employee ID. It seems to work but the issue I'm having is, I can enter any number into the employee id text box (rather than the actual employee id), and my code seems to be picking up the user id and password to allow entry to the form (although it still comes up with the correct form for the user), What I'm looking for is to also validate the employee id in the login screen. See my code below.
Any help would be greatly appreciated.
Private Sub cmdLogin_Click()
'---- Combobox validation ----
'UserName
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
MsgBox "You must provide a Username.", vbInformation
Me.txtUserName.SetFocus
Exit Sub
End If
'Password
If Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
MsgBox "You must provide a Password.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'EmployeeID
If Me.txtEmployeeID = "" Or IsNull(Me.txtEmployeeID) Then
MsgBox "You must provide your EmployeeID.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'-------------------------
'---- Validate login
'Login Fail
If DCount("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "'") <> 1 Then
MsgBox "Invalid Login", vbExclamation
Exit Sub
'Login Success
Else
'Load the variables
lngpubEmpID = DLookup("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
strpubSecLev = DLookup("SecurityLevel", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
'Admin (Full) Access
If strpubSecLev = "Admin" Then
'Open the form, showing all records
DoCmd.OpenForm "Copy Of Time Cards"
'User (specific) Access
ElseIf strpubSecLev = "User" Then
'Open the form, filtering for the loged in Employee's ID
DoCmd.OpenForm "Copy Of Time Cards", , , "EmployeeID=" & lngpubEmpID
End If
End If
'Close this form
DoCmd.Close acForm, "frmLogin"
End Sub
You can just do this, without bothering with the DCounts:
Dim rst As DAO.Recordset
Set rst = CurrentDB.OpenRecordset("S
If (rst.EOF and rst.BOF) Then
'/ a user with that Name, ID and Password was not found
MsgBox "Login not found"
Else
'/ valid credentials - continue
DoCmd.OpenForm "SomeForm"
End If
Dim rst As DAO.Recordset
Set rst = CurrentDB.OpenRecordset("S
If (rst.EOF and rst.BOF) Then
'/ a user with that Name, ID and Password was not found
MsgBox "Login not found"
Else
'/ valid credentials - continue
DoCmd.OpenForm "SomeForm"
End If
ASKER
That made sense so I tried putting this in, but it does not give me the message "Login not found") nor does it take me to the form when I click the login button. See below from my command button code which I have in the On Click Event proceedure
Private Sub cmdLogin_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
If (rst.EOF And rst.BOF) Then
'/ a user with that Name, ID and Password was not found
MsgBox "Login not found"
Else
'/ valid credentials - continue
DoCmd.OpenForm "Copy Of Time Cards"
End If
End Sub
Private Sub cmdLogin_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
If (rst.EOF And rst.BOF) Then
'/ a user with that Name, ID and Password was not found
MsgBox "Login not found"
Else
'/ valid credentials - continue
DoCmd.OpenForm "Copy Of Time Cards"
End If
End Sub
Set a breakpoint in the "If(rst.eof blah blah" line, and then run the code. Is the recordset at EOF and BOF? You can test by entering this in the immediate window:
?rst.EOF
and press the Enter key.
?rst.EOF
and press the Enter key.
ASKER
Sorry if this sounds stupid but when you say set a breakpoint do you mean to just put a space or something after the "If(blah) and the window will pop up?
1) Are you getting an error when running the code?
2) What datatype is EmployeeID?
2) What datatype is EmployeeID?
This is what LSM is talking about for breakpoints:
http://office.microsoft.co
http://office.microsoft.co
ASKER
Further to Irog Sinta's solution. I did make the change in the DCount as suggested, but now when I try to click the login button, it does display a message if invalid or even if valid the login button does not take you to the form.
Here's the new code I put in. Is there something else I have to change in the code other than DCount:
Private Sub cmdLogin_Click()
'---- Combobox validation ----
'UserName
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
MsgBox "You must provide a Username.", vbInformation
Me.txtUserName.SetFocus
Exit Sub
End If
'Password
If Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
MsgBox "You must provide a Password.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'EmployeeID
If Me.txtEmployeeID = "" Or IsNull(Me.txtEmployeeID) Then
MsgBox "You must provide your EmployeeID.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'-------------------------
'---- Validate login
'Login Fail
If DCount("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "' And EmployeeID='" & Me.txtEmployeeID & "'") <> 1 Then
MsgBox "Invalid Login", vbExclamation
Exit Sub
'Login Success
Else
'Load the variables
lngpubEmpID = DLookup("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
strpubSecLev = DLookup("SecurityLevel", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
'Admin (Full) Access
If strpubSecLev = "Admin" Then
'Open the form, showing all records
DoCmd.OpenForm "Copy Of Time Cards"
'User (specific) Access
ElseIf strpubSecLev = "User" Then
'Open the form, filtering for the loged in Employee's ID
DoCmd.OpenForm "Copy Of Time Cards", , , "EmployeeID=" & lngpubEmpID
End If
End If
'Close this form
DoCmd.Close acForm, "frmLogin"
End Sub
Here's the new code I put in. Is there something else I have to change in the code other than DCount:
Private Sub cmdLogin_Click()
'---- Combobox validation ----
'UserName
If Me.txtUserName = "" Or IsNull(Me.txtUserName) Then
MsgBox "You must provide a Username.", vbInformation
Me.txtUserName.SetFocus
Exit Sub
End If
'Password
If Me.txtPassword = "" Or IsNull(Me.txtPassword) Then
MsgBox "You must provide a Password.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'EmployeeID
If Me.txtEmployeeID = "" Or IsNull(Me.txtEmployeeID) Then
MsgBox "You must provide your EmployeeID.", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
'-------------------------
'---- Validate login
'Login Fail
If DCount("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "' And EmployeeID='" & Me.txtEmployeeID & "'") <> 1 Then
MsgBox "Invalid Login", vbExclamation
Exit Sub
'Login Success
Else
'Load the variables
lngpubEmpID = DLookup("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
strpubSecLev = DLookup("SecurityLevel", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'")
'Admin (Full) Access
If strpubSecLev = "Admin" Then
'Open the form, showing all records
DoCmd.OpenForm "Copy Of Time Cards"
'User (specific) Access
ElseIf strpubSecLev = "User" Then
'Open the form, filtering for the loged in Employee's ID
DoCmd.OpenForm "Copy Of Time Cards", , , "EmployeeID=" & lngpubEmpID
End If
End If
'Close this form
DoCmd.Close acForm, "frmLogin"
End Sub
To set a breakpoint, place your cursor in the code line and press the F9 key. Access will highlight the line in red.
Now run the code. When you do, and when Access hits that line, you'll be thrown into the code window in Debug mode.
You can then use the Immediate window to determine the state of your variables and such
And as trbaze asks: What DataType is EmployeeID? If it's numeric, then you'll have to alter the syntax you're using.
Now run the code. When you do, and when Access hits that line, you'll be thrown into the code window in Debug mode.
You can then use the Immediate window to determine the state of your variables and such
And as trbaze asks: What DataType is EmployeeID? If it's numeric, then you'll have to alter the syntax you're using.
ASKER
The Employee ID is an autonumbered field. Could that be the issue, and if so what would the syntax look like?
If the field is a number instead of a string, you do not need apostrophes. Also doing a DCount with an asterisk instead of a field name is suppose to be faster.
DCount("*", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "' And EmployeeID=" & Me.txtEmployeeID ) <> 1
DCount("*", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "' And EmployeeID=" & Me.txtEmployeeID ) <> 1
ASKER
The change worked with removing the apostrophes. However, the first time a user logs in to the their inital timesheet the EmployeeID text box is not populated. (after that first timesheet is completed and the user logs in to enter a new timesheet, it opens that first completed timesheet and of course the employee id is populated (since the user had to manually input the employee id for the initial timesheet. Also, when a user clicks on the "Add new timesheet" button from the form itself, the EmployeeID field does not populate. The "Add new timesheet" button is an embedded macro for adding a new record. Any suggestions?
Here's the version that would use a Recordset:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
If (rst.EOF And rst.BOF) Then
'/ a user with that Name, ID and Password was not found
MsgBox "Login not found"
Else
'/ valid credentials - continue
DoCmd.OpenForm "Copy Of Time Cards"
End If
This is a separate question and really should be asked as a new one, however:
If this is something like a Popup form, then you'll have to populate that yourself. Access has no way of "remembering" which Employee is adding the record, so you must do that yourself.
How are the tables setup? If you have an Employee table and then a TimeCard table which references the Employee table, then you could perhaps use a Main/Sub form setup for the Time Cards. In this scenario, the Mainform would be based on the Employee table (and show just the EmployeeID and Name, perhaps), and the Subform would show the TimeCard table information. If you set the Master/Child link fields correctly Access will populate the EmployeeID field in the TimeCard row.
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
If (rst.EOF And rst.BOF) Then
'/ a user with that Name, ID and Password was not found
MsgBox "Login not found"
Else
'/ valid credentials - continue
DoCmd.OpenForm "Copy Of Time Cards"
End If
Also, when a user clicks on the "Add new timesheet" button from the form itself, the EmployeeID field does not populate.
This is a separate question and really should be asked as a new one, however:
If this is something like a Popup form, then you'll have to populate that yourself. Access has no way of "remembering" which Employee is adding the record, so you must do that yourself.
How are the tables setup? If you have an Employee table and then a TimeCard table which references the Employee table, then you could perhaps use a Main/Sub form setup for the Time Cards. In this scenario, the Mainform would be based on the Employee table (and show just the EmployeeID and Name, perhaps), and the Subform would show the TimeCard table information. If you set the Master/Child link fields correctly Access will populate the EmployeeID field in the TimeCard row.
ASKER
I agree. I should probably ask a new question, but since my Employee ID text box is on the main form (and one where I want the employee ID to self populate, the MAster/Child link fields would not work. Is that correct?
So, my user would have to manually enter the employeeid on the main form. My only concern is if they enter someone's elses employee id then that other persons info (timecard info) would come up when they click the "Add New Timesheet" button.
So, my user would have to manually enter the employeeid on the main form. My only concern is if they enter someone's elses employee id then that other persons info (timecard info) would come up when they click the "Add New Timesheet" button.
I'm not clear what you mean by "main form" - are you referring to your separate login form, or are you referring to the mainform/subform scenario I mentioned?
You can always write the EmployeeID to a hidden textbox, and then add it to New records as needed. You can generally do this in the Current or Dirty event - for example, if my hidden textbox is named "txEmpID", I'd do this:
If Me.NewRecord Then
Me.EmployeeID = Me.txEmpID
End If
You can always write the EmployeeID to a hidden textbox, and then add it to New records as needed. You can generally do this in the Current or Dirty event - for example, if my hidden textbox is named "txEmpID", I'd do this:
If Me.NewRecord Then
Me.EmployeeID = Me.txEmpID
End If
ASKER
The the mainform/subform scenario you mentioned. My main form is called Time Card (and has the Employee ID text box on it. That's what I wanted to autopopulate when the Employee either entered their time for the first time or when they enter a new timesheet. I have attached ajpeg of my timesheet form. Perhaps that will provide you with a better undertanding of what I am trying to accomplish. (note in the screenshot, although Employee Name has "#ERROR" in it. Once the Employee Id is input, the correct Employee name appears.
But, it sounds like what you indicated for a hidden text box might do the trick. Let me look at it in the morning.
Screen-shot-of-Employee-Timeshee.JPG
But, it sounds like what you indicated for a hidden text box might do the trick. Let me look at it in the morning.
Screen-shot-of-Employee-Timeshee.JPG
So you have a setup as I mentioned earlier - and Employee table, and a TimeCard table? And your mainform is based on the Employee table, and your subform is based on the TimeCard table?
If so, then just make the mainform non-editable (i.e. the user cannot change to a different employee), and set the Master/Child links of the Subform control to the EmployeeID field, and Access should handle the rest. You don't need a hidden textbox in this setup, and in fact you could confuse matters quite a bit if you try that.
Your Prior and Next TimeSheet buttons - they move to the next record in the Subform (i.e. Time Worked)?
In other words - tell us more about your table structure, and we can help you better with this.
If so, then just make the mainform non-editable (i.e. the user cannot change to a different employee), and set the Master/Child links of the Subform control to the EmployeeID field, and Access should handle the rest. You don't need a hidden textbox in this setup, and in fact you could confuse matters quite a bit if you try that.
Your Prior and Next TimeSheet buttons - they move to the next record in the Subform (i.e. Time Worked)?
In other words - tell us more about your table structure, and we can help you better with this.
ASKER
Well actually my Time Card Table has the Employee ID in it. (that table supports my Main Form "Time Cards". My Time Cards Hours and Time Cards Expense Tables support my subforms "Time Card Hours" and "Time Card Expenses" accordingly. The link between the main form and subforms is on Time Card ID (which is in all 3 tables), but that's an issue I guess since the links between the form and subforms is on Time card ID (which probably now that I think about it explains why I can't programmatically have the employee id auto populate).
So, would the hidden textbox be the solution based on my structure? Prior and next timesheet button do move to the next record for both the main form and it's subforms.
My record source on the main form is the Time Cards Table. On my form properties I have a filter for employeeid =" " and filter on load. Then, I have hidden from the user the ability to unfilter the form so that other reords do not show. See screen shot of my properties on my main form (Time Cards). Any further suggestions? Perhaps I'm going about this all wrong.
But I really want to say thanks for the help and suggestions. (Kind of new to Access programming)
Time-Cards-Form-Properties.JPG
So, would the hidden textbox be the solution based on my structure? Prior and next timesheet button do move to the next record for both the main form and it's subforms.
My record source on the main form is the Time Cards Table. On my form properties I have a filter for employeeid =" " and filter on load. Then, I have hidden from the user the ability to unfilter the form so that other reords do not show. See screen shot of my properties on my main form (Time Cards). Any further suggestions? Perhaps I'm going about this all wrong.
But I really want to say thanks for the help and suggestions. (Kind of new to Access programming)
Time-Cards-Form-Properties.JPG
Your time card table should have the EmpID in it. That's what relates the TimeCard to the Employee
However, it sounds as if you have a TimeCard "header" and a TimeCard "detail" table (in fact, 2 of the detail tables). If that's the case, then you could use the hidden textbox approach - or include the EmployeeID value in the main form, and add it as a second Master/Child link (you can add more than one Master/Child link pairs). You'd still have to fill that value when the user enters a new TimeCard record, of course, so you'll still have to capture the value somewhere (again, the hidden textbox).
However, it sounds as if you have a TimeCard "header" and a TimeCard "detail" table (in fact, 2 of the detail tables). If that's the case, then you could use the hidden textbox approach - or include the EmployeeID value in the main form, and add it as a second Master/Child link (you can add more than one Master/Child link pairs). You'd still have to fill that value when the user enters a new TimeCard record, of course, so you'll still have to capture the value somewhere (again, the hidden textbox).
ASKER
Still working on this. Should be able to test Saturday or Sunday
ASKER
Hi,
I am working on this from the hidden textbox approach you suggested. I created a new textbox on my main for (Time Cards) asnd named it "txEmpID". I then tried putting the code you suggested:
If Me.NewRecord Then
Me.EmployeeID = Me.txEmpID
End If
on the main forms "On Dirty" event. I also tried putting it on the new textbox (txEmpID) and the current textbox (EmployeeID) but it did not have any effect. It actually prevented the user from entering an new timesheet and inputting the employeeid. I also tried passing the employeeid value from the login form to the textboxes. Again no effect. I'm probably doing something wrong in the coding
I guess what I am trying to accomplish is even if the user has to enter their employee id on the Time Cards main form which is Ok with me, they are prevented from entering someone else's id based on some type of logic (that I can't seem to code correctly) when they enter their employee id on the main time card for. Not even sure if this doable, but would welcome any suggestions.
I am working on this from the hidden textbox approach you suggested. I created a new textbox on my main for (Time Cards) asnd named it "txEmpID". I then tried putting the code you suggested:
If Me.NewRecord Then
Me.EmployeeID = Me.txEmpID
End If
on the main forms "On Dirty" event. I also tried putting it on the new textbox (txEmpID) and the current textbox (EmployeeID) but it did not have any effect. It actually prevented the user from entering an new timesheet and inputting the employeeid. I also tried passing the employeeid value from the login form to the textboxes. Again no effect. I'm probably doing something wrong in the coding
I guess what I am trying to accomplish is even if the user has to enter their employee id on the Time Cards main form which is Ok with me, they are prevented from entering someone else's id based on some type of logic (that I can't seem to code correctly) when they enter their employee id on the main time card for. Not even sure if this doable, but would welcome any suggestions.
You can try moving this to the form's Current event, which fires anytime the user moves to a different (or new) record.
they are prevented from entering someone else's id based on some type of logic (that I can't seem to code correctlyI'm not sure how the system could know which employee is entering the data, unless you have some sort of login system.
ASKER
My login form looks at the tblLogin table. If they have the "User" role, then when they login successfully, the form filters to their employee id. My problem is, when a user enters their time card for the first time, the employee id text box is empthy. That's where they have to put their employee id in and it successfully enters their name in another textbox on the next focus of a field.
So, after they complete their first timesheet and login again, and the user enters their time card, the first record they entered displays and shows the first completed time card with all the correct info (employee id, name, etc).
As the enters additional time cards, they can click the "Previous and "Next timesheet button to naviagate to prior/next timecards that have already been entered.
Once the user click the "New Timesheet button, a new record displays but the employee id is blank. So they have to enter their employee id. If they accidently enter another employee's id, then the employee's name who is associated with that employee id displays. I do have a message that comes up after entering an employee id and they move focus to the next text box that says".....Is this your name in the employye name field.....? they then have to click yes or no, but it's just a message box warning them of a correct name. with their employee id, employee name, etc
So, after they complete their first timesheet and login again, and the user enters their time card, the first record they entered displays and shows the first completed time card with all the correct info (employee id, name, etc).
As the enters additional time cards, they can click the "Previous and "Next timesheet button to naviagate to prior/next timecards that have already been entered.
Once the user click the "New Timesheet button, a new record displays but the employee id is blank. So they have to enter their employee id. If they accidently enter another employee's id, then the employee's name who is associated with that employee id displays. I do have a message that comes up after entering an employee id and they move focus to the next text box that says".....Is this your name in the employye name field.....? they then have to click yes or no, but it's just a message box warning them of a correct name. with their employee id, employee name, etc
The hidden textbox method works for things like this, depending on your data table setup. The user really shouldn't have to re-enter or re-select their EmployeeID.
It sounds as if something is off in regard to that, so we'd have to see your databse before we could help further.
If you could remove any sensitive data, then Zip the file and upload it here. Be sure to include instructions on how we can replicate the issue (i.e. "Open formA, enter someting in TextboxB, etc etc").
It sounds as if something is off in regard to that, so we'd have to see your databse before we could help further.
If you could remove any sensitive data, then Zip the file and upload it here. Be sure to include instructions on how we can replicate the issue (i.e. "Open formA, enter someting in TextboxB, etc etc").
ASKER
I will do that in the AM/PM tomorrow and foward. Thanks much
ASKER
As discussed I have attached a copy of the portion of my db that deals with time and expense. Sorry it took me so long, I am in the middle of training on this and this was the first opportunity to create the time and expense portion of my db into a new db for your review.
Two items though:
On the Copy of Time Cards Form, you'll see a combo box for employee. Disregard as I have that hidden in my other db that has the time and expense portion in it.
Thanks for all your help
Time-and-Billing.zip
Two items though:
On the Copy of Time Cards Form, you'll see a combo box for employee. Disregard as I have that hidden in my other db that has the time and expense portion in it.
Thanks for all your help
Time-and-Billing.zip
Can you provide a step-by-step detail of how to recreate your problem?
ASKER
Step 1 - Login using frmlogin. Enter UserID, Password, and Employee ID and click login.
The Copy of TimeCards5 form opens
Step 2 Enter employee ID in the Employee ID on the Copy of Time Cards Form.
Step 3 click another field ("Week Ending" field), the Employee Name populates (It says "Error" until you click on another field ("Week Ending").
Step 4 - Enter Time and Expense Information on the form and subforms.
Step 5, Click the "New Timesheet" Button and repeat the process above.
What I want is not to have the Employee ID entered by an emplopyee, since if they enter an incorrect Employee ID, then that other Employee's ID is in there (and populates the Employee name field) with that other Employee's Name.
If the user is unaqware, then they may end up entering a time and expense for that other employee instead of their own.
That's what I'm trying to prevent by having the employee ID and Employee Name fields self populate based on some logic such as entering their login info (including their employee id) and preventing the employee from inadvertently entering someone else's time and expense.
Let me know if I have adequately explained what I am trying to do.
Thanks for all your help
The Copy of TimeCards5 form opens
Step 2 Enter employee ID in the Employee ID on the Copy of Time Cards Form.
Step 3 click another field ("Week Ending" field), the Employee Name populates (It says "Error" until you click on another field ("Week Ending").
Step 4 - Enter Time and Expense Information on the form and subforms.
Step 5, Click the "New Timesheet" Button and repeat the process above.
What I want is not to have the Employee ID entered by an emplopyee, since if they enter an incorrect Employee ID, then that other Employee's ID is in there (and populates the Employee name field) with that other Employee's Name.
If the user is unaqware, then they may end up entering a time and expense for that other employee instead of their own.
That's what I'm trying to prevent by having the employee ID and Employee Name fields self populate based on some logic such as entering their login info (including their employee id) and preventing the employee from inadvertently entering someone else's time and expense.
Let me know if I have adequately explained what I am trying to do.
Thanks for all your help
ASKER
Hi,
Was wondering if you had any success after my explanation of the step by step approach. Thanks for any help you can provide with the hidden textbox approach
Was wondering if you had any success after my explanation of the step by step approach. Thanks for any help you can provide with the hidden textbox approach
I don't think you have the tables setup correctly, and thus your form is not behaving correctly.
The goal of this portion of your db is to capture timecard information, in a "per-employee" fashion. So the form should be based on the Employee table, with a subform that is based on the TimeCard form. That TimeCard form could include a subform that would show the TimeCardHours or Expenses, of course, but your main form should be based on Employee, not TimeCard.
In fact, your main form doesn't really have to do anything, other than specify the Employee associated with the TimeCard - so you could set the Subform to take up all the room on the MainForm, and the user wouldn't have to interact with that at all.
It would take some care in regard to your user actions. For example, you could stil include the buttons on the mainform, but you'd need to reference the SubForm (i.e. the form based on TimeCards) or the Sub-Subform (i.e the form based on TimeCardHours or TimeCardExpenses) when doing so.
So the heiarchy should be Employee - TimeCard - TimeCardHours (or TimeCardExpenses).
The goal of this portion of your db is to capture timecard information, in a "per-employee" fashion. So the form should be based on the Employee table, with a subform that is based on the TimeCard form. That TimeCard form could include a subform that would show the TimeCardHours or Expenses, of course, but your main form should be based on Employee, not TimeCard.
In fact, your main form doesn't really have to do anything, other than specify the Employee associated with the TimeCard - so you could set the Subform to take up all the room on the MainForm, and the user wouldn't have to interact with that at all.
It would take some care in regard to your user actions. For example, you could stil include the buttons on the mainform, but you'd need to reference the SubForm (i.e. the form based on TimeCards) or the Sub-Subform (i.e the form based on TimeCardHours or TimeCardExpenses) when doing so.
So the heiarchy should be Employee - TimeCard - TimeCardHours (or TimeCardExpenses).
ASKER
Ok thanks, I'll look into this but I have gone through 2 weeks of training (with a rollout scheduled for July 1st, so, I don't think I can change the interface at this point as it would would confuse everyone I have trained so far.
If I understand correctly, I can base my main form (time card form) on the employee table (disregard the Time card table) use the data from there and still show a time card hours subform and expense subform? Would that be a work around for the moment?
If I understand correctly, I can base my main form (time card form) on the employee table (disregard the Time card table) use the data from there and still show a time card hours subform and expense subform? Would that be a work around for the moment?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for all the help and assistance. I will plan on redesigning my forms
If DCount("EmployeeID", "tblLogin", "UserName=" & "'" & Me.txtUserName & "'" & " And " & "Password=" & "'" & Me.txtPassword & "' And EmployeeID='" & Me.txtEmployeeID & "'") <> 1 Then