Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Check if sub record already used in many to many? 2

Status
Not open for further replies.
misscrf

misscrf

Technical User
Jun 7, 2004
1,344
US
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.

Code: 
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:
Code: 
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.)

Can anyone help me put the pieces together on this, please?

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot
 
Sort by date Sort by votes
I took a further look at this and thought to move the code for this check to the before update of the combo on the subform. Here is the code I am working with now:

Code: 
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

It now debugs on:
Code: 
Set rs = db.OpenRecordset(strSQL, dbOpenDynamic, dbSeeChanges, dbOptimistic)

It is 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!


misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Howdy misscrf . . .
Getting back to the code in your post origination, try the following ... note the changes in [purple]purple[/purple]:
Code: 
[blue]   Dim [purple][b]db[/b][/purple] As [purple][b]DAO.Recordset[/b][/purple], rst As DAO.Recordset
   Dim AssetTag As String, SQL As String
   
   Set [purple][b]db[/b][/purple] = CurrentDb
   SQL = "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 = [purple][b]db[/b][/purple].OpenRecordset(SQL, doOpenSnapshot)
   
   If Not [purple][b]rst.BOF[/b][/purple] 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
       MsgBox "Do MY Other Code"
   End If[/blue]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
I will try this on Monday. Thanks, Aceman!

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
If I understand correctly, I would include all records in the combo box row source but include a column that identifies if the record is unique or a duplicate. Sort by this column so the unique values would be at the top and use the after update of the combo box to check the column and possibly cancel the update and inform the user.


Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Howdy dhookom . . .

I thought about that ... however I'm more interested in what happens if [blue]misscrf[/blue] can get a working SQL! I think it will tell all!

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

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
If the recordset method is chosen, I would change:
Code: 
             "WHERE Forms![frmHBCase]![PKHBCaseID]  = [tblKeyHBCaseAssets]![FKHBCase])));"
To this (assuming the FKHBCase/PKDBCaseID are numeric):
Code: 
             "WHERE [tblKeyHBCaseAssets]![FKHBCase]= " & _
    Forms![frmHBCase]![PKHBCaseID]  & ")));"


Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
dhookom . . .

Agreed! ... that has yet to surface ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Thank you both for jumping in on this. Looks like I got an interesting one for two smart guys like you!

I put in the new code and adjusted the sql. This is the new entire code:

Code: 
Private Sub FKAsset_AfterUpdate()

Dim db As DAO.Recordset, rst As DAO.Recordset
Dim AssetTag As String, SQL As String
       
   [COLOR=#ff0000][b] Set db = CurrentDb[/b][/color]
    SQL = "SELECT tblAsset.PKAssetID " & _
            "FROM tblAsset " & _
            "LEFT JOIN tblAssetType " & _
            "ON tblAsset.FKAssetType = tblAssetType.PKAssetType " & _
            "WHERE (((tblAsset.PKAssetID) IN " & _
            "(SELECT [tblKeyHBCaseAssets]![FKAsset]  " & _
                "FROM tblKeyHBCaseAssets " & _
                "WHERE [tblKeyHBCaseAssets]![FKHBCase]= " & _
                Forms![frmHBCase]![PKHBCaseID] & ")));"

    Set rst = db.OpenRecordset(SQL, doOpenSnapshot)
   
    If Not rst.BOF 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
        DoCmd.RunCommand acCmdSaveRecord
    
        Me!txtCurrRec = CStr(Me.CurrentRecord) & " of " & _
        CStr(Me.RecordsetClone.RecordCount) & " Case Assets"
   
        Me.intNextCaseAsset = DLookup("CountCaseAssets", "qryCountCaseAssets")

        AssetTag = Forms![frmHBCase]![intEMatter]

        If Me.intNextCaseAsset = 0 Then
            AssetTag = AssetTag & "_" & Format(1, "00000")
        ElseIf IsNull(Me.intNextCaseAsset) Then
            AssetTag = AssetTag & "_" & Format(1, "00000")
        ElseIf Me.intNextCaseAsset = 1 Then
            AssetTag = AssetTag & "_" & Format(1, "00000")
        Else: AssetTag = AssetTag & "_" & Format(Me.intNextCaseAsset, "00000")
        End If

        AssetTag = AssetTag & "_" & DLookup("txtAssetType", "qryCaseAssetType")

        Me.txtCaseAssetTag = AssetTag

    End If
End Sub

Now I get a type mismatch error on the line in red above for the set db. I will start researching this, but thought one of you might know what happened.

I feel so lucky to have gotten you both on this thread! The amount I have learned from you both over the years is awesome.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
ok, if you can even believe it, I think I found what that issue was.

Code: 
Dim db As DAO.Recordset

needed to be set as

Code: 
Dim db As DAO.Database
right?

Now I have the code "working" but it isn't actually working lol.

When I use the drop-down it pauses for a sec, gives me the message that the asset already exists, doesn't back out of the record, and leaves the combo populated, without generating the asset tag field.

It does this even if the asset doesn't already exist in the case.

Here is my code again, with some purple comments to explain what each line is supposed to do. Everything after the outer most ELSE was working before (the part to generate the asset tag). I just need to incorporate this check to not add the record and tell the person they can't have the same one twice.

Code: 
Private Sub FKAsset_AfterUpdate()

Dim rsDB As DAO.Database
Dim rsRST As DAO.Recordset
Dim strAssetTag As String
Dim strSQL As String

[COLOR=purple][b][i]' A sql statement to get the Primary ID of the asset from the asset table, where the primary id is in the Key table for case assets and within that key record, the case id matches the primary case id on the current record of the main case form.[/i][/b][/color]
    Set rsDB = CurrentDb
    strSQL = "SELECT tblAsset.PKAssetID " & _
            "FROM tblAsset " & _
            "WHERE (((tblAsset.PKAssetID) IN " & _
            "(SELECT [tblKeyHBCaseAssets]![FKAsset]  " & _
                "FROM tblKeyHBCaseAssets " & _
                "WHERE [tblKeyHBCaseAssets]![FKHBCase]= " & _
                Forms![frmHBCase]![PKHBCaseID] & ")));"

    Set rsRST = rsDB.OpenRecordset(strSQL, dbOpenSnapshot)
   
   [COLOR=purple][b][i]'If that SQL above is true/returns a record, that means this asset being chosen in the FKAsset combo control, already exists.  Throw an error message and undo the adding of this record.[/i][/b][/color]
    If Not rsRST.BOF 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
    
    [COLOR=purple][b][i]'If it doesn't exist, save the record, and concatenate infomration to create an asset tag value for this case asset record.[/i][/b][/color]
        DoCmd.RunCommand acCmdSaveRecord
    
        Me!txtCurrRec = CStr(Me.CurrentRecord) & " of " & _
        CStr(Me.RecordsetClone.RecordCount) & " Case Assets"
   
        Me.intNextCaseAsset = DLookup("CountCaseAssets", "qryCountCaseAssets")

        strAssetTag = Forms![frmHBCase]![intEMatter]

        If Me.intNextCaseAsset = 0 Then
            strAssetTag = strAssetTag & "_" & Format(1, "00000")
        ElseIf IsNull(Me.intNextCaseAsset) Then
            strAssetTag = strAssetTag & "_" & Format(1, "00000")
        ElseIf Me.intNextCaseAsset = 1 Then
            strAssetTag = strAssetTag & "_" & Format(1, "00000")
        Else: strAssetTag = strAssetTag & "_" & Format(Me.intNextCaseAsset, "00000")
        End If

        strAssetTag = strAssetTag & "_" & DLookup("txtAssetType", "qryCaseAssetType")

        Me.txtCaseAssetTag = strAssetTag

    End If
    
    [COLOR=purple][b][i]'The End ;-)[/i][/b][/color]
End Sub

Not sure where this is tripping up now, because there is no error. It just doesn't seem to be evaluating correctly. DHookom, it seemed like you had an interesting thought on how to maybe handle this:
I would include all records in the combo box row source but include a column that identifies if the record is unique or a duplicate. Sort by this column so the unique values would be at the top and use the after update of the combo box to check the column and possibly cancel the update and inform the user.
Click to expand...

I am not opposed to doing something like this. My thought was just that the normalization of my schema should not require me to add another field and do all that extra work. It seems like the information is all there. Isn't it?

Thanks for the help!

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
I don't know how limiting the combo box might look in your application. Consider a continuous form in Northwind sample database with a Record Source of the Order Details table. Each Order should only have one record for any given ProductID. A combo box to select the ProductID might have a Row Source like:
Code: 
SELECT Products.ProductID, Products.ProductName, (SELECT Count(*) FROM [Order Details] WHERE OrderID = 10543 and [Order Details].[ProductID] = Products.ProductID) AS ProductCount
FROM Products
ORDER BY Products.ProductName;
The code in the On Current event sets a new Row Source based on the current OrderID:
Code: 
Private Sub Form_Current()
    Dim strSQL As String
    strSQL = "SELECT ProductID, ProductName, " & _
      "(SELECT Count(*) FROM [Order Details] " & _
      "WHERE OrderID = " & Me.OrderID & " and [Order Details].[ProductID] = Products.ProductID) AS ProductCount " & _
    "FROM Products " & _
    "ORDER BY Products.ProductName;"
    Me.cboProductID.RowSource = strSQL
End Sub

This would allow selecting all products but the ProductCount would determine if the product is allready selected for that Order. This would be better if the Order Details table had a single primary key field.



Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
This is the row source for FKAsset:
Code: 
SELECT tblAsset.PKAssetID, [txtAssetType] & " Collected: " & [dtDateCollected] & " - " & [txtCollectedFrom] & " at " & [txtCollectedLocation] & " location" AS ListAsset
FROM tblAsset 
LEFT JOIN tblAssetType ON tblAsset.FKAssetType = tblAssetType.PKAssetType
ORDER BY tblAssetType.txtAssetType, tblAsset.dtDateCollected DESC;

Here is an illustration of the form setup

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
sorry, I hit sumbit accidentally. A nicer link:
Entry Form

Please let me know if you need more information. I appreciate your help.

Thank you.

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
I think I got a way to clean this up and make it a bit easier. You can tell me if it is not actually cleaner.

Here is the new code:
Code: 
Private Sub FKAsset_AfterUpdate()
Dim strAssetTag As String
      
[COLOR=purple][b] query syntax below - checking a dlookup instead of the sql.  no recordset, etc[/b][/color] 
    If DLookup("PKAssetID", "qryfrmCaseAssetsExists") > 0 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
        DoCmd.RunCommand acCmdSaveRecord
    
        Me!txtCurrRec = CStr(Me.CurrentRecord) & " of " & _
        CStr(Me.RecordsetClone.RecordCount) & " Case Assets"
   
        Me.intNextCaseAsset = DLookup("CountCaseAssets", "qryCountCaseAssets")

        strAssetTag = Forms![frmHBCase]![intEMatter]

        If Me.intNextCaseAsset = 0 Then
            strAssetTag = strAssetTag & "_" & Format(1, "00000")
        ElseIf IsNull(Me.intNextCaseAsset) Then
            strAssetTag = strAssetTag & "_" & Format(1, "00000")
        ElseIf Me.intNextCaseAsset = 1 Then
            strAssetTag = strAssetTag & "_" & Format(1, "00000")
        Else: strAssetTag = strAssetTag & "_" & Format(Me.intNextCaseAsset, "00000")
        End If

        strAssetTag = strAssetTag & "_" & DLookup("txtAssetType", "qryCaseAssetType")

        Me.txtCaseAssetTag = strAssetTag
        
    End If
End Sub

this is the SQL of the new query used in the dlookup:
Code: 
SELECT tblAsset.PKAssetID
FROM tblAsset
WHERE (((tblAsset.PKAssetID) In (SELECT [tblKeyHBCaseAssets]![FKAsset] FROM tblKeyHBCaseAssets WHERE Forms![frmHBCase]![PKHBCaseID]  = [tblKeyHBCaseAssets]![FKHBCase] and [tblAsset]![PKAssetID] = Forms![frmHBCase]![frmCaseAsset].Form![FKAsset] )));

Now everything works. If it does exist, I get the message box, and if it doesn't, the record saves and the txtAssetTag gets populated.

I have one minor lingering issue. If it does exist, I get the error message and the txtAssetTag does not populate, but The existing asset tag chosen in the fkAsset dropdown doesn't back out. I need that record that is about to be created to be backed out, so it doesn't just sit there. I tested and after the message, if I leave that there, I can go to a new record, with this bad record just sitting there.

I guess the
Code: 
Me.FKAsset.Undo
Me.FKAsset.Requery
is not really undoing the record completely. Any ideas how I can really get it to back out of creating that record?

Thanks you two! You really got me thinking about how to get to a solution!


misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
got it!
me.undo undoes the entire record! wohoo! on to the next challenge, lol.

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
misscrf . . .

Bravo on resolution ... and apologies on my bad declaration of [blue]Dim db as DAO.Recordset[/blue]. Don't know how I missed it!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
558
Kennelbloke
Kennelbloke
I
  • Question
How to get 2 codes to work together.
Replies
6
Views
367
hermanlaksko
hermanlaksko
rdpress
  • Locked
  • Article
Determine if Close Button is Used to Close Form 1
Replies
3
Views
179
Andrzejek
Andrzejek
caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
489
Andrzejek
Andrzejek
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
441
PrimoSteve
PrimoSteve

Part and Inventory Search

Sponsor

Back
Top