Solved Checking if a certain unique record exists using DCount (1 Viewer)

theferd

Registered User.
Local time
Today, 11:53
Joined
Dec 23, 2019
Messages
42
I am having difficulties creating a line to determine if a certain record exists within a table. I have a form where a user put in numbers in four different text boxes and those will be stored in the table. And before they are inserted I wish to check if it already exists.

From my understanding the line would start as:
exist = DCount("[Field1] + [Field2] + [Field3] + [Field4]", "tblcatalog", "[Field1] = '" & me.txt1 & "' ....

And this is where I struggled to find the correct way to construct this line. The usual error I get is a data mismatch even though every input is a number. If there is perhaps a better function to use to accomplish the same thing I would be open to this
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:53
Joined
Aug 30, 2003
Messages
36,118
If the fields are numeric, your syntax is wrong, there should be no single quotes. More like

exist = DCount("*", "tblcatalog", "[Field1] = " & me.txt1 & " AND Field2 = "...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:53
Joined
May 21, 2018
Messages
8,463
If you have four fields and the combination of those 4 fields is unique, simply make a composite unique index. You will not be able to enter a duplicate record having the same 4 field values.
 

theferd

Registered User.
Local time
Today, 11:53
Joined
Dec 23, 2019
Messages
42
I did make the four fields a composite primary key, but was concerned about how the error would produce itself while a user was on the form. Is there a certain line I'll have to introduce to exit the sub such that the VBA module doesn't open? Or if it is just a message box that appears, a way to customize the text?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:53
Joined
Aug 30, 2003
Messages
36,118
I'd use the index but I'd still test. I doubt the error you'll get is very descriptive for users, plus it doesn't get triggered until they try to save the record. Sometimes that's annoying if they've entered a bunch of other stuff.
 

theferd

Registered User.
Local time
Today, 11:53
Joined
Dec 23, 2019
Messages
42
exist = DCount("[Field1] + [Field2] + [Field3] + [Field4]", "tblcatalog", "[Field1] = " & Me.txt1 & " AND [Field2] = " & Me.txt2 & " AND [Field3] = " & Me.txt3 & " AND [Field4] = " & Me.txt4 & "")

Returns a missing operator in the final part of the expression. Is there something I'm missing?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:53
Joined
Aug 30, 2003
Messages
36,118
I don't see a syntax error right off, but I'd just use the * in the first argument, as I showed. The & "" at the end is also superfluous, but probably isn't causing an error.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:53
Joined
Feb 28, 2001
Messages
26,999
Check whether any of the Fieldn elements is null. Sometimes nulls can mess up your logic pretty badly. You can also save some typing.

Code:
exist = DCount ("*", "tblcatalog", "[Field1] = " & Me.txt1 & " AND [Field2] = " & Me.txt2 & " AND [Field3] = " & Me.txt3 & " AND [Field4] = " & Me.txt4 & "")
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:53
Joined
Jan 23, 2006
Messages
15,364
Maybe time to post a copy of the database - zip format and remove anything personal first.
We don't need all your data --just enough to highlight the issue.
 

theferd

Registered User.
Local time
Today, 11:53
Joined
Dec 23, 2019
Messages
42
I rewrote the entire on click event and added invisible text boxes to validate each value at each criteria and somehow that ended up fixing the data mismatch/syntax errors. I really have no clue as to why it is working now, but the line itself is not any different from what Doc Man gave.
Sorry I cant really explain why its working now but thanks all for your help
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:53
Joined
Jan 23, 2006
Messages
15,364
Glad you have it resolved --even if not fully understood.
 

Users who are viewing this thread

Top Bottom