Append query versus error handling

[isladogs]

That will be helpful. In the meantime it sounds like you are using an unbound form.
Also that you are trying to add a new record to the many side of a join before a record is added to the parents' table on the one side of the join.

[Remster]

Image 1 shows the table relationships. Image 2 shows the form's record source (which is obviously a query and not a table, as I may have said or implied previously). Image 3 shows the input form, which is a subform (I assume it's intuitive enough which control is bound to which field from the record source). Image 4 shows the Append query that I'm provisionally using to add records to Assets via the Before Update event of the AssetRef field in the form.

The desired behaviour is that a new record is added to Assets if the value entered in the form isn't already there. What I'm provisionally doing achieves this, but possibly not in the best way.


Image 1: relationships




Image 2: record source




Image 3: input form ('Loans_Subform')




Image 4: Append query

[isladogs]

You didn't answer my questions in post #31 though your screenshots appear to indicate the answer to the first question is No and the second is Yes

Screenshots 1-3 are all fine. Screenshot 4 makes no sense whatsoever

Is the main form bound to the Assets table? How are the main & subform linked?
Why aren't you adding a record to the Assets table BEFORE adding data to the Loans table?

[Remster]

Sorry, I didn't take them to be questions. However, your interpretation ("No", "Yes") is correct.

Is the main form bound to the Assets table? How are the main & subform linked?

No, the main form is bound to a table called "Borrowers" and linked via the query field BorrowerID. It isn't relevant here.

Why aren't you adding a record to the Assets table BEFORE adding data to the Loans table?

I am. That's what the Append query is supposed to achieve:

Step 1. The user enters a value in the AssetRef field in

Loans_Subform.

Step 2. The Append query is run, adding the value to the Assets table if it isn't already there and ignoring it if it is.
Step 3. The Loans table is updated.

This saves the user from having to enter the value in a separate form before entering it again in Loans_Subform. The rationale for having the Assets table at all is that more information is recorded for an asset than just the asset reference (currently it's just the asset type, but this might change).

[isladogs]

Your append query still makes no sense to me.
Change the record source of the subform so the AssetRef is from the Assets table.
Then you may not need an append query at all … at least as written now

[Remster]

As in this? Then the fields from the Loans table become uneditable.

[isladogs]

Good point.
Scrap the current setup and use a form/subform arrangement to manage this
Something like this but in your case Loans at the top & Assets at the bottom. No append queries needed. Very simple to use



In your case, use AssetRef as parent/child field to link the 2 forms
That will enable you to use existing or add a new record to the Assets table in the main form then add as many related Loans records as you want.

Make sense?

[Remster]

It makes perfect sense! Unfortunately the form structure shown in image 3 is what I've been asked to produce, so I'm duty-bound to try and make it work. The Append query shown in image 4 certainly does the job, so the only question I'm left with is whether it's better than:

(a) using a subquery to exclude any matching values,
(b) running a separate Select query to establish first whether the value needs to be added and running an Append query only if it does,
(c) falling back on error handling, or
(d) some other option that preserves the form structure.

[isladogs]

The only way I can see that append query working with a syntax Forms!Form.Form... is if your main form is called "Form".
You may have been asked to create a particular structure but the end user will only care about whether it works, not the data structure.
You could still base the subform on the Assets table and use a value list combo for the Type combo or, if you must, use a DLookup.
Anyway, I'll leave it in your hands.

Good luck from here.

[Remster]

Big apologies, the working name of the main form is indeed "Forms" and not "Borrowers", which is more meaningful and probably what it will be called in the end. I wish I'd thought to mention that.

Thanks for your suggestions.

[Remster]

This won't impress the purist, but I've gone with the following, which seems to be the fastest option:

Private Sub AssetRef_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_AssetRef_BeforeUpdate

    CurrentDb.Execute "INSERT INTO Assets([AssetRef]) SELECT '" & UCase(Me!AssetRef.Value) & "';", dbFailOnError

Exit_AssetRef_BeforeUpdate:
    Exit Sub
Err_AssetRef_BeforeUpdate:
    If Err.Number <> 3022 Then MsgBox "(" & Err.Number & ") " & Err.Description
    Resume Exit_AssetRef_BeforeUpdate
End Sub

Error 3022 is 'The changes you requested to the table were not successful because they would create duplicate values ...'. Any comments as to why this is bad practice would be welcome.

[davegri]

This won't impress the purist, but I've gone with the following, which seems to be the fastest option:

That was my recommendation back in post# 11. The purist don't like using error recovery as a means to a desired and predictable end, but I vote for the practical.

[Remster]

Ah, so it was. I'd confused your suggestion with my own option 1. Yours was a version of my option 3.

I'd imagined that the error for option 3 would be 3101, but I've discovered that 3101 doesn't occur until it's too late to insert the new value into the table.