I have a city table and form that allows you to add a new city to keep growing the list on the fly. I have a button that is to save and close the form after you add a city name. It does have an error event that tells in you in length a confusing message for the error. I don't want to change this error event because I want it to still pop up if there is a different error. because it seems this is an all purpose error handler.

I am new at macros and I can't seem to figure out the argument that will check my CityName text box and see if its a duplicate and then give me a message box saying this city is already entered. Go back and try again. Is there a quick and easy way to do this. I want to use a macro because I don't know anything about vba code and the macro is already started for the save and close button.

Any help would be greatly appreciated. I have guessed at this for about two hours now and I am about to give up.

Thanks
Kathy

Hi Kathy,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

The usual way I handle duplicate checks in code is by using the DCount() function. You might try to see if you can also use it in a macro.

Just my 2 cents...

Sorry. Thought I checked that. It's 2007

No worries. Just for future reference, if you "preview" your post before submitting it, you will have to select the version number again or it will not show up.

Cheers

I can't use the DCount for this. I had the error code = to 3022. And that seemed to work, but then I got the other error box also. Then I didn't same my changes and now I can't remember I set that up to start with. I decided to use a city table because in looking over the spreadsheet that is currently used by the company, I see that city are frequently misspelled so hopefully using a look up box will eliminate that. I have the field set to Index no duplicates in the the table. I just wanted a better error message specifically for the duplicate entry error. Can this be done with a quick macro error event?

Thank you.

Kathy

Hi Kathy,

Why did you say you can't use DCount()? You should be able to.

Try the following:

1. In the BeforeUpdate event of the Textbox, click on the Build button and select Macro Builder
2. When the macro design window opens, make sure to show the Condition column
3. Enter the following as the Condition:


4. For Actions, use the MsgBox
5. In the next row, enter "..." (without the quotes) in the Condition column
6. For Actions, use CancelEvent
7. Close the Macro builder and save the changes

Just my 2 cents...

Dear DBGuy,

I said I couldn't because I put it in the wrong place. Your instructions are working perfectly. I wish I had you on call to help me with these seemly simple things that I just can't seem to get unstuck from. LOL. I have read over 1000 pages in 7 days and just when I think I know what I am doing....I do not.


I'll Keep plugging away....especially thankful for this great site and all the help.

Kathy

Oh...I was trying to put in the on click event of my save and close button and in the action column in case you are curious as to where I went wrong.

Hi Kathy,

Glad to hear you got it to work.


That can be arranged. I've been known to answer the phone on occasions.





We are all happy to help. Good luck with your project.