Prevent Duplicate Record based on 2 Criteria? 2

[misscrf]

You may have seen some other posts on this form that I am working on. New question, new thread.

I have an application with a main entry form (frmHBCase) for new projects/cases. There are 3 sub forms -
Custodians (frmCaseCustodian) - people who will give us data for the project
Assets (frmCaseAsset) - data on media for the project
Asset Custodians (frmCaseAssetCustodian) - a many/many linking form to tie each asset to any custodians having data on it. (1 asset may have multiple custodian stuff on it, one custodian may have data on multiple assets)

I created code that runs on a command button from the main form. A user -
1) clicks the record selector of a single record on the custodian sub form
2) clicks the record selector of a single record on the asset sub form
3) clicks the command button to assign asset custodian relationship.

The command button will generate a new record in the asset/custodian key table and populate the sub form with the keys for the selected records from the other 2 sub forms.

Now I need to add a clause that will prevent the command button from executing the add record, if there is already a case asset assigned to that case custodian in that given case record.

This is the code I have for adding the record. The portion to catch the dup is in red:

Private Sub cmdAddCaseAssetCust_Click()
Dim frm As Form
Set frm = Me.frmCaseAssetCustodian.Form
[COLOR=red][b]If DLookup("[PKHBCaseCustodianAssetKeyID]", "qryfrmCaseCustodianExists") > 0 Then
        MsgBox "This custodian already exists in this case.", _
        vbCritical, _
        "Custodian May Only Exist Once in a Given Case"
Me.frmCaseAssetCustodian.Form.Undo[/b][/color]
Else
With frm.RecordsetClone
.AddNew
![FKCaseAsset] = [frmCaseAsset].Form![PKHBCaseAssetKeyID]
![FKHBCaseCustodian] = [frmCaseCustodian].Form![PKHBCaseCustodianKeyID]
.Update
frm.Bookmark = .LastModified
End With
Set frm = Nothing
[frmCaseAsset].Form![PKHBCaseAssetKeyID].Requery
[frmCaseCustodian].Form![PKHBCaseCustodianKeyID].Requery
End If

End Sub

The dlookup is to the query qryfrmCaseCustodianExists which has the following sql output:

SELECT tblKeyHBCaseAssetCust.PKHBCaseCustodianAssetKeyID, tblKeyHBCaseAssetCust.FKHBCaseCustodian, tblKeyHBCaseAssetCust.FKCaseAsset
FROM tblKeyHBCaseAssetCust
WHERE (((tblKeyHBCaseAssetCust.FKHBCaseCustodian)=[Forms]![frmHBCase]![frmCaseAssetCustodian].[Form]![FKHBCaseCustodian]) AND ((tblKeyHBCaseAssetCust.FKCaseAsset)=[Forms]![frmHBCase]![frmCaseAssetCustodian].[Form]![FKCaseAsset]));

The query is not limiting correctly. It is actually returning whatever the current record is in the frmCaseAssetCustodian sub form. I do not understand how to catch the FKCaseAsset and the FKCaseCustodian IDs that were just selected and compare them to the dlookup query, to catch if they are duplicate or not.

Am I missing something obvious?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I tried to revise the code and now it adds records, but doesn't stop a duplicate from being entered. This is the code:

Private Sub cmdAddCaseAssetCust_Click()
Dim frm As Form
Set frm = Me.frmCaseAssetCustodian.Form

If DLookup("PKHBCaseAssetKeyID", "qryCaseAssetExists") = frm![FKCaseAsset] And DLookup("PKHBCaseCustodianKeyID", "qryCaseCustodianExists") = frm![FKHBCaseCustodian] Then
        MsgBox "This custodian already exists in this case.", _
        vbCritical, _
        "Custodian May Only Exist Once in a Given Case"
    Me.frmCaseAssetCustodian.Form.Undo
Else
    With frm.RecordsetClone
        .AddNew
        ![FKCaseAsset] = [frmCaseAsset].Form![PKHBCaseAssetKeyID]
        ![FKHBCaseCustodian] = [frmCaseCustodian].Form![PKHBCaseCustodianKeyID]
        .Update
        frm.Bookmark = .LastModified
    End With
    Set frm = Nothing
    [frmCaseAsset].Form![PKHBCaseAssetKeyID].Requery
    [frmCaseCustodian].Form![PKHBCaseCustodianKeyID].Requery
End If

End Sub

The 2 dlookups are set to 2 queries.

qryCaseAssetExists

SELECT tblKeyHBCaseAsset.PKHBCaseAssetKeyID
FROM tblKeyHBCaseAsset
WHERE (((tblKeyHBCaseAsset.PKHBCaseAssetKeyID) 
In (SELECT [tblKeyHBCaseAsset]![PKHBCaseAssetKeyID] 
FROM tblKeyHBCaseAsset 
WHERE Forms![frmHBCase]![frmCaseAsset].Form![PKHBCaseAssetKeyID] = [tblKeyHBCaseAsset]![PKHBCaseAssetKeyID])));

qryCaseCustodianExists

SELECT tblKeyHBCaseCust.PKHBCaseCustodianKeyID
FROM tblKeyHBCaseCust
WHERE (((tblKeyHBCaseCust.PKHBCaseCustodianKeyID) 
In (SELECT[tblKeyHBCaseCust]![PKHBCaseCustodianKeyID] FROM
tblKeyHBCaseCust 
WHERE Forms![frmHBCase]![frmCaseCustodian].Form![PKHBCaseCustodianKeyID] = [tblKeyHBCaseCust]![PKHBCaseCustodianKeyID])));

Not sure what I am missing, but somehow I am not catching the record that is being potentially added and comparing the 2 foreign keys to the right queries.

Any help is appreciated!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

Maybe this will help to debug

Private Sub cmdAddCaseAssetCust_Click()
Dim frm As Form

dim custID as long
dim AssetID as long
dim strWhere as string

Set frm = Me.frmCaseAssetCustodian.Form
custID = Nz(frm.FKHBCaseCustodian,0)
assetID = nz(frm.FKCaseAsset,0)

strWhere = "tblKeyHBCaseAssetCust.FKHBCaseCustodian = " & cutID & " AND 
tblKeyHBCaseAssetCust.FKCaseAsset = " & assetID

'for debug
msgbox "Cutomer ID: " & custID & " AssetID: " & assetID
msgbox "Where: " & strWhere
msgbox "Dcount : " & DCOUNT("*", "tblKeyHBCaseAssetCust",strWhere)
'for debug remove after
  exit sub

If DCOUNT("*", "tblKeyHBCaseAssetCust",strWhere) > 0 Then
        MsgBox "This custodian already exists in this case.", _
        vbCritical, _
        "Custodian May Only Exist Once in a Given Case"

.....

 

[TheAceMan1]

Howdy misscrf . . .

In the SQL of your post origination ... your where clause is comparing against two controls in [red]frmCaseAssetCustodian[/red] (the reason your returning the current record). You should be comparing against [blue]frmCaseCustodian[/blue] & [blue]frmCaseAssets[/blue] ... aka your selection in those subforms.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

Thank you Maj and Ace.

Maj.

I put that code in and I get an object required on this line:

tblKeyHBCaseAssetCust.FKCaseAsset = " & AssetID"

I noticed that this line:

strWhere = "tblKeyHBCaseAssetCust.FKHBCaseCustodian = " & [COLOR=red][b]cutID[/b][/color] & " AND "

looks like it should be this:

strWhere = "tblKeyHBCaseAssetCust.FKHBCaseCustodian = " & [COLOR=red][b]cu[u]s[/u]tID[/b][/color] & " AND "

But that didn't help anything.

Not sure exactly how this is supposed to work, so I am not sure where to troubleshoot.

-----------------

Aceman, you are right! I will have to look at this. Let me try to change that and see what it does. This whole section of getting that query to compare the right stuff has been making my head spin.

Thank you both. I know this is do-able. Just need to get my head out of the "woods" so I can see the solution!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I got it! Ace's point was right. Here is the correct code:

Private Sub cmdAddCaseAssetCust_Click()
Dim frm As Form
Set frm = Me.frmCaseAssetCustodian.Form

If DLookup("[PKHBCaseCustodianAssetKeyID]", "qryfrmCaseAssetCustodianExists") > 0 Then
    MsgBox "This custodian already exists in this case.", _
    vbCritical, _
    "Custodian May Only Exist Once in a Given Case"
    Me.frmCaseAssetCustodian.Form.Undo
Else
    With frm.RecordsetClone
        .AddNew
        ![FKCaseAsset] = [frmCaseAsset].Form![PKHBCaseAssetKeyID]
        ![FKHBCaseCustodian] = [frmCaseCustodian].Form![PKHBCaseCustodianKeyID]
        .Update
        frm.Bookmark = .LastModified
    End With
    Set frm = Nothing
    [frmCaseAsset].Form![PKHBCaseAssetKeyID].Requery
    [frmCaseCustodian].Form![PKHBCaseCustodianKeyID].Requery
End If

End Sub

And correct sql for the dlookup, in case it can help others:

SELECT tblKeyHBCaseAssetCust.PKHBCaseCustodianAssetKeyID, tblKeyHBCaseAssetCust.FKHBCaseCustodian, tblKeyHBCaseAssetCust.FKCaseAsset
FROM tblKeyHBCaseAssetCust
WHERE (((tblKeyHBCaseAssetCust.FKHBCaseCustodian)=[Forms]![frmHBCase]![frmCaseCustodian].[Form]![PKHBCaseCustodianKeyID]) AND ((tblKeyHBCaseAssetCust.FKCaseAsset)=[Forms]![frmHBCase]![frmCaseAsset].[Form]![PKHBCaseAssetKeyID]));

You both get stars for helping me and providing feedback that gave me something to work on!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Why not simply replace this:
If DLookup("[PKHBCaseCustodianAssetKeyID]", "qryfrmCaseAssetCustodianExists") > 0 Then
with this (ie no external query neeeded)?
If DCount("*", "tblKeyHBCaseAssetCust", "FKHBCaseCustodian=" & frm!PKHBCaseCustodianKeyID & " AND FKCaseAsset=" & Me.frmCaseAsset.Form!PKHBCaseAssetKeyID) > 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

Thank you PHV. I appreciate learning an alternative to what I set up. Is there any benefit to doing it one way over the other?

misscrf

It is never too late to become what you could have been ~ George Eliot