New Parent Record When Clicking on Subform

[Kraj]

I feel like there's any easy answer here but I can't find it via searching nor trial and error.

Quick version: I want to force an autonumber field on a parent form to start a new record when clicking on a subform, so that value can be passed to new records in the subform. Is there a simple way to do this?


Long version:

I'm using Access 2010. I have a parent form based on a query that does not have any fields intended for input but has an autonumber ID field. The subform is based on a query that finds records based on the ID field in the parent. When the parent ID field has a value it works perfectly, including adding new records in the subform. When the parent ID field is blank, though, I can add new records in the subform but the ID field does not populate.

I made a workaround by adding a Yes/No field to the parent and its query that users can click to create a new record, so the ID field has a value to pass to the subform. This works fine, but I feel like there should be a better way to do this without user clicks.

Via VBA, I've tried setting the parent form to dirty, and setting the Yes/No field to Yes, along with a variety of less likely candidates like refreshing and requerying. I've tried the above on various events including the parent LostFocus; the subform Got Focus, On Click, On Mouse Move; and the same events for control on the subform.

At this point I'm not sure whether the problem is my event choice, or the object method I'm using, or just the syntax for my control references. Or maybe this is actually more complicated than I think and sticking with the checkbox option is the best way to go. Any suggestions?

 

[jdraw]

An analogy to describe your situation is :

You are trying to add a "child", but you have not identified the "parent".
The subform represents a child record, the main form represents the parent. You must have a parent before you can identify a child of the parent.

Access will not add an autonumber value to a new record when you add a record(or attempt to, via the subform. You must explicitly create a new parent record before you can add a child record via the subform.

You need a mechanism to create that parent record before adding the child.

Good luck.

 

[CJ_London]

Perhaps your parent form has a recordsource which is not updateable or has not been updated - rationale - if you click on a subform this forces an update of the main form with a message along the lines of 'this record has changed, do you want to update?'. However this only happens if you have entered something into one of the fields - if you haven't then the record is not updated and an autonumber 'set'.

 

[Kraj]

Thanks for responding, but as I described in my post this works by adding a simple checkbox on the parent form; I just can't figure out a better way to do it. Seems sloppy to force the user to do clicks that don't really do anything. I guess the answer is there isn't one?

 

[CJ_London]

I guess the answer is there isn't one?

Correct - as previously advised, a child record cannot be created until its parent exists - just like nature

You could break all the db rules and not set a relationship between parent and child in the relationships screen and subform properties and then have some code of some sort that will ensure your parent and child record gets linked at some point - but a dangerous road to go down.

 

[jdraw]

I think the bottom line is that if you're on a subform -in a typical form/subform set up-- and you want to create a new parent record, then you need some mechanism (button, checkbox,....new form, popup....) to create the new record so that you can add related records via the subform.
Perhaps if you described your situation in business terms, there may be other options.

OOOps: I see CJ has responded with similar info.

 

[vbaInet]

OOOps: I see CJ has responded with similar info.

You two just need to stop following each other