Append query versus error handling

[Remster]

I have a form field that's intended to behave like a combobox without the dropdown list. (If this sounds bizarre to you, please just trust me that it makes sense in the context.) What's the best way to handle additions to the "rowsource"? I can think of three options:

1. An Append query that runs every time data is entered in the field, but skips over values that are already in the "rowsource".
2. An Append query that runs depending on the results of a Select query that checks whether the value is already in the "rowsource".
3. An Append query that runs in response to the data error that occurs when a value isn't in the "rowsource".

There may be nothing to choose between these options, but, on the one hand, option 3 seems likely to me to be the quickest, while, on the other hand, I've seen people frown on using error handling in this kind of way.

[June7]

So how do you have a combobox that doesn't have dropdown?

Why would there be an error to handle?

Combobox has NotInList event which is intended for when combobox is set as LimitToList. This is where user can be given option to add new value or correct their input.

[Remster]

It's not a combobox. It's a regular text box that behaves like a combobox in certain respects. I've described it like this to try and keep the post to a reasonable length for the reader. What I'm trying to do, in effect, is simulate acDataErrAdded where the rowsource is a table.

[Remster]

PS I'll go into detail if it's the only way to get an answer!

[June7]

Hard to advise when I don't understand the setup. But it seems some code must run with every input so, as you said, don't really see much difference between the options. Choose what you are most comfortable with and when you encounter an actual code issue, post question.

[Remster]

That's fair enough. Below is my stab at a proper explanation.

The text box in question is bound to a foreign key in Table 1. Where the value entered is missing from the related primary key in Table 2, I want to know the best way of determining this (i.e. the one that isn't frowned on):

1. Through an Append query that ignores the value if it's already in Table 2.
2. Through a Select query that determines whether the value is already in Table 2.
3. Through error handling.

The next step is to add the value to Table 2 if it is missing. Does that tell you enough?

[June7]

You have Enforce Referential Integrity set in Relationship builder? So there is no 'RowSource' mimicry, just referential integrity restricting input to foreign key field?

I usually hold with the view that error handler should not be relied on for normal processing but will admit to using "On Error Resume Next" a few times.

In this case maybe error handler it would be more efficient than having to run code on every input.

[Remster]

Yes, you have it right. The combobox analogy turns out to have been a red herring.

Now, you say "I usually hold with the view that error handler should not be relied on for normal processing". That's precisely the sort of thing I'm talking about. Why is that? I presume using acDataErrAdded is a kind of error handling, but that seems respectable enough.

[June7]

That is part of the combobox NotInList event code which is intended to handle situation of user entering a value not in list and you want to give them option to add to source table or correct their input.

So is there any code that is already run with every record input such as a "Save and go to new record" button? Validating the key input would not be much more overhead. A DLookup could accomplish that.

[Remster]

That is part of the combobox NotInList event code which is intended to handle situation of user entering a value not in list and you want to give them option to add to source table or correct their input.

That kind of justifies my combobox analogy, then, because what I'm trying to handle is a situation in which the user enters a value not in Table 2 (the table in which the field in question is the primary key) so that the value is automatically added.

Here's a bit more information. My database is for recording loans of assets to borrowers. BorrowersForm is the main form, and LoansForm is a subform. The record source for LoansForm is LoansQuery, which joins LoansTable to AssetsTable. AssetRef is a foreign key in LoansTable and the primary key in AssetsTable. The controls of interest in LoansForm are bound to the following fields in LoansQuery:

LoansForm.AssetRef: LoansTable.AssetRef
LoansForm.AssetInfo: AssetsTable.AssetInfo
LoansForm.DateIssued: LoansTable.DateIssued

Now, when a user enters a value in LoansForm.AssetRef, I want a new record to be added to AssetsTable.AssetRef if and only if there isn't already one containing that value. I could do that with NotInList and acDataErrAdded if LoansForm.AssetRef was a combobox, but it isn't and isn't going to be. That's why I'm looking for the most efficient way of simulating NotInList and acDataErrAdded for an ordinary text box, but since the database doesn't yet contain more than a few test records, I can't tell just by testing what the most efficient method will be.

No code runs to save the record. However, I've heard that DLookup can be super slow. Is that not right?

[davegri]

I would use the append query. If it's a duplicate the error generated is 3022. then in the error handler:

Select case err
    case 3022
        resume next
    case else ...

[Remster]

You mean my option 1? And you think that won't be significantly slower than option 3 once I have a table full of records?

If you use a saved Append query, you don't need error handling, as Access will skip a duplicate - you get a warning to that effect if you haven't disabled them.

[davegri]

You mean my option 1? And you think that won't be significantly slower than option 3 once I have a table full of records?

If you use a saved Append query, you don't need error handling, as Access will skip a duplicate - you get a warning to that effect if you haven't disabled them.

To add one record? Should happen in milliseconds either way.

[Remster]

To add one record? Should happen in milliseconds either way.

The adding, yes, but it also has to check that there isn't already one there. That's the point at which I'm wondering what's the quickest method: a saved query, error handling, or (now) DLookup.

[davegri]

The adding, yes, but it also has to check that there isn't already one there.

I wouldn't bother with a dlookup. Method 1 would determine whether duplicates exist and ignore them or go ahead and insert them if not. It would work for DOZENS, even HUNDREDS of records in milliseconds. SQL is fast!