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_Before Update(Cancel As Integer)

End Sub

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

'Private Sub Label27_Click()

'DoCmd.OpenRepo rt "rptTimesheetAA RDEXNew", acPreview, "qryTimesheetSi ngleScreen"
'End Sub

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

Private Sub Label48_Click()
On Error GoTo Err_Command49_C lick


DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command49_ Click:
Exit Sub

Err_Command49_C lick:
MsgBox Err.Description
Resume Exit_Command49_ Click
End Sub

Private Sub Project_BeforeU pdate(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]

Adding a date field to db

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_GotFoc us()

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.