DLookup with option to enter text

[trstorer]

I have a data entry form with a field (casename) that automatically fills in using DLookup (from master table) when a user fills in the Case ID #. However, there are situations where there is not a Case ID # - in which case I want the user to be able to add the casename info manually and store the info into a field within that table (not the master table). [I realize this is not sound database design . . .but, this is the real world.]

In addition, when a user does an inquiry on the information (using a inquiry form), I want DLookup to work for the casename if a Case ID # exists, but if not, for the form to display the info manually entered and stored.

I assume I need to write code - any suggestions and where would the code be attached ?

Thank you !

 

[David R]

This is the real world, true. And I tried to do the same thing as you're asking once upon a time - I wanted to lookup a value if it existed, otherwise store a value in my primary table. The answer was "no way". Database design rules are there for a reason.

Is there a single CaseName that occurs when there is no CaseID? That is possible by adding in a default value (for example, autonumber 0). If not, how do you ever look up a case without a CaseID? If you want to add a new CaseID when the Case does not have one, that can be done with the NotInList event. In that case I would suggest using a combo box rather than Dlookup, it will run faster as well.

If you can give us a little more data perhaps we can offer an alternate way around your problem.

 

[trstorer]

David R. - thanks for your reply. I was afraid it might not work. I appreciate your suggestions. Generally, we aren't ever using the CaseId to find the information. Usually doing a keyword search in the "description of the document" field. Thanks again.