how find and prevent save duplicate

[eugzl]

Hi All.
I have form with four level cascade combo boxes. And I would like to check duplicate for entered data based on those four combo boxes. I creates code:

            stCriteria = Me.cboFacility.Text & " " & Me.cboBuilding.Text & " " & Me.cboWing.Text &  & "-" & Me.txtRoom.Text


            If DCount("*", "tblLocations", stCriteria) > 0 Then
                MsgBox "This location already exist." & _
                vbCrLf & "Please enter a different brand.", vbOKOnly Or VbMsgBoxStyle.vbExclamation, _
                "Duplicate Found"

but when I'm running form, entered data and then click Save button I got error message "Run-time error '2185': "You can't reference a property or method for control unless the control has the focus."


How to fix the problem?
Thanks.

[davegri]

Unclear about the dash with txtRoom. Code below assumes the combo boxes and the text box are unbound. If they're bound you'll always get a match.
Your runtime error is corrected via the red code.

Sub cmdSearch_Click()
    'stCriteria = Me.cboFacility.Text & " " & Me.cboBuilding.Text & " " & Me.cboWing.Text &  & "-" & Me.txtRoom.Text


    If DCount("*", "tblLocations", "Facility = '" & Me.cboFacility & "' And Building='" & Me.cboBuilding & "' AND Wing='" & Me.cboWing & "' And Room ='-" & Me.txtRoom & "'") > 0 Then
        MsgBox "This location already exist." & _
        vbCrLf & "Please enter a different brand.", vbOKOnly + vbExclamation, _
        "Duplicate Found"
    Else
        MsgBox "Not a duplicate"
    End If
End Sub

[eugzl]

Hi davegri. Thanks for reply.
In the Save button event I create such code

            stCriteria = "FacilityID = " & !FacilityID & " AND BuildingID = " & !BuildingID & " And WingID=" & !WingID & " And FloorID=" & !FloorID & " And Room='" & !Room & "'"
            If DCount("*", "tblLocations", stCriteria) > 0 Then
               MsgBox "This location already exist." & _
               vbCrLf & "Please enter a different location.", vbOKOnly + vbExclamation, _
               "Duplicate Found"
            Else
               MsgBox "Not a duplicate"
            End If

It give correct message:


But I would like to show in the message box information about location that are text values selected in the combo boxes. For instance, "The location NYU MB A1-2"
where NYU - Facility; MB - Building; A - Wing; 1 - Floor; 2 - Room are values selected in the combo boxes.
How to get such text in the message box?

When I tried code like this:

stCriteria = Me.cboFacility.Text & " " & Me.cboBuilding.Text & " " & Me.cboWing & Me.cboFloor & "-" & Me.txtRoom.Text

I got error message "Run-time error '2185': "You can't reference a property or method for control unless the control has the focus."
How to solve the problem?
Thanks.

[davegri]

2185 error caused by the .text - Remove that.

Sub cmdSearch_Click()
    Dim stCriteria As String
    stCriteria = Me.cboFacility & " " & Me.cboBuilding & " " & Me.cboWing & "-" & Me.txtRoom
    If DCount("*", "tblLocations", "Facility = '" & Me.cboFacility & "' And Building='" & Me.cboBuilding & "' AND Wing='" & Me.cboWing & "' And Room ='-" & Me.txtRoom & "'") > 0 Then
        MsgBox "This location already exists" & vbCrLf _
        & stCriteria & vbCrLf & "Please enter a different brand.", vbOKOnly + vbExclamation, _
        "Duplicate Found"
    Else
        MsgBox stCriteria & " Is not a duplicate"
    End If
End Sub

[eugzl]

The tblLocations has Integer data type fields FacilityID, BuildingID, WingID, FloorID which are foreign key of tblFacilities, tblBuildings, tblWings, tblFloors tables. And only Room field Short Text data type in the tblLocations table. That why when I try to use your code the system cannot recognize Facility and others such parameter under DCount function. I substitute those parameters by FacilityID, BuildingID, WingID, FloorID that exists in tblLocations table and debug code. The parameters Me.cboFacility, Me.cboBuilding, Me.cboWing show selected ID values of the combo boxes. In my previous post my goal display selected text value in the message box. That result still I cannot get. Will appreciate if you get decision for the problem.
Thanks

[davegri]

Lookups-eugzl-davegri-v01.zip

See if this will do it.

[eugzl]

Thanks a lot is very helpfull. I just modified in DCount function code by added quotes required for string value. I very appreciate for help.