By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Using sql insert into statement with access VB in a timesheet db

Jerry Maiapu
100+
P: 259
I am trying to create a simple clock in clock out (timesheet) in ms access. I have two tables (tblnames for staff names and tbldates for clocking times). tblnames has 2 fields namely id=autonumber,names =text. and tbldates has 4 fiels Id=autonumber,namesid=number, clockin=time/date and clock out =date/time.

the twos have a one to many relationship

I have a form that has a combo box that holds names of employees. in the same form i have a cmd button called "clock in" and another called "clock out"
Function:
The user selects the names from the combo box and when the the user clicks on the cmd clockin button the current date should be inserted into clockin field in the tbldates tables and and the cmd clockin button should be disable and the cmd clock out button should be enable.

When the user click clockout button the current date should be inserted into clockout field in the tbldates table. This becomes a single record in the tbldates tables and the next day the clockin and clock out dates/times should go in the second record of the tbldates tables.

I though of putting a sql "insert into" and "d look up" statement using VB so that when the user clicks on the clock in button the visual basic as to use the dlook up to cheeck if names (selected in the combo box) exist in table tblnmes and use to insert into statement to insert the corresponding (related) date -clockin and clock out values in its respective fields in the tbldates table.

The tables have one to many relationship so I could I go about inserting clock in clock out times into the tbldates table for the correct coresponding employeee .
I dont how to this in vb or whatever got the idea but hard to implemnt it..

How could I acheive this.

Attached is a screenshot of my desired form and some explaination....
Attached Images
File Type: jpg qst.jpg (20.4 KB, 511 views)
Feb 26 '10 #1
Share this Question
Share on Google+
18 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Is this a database located on a network, where everyone uses the same database? Is it located on a single PC? Will there be multiple users?

I think I have a good idea of what you want, but to better guide you, I would need some information on how you would intend to use this database and for what.
Feb 26 '10 #2

patjones
Expert 100+
P: 931
Here's what I did...

For the clock in button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdClockIn_Click()
  2.  
  3. Dim qryUpdate As QueryDef
  4. Dim strSQL As String
  5.  
  6. Me.txtDateCurrent.SetFocus
  7.  
  8. strSQL = "PARAMETERS [Name] SMALLINT, [TimeIn] DATETIME; INSERT INTO tbldates (namesid, clockin) VALUES ( [Name], [TimeIn])"
  9.  
  10. Set qryUpdate = CurrentDb.CreateQueryDef("ClockIn", strSQL)
  11. qryUpdate("Name") = Me.cboNames
  12. qryUpdate("TimeIn") = Now
  13. qryUpdate.Execute (dbFailOnError)
  14.  
  15. cmdClockIn.Enabled = False
  16. cmdClockOut.Enabled = True
  17.  
  18. 'Clean up
  19. qryUpdate.Close
  20. CurrentDb.QueryDefs.Delete "ClockIn"
  21.  
  22. End Sub

For the clock out button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdClockOut_Click()
  2.  
  3. Dim qryUpdate As QueryDef
  4. Dim strSQL As String
  5.  
  6. Me.txtDateCurrent.SetFocus
  7.  
  8. strSQL = "PARAMETERS [Name] SMALLINT, [TimeOut] DATETIME; UPDATE tbldates SET clockout = [TimeOut] WHERE namesID = [Name] AND clockout IS NULL"
  9.  
  10. Set qryUpdate = CurrentDb.CreateQueryDef("ClockOut", strSQL)
  11. qryUpdate("Name") = Me.cboNames
  12. qryUpdate("TimeOut") = Now
  13. qryUpdate.Execute (dbFailOnError)
  14.  
  15. cmdClockIn.Enabled = True
  16. cmdClockOut.Enabled = False
  17.  
  18. 'Clean up
  19. qryUpdate.Close
  20. CurrentDb.QueryDefs.Delete "ClockOut"
  21.  
  22. End Sub

For the combo box (to determine whether or not the person you picked is currently clocked in or out):

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboNames_AfterUpdate()
  2.  
  3. If IsNull(DLookup("[namesID]", "tbldates", " [namesID] = " & Me.cboNames & " AND clockout IS NULL")) Then
  4.  
  5.     Me.cmdClockIn.Enabled = True
  6.     Me.cmdClockOut.Enabled = False
  7.  
  8. Else
  9.  
  10.     Me.cmdClockIn.Enabled = False
  11.     Me.cmdClockOut.Enabled = True
  12.  
  13. End If
  14.  
  15. End Sub

Notice that an autonumber ID is not necessary in tbldates...instead, this method assumes that the person is either clocked in - in which case they have a record where clock out is null, or they are clocked out, in which case they have no records where clock out is null, and then DLookup returns null.

Also, namesID should NOT be set as a primary key in tbldates; otherwise Access won't allow you to have more than one entry for a particular person.

I quickly put together a form and tested this all out...seems to work fine. Let me know how it works out for you.

Pat
Feb 26 '10 #3

Jerry Maiapu
100+
P: 259
@TheSmileyOne

Thanks. this databse will be put on a network after completion but at the moment am working on it on a laptop and definetly there will be multiple users.

I am a graduate Trainee trying to create a timeshhet database for emplyees in a NGO. This databse will be used by the employees to clock in and out and am also planing to put a cmd button to display/run report only such time the name in the comco box is the boss's name.

Thanks for for time and good heart to help..
Mar 1 '10 #4

Jerry Maiapu
100+
P: 259
Pat you are the best... I needed this very much thanks alot for your coding. I'll try it out first..
Once again thanks..

Jerry
Mar 1 '10 #5

Jerry Maiapu
100+
P: 259
@TheSmileyOne

Thanks. this databse will be put on a network after completion but at the moment am working on it on a laptop and definetly there will be multiple users.

I am a graduate Trainee trying to create a timeshhet database for emplyees in a NGO. This databse will be used by the employees to clock in and out and am also planing to put a cmd button to display/run report only such time the name in the comco box is the boss's name.

Thanks for for time and good heart to help..
Mar 1 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Once on the network, will each user use his own personal PC, or shared PC's to access the database?

How do you intend to handle people forgetting to punch in/out?
Mar 1 '10 #7

patjones
Expert 100+
P: 931
That sounds great Jerry. My solution is intended to be the minimum functionality that you require. As Smiley says, you will probably want to put in other features to make it more user friendly - like handling the case of forgetting to clock in or out.

It also would not be a huge leap to have the employee clock in/out by entering some kind of employee ID. As it stands now anyone could pick any name from the drop-down list and clock them in or out at will. Having an ID that each employee enters makes it more secure.

Let us know how it goes.

Pat
Mar 1 '10 #8

Jerry Maiapu
100+
P: 259
Ok thanks guys for guiding me through. I am almost complete with the clock in/out form.

Attached is a screen shot of my login form and the clocking form.

One of my colleagues helped to have the log in form with username, password and domain from the windows login details to log into the database. Which is pretty cool. (See attached screen shoot)

On the timesheet form whenever the manager logs in the navigation and other buttons are enabled. Currently in the screen shot it is disabled.
If I need help in creating reports or any other thing Ill say so but for now you help me with this problem scenario?

What I want to do is when the wrong user name or password is entered instead my msgboxes telling users to re enter the correct names I would like to prompt that message in a tool tip (balloon) pointing to the correct textbox instead of the msgboxes because it quite irritating to see these msgboxes popping every now and then and pressing ok button several times.
The tool tip can be useful in displaying my messages for example when the user clocks in the tool tip can point to the clock in time with the message like “You just clocked in”

Please can one of you guys help me out if you can. I’ve pretty no idea..

Thanks in advance

Jerry

One more thing…..do any one of you know how to minimise the access window to the system tray and when double clicked it can pop up (maximised)

Sorry for a lot of things but this is what I would like to do:
When a user clocks in and press a ok button the access window minimises to the system tray (notification area) if the user forgets to clock out and shuts down or logs off the computer
I will create a pop up msgbox (vbyesno) that will prevent the close (unload) of clock in form if the user does not clock out (doesn’t matter weather it is the access application or the windows os itse trying tot unload the clockin form lf the dialogue pop up msgbox can message can still stop anything from closing the form unless the user press No to exit.

This will only happen if the access application is still running and that is why I want someone to help minimise the window to the system tray. If minimised to the task bar anybody can accidentally maximise when dealing with other application on the task bar..

Thankyou


Jerry M
Mar 4 '10 #9

patjones
Expert 100+
P: 931
Jerry -

The closest built-in thing that Access has to a balloon is the ControlTip property. This is basically just a box that pops up when you hover over a control, and displays whatever text you specify in the property. I am lazy about using these in my own projects, but it is a good practice to get into because it helps the people who will be using your application.

However, for what you are looking for...I think you need to find an ActiveX balloon control which can be downloaded and added in to your copy of Access. There are many vendors online which have such controls. Here is one example out of many that I found: http://www.softspecialist.com/Balloo...alloon-OCX.htm.

For minimizing Access to the Windows tray, I think what you need is a Windows API call in your VBA code. For instance: http://social.msdn.microsoft.com/For...7-c54b61cf13de

Hope this helps.

Pat
Mar 4 '10 #10

Jerry Maiapu
100+
P: 259
Thanks Zepphead.

Would you mind telling me (show me with an example) on how I would program it (using VB) or I mean how do I use the Active x balloon control to point to a textboxes when a button is clicked on a cmd button..Give a sample please..

Thanks you so much

Jerry
Mar 8 '10 #11

Jerry Maiapu
100+
P: 259
Ok thanks guys for guiding me through. I am almost complete with the clock in/out form.

Attached is a screen shot of my login form and the clocking form.

One of my colleagues helped to have the log in form with username, password and domain from the windows login details to log into the database. Which is pretty cool. (See attached screen shoot)

On the timesheet form whenever the manager logs in the navigation and other buttons are enabled. Currently in the screen shot it is disabled.
If I need help in creating reports or any other thing Ill say so but for now you help me with this problem scenario?

What I want to do is when the wrong user name or password is entered instead my msgboxes telling users to re enter the correct names I would like to prompt that message in a tool tip (balloon) pointing to the correct textbox instead of the msgboxes because it quite irritating to see these msgboxes popping every now and then and pressing ok button several times.
The tool tip can be useful in displaying my messages for example when the user clocks in the tool tip can point to the clock in time with the message like “You just clocked in”

Please can one of you guys help me out if you can. I’ve pretty no idea..

Thanks in advance

Jerry

One more thing…..do any one of you know how to minimise the access window to the system tray and when double clicked it can pop up (maximised)

Sorry for a lot of things but this is what I would like to do:
When a user clocks in and press a ok button the access window minimises to the system tray (notification area) if the user forgets to clock out and shuts down or logs off the computer
I will create a pop up msgbox (vbyesno) that will prevent the close (unload) of clock in form if the user does not clock out (doesn’t matter weather it is the access application or the windows os itse trying tot unload the clockin form lf the dialogue pop up msgbox can message can still stop anything from closing the form unless the user press No to exit.

This will only happen if the access application is still running and that is why I want someone to help minimise the window to the system tray. If minimised to the task bar anybody can accidentally maximise when dealing with other application on the task bar..

Thankyou


Jerry M
On how to handle cases of forgeting to log out, if you have any ideas/sample databse/Vba/sql etc.. please give
Mar 8 '10 #12

Jerry Maiapu
100+
P: 259
Hi zepphead80 ,
When minimised it does not respond. Access application is frozen.. Don know what to do ..any Idea..zepphead80 ?

Jerry
Mar 8 '10 #13

patjones
Expert 100+
P: 931
Jerry -

Sorry, I'm just not experienced enough with Windows API calls to be able to help you with this. Hopefully there is someone else on the forum here with the insight you need to work through the issue.

Pat
Mar 8 '10 #14

Jerry Maiapu
100+
P: 259
I have a date field of tbldates that records/captures date from a form txtbox.
In the same table (tbldates) has this clockin clock out times.
Question
if the user forgets to clock out then (ie clockout field will be null) How can I pop up a msg box that tell the user (the next day ....not next time same day... when access starts ) he did not clock out yetreday.. Other than yesterday if the clockin field is null for days after yesterday should not be accounted for..

What I did/thought..

Because on the clock in form 3 txtboxes are shown ( dateclockin,txtclockin,txtclockout)

this is what it to do..

1 on open (ie form open) go to last record
2. chech if date is yesterday
3 if yesterday then check txtclockout is null or empty
4. if empty then pop up msgbox message "you did not clock out yesrday"
5.else go to new record
can some give me the code for this

thanks
problem wth step 3
Ta Jerry
Mar 11 '10 #15

patjones
Expert 100+
P: 931
I'm confused about why you are checking to see if it was yesterday. What if someone forgets to clock out Friday evening? They still need to get the message Monday morning when they go to clock in. Don't you really just need to check and see if there is a record with a null clock out time, no matter what day it is?

Pat
Mar 11 '10 #16

Jerry Maiapu
100+
P: 259
Thank you for the direction.
What I meant was that we used to have power black out normally during the day so at times users might not clock out if stanby gen doesnt work. that is why I wanted to check thier clock out status so that they can enter the right clock out time the next day. Like you said I can check every days but the problem is with my table realtionship I have a one(tblusers) to many(tbldatetime) relationship and if it needs to check then it will check the entire field with previous dates. Don know what to do
Edit relationship?

Please help Ta..
Mar 12 '10 #17

patjones
Expert 100+
P: 931
Let me ask you this. Suppose I clock in Wednesday morning. Then the power goes out Wednesday afternoon and I cannot clock out. Thursday morning when I come in and choose my name from the combo list, what should happen?

The way I wrote my code back in post #3, the clock in button would be disabled because I still have a record with a null clock out time (the record from Wednesday).

I'm getting the sense from you that you still want the employee to be able to clock in even though Wednesday's clock out is still null. If that's the case, how do you want to fill in the clock out time from Wednesday?

I don't think the relationship between the tables should present a problem here. Remember, tblUsers is really just a "look up" table that tells you what the employee's name is.

I think we are going in the right direction with this, but we just need to figure out what to do when someone tries to clock in without having previously clocked out.

Pat
Mar 12 '10 #18

Jerry Maiapu
100+
P: 259
Hi Zepphead80,
I thankyou for guiding me through. I have completed the timesheet and now they are using it. However recently, I found out that for public holidays or power disruption the person with administrative privilege can clock in or clock out for all of the employees at a particular day.
How can I make it possible for him to just enter
1. Clockin time
2. Clockout time and
3. Date

..on a form so that when he clicks on a cmdupdate button, it will put the dates and times into its respective fields in the table for all the employess.

I have two tables tblemplyees and tbltimesheet. They have a one to many relationship:

I can use insert into statement but the problem is:
First I have to check the correct names in tblemplyees table and insert into the tbltimesheet the dates and the clockin and clock out times.

How can I achieve this? Please help..

A short table attributes

Table1

tblemplyees

eid autonumber (primary key)
efname text (first name)
esname text (second name)

…………………………………………..
Table 2
tbltimesheet
tid autonumber (primary key)
eid number (foreign key)
date date/time (date clocked in)
clockin date/time (time clocked in)
clockout date/time (time clocked out)
Apr 12 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.