Full Version: Selecting a Record using a combo box
UtterAccess Forums > Microsoft® Access > Access Forms
inventingdreams
I am writing a time card program for my facility and the form for entering times is very user-unfriendly. I am using a calander activex thing for the date entry, a combo-box for the employee and regular text entries for the rest. What I would like to do is: When the user selects the employee and the date, the info already entered for that day, to display in the time fields. If it is not entered it should be blank. I have not been doing this for very long so the more basic the explanation, the better.
Jack Cowley
Welcome to Utter Access Forums!
mmm. It sounds like your data may not be normalized. Do you have a table of Employee information and a table for their 'time'? If your data is not set up like this then you need to rethink your tables as the data is not normalized. You might want to take a look at this article on normalization.
hth,
Jack
inventingdreams
I do have them seperate, however I would like the data entry form to function differently. Right now it enters the data into the time form directly from the current record "status" As I change records down at the bottom I am able to enter different paramenters for different entries on the table. I would like to change the way I navagate those entries from the little arrow on the bottom of the form to actually using my activex calendar and the drop-down menu (employee), then enter the time according to those positions. That is really hard to explain I hope that did it.
Jack Cowley
To be candid I am a bit lost. If you want to find a specific record on a form you can create a combo box, using the Wizard, to find the record. Add the combo box to the form and on the first screen of the Wizard select the 3rd item, "Find a record..." Finish the Wizard. When you make a selection from the combo box that record will be shown on the form.
th,
Jack
Jack Cowley
I just realized you had an attachment and after seeing it I am still curious if your data is normalized. Do you have more than one 'In' and one 'Out' field in your table? Is the In and Out data in a table that is related to your Employee table? If your tables look something like these then you should be good to go...
blEmployees
EmployeeID (PK and auto)
LastName
FirstName
tblJobTimes
JobTimesID (PK and auto)
EmployeeID (FK)
JobID (FK)
TimeIn
TimeOut
JobDate
inventingdreams
The times are split the way they are due to our time clock being in 60 minute time rather than military like they should be. Anyway, I think I have it figured out, I just didn't see the selection on the wizard I should be able to figure out the rest form here.
Well actually I have figured this part out, probably many more questions before I finish this database
Thank you
Jack Cowley
You are not saving your time as 6:35AM meaning they clocked in at 35 minutes after 6? Is your 'time' really numbers and not actually Date/Time data types?
If you do not see the 3 item in the combo box wizard then I assume your form is unbound... if it is I am wondering why?
Anyway, good luck and let us know if you have questions...
Jack
inventingdreams
Well, The time is set up that way for speed. I have them auto indexed so there is a minimum #of keystrokes IE it is faster to use military hours and the minutes in 60 and have the program do the work than typing am or pm for every entry. The important part of the table is the total hours assigned to a certain date assigned to a certain employee. I think the structure is still a little innefficient though because I have one table that will end up with all of the information and I really can't figure out how to organize it better. If I had a table full of dates infinately I could use that to relate to the time sub-table but I don't think that's possible. So for now it looks like this (attachment) If you have any ideas...I would eventually like to tie all of this information into a couple of reports and possible add the schedule to the calendar to do a constant comparison for tardies, long lunches and so-forth.
Jack Cowley
Your data is not normalized. You have 'repeating groups' and this is a no-no in a relational database. You have the times as FIELDS in a record and they should be RECORDS in a table. What would happen if your company needs to add another slot for time in and time out? With your current setup you would need to modify your table, queries, forms, reports, etc. Take a look at my earlier suggestion for normalizing your structure.
ack
inventingdreams
How Do I go about making Dates Records?
Jack Cowley
I do not understand your question. You can, if you need the current date, use the Date() to enter todays date in a field in the table. If you can give me some more detail on what it is you want to do I will try and assist you.
ack
inventingdreams
Each employee should have one total time entry per date the dates should not repeat but the times may repeat. I really cannot figure out how to structure that. It seems really simple and right there out of my reach. Does it seem feasible to enter the date on one form (entering date into [Date] Table) that brings up another form with a list of employees. For each employee selected I could enter the times specific to that employee and date?
o in short i Would have three tables:
Date(SK)
FirstName
LastName
SS#(SK)
Total Time (FK)
IN1HOUR
IN1MINUTE
OUT1HOUR
OUT1MINUTE
IN2HOUR
IN2MINUTE
OUT2HOUR
OUT2MINUTE
This may be a little off.
Jack Cowley
We need to start at the beginning... Your sample tables are wrong and I cannot see a reason for a table of Dates. You should not use SSNumber as a primary key. If you look back a few posts I posted sample tables and that is closer to what you want then what you have. Your TotalTime table still has repeating groups and that is wrong so look at the table I suggested as it is right.
Well me what it is you are trying to do with a database. Why select a date before an Employee? Who enters the data, you or the employee? If you can give me a brief step-by-step of how you want to enter data I will do what I can to help, but first you need to normalize your structure. Take a look at this article (I can't recall if I already posted it) as it explains how to normalize data.
hth,
Jack
inventingdreams
I looked at the article and I am still trying to organize it in my head. I am the Manager. The employees have a punch clock and standard time cards. the timecard reads in normal 12/60 am/pm. Before I got there, the managers were converting and adding and subtracting mannually. I immediatly created a calculator using javascript to calculate without all of the math. But then we also have to enter the total time worked in an excel chart (x-axis - employee, y-axis - date) and also into a payroll file. (x-axis employee, entering total hours for two weeks) I would like to skip as much of this data entry as possible. I would like to enter the times in and out, get a total time, and with that info update the other forms automatically. Then if possible, I would like to program in a schedule to give me popups when an employee is late or whatever. It is in a plasma collection facility and we are pretty well automated otherwise but this stuff is just too much manually when it's mostly just basic math, and typing numbers over and over. The ins and outs are not repeating records they will have repeating data but that is just the nature of them.
On for [email=Day@8:00]Day@8:00[/email] am
Out for [email=Lunch@12:00pm]Lunch@12:00pm[/email]
In from [email=Lunch@1:00pm]Lunch@1:00pm[/email]
Out for [email=Day@5:00pm]Day@5:00pm[/email]
would look like:
IN1HOUR - 08
IN1MINUTE - 00
OUT1HOUR - 12
OUT1MINUTE - 00
IN2HOUR - 13
IN2MINUTE - 00
OUT2HOUR - 17
OUT2MINUTE - 00
It's fast to enter on a number pad. The math is done on an update query and updates [TotalTime] on the same Table.
Wait a minute...I think I see what you mean. Let me try something and I will repost later.
Thanks for your help by the way
Jack Cowley
With your current setup you cannot easily add to get your total as you must add 'across' fields. If the data is in RECORDS and not fields you can calculate the total elapsed time in for each In and Out in a query. From there you can get a total. I know it is normal in a paper world to save totals, but this is not the case for Access. You calculate the totals as you need them. If your data is properly structured this is easy to do. With your current structure it is not easy to do...
Good luck with you latest insight...
Jack
inventingdreams
OK here is what I did....I made two tables
blTimeCard
TimeCardID(pk)
InDay
OutLunch
InLunch
OutDay
tblEployees
EmployeeID(pk)
FirstName
LasName
PhoneNumber
Is this Closer to Normalized?
How Should I Link these?
How Do I Integrate the Date?
I have not set any relationships yet
Jack Cowley
tblEployees
EmployeeID(pk)
FirstName
LasName
PhoneNumber
blTimeCard
TimeCardID (PK)
EmployeeID (FK)
WorkDate (Date/Time)
TimeIn
TimeOut
The above is the correct way to do what you want. There is one record for each date and that date has a TimeIn and a TimeOut. If the user checks in at 8 and out at 12 on 1/5/2006 then there will be two records if the user checks in at 1 and out at 5 for 1/5/2006.
Access is not like a spreadsheet or a paper form so you have to change the way you approach what you want to do in a relational database. I suggest that if you are going to keep track of time in Access that you use the Date/Time data type rather than using raw numbers. Access does have a unique way of handling time so I suggest you read this article so you can see how it is done.
Jack
inventingdreams
Ok I have it set up exactly like that and I like it......I am done for tonight.
I will start on the input forms, math and the reports next time.
Thank you again.
Talk to you later.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.