If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Selecting a Value from a table in a form
Hello,
I have a time database set up for employees to enter the time. Right now there is a form where the date is selected, then there is a subform with a drop down list where the employee selects there name, then there is another subform where employees enter there time. Associated with each form is a Date table that displays the days of the year, there is an employee table that lists all the employees names, and then there is a work table which keeps track of employee time. However I am running into an issue with employees changing the employee name by accident in the employee subform so the time entered in the time subform gets associated with the wrong employee. To mitigate this I want to create a new time entry form for each employee that only displays that employees name in that form. Each employee form will be the same as that above but instead of having a drop down menu where employees pick there name, the name will already be entered there for that employee. This will mean a individual form will be created for each employee. I know this is not likely the best approach but I dont' want to have to re enter the time that already exists in the database. Any help on this would be greatly appreciated. Thanks in advance. |
#2
|
|||
|
|||
Selecting a Value from a table in a form
Sorry, but that is not a good plan. You are creating a maintenance nightmare
for yourself. There are better ways to do this. If each user logs on to a computer (not your app, but the computer) then you can use the code from this site to determine who is logged on: http://www.mvps.org/access/api/api0066.htm You can then use the user's name to find the correct employee in your table. If multiple people will be sharing a computer and you can't rely on the logged in user to be the one who is entering time, then I would suggest you add code to the combo's before update to present a message box for them to validate they have made the correct selection: If MsgBox("You have selected " & Me.MyCombo & vbNewLine & "Is This Correct", vbQuestion + vbYesNo) = vbNo Then Cancel = True Me.MyCombo = Null End If -- Dave Hargis, Microsoft Access MVP "Karl" wrote: Hello, I have a time database set up for employees to enter the time. Right now there is a form where the date is selected, then there is a subform with a drop down list where the employee selects there name, then there is another subform where employees enter there time. Associated with each form is a Date table that displays the days of the year, there is an employee table that lists all the employees names, and then there is a work table which keeps track of employee time. However I am running into an issue with employees changing the employee name by accident in the employee subform so the time entered in the time subform gets associated with the wrong employee. To mitigate this I want to create a new time entry form for each employee that only displays that employees name in that form. Each employee form will be the same as that above but instead of having a drop down menu where employees pick there name, the name will already be entered there for that employee. This will mean a individual form will be created for each employee. I know this is not likely the best approach but I dont' want to have to re enter the time that already exists in the database. Any help on this would be greatly appreciated. Thanks in advance. |
#3
|
|||
|
|||
Selecting a Value from a table in a form
Thanks for the reply. Multiple people will be using the same computer so the
first option isn't viable. I entered the second option but got an error message indicating Access can't find macro 'If Msg Box ('You have selected" & Me.' I'm not to familiar with Access and definitely don't know anything about macros so I'm not sure what that means. In addition, I am not sure this will solve the problem as I think employees will just get in the habitat of clicking okay anyway and won't actually check to verify they have the right name and time anyway. (If they would just check in the first place I wouldn't have this issue! lol!). So I believe giving each employee there own form is my only option. I should note we only have 7 employees so I realize maintenance issues may arise but I am not sure they will be that big of an issue with the small number of employees we have. My employee table is set up as such that each employee has a unique id and also there name. So it looks like this: 1 Karl 2 Shari 3 Joe 4 Mike So I'm hoping in my employee name box that is coded to select only one persons name from that list in each form. That way if the Karl form is opened all time entered in that form has to be associated with Karl and same thing for if the Shari form is opened only Shari will be selected from the list. I know this is a bad way to do things but think at this time its my most suitable option. "Klatuu" wrote: Sorry, but that is not a good plan. You are creating a maintenance nightmare for yourself. There are better ways to do this. If each user logs on to a computer (not your app, but the computer) then you can use the code from this site to determine who is logged on: http://www.mvps.org/access/api/api0066.htm You can then use the user's name to find the correct employee in your table. If multiple people will be sharing a computer and you can't rely on the logged in user to be the one who is entering time, then I would suggest you add code to the combo's before update to present a message box for them to validate they have made the correct selection: If MsgBox("You have selected " & Me.MyCombo & vbNewLine & "Is This Correct", vbQuestion + vbYesNo) = vbNo Then Cancel = True Me.MyCombo = Null End If -- Dave Hargis, Microsoft Access MVP "Karl" wrote: Hello, I have a time database set up for employees to enter the time. Right now there is a form where the date is selected, then there is a subform with a drop down list where the employee selects there name, then there is another subform where employees enter there time. Associated with each form is a Date table that displays the days of the year, there is an employee table that lists all the employees names, and then there is a work table which keeps track of employee time. However I am running into an issue with employees changing the employee name by accident in the employee subform so the time entered in the time subform gets associated with the wrong employee. To mitigate this I want to create a new time entry form for each employee that only displays that employees name in that form. Each employee form will be the same as that above but instead of having a drop down menu where employees pick there name, the name will already be entered there for that employee. This will mean a individual form will be created for each employee. I know this is not likely the best approach but I dont' want to have to re enter the time that already exists in the database. Any help on this would be greatly appreciated. Thanks in advance. |
#4
|
|||
|
|||
Selecting a Value from a table in a form
Or create a logon screen that requires them to at least select the
name for whom then are going to be entering time. At least that way it will NOT be accidental. If not a log on screen then a whole different preliminary form before time entry that requires them to select their employee name that then goes to a parent child form with their name etc and other aspects for that person and as a child form subform from that screen enter the time information. Both of those things will make it more difficult to accidentally change someone else's time. Ron |
#5
|
|||
|
|||
Selecting a Value from a table in a form
Ok, here is how it os done.
You don't put the code directly in the property box. Click the small button to the right of the box. Select code builder. The VB editor will open. The code goes there. You will need to change the names in the code I posted to use you names. -- Dave Hargis, Microsoft Access MVP "Karl" wrote: Thanks for the reply. Multiple people will be using the same computer so the first option isn't viable. I entered the second option but got an error message indicating Access can't find macro 'If Msg Box ('You have selected" & Me.' I'm not to familiar with Access and definitely don't know anything about macros so I'm not sure what that means. In addition, I am not sure this will solve the problem as I think employees will just get in the habitat of clicking okay anyway and won't actually check to verify they have the right name and time anyway. (If they would just check in the first place I wouldn't have this issue! lol!). So I believe giving each employee there own form is my only option. I should note we only have 7 employees so I realize maintenance issues may arise but I am not sure they will be that big of an issue with the small number of employees we have. My employee table is set up as such that each employee has a unique id and also there name. So it looks like this: 1 Karl 2 Shari 3 Joe 4 Mike So I'm hoping in my employee name box that is coded to select only one persons name from that list in each form. That way if the Karl form is opened all time entered in that form has to be associated with Karl and same thing for if the Shari form is opened only Shari will be selected from the list. I know this is a bad way to do things but think at this time its my most suitable option. "Klatuu" wrote: Sorry, but that is not a good plan. You are creating a maintenance nightmare for yourself. There are better ways to do this. If each user logs on to a computer (not your app, but the computer) then you can use the code from this site to determine who is logged on: http://www.mvps.org/access/api/api0066.htm You can then use the user's name to find the correct employee in your table. If multiple people will be sharing a computer and you can't rely on the logged in user to be the one who is entering time, then I would suggest you add code to the combo's before update to present a message box for them to validate they have made the correct selection: If MsgBox("You have selected " & Me.MyCombo & vbNewLine & "Is This Correct", vbQuestion + vbYesNo) = vbNo Then Cancel = True Me.MyCombo = Null End If -- Dave Hargis, Microsoft Access MVP "Karl" wrote: Hello, I have a time database set up for employees to enter the time. Right now there is a form where the date is selected, then there is a subform with a drop down list where the employee selects there name, then there is another subform where employees enter there time. Associated with each form is a Date table that displays the days of the year, there is an employee table that lists all the employees names, and then there is a work table which keeps track of employee time. However I am running into an issue with employees changing the employee name by accident in the employee subform so the time entered in the time subform gets associated with the wrong employee. To mitigate this I want to create a new time entry form for each employee that only displays that employees name in that form. Each employee form will be the same as that above but instead of having a drop down menu where employees pick there name, the name will already be entered there for that employee. This will mean a individual form will be created for each employee. I know this is not likely the best approach but I dont' want to have to re enter the time that already exists in the database. Any help on this would be greatly appreciated. Thanks in advance. |
#6
|
|||
|
|||
Selecting a Value from a table in a form
This sounds like it would work but I have absolutely no idea how to do create
a logon screen. Or I don't know how to create a preliminary form that would direct them to another form. Like I said I'm rather inexpereinced with Access. "Ron2006" wrote: Or create a logon screen that requires them to at least select the name for whom then are going to be entering time. At least that way it will NOT be accidental. If not a log on screen then a whole different preliminary form before time entry that requires them to select their employee name that then goes to a parent child form with their name etc and other aspects for that person and as a child form subform from that screen enter the time information. Both of those things will make it more difficult to accidentally change someone else's time. Ron |
#7
|
|||
|
|||
Selecting a Value from a table in a form
I don't understand what names in the code have to be changed. I am not
familiar with programming so I'm not sure what part of the code are names. I assume MyCombo refers to the name of my combo box (Employee ID). But I'm not sure what to put in for "You have Selected" or MsgBox. If MsgBox("You have selected " & Me.MyCombo & vbNewLine & "Is This Correct", vbQuestion + vbYesNo) = vbNo Then Cancel = True Me.MyCombo = Null End If "Klatuu" wrote: Ok, here is how it os done. You don't put the code directly in the property box. Click the small button to the right of the box. Select code builder. The VB editor will open. The code goes there. You will need to change the names in the code I posted to use you names. -- Dave Hargis, Microsoft Access MVP "Karl" wrote: Thanks for the reply. Multiple people will be using the same computer so the first option isn't viable. I entered the second option but got an error message indicating Access can't find macro 'If Msg Box ('You have selected" & Me.' I'm not to familiar with Access and definitely don't know anything about macros so I'm not sure what that means. In addition, I am not sure this will solve the problem as I think employees will just get in the habitat of clicking okay anyway and won't actually check to verify they have the right name and time anyway. (If they would just check in the first place I wouldn't have this issue! lol!). So I believe giving each employee there own form is my only option. I should note we only have 7 employees so I realize maintenance issues may arise but I am not sure they will be that big of an issue with the small number of employees we have. My employee table is set up as such that each employee has a unique id and also there name. So it looks like this: 1 Karl 2 Shari 3 Joe 4 Mike So I'm hoping in my employee name box that is coded to select only one persons name from that list in each form. That way if the Karl form is opened all time entered in that form has to be associated with Karl and same thing for if the Shari form is opened only Shari will be selected from the list. I know this is a bad way to do things but think at this time its my most suitable option. "Klatuu" wrote: Sorry, but that is not a good plan. You are creating a maintenance nightmare for yourself. There are better ways to do this. If each user logs on to a computer (not your app, but the computer) then you can use the code from this site to determine who is logged on: http://www.mvps.org/access/api/api0066.htm You can then use the user's name to find the correct employee in your table. If multiple people will be sharing a computer and you can't rely on the logged in user to be the one who is entering time, then I would suggest you add code to the combo's before update to present a message box for them to validate they have made the correct selection: I -- Dave Hargis, Microsoft Access MVP "Karl" wrote: Hello, I have a time database set up for employees to enter the time. Right now there is a form where the date is selected, then there is a subform with a drop down list where the employee selects there name, then there is another subform where employees enter there time. Associated with each form is a Date table that displays the days of the year, there is an employee table that lists all the employees names, and then there is a work table which keeps track of employee time. However I am running into an issue with employees changing the employee name by accident in the employee subform so the time entered in the time subform gets associated with the wrong employee. To mitigate this I want to create a new time entry form for each employee that only displays that employees name in that form. Each employee form will be the same as that above but instead of having a drop down menu where employees pick there name, the name will already be entered there for that employee. This will mean a individual form will be created for each employee. I know this is not likely the best approach but I dont' want to have to re enter the time that already exists in the database. Any help on this would be greatly appreciated. Thanks in advance. |
Thread Tools | |
Display Modes | |
|
|