Dcount add occurence

[chrisjames25]

Hi, i have a textbox (Txt_Newtier3) and in the afterupdate event i check whether the name i entered into the textbox already exists in the bound table using a dcount.

To clarify i can enter the same name in the textbox as long as the parent name is different. For example, i could have 2 brands of tv Sony and LG (parent name) and in the (child name) for Sony i could write 32 inch screen even if a 32 inch screen did exist for LG already. What my dcount will stop is me entering a duplicate 32 inch screen for sony if one is already entered in the table.

HEre is my code which works fine:

Field = StrTable
        source = "Tbl_" & Field
        criteria = Field & "= '" & Txt_NewTier3 & "'AND " & StrTier2 & "_ID= " & Me.Cbo_Tier2
        
        If DCount(Field, source, criteria) > 0 Then

THe problem has arisen when a name i need to put in the child textbox is Soleil D'Or . What appears to happen when i type this in is that because of the " ' " in the name and potentially because of the "or" letters after it it is changing how my code is read.

Any ideas how to combat this?

CHeers

 

[arnelgp]

Change the criteria ti:

criteria = Field & "= " & chr(34) & Txt_NewTier3 & chr(34) & "AND " & StrTier2 & "_ID= " & Me.Cbo_Tier2

 

[chrisjames25]

Nailed another for me Arnel.

Many thanks