check if record exists button

[bbwolff]

[SOLVED] check if record exists button

I need help again
i have a database that involves a lot of data about ppl and I'd like to prevent duplicate entries.

I have a form that gathers data
i'd like to have a button after name, surname and birthday are written
a click on the button would display a msgbox that says that person already exist
maybe even with the added - edit the old entry/add new record anyway/abort options.

tx for any help

 

[AOB]

Hi bbwolff,

Could you not use the DCount function to determine whether or not that record exists?

For arguments sake, let's say you have 1 table (tblData)

Where the primary key is an autonumber (ID)

And contains 3 fields (fldName, fldSurname, fldBirthday)

And your form has 3 textboxes (txtName, txtSurname, txtBirthday)

You could use...

=DCount("[ID]", "[tblData]", "[fldName]='" & txtName.Value & "' And [fldSurname]='" & txtSurname.Value & " And [fldBirthday]=#" & Format(txtBirthday.Value, "mm/dd/yyyy") & "#")

...as the control source in an extra textbox (which should return the number of records which have the same name, surname & birth date as those entered into each of the three textboxes)

Or use an Iif function to return a message (i.e. "Duplicate!") if the DCount function returns a number > 0

Something along those lines? You might want to adapt it with Nz's or use the AfterUpdate events of each textbox to only fire the function if all three textboxes contain valid data etc.

AOB

 

[bbwolff]

I never used dcount before, and I'm completely lost

=DCount("id";"tblData";[fldName]="Josh")

this returns 0, even though there are a few Josh ppl in my test database

Do i need unbound txtboxes in form or regular bound txtboxes before I check?
and how do i set input boxes to diff names.

 

[AOB]

Hi bbwolff,

Not to worry...

First off, some syntax - your expression should look like this :

=DCount("id","tblData","[fldName]='Josh'")

If you're using this expression as the Control Source for a separate 'warning' textbox, then yes, it must be unbound (or, rather, it will be bound to this expression rather than to a field or combination of fields in a table or query)

Your input boxes should stay exactly the same as they are - this is a separate textbox used purely to highlight duplications. I tend to put textboxes such as these just to the right of text-entry boxes, lock & disable them and then use an Iif / DCount combo so that rather than seeing the number returned, I get a simple warning message :

=Iif(DCount("id","tblData","[fldName]=[txtName]")>0,"Duplicate!","")

Does this help?

AOB

 

[bbwolff]

tx for your help
no idea why but my access wants ; in place of , - took me a lot of time to find that out
I've got this semi working

=IIf(DCount("[ID]";"tblData";"[fldName] =[txtName]");"Check for duplicates";"Good to go")

as i tried it out it returns check for duplicates for every record that I check. Which is ok
When I start new record, it says good to go at the beginning, then checks when I start writing and always say good to go, even if I enter a name already in the db.

I've got a feeling this should be really easy to implement but it's taking me a lot of time and nerves

can i append all this on a button that would write good to go or check in txtbox when pressed (my attempt failed miserably so far)

Private Sub Command15_Click()
IIf(DCount("[ID]","tblData","[fldName] =[txtName]"),Me.txtCheckBox="Check for duplicates",me.txtCheckBox="Good to go")

End Sub

sample: https://dl.dropboxusercontent.com/u/2229730/TestDbb.accdb

 

[bbwolff]

I might have the second part under control

Private Sub Command3_Click()
Dim chktxt As String
chktxt = IIf(DCount("[ID]", "tblData", "[fldName] =[txtName]"), "Check for duplicates", "Good to go")
Me.Text0.SetFocus
Text0.Text = chktxt
End Sub

 

[bbwolff]

to further expand my giant success in the field of vba

Private Sub Command3_Click()
Dim chktxt As String
chktxt = IIf(DCount("[ID]", "tblData", "[fldName] =[txtName] And [fldSurname]=[txtSurname] And [fldBirthDate]=[txtBirthdate]"), "Check for duplicates", "Good to go")
Me.Text0.SetFocus
Text0.Text = chktxt
End Sub

It seems this works for me, ie is exactly what i was looking for. So thanks again
A question nonetheless, is there a reason I should use the more complex syntax you wrote in the first reply, something I'm missing?

 

[AOB]

Hi bbwolff

Nope - the syntax I provided was for use as a control source in a textbox which would dynamically recalculate as the other relevant textboxes are changed. If you are putting it into a sub or function in VBA which is triggered by an event (such as clicking a button or updating a control) then the syntax becomes less complex. I don't see anything wrong with what you've got here.

One word of warning though - remember that the DCount function returns a numerical value, i.e. the number of records which satisfy the conditions specified.

And the IIf function evaluates an expression (true / false) and returns a value based on your specified true and false conditions.

Your expression...

chktxt = IIf(DCount("[ID]", "tblData", "[fldName] =[txtName] And [fldSurname]=[txtSurname] And [fldBirthDate]=[txtBirthdate]"), "Check for duplicates", "Good to go")

...works because VBA considers '0' as 'False' and all other integer values as 'True'. So if there are duplicates, the DCount piece evaluates as some number greater than 0 (the number of duplicate records), which the IIf function considers as a 'True' and then applies the condition for 'True' ("Check for duplicates")

For clarity, I personally would code it thus :

chktxt = IIf(DCount("[ID]", "tblData", "[fldName] =[txtName] And [fldSurname]=[txtSurname] And [fldBirthDate]=[txtBirthdate]")[COLOR=red][B]>0[/B][/COLOR], "Check for duplicates", "Good to go")

But for the reasons above, it will still work for you, so it's purely a question of how easily you will understand this code should you need to come back to it some time down the line.

Glad you were able to get it working anyway - nice feeling, innit??

Best of luck with the rest of it!

AOB