Adding a date field to db

[Rio Liaden]

Hi all!

My database allows all employees to enter their time and get a report at any time. The first screen has a calendar which is locked to the last day of the pay period and that date is the only one currently in place. The user selects his/her name from a dropdown, clicks the calendar, then "Next" to get to the time input screen. There are combo boxes to select the cost code, project, and company name. Separate boxes are available for the number of hours worked Sunday thru Saturday, and the employee can input their time for each day on separate lines, if they choose to. This allows them to have a separate line for regular time, vacation hours, hours worked in different projects, etc. Most of our employees are exempt, but the salaried ones are needing a mechanism to enter the date in each line for all of their hours. With this need in mind, I put in three combo boxes based on three tables: tblMonth, tblDay, and tblYear. Problem is, when I select January, as an example, from cboMonth, all of the available lines are populated with January. The same thing occurs with cboDay and cboYear. I really can't figure out where I have gone wrong and would appreciate any help.

Thanks in advance for whatever help anyone can render, and if you need to actually see the database, I will gladly send it.

Thanks!

Rio

[MMcCarthy]

Hi all!

My database allows all employees to enter their time and get a report at any time. The first screen has a calendar which is locked to the last day of the pay period and that date is the only one currently in place. The user selects his/her name from a dropdown, clicks the calendar, then "Next" to get to the time input screen. There are combo boxes to select the cost code, project, and company name. Separate boxes are available for the number of hours worked Sunday thru Saturday, and the employee can input their time for each day on separate lines, if they choose to. This allows them to have a separate line for regular time, vacation hours, hours worked in different projects, etc. Most of our employees are exempt, but the salaried ones are needing a mechanism to enter the date in each line for all of their hours. With this need in mind, I put in three combo boxes based on three tables: tblMonth, tblDay, and tblYear. Problem is, when I select January, as an example, from cboMonth, all of the available lines are populated with January. The same thing occurs with cboDay and cboYear. I really can't figure out where I have gone wrong and would appreciate any help.

Thanks in advance for whatever help anyone can render, and if you need to actually see the database, I will gladly send it.

Thanks!

Rio

What event code are you using on the combo boxes? Can you post it.

[Rio Liaden]

I did not write code, merely built from wizard. Sorry.

Rio

[NeoPa]

If you open the form in design view then select the controls (ComboBoxes; TextBoxes; etc) in order then look through the properties (There is a button on a toolbar or Alt-Enter should work equally well) look for any properties set to 'Event Procedure'. Where you find any, click on the elipsis box (little box with three '.'s (...)) on the right AFTER you select the property then copy the code found in that window.

[Rio Liaden]

Well, here it is, everything found in the editor...


Private Sub cboMonth_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_Load()
Me.Calendar1.Value = [Forms]![frmTimesheet]![Date1]
End Sub

'Private Sub Label27_Click()

'DoCmd.OpenReport "rptTimesheetAARDEXNew", acPreview, "qryTimesheetSingleScreen"
'End Sub

'Private Sub Label36_Click()
'DoCmd.Close
'End Sub

Private Sub Label48_Click()
On Error GoTo Err_Command49_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command49_Click:
Exit Sub

Err_Command49_Click:
MsgBox Err.Description
Resume Exit_Command49_Click
End Sub

Private Sub Project_BeforeUpdate(Cancel As Integer)

End Sub

[MSeda]

Check the "Row Source" property for the combo boxes if this refers to your data table then it will show a list of records from the data table (i.e. if you have 15 january records january will be listed 15 times). If you used the wizard and selected the first option, on the first screen; "I want the combo box to lookup values in a table or Query" and then chose the table where you want the data to be stored that would have caused this to happen.

You need to either create a table called "months" and list the months and select this table when the wizard ask which table you want the values to come From, or select the second option on the first wizard screen. "I will type in the values I Want" and then list the months in the grid provided.

To save the value in the table make sure the "Control Source" for the form is set to the table you want to save your data in and the combos "control Source" property is set to tblMonth or whatever field you want to save to in the table.

It maky be easier to use a single date field instead of separtate Month, Day Year fields. With a Single date field you could use a calendar control for users to select the Date.

[Rio Liaden]

Strange things happened when I tried the aforementioned solution, so I'm trying another, more logical way. If I put a calendar (MSCAL.Calendar.7) on the sheet so that if the employee clicks on the date it will populate a field. What is the code to connect it to a text box? This is the solution that was suggested.

Rio

[MSeda]

Place a command button on the form with the calendar control and put this in the buttons On Click Event procedure:

Private Sub Command3_Click()

Me.Text1 = Me.Calendar0

End Sub

of course change the names to match your objects.

[NeoPa]

Or you could put the same code in the AfterUpdate event of the calendar control perhaps, and save on the extra command button?

[Rio Liaden]

Or you could put the same code in the AfterUpdate event of the calendar control perhaps, and save on the extra command button?

When I click on the calendar on the date I need, it puts it in every line. When I change it for the next line, all lines are populated by the new date and so on. I can send a screen shot if necessary.

[NeoPa]

I think copying and pasting the code you're actually using would be more useful - to make sure everything is there and in the right place etc.

[Rio Liaden]

This is the code:


Private Sub Calendar1_Click()

WorkDate = Calendar1.Value

End Sub

[NeoPa]

I should be a dentist ;) (private joke - please ignore)
What is 'WorkDate' and where is it defined.

[Rio Liaden]

I should be a dentist ;) (private joke - please ignore)
What is 'WorkDate' and where is it defined.

Is that because I'm being a pain? My life's work and I'm good at my job! ;)Anyway, WorkDate is a text box, the receptacle for the date from Calendar1. There is no code associated except:

Private Sub WorkDate_Click()

End Sub

Private Sub WorkDate_GotFocus()

End Sub


Rio

[MSeda]

Question
In your Initial post you said you have multiple lines to enter employee time. On your form do the text boxes on each line link to the same "control source"?
i.e. do you have a Monday line with a date box with "Control Source" TblDate and the same for Tuesday etc.

[Rio Liaden]

Question
In your Initial post you said you have multiple lines to enter employee time. On your form do the text boxes on each line link to the same "control source"?
i.e. do you have a Monday line with a date box with "Control Source" TblDate and the same for Tuesday etc.

Yes. Each line will enter that day's date, and as I said in the last post, I changed it from tblDay, tblMonth, and tblYear to a calendar on the form. A text box on each of the lines will populate with the date when you click on the calendar date. The problem is, that particular date will populate on each line, and as the date for each new line is inserted, the new, changed date then populates each line, i.e. click on the calendar for Monday, Nov. 6. and that date would be put in the text box on the first line. The next day, Nov. 7 click on the calendar and Nov. 7 would be would be put in the text box on that line and also in the text box for the previous line, replacing Nov. 6.

Thanks for your help!

[MSeda]

If multiple text boxes on your form have the same control source that would cause what you described. A single form only shows one record at a time therefore regardless of the fact that you have multiple textboxes they are all looking at the same feild and record so when calendar or combos update one they all change. send me a screen shot of the design view of your form with a description of the underlying tables, I may be able to make a suggestion.

[NeoPa]

Is that because I'm being a pain? My life's work and I'm good at my job! Anyway, ...

I like to see someone with pride in their accomplishments ;)
If the WorkDate control is a TextBox, then I'm confused.
I'm not a great expert on working with forms and all that, but I would have expected that to work on the current record only (as obviously you did).

BTW if your code is exactly as posted, then you have some empty event procedures in there. They can safely be deleted. They are entered automatically by Access as a type of template for code when you select them. Sometimes you just select them to look and don't want to add code. In that case the empty shells are redundant.

Sorry I couldn't be more help.

[Rio Liaden]

If multiple text boxes on your form have the same control source that would cause what you described. A single form only shows one record at a time therefore regardless of the fact that you have multiple textboxes they are all looking at the same feild and record so when calendar or combos update one they all change. send me a screen shot of the design view of your form with a description of the underlying tables, I may be able to make a suggestion.

Here it is...[IMG]C:\Documents and Settings\blee\Desktop\Screen shot.doc[/IMG]

[MSeda]

That didn't display you can e-mail if you like.

[Rio Liaden]

That didn't display you can e-mail if you like.

Hopefully you have received the screen shots. Ignore the comment box that says the calendar is informational. I want it to be functional.

[NeoPa]

Some links don't work when first posted (many web links for instance).
I get around it by editing post immediately and saving with the 'Automatically parse links in text' option set.

[Rio Liaden]

Some links don't work when first posted (many web links for instance).
I get around it by editing post immediately and saving with the 'Automatically parse links in text' option set.

Thanks! I actually tried that but my shots were too large even after I zipped them. Is that common?

[MSeda]

Just to describe the form for others:
the form view is set to continuous forms so a user can enter multiple records into the time sheet. there is a calendar control in the form footer whose on click sets the work_date textbox in the detail section.

My previous comment of having multiple textboxes applied to a single form view. you have a continuous form view. The work_date text box is currently unbound.
so when calendar changes the date all work_dates in the detail section change since there is nothing to hold the value.
Once you set work_dates "control source" property to the field you want to store the date in, this will stop and the calendar will only reset the current records date.

[NeoPa]

I thought I saw star quality.
MSeda - a credit to these forums.

[Rio Liaden]

Just to describe the form for others:
the form view is set to continuous forms so a user can enter multiple records into the time sheet. there is a calendar control in the form footer whose on click sets the work_date textbox in the detail section.

My previous comment of having multiple textboxes applied to a single form view. you have a continuous form view. The work_date text box is currently unbound.
so when calendar changes the date all work_dates in the detail section change since there is nothing to hold the value.
Once you set work_dates "control source" property to the field you want to store the date in, this will stop and the calendar will only reset the current records date.

What is the WorkDate control source? I sort of understand, but I need to bind the text box to the control source and I'm not sure what the control source is. It is the calendar but I'm a bit confused.

[MSeda]

The Control Source is the field in the underlying table where the data is stored.
i.e. when you have a combo box named Task ID on your form when a user selects a task it saves there selection in the task ID column of the underlying table. if you open the properties menu for the task ID combo box on the data tab you will see "control source" says "task ID".
The Work Date text box on the form you sent me reads "Unbound" meaning it is not set to save the data anywhere. open the properties window for work date and set the control source to the appropriate feild in your table. you will find Control Source has a drop down list of all feilds in the source table or query. If you don't already have a date feild in the table you need to create one with a date/time data type before you can do this.

[Rio Liaden]

The Control Source is the field in the underlying table where the data is stored.
i.e. when you have a combo box named Task ID on your form when a user selects a task it saves there selection in the task ID column of the underlying table. if you open the properties menu for the task ID combo box on the data tab you will see "control source" says "task ID".
The Work Date text box on the form you sent me reads "Unbound" meaning it is not set to save the data anywhere. open the properties window for work date and set the control source to the appropriate feild in your table. you will find Control Source has a drop down list of all feilds in the source table or query. If you don't already have a date feild in the table you need to create one with a date/time data type before you can do this.

You know, MSeda, you are a credit to this forum. I needed a memory jog and you gave it to me. I have been so frustrated with other aspects of my job that I could not think. I have built many databases from scratch but unfortunately, I inherited this one. I did not go back to basics and therefore, sounded like an idiot on this forum. Forgive me. I did as you instructed, put in a WorkDate field (duh!) bound it and my form is doing exactly what I needed.

Thank you so much again and when my head settles back on my shoulders, believe it or not, I may one day be able to help you! Definetely not as much as you helped me, but hopefully a fraction!

Rio