DCount to find existing records

[Design by Sue]

I am trying to write code to detect if there are related records in a table to the item selected on a form. I am trying to use DCount but must have an error in my code. Please Please help.

Here is my code:

If DCount("[Shade Type]", "[Item Master TBL]", "[Shade Type]" = "Me.Shade_Type") > 0 Then

MsgBox "You cannot delete this type because it is used in the database.", vbExplination, "Cannot Delete"
Exit Sub
End If

Shade Type is the column in Item Master TBL where I am trying to find the related records. My criteria (I am trying to make) is where Shade Type in the table matches the Shade Type on the open form. When there are more than none, I want the message box to appear.

Please if someone can help me I would be so grateful - this has taken a day to get this far and I still am nowhere.

Sue

 

[Addyman]

If DCount("[Shade Type]", "[Item Master TBL]", "[Shade Type] = " & Me.Shade_Type) > 0 Then

This assumes [Shade Type] is a numeric field. If it is text then try:

If DCount("[Shade Type]", "[Item Master TBL]", "[Shade Type] = '" & Me.Shade_Type & "'") > 0 Then

 

[Design by Sue]

I LOVE you!!!!! that was it. Thank you so much for responding so quickly.

I only know enough about programming access to be dangerous.

Sue

 

[Addyman]

No problem Sue, glad I could help

 

[Design by Sue]

OK - I'm back and begging again. What you gave me did work. I know it did. I then closed Access to work on some other things I needed to get done, and now that I opened it again, it is broken. Now no matter what item I select, even it is is not used in the table, I get the message box.

What the heck am I doing wrong?

Sue

 

[Design by Sue]

Now it is working again - I am so frustrated. Please ignore this until I get stuck again!

Appreciate your patience.

Sue