Not in List

[RuralGuy]

There is a lot to be said for "it works as I want". Thanks.

[Mclaren]

should i elaborate more ? Not really sure what you mean ?

[goodguy]

To cut a long story short, I set this code on your combo's NotInList event to filter the combo row source directly without the need for opening a search form etc:

Private Sub cbo_UPNCode_NotInList(NewData As String, Response As Integer)
    Dim str As String
    str = NewData
    cbo_UPNCode.Undo
    cbo_UPNCode.RowSource = "SELECT ProductID, UPN FROM tbl_Product WHERE UPN LIKE ""*" & NewData & "*"" ORDER BY tbl_Product.UPN"
    cbo_UPNCode.Requery
End Sub

I also set the combo After Update event to remove the filter:

Private Sub cbo_UPNCode_AfterUpdate()
    cbo_UPNCode.RowSource = "SELECT tbl_Product.ProductID, tbl_Product.UPN FROM tbl_Product ORDER BY tbl_Product.UPN "
    cbo_UPNCode.Requery
End Sub

I tested it and the subform filters the combo and saves the records OK.
Hope this solves your problem?

[RuralGuy]

Just an FYI, I'm pretty sure any time you change a RowSource or RecordSource Access will do the Requery for you.

[goodguy]

Actually, I thought so too, but when I found blanks in my dropdown, so I added it.

[Mclaren]

Hmmmm, will try this over the weekend, this might make things a lot simpler.

[RuralGuy]

Actually, I thought so too, but when I found blanks in my dropdown, so I added it.

Interesting. Thank you for the information. I stand corrected.

[goodguy]

You are both welcome. We learn more when we help/teach.

[Mclaren]

Works a treat. added "cbo_UPNCode.Dropdown" to the not in list event. thus ensuring the user sees that there is a problem or options to choose from.

Now just need to supress the default warning message of the not in list trigger.

[goodguy]

Let me know when you are done!

[goodguy]

In case you are still searching:

Dim str As String
    DoCmd.SetWarnings False
    str = NewData
    cbo_UPNCode.Undo
    cbo_UPNCode.RowSource = "SELECT ProductID, UPN FROM tbl_Product WHERE UPN LIKE ""*" & NewData & "*"" ORDER BY tbl_Product.UPN"
    Response = acDataErrContinue
    'cbo_UPNCode.Requery
    cbo_UPNCode.Dropdown
    DoCmd.SetWarnings True

works perfectly.

[Mclaren]

Not Searching, Already done, just didn't get around to posting yet.

Thanks all for the help.

[goodguy]

You are welcome, buddy.