i want the autonumber field in the main form to be automatically populated even when you add data in the subform first

[AHMEDRASHED]

Hi every one
want the Autonumber field in the main form to be populated even when you add data in the subform first and have the ability to save the main form without losing the entered data in the subform.

I have a main form called "TBLinv" (parent),
and I have multiple subforms: "tblDescription" (child), "tblNote" (child), "tblBillTKT" (child), and "tblSelling" (child).
Each subform is related to the main form using a foreign key.

 

[ebs17]

Correctly set referential integrity means that a record with a primary key must first be created and stored in the primary table before this key can be used as a foreign key in data records in secondary tables. This also applies to implementation in bound forms.

So your wish cannot be implemented.

 

[The_Doc_Man]

Eberhard is absolutely correct. I will say this another way simply to clarify.

When speaking of sub-forms feeding child tables, you are creating a parent/child relationship. When you enable referential integrity, the rule is that you cannot have a child unless there is a parent to go with it and the parent must come first. Access will not create orphans.

 

[mike60smart]

Hi every one
want the Autonumber field in the main form to be populated even when you add data in the subform first and have the ability to save the main form without losing the entered data in the subform.

I have a main form called "TBLinv" (parent),
and I have multiple subforms: "tblDescription" (child), "tblNote" (child), "tblBillTKT" (child), and "tblSelling" (child).
Each subform is related to the main form using a foreign key.



View attachment 108176

View attachment 108179

Hi Ahmed

In addition to what everyone else has said I have the following observations:-

1. You should not use Date as a Fieldname as this is a Reserved Word in Access.
2. You should not use Lookup Fields in Access Tables.
3. You should give your Autonumber Fields a proper name instead of using Just "ID". It shoulkd reflect the name of the table
e.g. tblCustomers should have an Autonumber Fieldname of CustomerID vice ID.
This applies to all of your tables.
4. You should not use any Spaces in Fieldnames.

 

[AHMEDRASHED]

Correctly set referential integrity means that a record with a primary key must first be created and stored in the primary table before this key can be used as a foreign key in data records in secondary tables. This also applies to implementation in bound forms.

So your wish cannot be implemented.

Thank you for your clarifications
I got it ,

 

[AHMEDRASHED]

Eberhard is absolutely correct. I will say this another way simply to clarify.

When speaking of sub-forms feeding child tables, you are creating a parent/child relationship. When you enable referential integrity, the rule is that you cannot have a child unless there is a parent to go with it and the parent must come first. Access will not create orphans.

@The_Doc_Man
Thank you for your clarifications

 

[AHMEDRASHED]

Hi Ahmed

In addition to what everyone else has said I have the following observations:-

1. You should not use Date as a Fieldname as this is a Reserved Word in Access.
2. You should not use Lookup Fields in Access Tables.
3. You should give your Autonumber Fields a proper name instead of using Just "ID". It shoulkd reflect the name of the table
e.g. tblCustomers should have an Autonumber Fieldname of CustomerID vice ID.
This applies to all of your tables.
4. You should not use any Spaces in Fieldnames.

Hello Mr.Mike @mike60smart
I hope you're well. Thank you for your advice , i appreciate it

 

[KitaYama]

So your wish cannot be implemented.

I know that it's the wrong way to do things and data input in parent comes first, but why you think it's impossible the other way?
You can dirty parent form from before-insert of the child and get the necessary FK for the child form.
As soon as the parent get dirty, all the default values for the fields are retrieved.

Private Sub Form_BeforeInsert(Cancel As Integer)   
    With Me.Parent
        .field1 = ""
        .Dirty = False
    End With
End Sub

 

[AHMEDRASHED]

No record should be saved with ONLY a pk.

The proper solution is to prevent entry into subforms prior to creating a parent record. The method is simple. In the BeforeInsert event of the subform, check for the presence of an ID in the parent record. If it is null, cancel the update and backout the update (one character) to the subform.
This sample uses a state as the parent record and cities as the child records.

Private Sub Form_BeforeInsert(Cancel As Integer)
    If IsNull(Me.Parent!StateID) Then
        MsgBox "Please add a State before entering a City.", vbOKOnly
        Cancel = True
        Me.Undo
        Me.Parent!StateAbbr.SetFocus
        Exit Sub
    End If
End Sub

its good idea , work perfect , Thank you Mr. @Pat Hartman

 

[AHMEDRASHED]

I know that it's the wrong way to do things and data input in parent comes first, but why you think it's impossible the other way?
You can dirty parent form from before-insert of the child and get the necessary FK for the child form.
As soon as the parent get dirty, all the default values for the fields are retrieved.

Private Sub Form_BeforeInsert(Cancel As Integer)  
    With Me.Parent
        .field1 = ""
        .Dirty = False
    End With
End Sub

Thank you @ebs17 , its work now

 

[KitaYama]

@Pat Hartman Thanks for the extended description on relationship, but I wish you could read my whole comment.
If you reply to my comment, Please don't cut out its most important section. It ruins the whole intent of the comment.
Since it seems you don't read comments carefully and have missed the main part, once again from my post:

I know that it's the wrong way to do things and data input in parent comes first, but why you think it's impossible the other way?
You can dirty parent form from before-insert of the child and get the necessary FK for the child form.
As soon as the parent get dirty, all the default values for the fields are retrieved.

Somebody said it's impossible to start from child and I explained : No, it's possible. But it's not the correct way.

You can do it the other way but only by NOT defining a proper relation between the two tables.

Again wrong. Even with a proper relationship, you can do it the other way. Test it. Just dirty parent form and un-dirty it in before-Insert event of the child form.
And once more : I know it's the wrong way. I'm just saying it's possible.

Don't do it!

I don't. I won't and I've never done it. Thanks for the warning. I was just saying it's possible. Being possible doesn't mean being correct.

With regards.

 

[The_Doc_Man]

@Pat Hartman Thanks for the extended description on relationship, but I wish you could read my whole comment.
If you reply to my comment, Please don't cut out its most important section. It ruins the whole intent it's been commented on.
Since it seems you don't read comments carefully and have missed the main part, once again from my post:



Somebody said it's impossible to start from child and I explained : No, it's possible. But it's not the correct way.


Again wrong. Even with a proper relationship, you can do it the other way. Test it. Just dirty parent form and un-dirty it in before-Insert event of the child form.
And once more : I know it's the wrong way. I'm just saying it's possible.


I don't. I won't and I never have done it. Thanks for the warning. I was just saying it's possible. Being possible doesn't mean being correct.

With regards.

Specifically, if an autonumber is involved, dirtying and then undirtying consumes one step of the autonumber. Therefore if you dirty the form and then undirty it by saving, no problem. But if you undirty it by an UnDo, in the interval just before that action you have copied an autonumber that will not appear in the parent table when you are done. Yes, you can do this in the wrong order, but the key field (pun at least slightly intended) could be changed depending on how you chose to do this. So @KitaYama you are quite right in that you could do this the wrong way and it might work - but depending on exact choice of method for undirtying the form, it might also break. I think that a theoretical discussion of alternate methods is good but I also think at least one caveat is appropriate to show that there are viable and non-viable ways to do it backwards.

 

[KitaYama]

But if you undirty it by an UnDo, in the interval just before that action you have copied an autonumber that will not appear in the parent table when you are done.................but depending on exact choice of method for undirtying the form, it might also break.

@The_Doc_Man Thanks for your comment. But I think we have a communicating problem.
I don't know how the type of UnDo you're talking about comes into this.

We are talking about the possibility of starting from child form (the wrong way. Yes it's wrong. But again we are talking about the possibilities)

A database is attached. When you open the database, a form with parent/child link is opened.
Start entering data in field1 of the subform. (Without entering data in main form)

When you're done, I'd be glad to hear what the type of UnDo you're talking about enterfers.

Thanks.

 

[Josef P.]

[A bit OT, but mentioned anyway as a preacher for encapsulated code ]

Code in example 'Database4'

frmChild (used as subform in frmParent:

Private Sub Form_BeforeInsert(Cancel As Integer)

    With Me.Parent
        .RegisteredOn = Now
        .Dirty = False
    End With

End Sub

This code makes the subform dependent on the form 'frmParent' , since it uses the data field 'RegisteredOn'.

Alternative:
Control the creation from the main form, because it knows its own controls.
Also, this makes it the responsibility of the main form to decide if it will allow the new record to be created.

Private Const EventProcedureTag As String = "[Event Procedure]"
Private WithEvents m_ChildForm As Form

Private Sub m_ChildForm_BeforeInsert(Cancel As Integer)
   If Me.NewRecord Then
   If IsNull(Me.ParentPK.Value) Then
      Me.RegisteredOn.Value = Now()
      Me.Dirty = False
   End If
   End If
End Sub

Private Sub SetChildFormRef(ByVal sfr As SubForm)
   Set m_ChildForm = sfr.Form
   If m_ChildForm.BeforeInsert <> EventProcedureTag Then
      m_ChildForm.BeforeInsert = EventProcedureTag
   End If
End Sub

Private Sub frmChild_Enter()
   SetChildFormRef Me.frmChild
End Sub

Private Sub frmChild_Exit(Cancel As Integer)
   Set m_ChildForm = Nothing
End Sub

You can then use this code for multiple subforms.

Private Sub AnotherSubform_Enter()
   SetChildFormRef Me.AnotherSubform
End Sub

Private Sub AnotherSubform_Exit(Cancel As Integer)
   Set m_ChildForm = Nothing
End Sub

 

[ebs17]

    With Me.Parent
        .RegisteredOn = Now
        .Dirty = False
    End With

This creates or edits a record in the main form / in the primary table and saves it in the table.
Only then can the record from the subform be saved.

Here only a usual input by hand is replaced by an input by code. Nothing changes in principle. The RI achieves what it is there for.
Without saving the primary key (without Me.Dirty = False) your construction fails.

Now you could have a wordy and varied discussion about what INPUT means. The RI doesn't care. The primary key is stored in the table, only then does it continue. It doesn't matter which way (manual input in table, manual input in bound form, form input with code support, query, recordset action, import).
That a stored value is preserved is then evident.

A presumably meaningless value is used to create the record for the primary table. You would have to think twice about the meaning of this.

In practice, you need real content for the record in the main form (attribute as the task of the record). Where would something like that fly from? We don't even want to talk about mandatory fields.

 

[gemma-the-husky]

I think the real underlying point is that we have a relational database. If you could create a subform (therefore create a child record) before you create the entry of the main form(the parent record) then you undermine the relational nature of the data, and potentially have an orphan record. It's much better to design the database to maintain the integrity of the data, and just prevent orphans occurring.

Thus, no, you can't correctly create a child record before you create the parent. I can't see a circumstance where you would want to do this, to be honest.

 

[KitaYama]

........and potentially have an orphan record........Thus, no, you can't correctly create a child record before you create the parent.

Did you test the database in #15?

I can't see a circumstance where you would want to do this, to be honest.

There may not be a circumstance at all. Op asked for this. You can ask him the question.
But after all we are talking about possibilities not necessities.


I don't know how many times I should say I'm not talking about this method being correct.
OP asked if it's possible to start entering data in sub form first.
Someone said it's impossible and I said/showed it's possible. Josef showed a better and clearer way.

I give up. I'm wasting my time on this thread.
You guys win.
I'm out.

 

[gemma-the-husky]

Did you test the database in #15?


There may not be a circumstance at all. Op asked for this. You can ask him the question.
But after all we are talking about possibilities not necessities.


I don't know how many times I should say I'm not talking about this method being correct.
OP asked if it's possible to start entering data in sub form first.
Someone said it's impossible and I said/showed it's possible. Josef showed a better and clearer way.

I give up. I'm wasting my time on this thread.
You guys win.
I'm out.

I don't want to examine a database to see if there is an artificial way to achieve this. I agree with Pat that it's both bad practice and can't actually be done. Why would you even want to try to create a subform record without having created the master record? The OP ought to see that what he is trying to do is not really good practice, and that there is likely to be a better way of managing his data.

An experienced developer such as yourself would never do this, and I would have thought it was better to explain why it's a bad idea than to try to provide a questionable work round.

 

[The_Doc_Man]

I will stand with KitaYama that - as KY clearly stated - some things are possible even when done the wrong way. But I will also stand with the others who point out that doing things the wrong way is a path fraught with peril leading to disasters. You CAN do things the wrong way. Murphy's Law DOES DEFINITELY apply to programming. And with some ingenuity, you might even make it seem like it is working. But doing things the wrong way often come back to bite you, which is why we advise against doing anything the wrong way. Some of us will tell people "Don't Do That" with such authority that we seem to be denigrating their abilities. That happens because we don't always have the time or energy to fully explain WHY their suggested method isn't going to work.

Among other things, the timing becomes tricky if you work backwards since Access will do things behind the scenes each time you do an EXIT SUB from an event routine. Approaching from the wrong direction, you will surely end up doing more work than you might wish to do. I.e. you add extra steps which, in software engineering terms, means you add extra points of failure, thus increasing your overall risk of failure. You negate the benefits of actions that Access would normally do for you silently behind the scenes. You run the risk of breaking or mis-directing the relationships. The last two factors are actually a lot more critical than the others, since you are dealing with a relational database and are bypassing the database's built-in management of relationships.

@AHMEDRASHED - as you have seen from all this controversy in your thread, the consensus is that doing it the way you originally suggested is a choice we would not advise. You do better to rethink your process in a way that lets Access work with you, not against you. But it isn't actually Access that is causing any issues. It is the inherent nature and the requirements of relational databases in general. Access isn't the only database tool that imposes limits on how you approach relationships.