When the source record for an unbound control on a form using a DLookup is deleted, it causes a flashing #Error. Is their any protection against this?
Thanks for any comments.
When the source record for an unbound control on a form using a DLookup is deleted, it causes a flashing #Error. Is their any protection against this?
Thanks for any comments.
That's going to happen, if Access cannot evaluate the expression (in this case the DLookup function) used as Control Source. You haven't given much in the way of exactly what you're doing, here, but the only thing I can think of, off the top of my head, would be to
- Add a Field to the underlying Table
- Bind it to the currently Unbound Control
- Use the DLookup as the Default Value, instead of as the Control Source.
Maybe someone else will have a better idea!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
Thanks for the suggestion. The timing is wrong to use the default value. I open the main data entry form, then open another form while that form is open to enter the data I need to look up, then close that form, back to the main data entry form.
Is there anyway I can test evaluating the expression, and only go ahead and evaluate if I know it is there?
I have many users of this new system. I have 2 different tables that essentially have the same data in. So instead of asking people to add the same data to both tables, I want to copy the contents of one table to the other table. In so doing, I will be changing data that has already been used in previous records. This will cause the #error message. The whole system goes haywire at this stage, the #error message flashes and nothing works, so I have to close the database. So I hope their is a way to skin this cat!
>>I have 2 different tables that essentially have the same data in.
>> I want to copy the contents of one table to the other table.
This looks like a headache waiting to happen. Are the tables normalized? This is the first step in creating forms which work well.
What is the purpose of the two tables? Can you provide a little of what you are trying to accomplish with the tables?
After the fact, I realized that the 2 tables essentially contain the same people. No one wants to do the double the data entry. So I want one table to be the feed to both sources of data. So at ay one time, a person in the table can be either a or b in the transaction.
I solved the problem with #Error by putting the DLookup in an IIf statement. I said IIf IsNull Dlookup, 0, Dlookup. If the value cannot be evaluated I get a 0 which is fine. They can just choose another value which is there.