MS Access forms VBA

Question

I have a Single form where I am trying to get a message box to prompt the user when 2 fields from my form match what is in the table and other one doesn't match.

This is the code I have which is in a button on click event.

If Not IsNull(Me.L_dt) Then 
    If Me.Ref = DLookup("[Ref]", "tbl", "[Ref]='" & Forms!MyForm!Ref & "'") And _
        Me.Class = Nz(DLookup("[ClassTy]", "tbl", "[ClassTy]<>'" & Forms!MyForm!ClassTy & "'"), 0) And _
        Me.L_dt = DLookup("L_dt", "tbl", "L_dt= #" & Format(Forms!MyForm![L_dt], "yyyy\/mm\/dd") & "#") Then
            If MsgBox("This record has an existing Learn Dt for this Ref", vbOKCancel) = vbCancel Then
                Cancel = True
                Me.Undo
                Exit Sub
            End If
    End If
End If

Expected Result should be if the Ref and L_Dt are the same but the Class is different then prompt user, if L_Dt, Ref and Class are all the same this is fine.

Currently this is not working as expected as getting prompt when all 3 match.

I think it's me.ClassTy line that is wrong but when I change the <> to = it still does not work.

*if the Ref and L_Dt are the same but the Class is different then prompt user, if L_Dt, Ref and Class are all the same this is fine.* So if the user inputs info that match with an existing record on fields `Ref ` and `L_Dt` but different `Class`, is wrong? that means your table only accepts records that match those 3 fields. Is that right? — Foxfire And Burns And Burns, Sep 06 '19 at 12:56
`Class` is used in VBA and should not be used as field/control name. You can try `Me![Class]` but it still is a bad idea. — Andre, Sep 06 '19 at 13:07
There are many fields within the table but for the example its the 3 fields, I just need to make the user aware that they are using an unknown/Different Class where the other 2 fields match and let the user decide if they wish to continue. — New2Programming, Sep 06 '19 at 13:08
Thanks I have renamed this now to ClassTy but still the same issue — New2Programming, Sep 06 '19 at 13:10
Use [DCount](https://learn.microsoft.com/en-us/office/vba/api/access.application.dcount) to count how many records match with the inputs of the user. If the count is 0, it means the user is creating a new record, but if the count is not 0, it means the `ClassTy` already exists (asiggned to that specific `Ref` and `L_Dt`). — Foxfire And Burns And Burns, Sep 06 '19 at 13:26
Thanks Foxfire although I don't know how to do this in the above query as I'm not very good at vba. Can you show what me please. thanks — New2Programming, Sep 06 '19 at 13:45
Never do `DLookup("L_dt", "tbl", "L_dt= #" & Format(Forms!MyForm![L_dt], "yyyy\/mm\/dd") & "#")`! Always do `DLookup("L_dt", "tbl", "L_dt=Forms!MyForm![L_dt]")` instead. You're making your code more complicated and error-prone than necessary. See [this](https://stackoverflow.com/q/49509615/7296893) on where form-based parameters are supported. — Erik A, Sep 06 '19 at 13:46
Are you writing this code to avoid dupes ? If so why not just create a unique index on the set of fields ? Much faster / safer / easier — iDevlop, Sep 06 '19 at 14:46
Patrick, This is not to avoid duplicates but to notify the user that 2 of the same fields already exist in the table. — New2Programming, Sep 09 '19 at 07:12

score 1 · Answer 1 · answered Sep 06 '19 at 12:55

1

You miss a field name:

Format(Forms!MyForm![], ...

answered Sep 06 '19 at 12:55

Gustav

53,498
7
29
55

Sorry it is on my actual form but missed off of here, now updated. thanks – New2Programming Sep 06 '19 at 13:03

score 0 · Accepted Answer · answered Sep 09 '19 at 08:39

If Not IsNull(Me.L_dt) Then 
  If Me.Classty = Nz(DLookup("[ClassTy]", "tbl", "[ClassTy]='" & Forms!MyForm!ClassTy & "'"), 0) = false then
    If Me.Ref = DLookup("[Ref]", "tbl", "[Ref]='" & Forms!MyForm!Ref & "'") And _
        Me.L_dt = DLookup("L_dt", "tbl", "L_dt= Forms!MyForm![L_dt]") Then
            If MsgBox("This record has an existing Learn Dt for this Ref", vbOKCancel) = vbCancel Then
                Cancel = True
                Me.Undo
                Exit Sub
            End If
     End if
  End If
End If

MS Access forms VBA

2 Answers2