RecordSET under the YEAR and Month. (1 Viewer)

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
Any help and advice will be appreciated.
Before my payroll system work another processing. I try to modify/update payroll system. And finding out in the google to solving this issue, But not success.
Untitled-1.jpg

Attached, first form is the main Form. In this form if i selected year from the combo box, then selected year is show in the second Form corner. and select month name from combo box.
I try to RecordSet save/update in under the Selected Year and Month.

Q: what I should put code in the SUBMIT button for show in the SECOND form selected year ?
Q: how to processing record set save to under the Selected Year and Month.
Attached also relationship table.

Note:
Below subform work well. before help Mr. Mike Smart,
 

Attachments

  • Capture.PNG
    Capture.PNG
    34.5 KB · Views: 176

Minty

AWF VIP
Local time
Today, 06:02
Joined
Jul 26, 2013
Messages
10,355
You should simply have a Payroll Date and default it to the first of the month.
You can then easily use date part functions to get all the records you need.

In isolation, your separate fields make this a lot harder and mean sorting and grouping records over periods will be harder and less intuitive.
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
You should simply have a Payroll Date and default it to the first of the month.
You can then easily use date part functions to get all the records you need.

In isolation, your separate fields make this a lot harder and mean sorting and grouping records over periods will be harder and less intuitive.
Thank you so much .
Its I have another table.
PayrollBegDate, PayrollEndDate, PayrollRelease, NetSalary.
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
Why I try to second form. Because many time I wrong entry for forget selecting Current Year. Then I have this Idea to separating.
 

Minty

AWF VIP
Local time
Today, 06:02
Joined
Jul 26, 2013
Messages
10,355
If that is an issue simply set the form to default to the current year, it forces the selecting of a different one?
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
If that is an issue simply set the form to default to the current year, it forces the selecting of a different one?
Any you suggest that my YEAR COMBO box put default current year. And cancel FIRST form. Not bad Idea. Okay.
Code:
=Year(Date,Now())
Where is the best for this code Current event or put in the Expression > default value.
 

Minty

AWF VIP
Local time
Today, 06:02
Joined
Jul 26, 2013
Messages
10,355
Default value only works for new records, so instead set the combo value in the form's load event.
I would only have one form with both combo's on and default the year one to this year.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:02
Joined
Feb 19, 2002
Messages
42,981
Please don't forget to create relationships among the other tables. Also, The Payroll table looks wrong. It should not duplicate data that is in other tables. I can't figure out if it is supposed to be a summary of all employee data for the period or for ONE employee. If it is for one employee, it needs the EmployeeID.
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
Thank you so much for your advice. Yes, I have add all of table with EmployeeID (FK) and Payroll form Parent (txtEmployeeID) and subform Child Form (EmployeeID) to connection.
but when I do this. then how to connection or add YEAR and Month as like my combo box cboYear and cboMonth
Capture.PNG

For that I like to two form seperated.

Hope someone give a solution?
 

Minty

AWF VIP
Local time
Today, 06:02
Joined
Jul 26, 2013
Messages
10,355
Simply build a filter or set the recordset for the (sub)form records based on the combo box values?

I'm not sure what you need help with.
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
Simply build a filter or set the recordset for the (sub)form records based on the combo box values?
I thing, you don't understand me what I need or I cant explain you well.
can you check my relationship image. Its possible to record set with based in two combo box.

because, I have auto default value Two combo box and Subform (Child form) connection with PK - EmployeeID.
How I set record based combo box ? can explain me please... I don't understand it
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
Simply build a filter or set the recordset for the (sub)form records based on the combo box values?

I'm not sure what you need help with.
Looks, msg #9 this Main form is recordsource from tblEmployee.
then each (5 subforms) subform LINK with employeeID (See msg # 1)
so, how to Link with YEAR and MONTH... this is I dont understand.
I am sorry, explain me last line please.
 

Minty

AWF VIP
Local time
Today, 06:02
Joined
Jul 26, 2013
Messages
10,355
I personally wouldn't use a link, but if you need to include it in the master child link properties something like

EmpID; cmbYear; cmbMonth

I think this is really awkward when you can simply filter the subforms to the dates in the form header.
To keep things simple, when you set a date in the header get the first and last dates of that month into two hidden text boxes.
Use those dates in your subforms underlying queries to restrict what you see.
That is why you should use a date rather than separate fields for Year and Month.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:02
Joined
May 7, 2009
Messages
19,169
maybe its time for some sample db to show.
upload one so we can resolve this the soonest.
 

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
I personally wouldn't use a link, but if you need to include it in the master child link properties something like

EmpID; cmbYear; cmbMonth

I think this is really awkward when you can simply filter the subforms to the dates in the form header.
To keep things simple, when you set a date in the header get the first and last dates of that month into two hidden text boxes.
Use those dates in your subforms underlying queries to restrict what you see.
That is why you should use a date rather than separate fields for Year and Month.
So, Subform all of the table need added two row YEAR and MONTH.
this is before I try Version1 but have problem when create Query for Salary Slip and report printing, then coming duplicated rows.

Because, have a query with on EmployeeID, YEAR, MONTH have a query for Net salary sending to Bank.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:02
Joined
May 7, 2009
Messages
19,169
i only touch payrollmainform->salary detail tab.
i added tbPayHdr and additional field to EmployeeSalary.
see the code on this form/subform.
 

Attachments

  • PayRoll System V3.accdb
    1.6 MB · Views: 192

smtazulislam

Member
Local time
Today, 09:02
Joined
Mar 27, 2020
Messages
806
i only touch payrollmainform->salary detail tab.
i added tbPayHdr and additional field to EmployeeSalary.
see the code on this form/subform.
Thank you for your reply. I will check and give you feedback as sooner...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:02
Joined
May 7, 2009
Messages
19,169

Users who are viewing this thread

Top Bottom