Results 1 to 9 of 9
  1. #1
    phil210293 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    10

    Help required filtering records displayed on a picklist


    Hello AccessForums,

    I am creating a database to track inspections & recorded defects on assets. Frequently, defects will appear on multiple inspections, so to save creating new defect records, I have a join table between inspections and defects, so that one inspection can identify multiple defects and one defect may appear on multiple inspections. In order to "pick" the defects, I have a form with a list showing all of the defects from the previous inspection:
    Code:
    SELECT Max(ExtantDefectsAll.InspectionID) AS MaxOfInspectionID, DefectLog.Description, ExtantDefectsAll.DefectID, DefectLog.AssetID FROM (DefectLog INNER JOIN ExtantDefectsAll ON DefectLog.DefectID = ExtantDefectsAll.DefectID) LEFT JOIN InspectionDefectJoin ON DefectLog.DefectID = InspectionDefectJoin.DefectID GROUP BY DefectLog.Description, ExtantDefectsAll.DefectID, DefectLog.AssetID HAVING (((DefectLog.AssetID)=GetAssetID()));
    I have a double click action where this creates a new entry on the join table to associate this defect with the new inspection:
    Code:
    Private Sub List0_DblClick(Cancel As Integer)
    
        Dim StrSQL As String
        
        StrSQL = "INSERT INTO InspectionDefectJoin(InspectionID,DefectID) VALUES (GetInspectionID()," & Me.List0.Column(2) & ")"
            
        CurrentDb.Execute StrSQL, dbFailOnError
    
    
        Me.List0.Requery
    
    
        Me.List2.Requery
        
    End Sub
    I also have a second list that shows all the defects associated with the current inspection:
    Code:
    SELECT InspectionDefectJoin.InspectionID, DefectLog.Description, InspectionDefectJoin.DefectID, DefectLog.AssetID, InspectionDefectJoin.DefectJoinID FROM DefectLog LEFT JOIN InspectionDefectJoin ON DefectLog.DefectID = InspectionDefectJoin.DefectID GROUP BY InspectionDefectJoin.InspectionID, DefectLog.Description, InspectionDefectJoin.DefectID, DefectLog.AssetID, InspectionDefectJoin.DefectJoinID HAVING (((InspectionDefectJoin.InspectionID)=GetInspectionID()) AND ((DefectLog.AssetID)=GetAssetID()));
    Along with a double click action to remove this association:
    Code:
    Private Sub List2_DblClick(Cancel As Integer)
    
        Dim StrSQL As String
        Dim DeleteID As Long
        
        DeleteID = Me.List2.Column(4)
    
    
        StrSQL = "DELETE * from InspectionDefectJoin WHERE DefectJoinID = " & DeleteID
    
    
        CurrentDb.Execute StrSQL, dbFailOnError
    
    
        Me.List0.Requery
    
    
        Me.List2.Requery
    
    
    End Sub
    I need to create a condition where a row in the list showing defects on the previous inspection is hidden (without touching the record itself) once it has been double clicked to associate it with the new inspection; currently, the defect remains in place and permits the user to double click it multiple times, and create multiple records on the join table for the same defect and inspection.

    (The GetFIELD() functions are to supply numbers to the SQL statement from public variables in order to pass data from the "add inspection" form to this form)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    don't use multi select lists. Instead use the list to 'dbl-click' to add the item to a tPicked table.
    then just join that table to the main list and get only those items.
    Attached Thumbnails Attached Thumbnails pick state lbls.png  

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Change the row source of the list for the previous inspection to include the new inspection records and do an unmatched (left outer join towards the new inspection table and DefectID IS Null (from the new inspections side). Then simply requery the listbox after double-click.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    phil210293 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    10
    Thanks for replying Gicu,

    Does this mean I need to create a new table to temporarily store the records for the new inspection? I am currently populating the existing join table with new records, and the previous inspection defects list is populated using the ExtantDefectsAll query; I have tried using a separate table to hold the new inspection defects but my previous attempts have resulted in the list of previous defects not showing any records at all. I just want to requery the list to display extant defects from the previous inspection and exclude any record where the DefectID fields match with a defect that has been associated (double-clicked) over to the second list, much like the lists you get when choosing fields etc. on the wizards. My current method essentially works, aside from removing selections from the list of previous to prevent clone associations to the new inspection.

    I hope that makes sense; I am not particularly well acquainted with Access & databases, so any help is appreciated.

    Cheers,

    Phil

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Phil,

    No, you don't need a new table, use the existing join table filtered by the new inspection. Can you upload a small sample of your db with only the objects in question (remove any sensitive data, only need a couple records to show the issue)?

    Basically create a query that shows all the defects already associated with the new inspection (lets say you call it qryNewInspectionDefects) by saving the SQL statement that you already have as a query:

    Code:
    SELECT InspectionDefectJoin.InspectionID, DefectLog.Description, InspectionDefectJoin.DefectID, DefectLog.AssetID, InspectionDefectJoin.DefectJoinID FROM DefectLog LEFT JOIN InspectionDefectJoin ON DefectLog.DefectID = InspectionDefectJoin.DefectID GROUP BY InspectionDefectJoin.InspectionID, DefectLog.Description, InspectionDefectJoin.DefectID, DefectLog.AssetID, InspectionDefectJoin.DefectJoinID HAVING (((InspectionDefectJoin.InspectionID)=GetInspectionID()) AND ((DefectLog.AssetID)=GetAssetID()));
    Save the existing SQL statement for the previous inspection as a query (lets say you call it qryPreviousInspectionDefects):

    Code:
    SELECT Max(ExtantDefectsAll.InspectionID) AS MaxOfInspectionID, DefectLog.Description, ExtantDefectsAll.DefectID, DefectLog.AssetID FROM (DefectLog INNER JOIN ExtantDefectsAll ON DefectLog.DefectID = ExtantDefectsAll.DefectID) LEFT JOIN InspectionDefectJoin ON DefectLog.DefectID = InspectionDefectJoin.DefectID GROUP BY DefectLog.Description, ExtantDefectsAll.DefectID, DefectLog.AssetID HAVING (((DefectLog.AssetID)=GetAssetID()));
    Finally for the first list (previous inspection) row source use:

    Code:
    SELECT qryPreviousInspectionDefects.* FROM qryPreviousInspectionDefects LEFT JOIN qryNewInspectionDefects ON qryPreviousInspectionDefects.DefectID=qryNewInspectionDefects.DefectID  WHERE qryNewInspectionDefects.DefectID Is Null;
    After double click requery the list.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    phil210293 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    10
    Hi Vlad,

    I have attempted to implement your solution with no success, and I cannot upload a sample database due to the filesize limitation on here I will have another look at it in the week and let you know if I have any joy.

    Cheers,

    Phil

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Phil,

    You can import in a new Access file just the form in question along with the table(s) and queries needed to support it. Run a compact a repair then zip it and you should be able to upload it without any problems. Only need few records in the table to show the issue you are having.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    phil210293 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    10
    Vlad,

    Not sure what happened, I have attempted again and it is working!!

    Thanks very much

    Onto the next problem...

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Phil,
    Glad to hear you got it working!
    Good luck with your project!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-03-2020, 10:39 AM
  2. Replies: 4
    Last Post: 09-05-2015, 05:39 PM
  3. Replies: 12
    Last Post: 03-02-2015, 01:21 PM
  4. Replies: 10
    Last Post: 01-29-2013, 08:59 AM
  5. Replies: 11
    Last Post: 10-20-2011, 08:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums