Concept Help on Filtering a Form. (1 Viewer)

[Darrenc]

Hi, i need some help with filtering a form. I've read many posts on the subject, and for some reason (mainly my lack of understanding ) i'm having a problem finding a suitable solution.

If someone could help me understand the best way/method of achieving my aim then i would be very grateful.

I'm trying to filter a form based off a query, the query holds some of the unique ID numbers of the form i'm trying to filter.

I have a button that shows the number of records based off this particular query. I want the user to be able to click this button and then i want the main form to be filtered based of the ID numbers that are held in the query.

I've looked at a few things and read a few posts, but for some reason i can't seem to get my head around what the best way is to achieve this?

I've already done some quite complex things with my current database project, and i find it very frustrating that i can't get my head round this basic problem.

If anyone could give me some guidance with this i would be very grateful.

Thanks.

Darren.

 

[ajetrumpet]

I have a button that shows the number of records based off this particular query. I want the user to be able to click this button and then i want the main form to be filtered based of the ID numbers that are held in the query.

I would use controls and user-entered values to specify criteria for the filtered records. If the recordsource of the form right now is not the query, you'd have to change it.

 

[Darrenc]

Thanks for the reply ajetrumpet.

The recordsource of the form is a table, i know i should really base forms on queries, for some reason i didn't in this case.

I've attached a screenshot, i think a picture will explain better than my words.

The results of the query that make up the numbers used for the buttons are dynamic (they are updated every 40 seconds)

The part circled are the buttons that are based off the query, and the same query also holds the main ID number (incident ID). So i want the user to click the button, and then the main form to only show the relevant records.

 

[ajetrumpet]

I would say use 3 different queries for it. But I can't say for sure, nor do I want to without looking at the query statement you're currently using. Possible to post it??

 

[Darrenc]

I'm back, and i'm still struggling!

I've now based my mainform on a query. And i thought i would just change the recordsource of the mainform based on a new recordsource depending on which the button the user clicks.

Now my next problem is that the recordset is not updateable. I've read a few posts on this problem, and its turns out if you try and use a recordsource made up of to many queries or queries with bad joins then you will get this problem.

I've also read that a workaround to this is to set the recordset type to Dynaset (Inconsistent Updates), alas this didn't work.

The query i'm using to filter 'Outstanding', 'Overdue' and 'On Hold' records is based on a Union query that is made up from 4 different tables.

SELECT tblCustomerCredit.CreditIncidentID, tblCustomerCredit.CreditCompleteBy, tblCustomerCredit.CreditAssignedTo, tblCustomerCredit.CreditOnHold, tblCustomerCredit.CreditID, tblCustomerCredit.CreditAssignedDate, tblCustomerCredit.CreditExtend
FROM tblCustomerCredit
WHERE (((tblCustomerCredit.CreditCompleteBy) Is Null))

UNION SELECT tblInternalErrorTable.IntIncidentID, tblInternalErrorTable.IntCompletedBy, tblInternalErrorTable.IntAssignedTo, tblInternalErrorTable.IntOnHold, tblInternalErrorTable.InternalID, tblInternalErrorTable.IntDateAssigned, tblInternalErrorTable.IntExtend
FROM tblInternalErrorTable
WHERE (((tblInternalErrorTable.IntCompletedBy) Is Null))

UNION SELECT tblCustomerRMA.CustRMAIncidentID, tblCustomerRMA.CustRMAActBy, tblCustomerRMA.CustAssignedTo, tblCustomerRMA.CustRMAOnHold, tblCustomerRMA.CustRMAID, tblCustomerRMA.CustAssignedDate, tblCustomerRMA.CustRMAExtend
FROM tblCustomerRMA
WHERE (((tblCustomerRMA.CustRMAActBy) Is Null))

UNION SELECT tblSupplierQuerys.SupIncidentID, tblSupplierQuerys.SupQueryCompBy, tblSupplierQuerys.SupWhoAssigned, tblSupplierQuerys.SupOnHold, tblSupplierQuerys.SupplierID, tblSupplierQuerys.SupWhenAssigned, tblSupplierQuerys.SupExtend
FROM tblSupplierQuerys
WHERE (((tblSupplierQuerys.SupQueryCompBy) Is Null));

This query holds all the information i need to hopefully filter the mainform in a variety of ways. I just don't know how best to use this data to filter my mainform.

If someone could give me some advise, or even tell me i've gone around this in completely the wrong way, then i'll be more than happy to listen.

Thanks

Darren.

 

[Darrenc]

I'm going to bump this just once.

I didn't think i was aking for something that complicated?