![]() |
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
|
|||
|
|||
![]()
I would like to know if there is any way on a form that you can type in a
number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#2
|
|||
|
|||
![]()
What is your table structure? Do you have fields like WorkerID,
SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#3
|
|||
|
|||
![]()
My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently
obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#4
|
|||
|
|||
![]()
To answer your question specifically:
I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#5
|
|||
|
|||
![]()
Compile error:
Expected: end of statement i keep getting this error message for this line; Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" sorry i know nothing about codes so it may probably be an easy mistake "Theo Geer" wrote: To answer your question specifically: I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#6
|
|||
|
|||
![]()
Sorry! this is my bad. There needs to be a closing parentheses after the last
quote. Replace the line with: Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;") This is what I get for not testing code I post first! Theo Geer "MGCurz" wrote: Compile error: Expected: end of statement i keep getting this error message for this line; Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" sorry i know nothing about codes so it may probably be an easy mistake "Theo Geer" wrote: To answer your question specifically: I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#7
|
|||
|
|||
![]()
i added the recent changes and came over another problem when i go to put in
the memberID number on the form it comes up with Run-time error '94': Invalid use of Null then i click on debug and is has; ID = Me.MemberID.Value highlighted in yellow "Theo Geer" wrote: Sorry! this is my bad. There needs to be a closing parentheses after the last quote. Replace the line with: Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;") This is what I get for not testing code I post first! Theo Geer "MGCurz" wrote: Compile error: Expected: end of statement i keep getting this error message for this line; Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" sorry i know nothing about codes so it may probably be an easy mistake "Theo Geer" wrote: To answer your question specifically: I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#8
|
|||
|
|||
![]()
ok sorry my mistake i had mucked with the code but now my problem is that i
can record the member id but not the time in or out "MGCurz" wrote: i added the recent changes and came over another problem when i go to put in the memberID number on the form it comes up with Run-time error '94': Invalid use of Null then i click on debug and is has; ID = Me.MemberID.Value highlighted in yellow "Theo Geer" wrote: Sorry! this is my bad. There needs to be a closing parentheses after the last quote. Replace the line with: Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;") This is what I get for not testing code I post first! Theo Geer "MGCurz" wrote: Compile error: Expected: end of statement i keep getting this error message for this line; Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" sorry i know nothing about codes so it may probably be an easy mistake "Theo Geer" wrote: To answer your question specifically: I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#9
|
|||
|
|||
![]()
MGC - What is the code you're using? The code I gave you should capture the
member ID and time into your table. - Make sure that the ..fields("FIELDNAME") = now() line has FIELDNAME as the name of the actual field in your table. "MGCurz" wrote: ok sorry my mistake i had mucked with the code but now my problem is that i can record the member id but not the time in or out "MGCurz" wrote: i added the recent changes and came over another problem when i go to put in the memberID number on the form it comes up with Run-time error '94': Invalid use of Null then i click on debug and is has; ID = Me.MemberID.Value highlighted in yellow "Theo Geer" wrote: Sorry! this is my bad. There needs to be a closing parentheses after the last quote. Replace the line with: Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;") This is what I get for not testing code I post first! Theo Geer "MGCurz" wrote: Compile error: Expected: end of statement i keep getting this error message for this line; Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" sorry i know nothing about codes so it may probably be an easy mistake "Theo Geer" wrote: To answer your question specifically: I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
#10
|
|||
|
|||
![]()
using this code it only records the memberid;
Private Sub Form_KeyPress(KeyAscii As Integer) Dim rst As String ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Membership.MemberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [Membership] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[Membership]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub "Theo Geer" wrote: MGC - What is the code you're using? The code I gave you should capture the member ID and time into your table. - Make sure that the .fields("FIELDNAME") = now() line has FIELDNAME as the name of the actual field in your table. "MGCurz" wrote: ok sorry my mistake i had mucked with the code but now my problem is that i can record the member id but not the time in or out "MGCurz" wrote: i added the recent changes and came over another problem when i go to put in the memberID number on the form it comes up with Run-time error '94': Invalid use of Null then i click on debug and is has; ID = Me.MemberID.Value highlighted in yellow "Theo Geer" wrote: Sorry! this is my bad. There needs to be a closing parentheses after the last quote. Replace the line with: Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;") This is what I get for not testing code I post first! Theo Geer "MGCurz" wrote: Compile error: Expected: end of statement i keep getting this error message for this line; Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" sorry i know nothing about codes so it may probably be an easy mistake "Theo Geer" wrote: To answer your question specifically: I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. In short: Yes. The trick is for the form to know whether it is signing someone in, or out. Having a method of knowing whether the sign-out time has been entered or not that is as simple as possible would help. Using your datastructure, here is what I would recommend. First, instead of using "Date" and "Time In" and "Time Out" - I'd recommend you kill the "Date" field and just use Time In and Time Out as "date" types using the now() function. This will let you do math more easily in the future, and will allow for easier error catching when someone scans in, but forget to scan out. Or scans out twice etc. I'll write the following assuming you've done that. All your form needs is a Text Box with the "OnKeyPress" event activated. Here is a sample of the Event. (Note: make certain you don't have a default value for TimeOut. The Field must remain null after a Sign-In Entry for this to work.) Private Sub MemberID_KeyPress(KeyAscii As Integer) Dim rst As Recordset ' This will be the Recordset of your table, or your row. Dim ID As String ' This will hold the Members ID Dim Q As String ' This is a String to hold a small Query ' Define the ID ID = Me.memberID.Value ' Determine if there is a row where the "TimeOut" has not been entered. Q = "SELECT * FROM [TABLENAME] WHERE memberID='" & ID & "' AND isnull(TimeOut)=true;" Set rst = CurrentDb.OpenRecordset(Q) ' Opens a Recordset from Q If IsNull(rst.RecordCount) = True Then ' If there is no record rst.Close ' Close the Recordset Set rst = CurrentDb.OpenRecordset("[TABLENAME]") ' Open the whole Table With rst ' In that table .AddNew ' Add a new Row .Fields("MemberId") = ID ' Set MemberId .Fields("TimeIn") = Now() ' Set Date/Time of Entry .Update ' Update the Record End With rst.Close ' Close the Recordset Else With rst ' With the single-Row Recordset .Edit ' Edit this Row .Fields("TimeOut") = Now() ' Set Date/Time of Exit .Update ' Update the Record End With rst.Close ' Close the Recordset End If End Sub Hope this Helps! Theo Geer "MGCurz" wrote: My table is setup as follows MemberID,Date,TimeIn, and TimeOut. I recently obtained a barcode wedge scanner which just acts as typin the number then pressing enter key. In my Online Centre there are people running courses and people can also come and go as the please but we don't have anyone on the reception desk and i need to take note of their sign in and sign out times as we need to produce evidence of the times to our funding agency.At the moment im using our old database (created by an ex employee) and we have to put in the time infomation by hand. We are usually open from 9 til 9 everyday and usually don't leave till about 11 because we need to input data "John Spencer" wrote: What is your table structure? Do you have fields like WorkerID, SignInDateTime, and SignOutDateTime? Or do you have fields like WorkerID, ActionDateTime, and In_or_Out? You can use defaults for your fields of Date() and Time() that will automatically be entered when a record is created. Personnally I would use a field that stored both the date and time - Now() - as that will make it much easier to do calculations on the duration of time between two events (sign in/sign out). "MGCurz" wrote in message ... I would like to know if there is any way on a form that you can type in a number e.g. 1234 press enter then in a table it adds 1234, the date and sign in time.Also usin the same form if you type the 1234 in again it will add sign out time. Any help please? |
|
Thread Tools | |
Display Modes | |
|
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
time sheet drop down lists | Steve | General Discussion | 12 | March 18th, 2006 11:30 PM |
Time and Attendance System Query | Dan Young | Running & Setting Up Queries | 2 | March 7th, 2006 10:01 PM |
Can this even be done? Tracking Date / Time Elapsed | scd | New Users | 6 | February 3rd, 2006 08:44 PM |
Calendar Question | Josh | General Discussion | 7 | March 28th, 2005 11:19 PM |
Garbage at end of document | Lexisch | General Discussion | 2 | December 2nd, 2004 09:35 PM |