Check if subform record exists before update / after update?

[misscrf]

I have a main form for entering new projects (cases). There is a subform that has a key table as it's control source.

The key subform is for linking a case to Assets (or media received for that project). There could be a CD, DVD, etc. Each of these assets can belong to multiple cases, which is why the subform is a key table. There is a button on the subform to enter new assets in a pop-up form.

Anyway, I need to make sure people don't enter the same asset for one case multiple times. The subform is continuous with a combo FK to list the assets that exist. I thought of setting the row source of the combo box to filter against the key table where the case FK isn't = to the PK case key on the main form.

This doesn't work because then the requery of the combo makes the previous records on the subform go away. It filters them out.

So now, I am figuring I should use VBA to check and see if the record chosen is already being used. If it is, then show a message box telling the person, and undo the selection in the combo and requery it. Make sense?

So I started researching and found I can do a sql string of my query, but then I need to use a recordset (rst). I am not too familiar with these, so I am bombing out on getting it to work.

This is what I have. Please keep in mind that I know my rst stuff doesn't make sense lol.

Private Sub FKAsset_AfterUpdate()
Dim AssetTag As String
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tblAsset.PKAssetID " & _
"FROM tblAsset LEFT JOIN tblAssetType ON tblAsset.FKAssetType = tblAssetType.PKAssetType " & _
"WHERE (((tblAsset.PKAssetID) IN " & _
"(SELECT [tblKeyHBCaseAssets]![FKAsset]  " & _
"FROM tblKeyHBCaseAssets WHERE Forms![frmHBCase]![PKHBCaseID]  = [tblKeyHBCaseAssets]![FKHBCase])));"

Set rst = CurrentDb.OpenRecordset(strSQL, doOpenSnapshot)
rst.FindFirst strSQL
If Not rst.NoMatch Then
   
    MsgBox "This Asset already exists in this case.", vbCritical, "Asset May Only Exist Once in a Given Case"
    Me.FKAsset.Undo
    Me.FKAsset.Requery

Else
 [do my other code that works to save record and generate a tag for the new case asset]

End If

when I run this, I get a run-time error '3001' Invalid argument.

I debug and it highlights this:

Set rst = CurrentDb.OpenRecordset(strSQL, doOpenSnapshot)

I can put the strSQL in the immediate window and it does run. I also put it into a query and get results (if I have the form running.)

I also thought of putting the evaluation in the before update instead:

Private Sub FKAsset_BeforeUpdate(Cancel As Integer)
Dim db As Database
Dim rs As ADODB.Recordset
Dim qryCount As QueryDef
Dim intCount As Integer
Dim strSQL As String

strSQL = "SELECT tblAsset.PKAssetID " & _
"FROM tblAsset LEFT JOIN tblAssetType ON tblAsset.FKAssetType = tblAssetType.PKAssetType " & _
"WHERE (((tblAsset.PKAssetID) IN " & _
"(SELECT [tblKeyHBCaseAssets]![FKAsset]  " & _
"FROM tblKeyHBCaseAssets WHERE Forms![frmHBCase]![PKHBCaseID]  = [tblKeyHBCaseAssets]![FKHBCase])));"


Set db = Application.CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynamic, dbSeeChanges, dbOptimistic)

If rs.RecordCount <> 0 Then
    MsgBox "Asset already exists in this case.  An Asset may only be assigned to a case once"
    Cancel = True
    
    If Me.NewRecord = True Then
        Me.Undo
    End If
    
End If

End Sub

This one debugs on:

Set rs = db.OpenRecordset(strSQL, dbOpenDynamic, dbSeeChanges, dbOptimistic)

It is also a run-time error '3001'Invalid argument.

I am just not sure what I am doing with recordsets. I have been reading through posts, but it is not making sense. Anyone able to help me out of this rut? Thanks!