Time/Date

[jmorris]

I am trying to get access to insert time and date in a record on pushing a button, a bit like clocking in, the database is for a charity so they know who is in and who left at the end of the day before they lock up.

Any ideas please?

[June7]

Various ways to accomplish. Could have field with default value of Now(). When new record is created, date/time automatically set. How are you creating the new record?

[jmorris]

Various ways to accomplish. Could have field with default value of Now(). When new record is created, date/time automatically set. How are you creating the new record?

I have created a sign in/sign out table and hoped to link in a button so they find their id then push sign in button/sign out it will also give some idea of how many hours our volunteers put in to help the rescue

Any help is much appreciated as creating the whole thing has been a steep (vertical almost) learning curve. Thanks

[June7]

You have built a form as interface to the table? Are you using username/password login so each employee can enter record only for themselves? Have button to automatically go to new record and populate the user info and date.

[jmorris]

You have built a form as interface to the table? Are you using username/password login so each employee can enter record only for themselves? Have button to automatically go to new record and populate the user info and date.

I have a form for each of our volunteers, with their detail on which they do not log into just call up their form, the form having been produced from a table, what I wanted to do was put a button on the bottom of the form so they could use it to sign in and out we then know how many people are on site and how much free time the volunteers actualy give the charity.
It was just a thought.
Really appreciate your time with this

[June7]

You must have table to store time in and time out data. If this table is not included in the RecordSource of the form they work with, then need code to save data. Saving time in and out means creating/updating records in time table. Each will require an SQL action statement (INSERT or UPDATE) in some event, such as button Click or form Close.

A form for each volunteer? Are these forms identical? I would build one form to serve all.

[jmorris]

You must have table to store time in and time out data. If this table is not included in the RecordSource of the form they work with, then need code to save data. Saving time in and out means creating/updating records in time table. Each will require an SQL action statement (INSERT or UPDATE) in some event, such as button Click or form Close.

A form for each volunteer? Are these forms identical? I would build one form to serve all.

I have created a table which includes ID, Sign In Sign Out First & Last Name just can't get further and yes the forms are identical for each volunteer

[June7]

Should not save names. Each volunteer should have a unique ID. Save unique ID. Maybe you will never have two Jane Smith but possibility is there.
So in the form Open event have code to create record:
CurrentDb.Execute "INSERT INTO tablename([Sign In], VolID) VALUES(#" & Now() & "#, " & Me.volID
Then in the form Close event (this is tricky, INSERT is easier than UPDATE):
CurrentDb.Execute "UPDATE tablename SET [Sign Out] = #" & Now() & "# WHERE volID= " & Me.volID & " AND ID = " & DMax("ID", "tablename", "volID=" & Me.volID)

I use only VBA, not macros. Macro might be able to do this action but with slightly different code. Don't think CurrentDB.Execute will work in macro. Would be DoCmd.RunSQL instead.

How you pass the volID parameter is delimma. Is this value available by reference to a textbox on the open form?

BTW, should avoid spaces, special characters, punctuation (underscore is exception) in names. Also, don't use reserved words as names.

[jmorris]

Should not save names. Each volunteer should have a unique ID. Save unique ID. Maybe you will never have two Jane Smith but possibility is there.
So in the form Open event have code to create record:
CurrentDb.Execute "INSERT INTO tablename([Sign In], VolID) VALUES(#" & Now() & "#, " & Me.volID
Then in the form Close event (this is tricky, INSERT is easier than UPDATE):
CurrentDb.Execute "UPDATE tablename SET [Sign Out] = #" & Now() & "# WHERE volID= " & Me.volID & " AND ID = " & DMax("ID", "tablename", "volID=" & Me.volID)

I use only VBA, not macros. Macro might be able to do this action but with slightly different code. Don't think CurrentDB.Execute will work in macro. Would be DoCmd.RunSQL instead.

How you pass the volID parameter is delimma. Is this value available by reference to a textbox on the open form?

BTW, should avoid spaces, special characters, punctuation (underscore is exception) in names. Also, don't use reserved words as names.

Thanks yes the ID is available on the form, each member has a unique ID but the ID field is hidden does that matter?

[June7]

Hidden does not matter. Use your textbox name in place of my volID reference and use your ID field name in the SQL and DMax.

[jmorris]

Hidden does not matter. Use your textbox name in place of my volID reference and use your ID field name in the SQL and DMax.

Hi

I have inserted the following
CurrentDb.Execute "INSERT INTO sign in_out([Sign In], ID) VALUES(#" & Now() & "#, " & Me.ID

where sign in_out is the form where I want it to record the date / time and ID is the unique ref no in my volunteers form and table which is unique

I went to the button opened Visual basic and copied in the code above but still got an error message. As you can gather I am very new to this and the database I have built is my first one, managed to get most of it working but this is a stumbling block for me

[June7]

The code must be in some event, such as button Click.

The code will insert a record into table, not form, so use the table name.

Search Access Help for topic 'Introduction to Programming in Access'.

Spaces in any names require name be enclosed in []. Advised not to use spaces, special characters, punctuation (underscore is exception) in any names. Also, don't use reserved words, such as Date, as field names.

Did you consider using DefaultValue property of the date textbox?

[jmorris]

The code must be in some event, such as button Click.

The code will insert a record into table, not form, so use the table name.

Search Access Help for topic 'Introduction to Programming in Access'.

Spaces in any names require name be enclosed in []. Advised not to use spaces, special characters, punctuation (underscore is exception) in any names. Also, don't use reserved words, such as Date, as field names.

Did you consider using DefaultValue property of the date textbox?

Sorry do not understand what you mean by DefaultValue property of the date text box wich will no doubt show you the level I am at.
I have tried and tried but get a runtime error 3134 and when I type that in it does not show up on my search

[June7]

DefaultValue property can be set for a field with table in design view. Then anytime new record is created it will have the current date already in the field. Or this property can be set for the textbox with form in design view. All properties of controls or forms/reports can be set from the Properties dialog box. However, this won't save the ID value into the record so might not be any advantage to setting DefaultValue.

Access Help has guidelines about setting properties. Did you search for the programming topic?

If you want to provide project by attachment to post, I will look at.

[jmorris]

DefaultValue property can be set for a field with table in design view. Then anytime new record is created it will have the current date already in the field. Or this property can be set for the textbox with form in design view. All properties of controls or forms/reports can be set from the Properties dialog box. However, this won't save the ID value into the record so might not be any advantage to setting DefaultValue.

Access Help has guidelines about setting properties. Did you search for the programming topic?

If you want to provide project by attachment to post, I will look at.

Hi
Thanks for your offer but I do not seem to be able to attach a file I have even tried to winzip it, apparently a security bit was missing