Need to override where Form field stores data if duplicate value

[danyd]

I have a Claim table and an Estimate table. The Claim table has a key field called Claim_Number, and the Estimate table has a linked child field with the same name, Claim_Number.

My Form is based on the following query:

SELECT ESTIMATE.ESTIMATE_ID, ESTIMATE.CLAIM_NUMBER, CLAIM.CLAIM_NUMBER AS Claim FROM ESTIMATE INNER JOIN CLAIM ON ESTIMATE.CLAIM_NUMBER = CLAIM.CLAIM_NUMBER;

The form field Limit To List is set to No. In the field, I need to be able to add a new number to the Claim table and also to the Estimate table (which it does, if it’s a new number), but if it’s a number that already exists in the Claim table, then I need the data added only to the Estimate table.



I can’t have the extra step of having to first add the number to the Claim table before it can be used in the Estimate table.

I hope this makes sense… It seems like this should be simple, but I’m so stuck on this. I don’t really know VBS, but really need the help. Thank you.

[June7]

I don't understand.

Is Claim table the parent and Estimate is related child? Then can't have record in Estimate without record in Claim.

Can each claim have more than one estimate?

Are you using form/subform arrangement? That is conventional approach for parent/child data entry.

[danyd]

Thank you so much for responding. Yes, the Claim table is the parent and the Estimate table is the child.
It's correct that I only want to have data in the Estimate table if it's also in the Claim table.
It's correct that each claim can have more than one estimate.
I just don't want to have to have a separate form to first enter the claim in the Claim table before it can be used in the Estimate table.
It's not a form/subform arrangement. I just have the continuous form based on a query where the two tables are linked. I think you've already given me something to work with. I'll have to check it out tomorrow.
Thank you again and I'll let you know tomorrow how it goes.

[danyd]

After posting my reply, I realized that the reason I didn't do a form/subform arrangement is because I want a continuous form.
I think what's going on, is that my form is really a subform since I want to display/add/edit all the Estimate(the child) data and need to view it in continuous form. At the same time, if there's a new Claim_Number to add, then I would like it to automatically add to the Claim(the parent) table. Is there a way to do this?
Thanks

[June7]

Main form bound to Claim. Subform bound to Estimate. Subform can be in Continuous view (or Single or Datasheet). The main form would be in Single view. There is a trick to allow both to be subforms and still related if that's what you want.

[danyd]

I eliminated the parent table - it really wasn't necessary. that solved the problem. Thank you so much, dany.