Quering a filtered listbox displaying the filtered list for further

[MKH2001]

What I have is a list box based on queries Choose and Chosen

And function button that will pull accross information from the "Choose (aka choose from) list" in the Chosen list

Option Compare Database
Option Explicit

Private Sub AddOneBcc_Click()
    MoveToChosen ("Bcc")
End Sub
Private Function MoveToChosen(GROUPID)
On Error GoTo Err_MoveToChosen

Dim MyDB As DAO.Database
Dim Choose As DAO.Recordset
Dim Chosen As DAO.Recordset
Dim Criteria As String

Set MyDB = CurrentDb()
Set Chosen = MyDB.OpenRecordset("qry" & GROUPID & "Chosen", DB_OPEN_DYNASET)
Set Choose = MyDB.OpenRecordset("qry" & GROUPID & "Choose", DB_OPEN_DYNASET)

Select Case GROUPID
Case "Bcc"
    Criteria = "[GROUPID] = " & "'" & BccChoose & "'"
End Select

If Not Choose.BOF Then
    Choose.MoveLast
    Choose.FindFirst Criteria
    Chosen.AddNew
    
    Select Case GROUPID
    Case "Bcc"
        Chosen!GROUPID = BccChoose
    End Select

    Chosen.Update
    If Choose.NoMatch = False Then
       Choose.Delete
    End If
    
    If Choose.RecordCount > 0 Then
        Choose.MoveNext
        If Choose.EOF Then
            Choose.MovePrevious
        End If
        
        Select Case GROUPID
        Case "Bcc"
            BccChoose = Choose!GROUPID
        End Select
        
    End If
End If

Chosen.MoveFirst
 Select Case GROUPID
 Case "Bcc"
    BccChosen = Chosen!GROUPID
    BccChoose.Requery
    BccChosen.Requery
End Select

Exit_MoveToChosen:
    Exit Function
    
Err_MoveToChosen:
    MsgBox Err.Description
    Resume Next

End Function

Private Sub MoveAllBcc_Click()
    MoveAll ("Bcc")
End Sub
Private Function MoveAll(GROUPID)
On Error GoTo Err_MoveAll

Dim MyDB As DAO.Database
Dim Choose As DAO.Recordset
Dim Chosen As DAO.Recordset

Set MyDB = CurrentDb()
Set Chosen = MyDB.OpenRecordset("qry" & GROUPID & "Chosen", DB_OPEN_DYNASET)
Set Choose = MyDB.OpenRecordset("qry" & GROUPID & "Choose", DB_OPEN_DYNASET)

Choose.MoveFirst
    Do
        Chosen.AddNew
        Chosen!GROUPID = Choose!GROUPID
        Chosen.Update
        Choose.MoveNext
    Loop Until Choose.EOF
    
    If Not Choose.BOF Then
        Choose.MoveFirst
        Do
            Choose.Delete
            Choose.MoveNext
        Loop Until Choose.EOF
    End If
    
    Chosen.MoveFirst
    Select Case GROUPID
    Case "Bcc"
        BccChosen = Chosen!GROUPID
        BccChoose.Requery
        BccChosen.Requery
    End Select
    
MoveAll:
    Exit Function
Err_MoveAll:
    MsgBox Err.Description
    Resume MoveAll
End Function
Private Function RemoveAll(GROUPID)
On Error GoTo Err_RemoveAll

Dim MyDB As Database
Dim Choose As DAO.Recordset
Dim Chosen As DAO.Recordset
Dim ChooseFrom As DAO.Recordset

Set MyDB = CurrentDb()
Set Chosen = MyDB.OpenRecordset("qry" & GROUPID & "Chosen", DB_OPEN_DYNASET)
Set Choose = MyDB.OpenRecordset("qry" & GROUPID & "Choose", DB_OPEN_DYNASET)
Set ChooseFrom = MyDB.OpenRecordset("qryFill" & GROUPID & "Choose", DB_OPEN_DYNASET)
If Not Choose.BOF Then
    Choose.MoveFirst
    Do
        Choose.Delete
        Choose.MoveNext
    Loop Until Choose.EOF
End If

If Not Chosen.BOF Then
    Chosen.MoveFirst
    Do
        Chosen.Delete
        Chosen.MoveNext
    Loop Until Chosen.EOF
End If

ChooseFrom.MoveFirst
    Do
        Choose.AddNew
        Choose!GROUPID = ChooseFrom!GROUPID
        Choose.Update
        ChooseFrom.MoveNext
    Loop Until ChooseFrom.EOF
        
Choose.MoveFirst

Select Case GROUPID
Case "Bcc"
    BccChoose = Choose!GROUPID
    BccChoose.Requery
    BccChosen.Requery

End Select

RemoveAll:
    Exit Function
    
Err_RemoveAll:
    MsgBox Err.Description
    Resume RemoveAll
    
End Function

Private Sub RemoveAllBcc_Click()
    RemoveAll ("Bcc")
End Sub
Private Sub RemoveOneBcc_Click()
    RemoveOne ("Bcc")
End Sub
Private Function RemoveOne(GROUPID)
On Error GoTo Err_RemoveOne

Dim MyDB As Database
Dim Choose As DAO.Recordset
Dim Chosen As DAO.Recordset
Dim Criteria As String

Set MyDB = CurrentDb()
Set Chosen = MyDB.OpenRecordset("qry" & GROUPID & "Chosen", DB_OPEN_DYNASET)
Set Choose = MyDB.OpenRecordset("qry" & GROUPID & "Choose", DB_OPEN_DYNASET)

Select Case GROUPID
Case "Bcc"
    Criteria = "[GROUPID] = " & "'" & BccChosen & "'"
End Select

If Not Chosen.BOF Then
    Chosen.MoveLast
    Chosen.FindFirst Criteria
    Choose.AddNew
    
    Select Case GROUPID
    Case "Bcc"
        Choose!GROUPID = BccChosen
    End Select
    Choose.Update
    If Chosen.NoMatch = False Then
        Chosen.Delete
    End If
    
    If Chosen.RecordCount > 0 Then
        Chosen.MoveNext
        If Chosen.EOF Then
            Chosen.MovePrevious
        End If
        
        Select Case GROUPID
        Case "Bcc"
            BccChosen = Chosen!GROUPID
        End Select
        End If
        
    End If
    
    Select Case GROUPID
    Case "Bcc"
        BccChoose.Requery
        BccChosen.Requery
    End Select
    
Exit_RemoveOne:
    Exit Function

Err_RemoveOne:
    MsgBox Err.Description
    Resume Exit_RemoveOne
End Function

What I then want is to run a query which queries the "chosen list" and add another value from a seperate table. This I have done as a make table query.

I then want to show these other values for selection in a similar "Choose (aka choose from) and Chosen list.

So that the end result is that I can select from the BCC codes and then all the "other value" and run a final query by that.

EG Out of the 70odd BCC routes i can see that there are say 3 Scout routes associated against it but i can run a query based on 1 (or more BCC route)from the chosen list which also have 1 (or more) selected Scout routes (from a chosen list)associated with them.

My current attempts are resulting in locked tables and read only queries that cant be added or deleted from.

I know this may seem confusing but hopefully it is enough to get the gist of what I am attempting across?

HELP! Please

 

[MKH2001]

Have got this finally sussed.... result is too long winded to post but if any has a reason to require I could possibly send them an example.