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

[Jerry Maiapu]

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,n ames =text. and tbldates has 4 fiels Id=autonumber,n amesid=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... .

[TheSmileyCoder]

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.

[patjones]

Here's what I did...

For the clock in button:

Private Sub cmdClockIn_Click()

Dim qryUpdate As QueryDef
Dim strSQL As String

Me.txtDateCurrent.SetFocus

strSQL = "PARAMETERS [Name] SMALLINT, [TimeIn] DATETIME; INSERT INTO tbldates (namesid, clockin) VALUES ( [Name], [TimeIn])"

Set qryUpdate = CurrentDb.CreateQueryDef("ClockIn", strSQL)
qryUpdate("Name") = Me.cboNames
qryUpdate("TimeIn") = Now
qryUpdate.Execute (dbFailOnError)

cmdClockIn.Enabled = False
cmdClockOut.Enabled = True

'Clean up
qryUpdate.Close
CurrentDb.QueryDefs.Delete "ClockIn"

End Sub

For the clock out button:

Private Sub cmdClockOut_Click()

Dim qryUpdate As QueryDef
Dim strSQL As String

Me.txtDateCurrent.SetFocus

strSQL = "PARAMETERS [Name] SMALLINT, [TimeOut] DATETIME; UPDATE tbldates SET clockout = [TimeOut] WHERE namesID = [Name] AND clockout IS NULL"
 
Set qryUpdate = CurrentDb.CreateQueryDef("ClockOut", strSQL)
qryUpdate("Name") = Me.cboNames
qryUpdate("TimeOut") = Now
qryUpdate.Execute (dbFailOnError)

cmdClockIn.Enabled = True
cmdClockOut.Enabled = False

'Clean up
qryUpdate.Close
CurrentDb.QueryDefs.Delete "ClockOut"

End Sub

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

Private Sub cboNames_AfterUpdate()

If IsNull(DLookup("[namesID]", "tbldates", " [namesID] = " & Me.cboNames & " AND clockout IS NULL")) Then

    Me.cmdClockIn.Enabled = True
    Me.cmdClockOut.Enabled = False
    
Else

    Me.cmdClockIn.Enabled = False
    Me.cmdClockOut.Enabled = True
    
End If

End Sub

Notice that an autonumber ID is not necessary in tbldates...inst ead, 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

[Jerry Maiapu]

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.

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..

[Jerry Maiapu]

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

[Jerry Maiapu]

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.

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..

[TheSmileyCoder]

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?

[patjones]

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

[Jerry Maiapu]

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

[patjones]

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

[Jerry Maiapu]

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

[Jerry Maiapu]

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

[Jerry Maiapu]

Hi zepphead80 ,
When minimised it does not respond. Access application is frozen.. Don know what to do ..any Idea..zepphead8 0 ?

Jerry

[patjones]

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

[Jerry Maiapu]

Problems with date

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,txt clockin,txtcloc kout)

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