Dlookup to find duplicates of two fields

[Haz]

I have a table with two fields that each can have duplicate values but both can't have the same values in other records. The form has the type field as combo box and refNo as combo box and text box.

example of data
tbl_main,
type refNo
small W239
large W239
small W250
large W239 duplicate
small W239 duplicate

I came up with this code but seems like I'm missing some kind of qoutes or something easy. help please....

Private Function IsDuplicateRecord() As Boolean

On Error Resume Next
Dim previousRecordID As Long
IsDuplicateRecord = False

previousRecordID = 0
previousRecordID = DLookup("Dummy", "tbl_main", "type= " & Forms!frm_entry!cbotype&
AND"refNo=" & Forms!frm_entry!cborefNo OR Forms!frm_entry!txtrefNo&)
If previousRecordID <> 0 Then
MsgBox "Record Exists Already"
IsDuplicateRecord = True
End If

End Function

 

[HiTechCoach]

Is "Dummy" the name of a field in the table tbl_main that is a data type Long Integer? That is what the DLookup expects. If not, then it will not work correctly.

The problem with using th the DLookup to do this is that it will always return the first match. If you want to look for a duplicate, you will also need to filter out the current record by excluding that current record's primary key.

Hopefully you have a primary key field that is an auto number or at least an auto number field that is set to no dups

previousRecordID = DLookup("Dummy", "tbl_main", "type= """ & Forms!frm_entry!cbotype & """ AND ( refNo=""" & Forms!frm_entry!cborefNo & """ OR  refNo= """ & Forms!frm_entry!txtrefNo &""")  And PrimayKey <>" &  Forms!frm_entry.PrimaryKey )

 

[jjturner]

Try:

previousRecordID = DLookup("Dummy", "tbl_main", "type = """ _
            & Forms!frm_entry!cbotype _
            & """ AND (refNo = """ & Forms!frm_entry!cborefNo _
            & """ OR refNo = """ & Forms!frm_entry!txtrefNo _
            & """)")

(Note: I'm using the underscore "_" only as a line continuation marker)

 

[jjturner]

Boyd, as expected, your response is the quick, thorough and correct one!

Cheers,
John

 

[Haz]

I do have the dummy field as long Integer and no duplicates but the form is not bounded to the table and can't have the dummy in it. I have a code that update the table. I used the code with out the primary key but still can't get it to work. I don't have any field that is primaryKey and no duplicates except the "dummy" field.I tried to put text box of the dummy field on the form but wont work.

 

[jjturner]

What error are you getting? Or what incorrect result is it giving you?
Where is the function getting called from?

 

[Haz]

What error are you getting? Or what incorrect result is it giving you?
Where is the function getting called from?

no errors. it's just not catching the duplicate records. The funtion is called inside the save record button.

 

[jjturner]

What is the RowSource for your cborefno control? Are you using more than 1 column for it and is the bound column different than the actual text (i.e., "W239") column?

 

[Haz]

What is the RowSource for your cborefno control? Are you using more than 1 column for it and is the bound column different than the actual text (i.e., "W239") column?

RowSource is query with only one column. Both cborefno and txtrefno are not bound to the table. they are unbound controls.

 

[jjturner]

RowSource is query with only one column. Both cborefno and txtrefno are not bound to the table. they are unbound controls.

Yes, I understand your controls are unbound, but nevertheless the list for your combo box has a BoundColumn property. If you only have 1 column of data, as you say, then the BoundColumn property would be set to 0. As long as the values in that 1 column are equal to the values for your "Type" field (i.e., 'small', 'large',...) then I don't see a problem.

Aside from that, I really have no idea why your domain function won't work. Sorry.

 

[Haz]

I have a query that find all duplicate ones the way I want, but how can I make this on the form to prevent the user. I appreciate your help

the query sql is like this.

SELECT tbl_main.type, tbl_main.refNo,
FROM tbl_main
WHERE (((tbl_main.type) In (SELECT [type] FROM [tbl_main] As Tmp GROUP BY [type],[refNo] HAVING Count(*)>1 And [refNo] = [tbl_main].[refNo])))
ORDER BY tbl_main.type, tbl_main.refNo;

 

[Haz]

Finally I got it managed to work by adding the dlookup function to a control box in the form. But for some reason, its returning the first value of the first criteria only and the "AND" and "OR" not doing anything, maybe the qoutation is wrong. Here is the function:

=DLookUp("[Dummy]","tbl_main"," [type]='" & [cbotype] & "'" And " [refNo] ='" & [cborefNo] & "'" Or "[refNo] = '" & [txtrefno] & "'")

This should return the dummy number if exist. If exist than there is a record already this way I can have anothe if statement to cancel saving a record if there is a dummy number if not proceed to save. But I can't get the AND and OR to work and return the dummy number for both criterias.

 

[jjturner]

Try:
=DLookUp("[Dummy]","tbl_main"," [type] = '" & [cbotype] & "' And ([refNo] = '" & [cborefNo] & "' Or [refNo] = '" & [txtrefno] & "')")

 

[Haz]

Thanks alot got it working.....

 

[jjturner]

NP - I'd also remind you not to forget the advice from HiTechCoach

(note, I had to edit the placement of the final double-quotation mark to be after the blue parentheses)