Before Dcount update to check value doesnt already exist

[chrisjames25]

Hi. Previously had some help on here regarding checking a value didnt exist prior to updating the filed so i could populate my own message rather than the required field message.

If DCount("genus", "Tbl_genus", "genus='" & Txt_NewGenus & "'") > 0 Then

    MsgBox "The genus already exsits!!!", vbOKOnly
    Cancel = True
    Me.Txt_NewGenus.Undo
    Me.Txt_ConfirmGenus.Enabled = False

Exit Sub

Above is code we got to.

Question is this. Within the Tbl_Genus table there is a column called category. What i need to test for is that the genus i am typing doesnt already exist within the chosen category rather than within the whole table.

IS this possible by tweaking the dcount or would i need to create a qry and run a dcount on that as opposed to the table and use the category input as a criteria within the qry?

 

[Minty]

Add the extra criteria to your DCount

If DCount("genus", "Tbl_genus", "genus='" & Txt_NewGenus & "' AND [CategoryField] = '" & Me.YourCategoryControl & "'") > 0 Then

 

[chrisjames25]

Cheer Minty

I keep getting a datatype mismatch error. Anything stupid i have done that you can see.

Not sure if it matters that im pulling the seoncd criteria form a combobox?

If DCount("genus", "Tbl_genus", "genus='" & Txt_NewGenus & "' AND Category_ID='" & Me.Cbo_Category & "'") > 0 Then

 

[Minty]

Is category a number (Not what you display but the bound column value), assuming it is change it to

If DCount("genus", "Tbl_genus", "genus='" & Txt_NewGenus & "' AND Category_ID= " & Me.Cbo_Category ) > 0 Then

 

[chrisjames25]

Works like a charm. Many thanks Minty for this assistance and all the assistance beforehand.

 

[chrisjames25]

Minty one quick follow up

If DCount("genus", "Tbl_genus", "genus='" & Txt_NewGenus & "'") > 0 Then

    MsgBox "The genus already exsits!!!", vbOKOnly
    Cancel = True
    Me.Txt_NewGenus.Undo
    Me.Txt_ConfirmGenus.Enabled = False

Exit Sub

THe code i have works in before update event. QUick question. is there a way to tweak the code so that the textbox goes back to contianing no value in it rather than highlighting the text already inputted if it throws up a dcount value. Tried the obvious like

me.Me.Txt_NewGenus.text = ""

But getting an error relating to the before update event.

 

[Minty]

Try Me.txt_NewGenus = Null

 

[chrisjames25]

Sadly not. Keeps throwing the attached error message

 

[Minty]

Which line does it highlight in your code? I would have thought that Me.Undo would revert all attempted changes?

 

[chrisjames25]

It flags issue when add in the me.txt = null.

If i type in Coke in the textbox and it already exists in the table it lets me know it exists but the me.txt.undo simply highlights coke in the txtbox rather than deleting it. The problem with this is then when i hit tab again there has been no change so before update isnt triggered so it is allowing me to put in a duplicate.

I should say the textbox is unbound and the info only gets transferred to the table when i click a cmd button so should i switch this to afterupdate event?

 

[Minty]

If it's unbound - it can't undo. That is why it simply highlights it I'm guessing.

If the changes you are making are all in unbound controls then, yes I think it won't matter moving it to the after update. In fact the forms before update event cancel won't work on unbound controls, as they aren't linked to a recordset.

 

[chrisjames25]

ah ok. that explains it then. I think when i first got help on dcount i was using bound controls and then i switched to unbound with a later validation on click of button so just kept previous code so will play with afterupdate now.