New Record if Matching Record Not Found (1 Viewer)

[jacko6]

I've always been of the assumption that if you open a bound form (that allows additions) using linking criteria (eg DoCmd.OpenForm stDocName, , , stLinkCriteria) Access will:

Display the matching record(s) if the linking criteria was found.

or

Move to a new record, ready for data entry, if no matching record was found.

I've tested this by checking the value of me.newrecord and my assumption proves correct, but is there anything wrong with this approach?

 

[tvanstiphout]

Nothing wrong with that. You have my blessing.
If you wanted the new record to match the criteria by defaulting a few controls to those values, that is possible but would require some VBA.

 

[CJ_London]

think your interpretation of what is happening is slightly off

1. when a form opens, it moves to the first record, regardless
2. the 'new record' is always last.

If there are no existing records and the form allowadditions is true then the new record is effectively the 'first' record since there are no records before it.

 

[MajP]

but is there anything wrong with this approach?

No, but what is it you really want to do?
The criteria passes a Filter to the popup form and filters to that record/s. If nothing is found the filter still applied, but you can always add new.

How are you passing a link for something that does not exist?
The only way I can think of is that your are passing the Parent ID and the form should open to related child records. If there is no child records then you can add one. But in that case you want to relate the new record to the parent passed in the link by inserting parent ID as a foreign key in the new record. This is done using the before insert event of the popup form. You may want to provide more details if that is what you are trying to do.

 

[jacko6]

Thanks for the feedback everyone. I just wanted confirmation that I wasn't using an incorrect approach, rather than verbosely explaining my exact situation.

For clarity:
Parent form opens a popup form.
They have a 1to1 data relationship.
If child record is found by link criteria, it is displayed.
If child record is not found by link criteria the form is at a new record and the Parent ID is passed to the relevant foreign key field on the child form and a new record can be entered.

I'd be interested to know what different techniques others use for this common scenario.

 

[gemma-the-husky]

It would be wrong to say your popup form has a 1 to 1 relationship. The popup form in principle is 1 to many. Why do you think it should be 1 to 1?

 

[jacko6]

Not sure why you would suggest that. 1to1 or 1toMany depends on the design of the db.

 

[gemma-the-husky]

Not sure why you would suggest that. 1to1 or 1toMany depends on the design of the db.

if genuinely 1 to 1, there's no need for a second table. You can just put the fields in the main table.
Why do you want to restrict items in the second table to enforce a 1 to 1 relationship? There are some particular circumstances where it may be a good idea, but I doubt you would be enquiring if this was one of them.

You may think you have a one to one relationship, but it's more likely you haven't.

The fact you are referring to a foreign key is a case in point. I would have thought you would have the same PK value in each table with a 1 to 1 relationship, rather than a foreign key. A foreign key automatically allows for a 1 to many construct.

Rather than talk generalities, what are you modelling in your database?

 

[Pat Hartman]

If child record is not found by link criteria the form is at a new record and the Parent ID is passed to the relevant foreign key field on the child form and a new record can be entered.

You don't say how you are doing this so I will explain a "best" practice.

When I use the technique of opening a popup form for data entry in addition to using the WHERE option of the OpenForm method, I pass the FK value in the OpenArgs argument. Then, in the popup form's BeforeInsert event, I copy the OpenArgs value to the FK field on the form. This means that my code does not dirty the new record. When your code dirties the new or current record, you run the risk of creating "empty" records unless your validation code in the form's BeforeUpdate event prevents that. You can also end up giving the user strange (to his mind) error messages since he KNOWS that he didn't type anything so why are you complaining that he is trying to close the form without adding/saving a record?

I also use the model property of the new form to stop the code in the calling form at the point where the popup opens. That forces the user to close the popup before going back to the calling form. It also gives me a place to requery the main form to pick up any change made by the popup.

There are a couple of other acceptable methods such as setting the default property of the FK control on the popup to the value passed by the OpenArgs.

Both of these methods handle the situation where the popup might need to add more than one record. All records added will have the FK properly populated but not until it is necessary.

I also agree with @gemma-the-husky 's point about the 1-1 relationship. It is quite unlikely that you actually have one and it is probably better to put the data into the main table.

 

[jacko6]

Thanks for the feedback everyone.