DLookup Help

[d9pierce1]

Hi JojoWhite,
I figured it out, lost some code in the process but put it back in and all works like a charm!
Again,
Thank you very much....
Dave

[Ken Sheridan]

I dont want it to be able to duplicate the CatName if the CatTypeID is = the CatTypeID on my form. If CatTypeID is different, then I want it to add

If I understand you correctly isn't the solution much simpler? Don't you simply need to include the CatName and CatTypeID columns in a single unique interest? If you then try to insert a row with values which are already present in another row in the table, the index will be violated and you can handle the error in the form's Error event procedure. Otherwise the values will be accepted.

If you are not familiar with creating an index on multiple columns this is done by first selecting Indexes on the table Design ribbon. In the dialogue which opens enter a suitable name for the index in the first empty row of the Index name column, CatTypeCatName say. In the Field Name column of the same row enter CatTypeID, and with this row still selected, in the Index Properties below, select 'Yes' as the Unique property. Then enter CatName in the Field Name column in the row immediately beneath CatTypeID. Don't enter anything in the Index name column for this row.

Having created the index, deliberately generate the error by entering two values you know to already exist in another row. The will enable you to identify the error number. In the Error event procedure, if the dataerr number equals that you've identified call the MsgBox function to inform the user and set the return value of the Response argument to acDataErrContinue.