DLookup Help

[d9pierce1]

Hi all,


Still struggling with these DLookups....Sorry but have tried this 100 different ways

What I am atttempting to do is:
LookUp the SystemCategoryName and if it exist (>0) where the SystemCategoryTypeID = Me.SystemCategoryTypeID then Msg box, else add it

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If DLookup("[SystemCategoryName]", "tblSystemCategory", "[SystemCategoryName] WHERE [SystemCategoryTypeID] = " & Me.SystemCategoryTypeID) > 0 Then
        MsgBox "This Category Value has all ready been entered"
        Me.Undo
        Exit Sub
    End If
End Sub

Thanks
Dave

[Welshgasman]

If you are testing for > 0, then you need DCount.

strCatName = DLookup("[SystemCategoryName]", "tblSystemCategory", "[SystemCategoryTypeID] = " & Me.SystemCategoryTypeID)

Dcount("[SystemCategoryName]", "tblSystemCategory", "[SystemCategoryTypeID] = " & Me.SystemCategoryTypeID) > 0

DLookUp will return Null if it does not exist.

[d9pierce1]

SystemCategoryData.zip

I attached the db, form is called frmSystemCategoryData,
I used your suggestion but it still wont let me add new data, just msgbox all ready is added..

I want to see if the Txt (SystemCategoryName exist and where the SystemCategoryTypeID = Me.SystemCategoryTypeID

If I enter a CatName and it all ready exist where It has the same CatTypeID then msgbox, Else add it.
I am trying to be able to not add the same CatName if it is in the same CatTypeID as I am currentlly in.
Example
If CatTypeID = 1 and I put in Test, OK
If CatTypeID = 1 and I put in Bark, It should add but it doesnt. Gives msgbox
If CatTypeID = 2 and I put in Test, OK (should let me enter that)

I dont want it to be able to duplicate the CatName if the CatTypeID is = the CatTypeID on my form. If CatTypeID is different, then I want it to add

[ArviLaanemets]

Why do you need all this?

Simply create an unique ID for table SystemCategory based on field SystemCategoryName. Then whenever you try to enter non-unique category name, Access returns an error message, and the new record is not added. In case you need specific message displayed instead the standard one, you can catch the error code, and return your own message instead of standard one.

[d9pierce1]

Thanks Welshgasman & ArviLaanemets
There are instances where I will add a duplicate but not with the same SysCatTypeID. If I try to add a duplicate value to the same SysCatTypeID, then I dont want it to add, However,
If I am at a different SysCatTypeID, then I want to be able to add the duplicate name.

For the sample db provided:
If My SystemCategoryType ID = 1 (Testing)
If my SystemCategoryName = (Test) and I enter Test again, I dont want it added (Msgbox) If not duplicate, then add new txt

If My SystemCategoryType ID = 2 (NewTest)
If my SystemCategoryName = (Test) I want to be able to add it

Clarification:
There is one form and three sub forms on this db:
First CategoryType - Gives the Name of category Type (Fields) CatTypeID, CategoryType
Second CategoryName - Gives name of the category (Fields) CategoryID, CatTypeID, CategoryName
Third CategorySub - Gives the Name of the Category Sub (Fields) CategorySubID, CategoryID, CatTypeID, CategorySubName

If you open db up I think you will see what I am doing here.
If I Enter into the first subform(Continous Form), Testing, That opens up the second subform(Continous Form) and I put in Test which then opens up the thrid subform and I put in Testing123
Sort of like cascading combos...
I need duplicate values only if the CategoryType is different. If same CategoryType, I dont want a duplicate
Same with CategoryName to CategorySubName....

[ArviLaanemets]

I need duplicate values only if the CategoryType is different. If same CategoryType, I dont want a duplicate

So create an unique ID based on those 2 fields! Any combination of those values will be allowed only once!

And in case you have situation, where some duplicate values are allowed, and some not, my advice will be to restructure your database. All table info must follow same rules!

[d9pierce1]

I didnt think a multi criteria DLookup would be so complicated but who knew...
I will keep searching for an answer...
Thanks

[Welshgasman]

Put the criteria into a string variable and debug.print it until you get it correct. Then use that variable in the domain function.
Build it up one piece at a time. It really is not that complicated .

[d9pierce1]

Hi Welshgasman,
I have tried everything I can even think of with no luck.
I tried lower codes...
The code only looks at first record in that table, not all records.
I only have 2 records in that table... 1 Test, 2 Bark and Both have CatTypeID of 1
Immediate window shows Test

If I type in Test again, it gives msgbox as it should, If I type in Bark Again, then it adds another Bark?
This is way over my head...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCatName As String
'strCatName = DLookup("[SystemCategoryName]", "tblSystemCategory", "[SystemCategoryTypeID] = " & Me.SystemCategoryTypeID)
     'Debug.Print strCatName
If Me.SystemCategoryName = DLookup("[SystemCategoryName]", "tblSystemCategory", "[SystemCategoryTypeID] = " & Me.SystemCategoryTypeID) Then
  
        MsgBox "This Category Value has all ready been entered"
        Me.Undo
        Exit Sub
    
End If
End Sub

[Welshgasman]

No, just the criteria into a string variable.
If you have multiple records with same category Id then you need to search on the category name, but that can lead to spelling mistakes like Radiator and Radioter

[d9pierce1]

Hi all,
OK, I got the code below to work on first subform. I have updated code on this so have attached a new database...
So, my question is, how do I get the second and third subform to incorporate the ID Fields?
Example: On second subform, "sfrmSystemCategory", If I enter a value say "Test" and it all ready exist with the same "SystemCategoryTypeID" then msgbox, undo, exitsub... else add it.
If I add "Test" and it exists but not with the same "SystemCategoryTypeID" Then add it....

SystemCategoryData (2).zip

Private Sub SystemCategoryName_BeforeUpdate(Cancel As Integer)


        Dim IT As String
        Dim tricriteria As String
        Dim rsc As DAO.Recordset
        Dim ITkey As Long
    
        On Error GoTo Err_Handler
    
        Set rsc = Me.RecordsetClone
    
        IT = Nz(Me.SystemCategoryName, "")
        
        ITkey = Nz(DLookup("SystemCategoryID", "tblSystemCategory", "SystemCategoryName=" & "'" & IT & "'"), 0)
      
        'does not exist, add record
        If ITkey = 0 Then
            Exit Sub
        End If
        
        'if exististing, then get that record
        If ITkey > 0 Then
            Me.Undo
            'Cancel = True
            MsgBox "This Category Value has all ready been entered"
            Exit Sub
        End If
Exit_Handler:
        Exit Sub
Err_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err & ": " & Error$, vbExclamation, "SystemCategoryName_BeforeUpdate(Cancel As Integer)"
                Resume Exit_Handler
        End Select


    If Not Me.NewRecord And SystemValue Then
        If MsgBox("This is a SYSTEM VALUE. Are you SURE you want to change it?", vbYesNoCancel) <> vbYes Then
            Cancel = True
            Undo
            Modified = Now()
            Exit Sub
        End If
    End If
End Sub

Thank you,
Dave

[d9pierce1]

I beleive I am getting close now, Am getting a mismatch error... So I dont have it quite correct?
Any Thoughts?
Code Below....

Private Sub SystemCategoryName_BeforeUpdate(Cancel As Integer)
    Dim IT As String
    Dim TGT As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Dim ITkey As Long
    Dim TGTkey As Long
    
    On Error GoTo Err_Handler


    
    
    Set rsc = Me.RecordsetClone


    IT = Nz(Me.SystemCategoryName, "")
    TGT = Nz(Me.SystemCategoryTypeID, "")
    
    ITkey = Nz(DLookup("SystemCategoryID", "tblSystemCategory", "SystemCategoryName=" & "'" & IT & "'"), 0)
    TGTkey = Nz(DLookup("SystemCategoryTypeID", "tblSystemCategory", "SystemCategoryTypeID=" & "'" & TGT & "'"), 0)
    
    'does not exist, add record
    If ITkey = 0 And TGTkey = TGT Then
        Exit Sub
    End If
    
    'if exist sting, then don't add
    If ITkey > 0 And TGTkey = TGT Then
        Me.Undo
        'Cancel = True
        MsgBox "This Category Value has all ready been entered"
      
        Exit Sub
    End If
Exit_Handler:
    Exit Sub
Err_Handler:
    Select Case Err
        Case Else
            MsgBox "Error " & Err & ": " & Error$, vbExclamation, "SystemCategoryName_BeforeUpdate(Cancel As Integer)"
            Resume Exit_Handler
    End Select




    If Not Me.NewRecord And SystemValue Then
        If MsgBox("This is a SYSTEM VALUE. Are you SURE you want to change it?", vbYesNoCancel) <> vbYes Then
            Cancel = True
            Undo
            Modified = Now()
            Exit Sub
        End If
    End If
End Sub

[Welshgasman]

I believe you need to check for the name and the same CatID, so two parts for the criteria. You need to combine, not check separately.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything
Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

Any ID should be numeric (I hope), so quotes ARE NOT NEEDED

[jojowhite]

here test your form.
I added Link Master/Child Fields to the 2 rightmost subforms.

[d9pierce1]

Hi Jojowhite,
First off thank you! I did run into a problem as if I add new data, it doesnt show up? The before update works perfect but the data dont show up in the fields once entered and re opened?
Been working on it, but cant get it to show up?
Any Suggestions?
Thanks