Solved How to filtering by List box as like cascading combo box

[smtazulislam]

I have a form screenshot attached.
Firstly,
If I select Year and Month need to Append Data from two tables tblEmployee & tblEmployee1 with YEAR and MONTH,
Criteria from two tables LIKE EmployeeStatus="Active" & "Not Appending Duplicate Data not Match within Month and Year"

These data display a form CALLED "frmTESTPayList" when you are CLICK CONFIRM BUTTON then insert data into subform "sfrmPayRollData" see attached subform

Secondly,
Filtering By List box ("lstCompRef", "lstWorkCategory", "lstWorkLocation", "lstPageNo" ) and also requery in the subform.
lstCompRef most be select,
Others all optional : If select then filter If not Nothing to do.

How to do it.
Any help will appreciate

 

[June7]

Exactly what is the issue? How to insert? How to filter?

I would use VBA to build filter criteria. Review http://allenbrowne.com/ser-62.html

 

[Pat Hartman]

insert data into subform

Forms don't store data. Tables store data.

 

[smtazulislam]

Exactly what is the issue? How to insert? How to filter?

I would use VBA to build filter criteria. Review http://allenbrowne.com/ser-62.html

Firstly,
I need an append query that selected Year and selected Month with two tables tblEmployee & tblEmployee1
append query condition, two tables have "[employee Status]" Field took if [Employee Status]="Active" & "Duplicate Data Match within Month and Year"

Its create QueryDef temp stored data Insert into subform "sfrmPayRollData" see attached Post #1

EDIT :
did you see there is employee TWO tables. 1) Employee field Name is EmployeeID & another EmployeeID1.
But Starting different Numbers. Kindly see attached Post #1 subform data.

 

[smtazulislam]

How to filter?

Filtering By List box "lstCompRef" most select
"lstWorkCategory", "lstWorkLocation", "lstPageNo" In this 3 List Boxes is optional. User can select anyone OR Select All of List boxes by Items Selected Multi or Single.

 

[June7]

I cannot make sense of your append requirement.

As for the filtering, did you review posted link?

 

[smtazulislam]

I cannot make sense of your append requirement.

Append I create it. Need to only Condition that take only "StatusID = 1"
And Year and Month Not duplicate.

As for the filtering, did you review posted link?

I seem, I would like to Multi List Box.
I upload my dB.

 

[smtazulislam]

any help

 

[June7]

The link I provided includes a link to another tutorial on how to use multi-select listbox. It's in the section:

Trouble-shooting and extending​

So read the article thoroughly.

 

[smtazulislam]

The link I provided includes a link to another tutorial on how to use multi-select listbox. It's in the section:

Trouble-shooting and extending​

So read the article thoroughly.

Mrs. June, Thanks for your Link and reply.
You dont understand me or Maybe i can't able to explain you well. That I upload my dB.
I read this article, There debug all SELECTED combo boxes or text or List boxes with one Command button "Filter".
But its not my inquire. I would like EACH LIST BOX click to requery next list boxes,

I want to do

1. If I click First List Box then REQUERY under the data in second/Third/ Fourth List Box and also Filter data in the subform.
2. If I click Second List Box then REQUERY under the data in third/Fourth List Box and Filter data in the subform. .
3. If I Click First List Box and LEAVE SECOND/THIRD then Fourth List box also REQUERY and Filter data in the subform.

Kindly review post #7 dB update now.

EDIT :
As my inquire, If you are provide me FIRST two list boxes code example. I will try to others list boxes.

 

[arnelgp]

here check your filter.

 

[June7]

Then you did not open the code sample in AllenBrowne's tutorial, you viewed only the query option. The VBA code includes control in filter criteria only if there is a selection made. That's what If Then structures accomplish.

I expect Arnel's sample db incorporates same sort of code.

 

[smtazulislam]

here check your filter.

Thank you very much for response my pm and really you are great and good heart people. I see, you have been work in my lots of the projects. I appreciate.

One more question :
Please again see my post #1
Here was two combo boxes Select Year = cboYear and Select Month = cboMonth
I want to adding Year and Month with my Append query. I tried it

INSERT INTO tblPayRollData ( EmployeeID, EmployeeName, CompanyRef, WorkCategory, DeptID, PStatusID, CityID )
SELECT qryEmployeeExtended.EmployeeID, qryEmployeeExtended.EmployeeName, qryEmployeeExtended.CompanyRef, qryEmployeeExtended.ContractType, qryEmployeeExtended.DeptID, qryEmployeeExtended.StatusID, qryEmployeeExtended.CityID
FROM qryEmployeeExtended
WHERE (((qryEmployeeExtended.StatusID)=1));

Its works. In the above this query if I added Year N Month column then Its create so many duplicate rows. I guess, its happened because, there was 3 years add in the table they taken all and combine with month Name.

SEE THE SECOND QUERY WITH MONTH AND YEAR.

INSERT INTO tblPayRollData ( EmployeeID, EmployeeName, CompanyRef, WorkCategory, DeptID, PStatusID, CityID, [Year], [Month] )
SELECT qryEmployeeExtended.EmployeeID, qryEmployeeExtended.EmployeeName, qryEmployeeExtended.CompanyRef, qryEmployeeExtended.ContractType, qryEmployeeExtended.DeptID, qryEmployeeExtended.StatusID, qryEmployeeExtended.CityID, tblYears.Year, tblMonths.Month
FROM qryEmployeeExtended, tblYears, tblMonths
WHERE (((qryEmployeeExtended.StatusID)=1));

for that, I was create two combo boxes (post #1). I guess, its need code in the append button, But no ideas how to call under the query with SELECTED YEAR and SELECTED MONTH only appending.

Private Sub btnAppendQuery_Click()
    DoCmd.OpenQuery "qryAppendPayList", acViewNormal, acEdit
    MsgBox "Your append query record has been successfully saved,"
    'Docmd.OpenForm "frmPayListEdit", acNormal
End Sub

 

[arnelgp]

i will look into it and see what i can do.
in the meantime, i have to attend to some important task.

 

[smtazulislam]

i will look into it and see what i can do.
in the meantime, i have to attend to some important task.

Its Okay Sir. Take your time

 

[arnelgp]

unfortunately the db you posted is not the one in post #1.

 

[smtazulislam]

Uploaded

 

[arnelgp]

i added 2026 on your year table just to test.
already jan 2026 is in tblPayrollData so you need to test the rest of the months for 2026.

 

[smtazulislam]

i added 2026 on your year table just to test.
already jan 2026 is in tblPayrollData so you need to test the rest of the months for 2026.

Excellent ! its works. Thank you very much.

 

[arnelgp]

you need to look at the index (Unique) of table tblPayRollData on design view.
this index will prevent duplicate when adding record of same employee, month, and year.