Is there a way to filter which Form records are shown with Query results?-CodePudding

For background, I have 2 tables here. One is a JobTicket table with all of our required production info, and the other is a ScheduledItems table with all of our job ticket data, plus the dates we are supposed to run that Job. I am having no trouble getting all of the necessary data over there, my issue comes when I would like to do the opposite. I have created a Query to search for the Sales Order Number from both tables, and then set the criteria for Scheduled Items to null so it will only show the unscheduled Jobs. Query works great, shows me all of the jobs that have not been Scheduled yet, but for the life of me I cannot figure out the best way to go about filtering my Form JobTicket to show only those unscheduled Jobs. I don't want to just give my coworker those Query results and tell him to search Jobs one by one; some days we can get 25 Jobs with 10 Line Items, so the extreme end we would be looking at around 250 manual searches a day.

If anyone has dealt with this in the past and has any sort of jumping off point or recommendation for my methodology I would very sincerely appreciate it. Thanks all

the acess query

SELECT 
    Qry_JobTicket.Sales_Order_Number,      
    Qry_ScheduledItems.Sales_Order_Number   
FROM Qry_JobTicket 
LEFT JOIN Qry_ScheduledItems 
ON    Qry_JobTicket.Sales_Order_Number = Qry_ScheduledItems.Sales_Order_Number   
WHERE (((Qry_ScheduledItems.Sales_Order_Number) Is Null));

CodePudding user response：

Okay I have figured out how to go about it, all though I am sure it's not the best in the way of design principles. Here goes:

After creating the Query (SQL shown above, Qry_UnscheduledList) to find all of the Un-Scheduled Jobs, I created another query that brought in my Qry_JobTicket and my Qry_UnscheduledList. I inner joined on Sales Order Number, so we only see the Jobs yet to be scheduled, and brought in every field from Qry_JobTicket. Once that was done, I then created a button on my form and inserted the code below to change the Form's Record Source, similarly to as @HansUp suggested with the FormFilter property.

Private Sub Btn_ToBeScheduled_Click()

    DoCmd.OpenForm "Frm_JobTicket"
    With Forms("Frm_JobTicket")
        If .RecordSource = "Qry_JobTicket" Then
        .RecordSource = "Qry_ToBeScheduled"
        ElseIf .RecordSource = "Qry_ToBeScheduled" Then
        .RecordSource = "Qry_JobTicket"
            End If
            End With

End Sub

Now as I said earlier this may not be the best designing, but I was doing research for about an hour and couldn't find very many people who needed to filter their FormA records based off of corresponding records that don't exist in their FormB.