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

[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

 

[Pat Hartman]

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

 

[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

 

[Pat Hartman]

but why you think it's impossible the other way?

In a relational database, the "child" table includes a field called a Foreign Key. That field contains the value of the PK of the parent. THAT is what is used to relate the two tables when you create queries to join the two tables and also to enforce referential integrity. If you don't insert the parent record first, you have no value with which to populate the FK and therefore, no way to relate the two tables. You also cannot enforce referential integrity and so the database engine can't do the work of enforcing the relationship. YOU must, using code, do the work of the database engine. Pretty silly if you ask me.

In an Access application, when you use bound forms, you specify the master/child relationship and when the child record is inserted, Access automatically populates the FK for you based on the information you provided.

You can do it the other way but only by NOT defining a proper relation between the two tables. That also leaves Access unable to help you and so you would somehow have to figure out which orphaned child records belong to which parent records and populate the FK yourself.

Don't do it! Relational database rules are there for a reason. But, if you are asking this question at all, it could be because you have defined your relationships backwards so you might want to look into them more closely.

 

[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.

 

[Pat Hartman]

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.

Sorry @KitaYama I did read your comment. Please feel free to do whatever wrong things you choose to do for yourself but don't try to "sell" them to people who know better and also please don't confuse those who don't know any better. You are doing them no favor by giving them bad advice.

Once you dirty the main form, Access will save a record unless you stop Access from doing so. If the parent record gets saved, then you have NOT inserted the child before the parent. You have simply fooled yourself. All you've done is violate one of the most important concepts of relationships and that is to save a record that contains nothing except the primary key. If you do stop Access from saving the record, then the PK won't be committed and therefore, you won't be able to use it as a FK if you have actually defined a relationship. So, NO, unless you do NOT define a relationship and enforce RI - because what is the point of defining a relationship if you are not going to enforce RI - you cannot insert the child record before the parent record because that's just the way RI works.

There may not be a circumstance at all. Op asked for this.

In addition to loading the bullets into his gun, are you also going to help him to point it at his head to assist him with his suicide?