Filtering a combo box question.

[JGalletta]

I've seen and read posts regarding filtering of combo boxes, but haven't seen one about filtering based on prior entries of that combo box... Let me explain:

I have a subform in continuous view on my main form. The combo box in question is in the subform. I'd like to be able to eliminate values chosen from the combo box as they are chosen. i.e. Record 1 chooses option 1, Record 2 cannot choose option 1.

How can I filter this combo box base on which selection have been made on previous records in this subform (but only on the current record of the main form)? I have primary keys and joining primary keys set up, and would use these as criteria for the filters, I really just need to know how to format the filters and where, I guess.

Thanks,
John

 

[John Big Booty]

Cascading Combo boxes on a continuous form are rather tricky beasts. You will find one solution here in the samples section. However I don't think that is what you are looking for. What I have done in the past for similar situations is to have the subform populated by a query that is able to pull all the information together for display. This subform is then populated by a pop-up form that contains the combo which is filtered to exclude all previous selections for that master record.

 

[JGalletta]

Yeah, that example doesn't quite cut it for me. I think you understand the gist of what I am trying to do though. Perhaps I can run some append/delete queries on a separate table which would be used solely to populate the drop downs, but I know I'll be running into issues when a value needs to be changed - adding the past value, deleting the current value from the population table. Does this idea seem like it's worth pursuing?

 

[JGalletta]

Here is what I've been thinking about, and fooling around with:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblFieldListPopulation.[Field Number] FROM tblFieldListPopulation" 'To clear list.
DoCmd.RunSQL "INSERT INTO tblFieldListPopulation ( [Field Number] ) SELECT tblFieldInformation.[Field Number] FROM tblFieldInformation" 'To populate a FULL list
DoCmd.RunSQL "DELETE tblFieldListDeletion.[Field Number] FROM tblFieldListDeletion" 'To clear deletion list.
DoCmd.RunSQL "INSERT INTO tblFieldListDeletion ( [Field Number] ) SELECT tblFieldRecJoin.[Field Number] FROM tblFieldRecJoin WHERE (((tblFieldRecJoin.[Record Number])=[Forms]![frmScoutingRecords]![Record Number]))" 'To add selected fields to list for deletion
DoCmd.RunSQL "DELETE tblFieldListPopulation.[Field Number] FROM tblFieldListPopulation Where (((tblFieldListDeletion.[Field Number]) = tblFieldListPopulation.[Field Number]))" 'To delete list for deletion from population list (this line does not function properly)
DoCmd.SetWarnings True
Me.FieldNo.Requery

I just need to work on the syntax of the last DELETE query. Can anyone help me with this? I'm attempting to delete the fields in tblFieldListDeletion from tblFieldListPopulation and it keeps prompting me for a value at runtime.

 

[John Big Booty]

Sorry for the delay, I haven't had a chance to sit down and put together a sample.

The attached is How I would handle what you are looking to do.

 

[JGalletta]

This is what I ended up coming up with:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblFieldListPopulation.[Field Number] FROM tblFieldListPopulation"
DoCmd.RunSQL "INSERT INTO tblFieldListPopulation ( [Field Number] ) SELECT tblFieldInformation.[Field Number] FROM tblFieldInformation"
DoCmd.RunSQL "DELETE tblFieldListDeletion.[Field Number] FROM tblFieldListDeletion"
DoCmd.RunSQL "INSERT INTO tblFieldListDeletion ( [Field Number] ) SELECT tblFieldRecJoin.[Field Number] FROM tblFieldRecJoin WHERE (((tblFieldRecJoin.[Record Number])=[Forms]![frmScoutingRecords]![Record Number]))"
    If CurrentDb.TableDefs("tblFieldListDeletion").RecordCount > 0 Then
        Dim MySQL As String
        MySQL = "DELETE tblFieldListPopulation.[Field Number] FROM tblFieldListPopulation Where tblFieldListPopulation.[Field Number] IN ("
        Dim r As DAO.Recordset
        Set r = CurrentDb.OpenRecordset("Select * From tblFieldListDeletion")
        
            r.MoveFirst
            Do Until r.EOF = True
                MySQL = MySQL & "'" & r![Field Number] & "',"
                r.MoveNext
            Loop

        r.Close
        Set r = Nothing
        MySQL = Left(MySQL, (Len(MySQL) - 1)) & ")"
        DoCmd.RunSQL MySQL
    End If
DoCmd.SetWarnings True
Me.FieldNo.Requery

This runs on the current event of the form in question and works quite well without the need for an additional form. I am trying to optimize things though, and this does run quite slow. However, speed is not that big of an issue for me. I have a bigger issue with one of my subforms running its Form_current() event twice...