Create record on opening

  • Thread starter Thread starter Dudley
  • Start date Start date
D

Dudley

When I open a form with a sub-sub-form, I have code 'On Open' to go to the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form but not
in the subform, so I would also like the code to create and save a record in
the subform (if one does not already exist) with default data, and I have not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
If a new sub-sub-form record is created, how is Access supposed to know who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP
 
The form is only opened by command buttons from other forms with code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

Jeff Boyce said:
If a new sub-sub-form record is created, how is Access supposed to know who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

Dudley said:
When I open a form with a sub-sub-form, I have code 'On Open' to go to the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form but not
in the subform, so I would also like the code to create and save a record
in
the subform (if one does not already exist) with default data, and I have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
Dudley

I was referring to the parent and grandparent records, not the sub-form and
form within which your sub-sub-form was embedded.

But perhaps I've assumed (?in error) that your use of main
form/sub-form/sub-sub-form reflects an underlying grandparent/parent/child
relationship among the tables behind your forms...

Regards

Jeff Boyce
Microsoft Access MVP


Dudley said:
The form is only opened by command buttons from other forms with code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

Jeff Boyce said:
If a new sub-sub-form record is created, how is Access supposed to know
who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

Dudley said:
When I open a form with a sub-sub-form, I have code 'On Open' to go to
the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form but
not
in the subform, so I would also like the code to create and save a
record
in
the subform (if one does not already exist) with default data, and I
have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
Thanks for your help. As you will have gathered, my knowledge if Access
programming is limited, although I have always got there in the end with a
combination of trial and error and asking advice.

I have a structure in the back end of one parent, three child, and one of
the 'children' having a grandchild. They are linked by field EnvelopeNumber.
I have tried an alternative approach of putting code in the command buttons
opening the form and the following seems to work:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CapitalSubForm"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Forms!CAPITALSUBFORM.EnvelopeNumber = 0 Then
Forms!CAPITALSUBFORM.EnvelopeNumber = Me.EnvelopeNumber
Else: GoTo Capform
End If

Forms!CAPITALSUBFORM.ShareClass = "ORD"
Forms!CAPITALSUBFORM.ShareValue = "1"
Forms!CAPITALSUBFORM.PaidPerShare = "1"
Forms!CAPITALSUBFORM.Particulars = "Standard"
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

Capform:

DoCmd.Close acForm, "CapitalSubForm"

Does this seem OK?

Thanks
Dudley

Jeff Boyce said:
Dudley

I was referring to the parent and grandparent records, not the sub-form and
form within which your sub-sub-form was embedded.

But perhaps I've assumed (?in error) that your use of main
form/sub-form/sub-sub-form reflects an underlying grandparent/parent/child
relationship among the tables behind your forms...

Regards

Jeff Boyce
Microsoft Access MVP


Dudley said:
The form is only opened by command buttons from other forms with code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

Jeff Boyce said:
If a new sub-sub-form record is created, how is Access supposed to know
who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

When I open a form with a sub-sub-form, I have code 'On Open' to go to
the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form but
not
in the subform, so I would also like the code to create and save a
record
in
the subform (if one does not already exist) with default data, and I
have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
What I was attempting to point out is that a child (or a grandchild) record
would (and could) not be stored in a well-normalized relational database
design UNTIL AFTER the respective parent records were stored. Without the
parent(s) already in place, you only have an orphan.

Let's step back for a second and ask why? Why do you want to store a
sub-sub-record before knowing/recording its parent & grandparent? What will
having that child record stored allow you to do?

Regards

Jeff Boyce
Microsoft Access MVP

Dudley said:
Thanks for your help. As you will have gathered, my knowledge if Access
programming is limited, although I have always got there in the end with a
combination of trial and error and asking advice.

I have a structure in the back end of one parent, three child, and one of
the 'children' having a grandchild. They are linked by field
EnvelopeNumber.
I have tried an alternative approach of putting code in the command
buttons
opening the form and the following seems to work:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CapitalSubForm"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Forms!CAPITALSUBFORM.EnvelopeNumber = 0 Then
Forms!CAPITALSUBFORM.EnvelopeNumber = Me.EnvelopeNumber
Else: GoTo Capform
End If

Forms!CAPITALSUBFORM.ShareClass = "ORD"
Forms!CAPITALSUBFORM.ShareValue = "1"
Forms!CAPITALSUBFORM.PaidPerShare = "1"
Forms!CAPITALSUBFORM.Particulars = "Standard"
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

Capform:

DoCmd.Close acForm, "CapitalSubForm"

Does this seem OK?

Thanks
Dudley

Jeff Boyce said:
Dudley

I was referring to the parent and grandparent records, not the sub-form
and
form within which your sub-sub-form was embedded.

But perhaps I've assumed (?in error) that your use of main
form/sub-form/sub-sub-form reflects an underlying
grandparent/parent/child
relationship among the tables behind your forms...

Regards

Jeff Boyce
Microsoft Access MVP


Dudley said:
The form is only opened by command buttons from other forms with code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

:

If a new sub-sub-form record is created, how is Access supposed to
know
who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

When I open a form with a sub-sub-form, I have code 'On Open' to go
to
the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form
but
not
in the subform, so I would also like the code to create and save a
record
in
the subform (if one does not already exist) with default data, and I
have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
The user always first opens a form to create a record in the parent table.
Once he has done that he uses command buttons to open forms to enter data in
child tables. Each form has command buttons to go to the other child forms
and back to the parent on the same record. However the child table which has
a grandchild almost always has the same default data, so I want to allow the
user on opening this form (the last form to be opened, after entering data in
the parent and other child tables) to be able to go straight to the
grandchild to enter data without accidentally failing to create a parent
record (of the grandchild) because he saw no need to change its default data.
However I also have to allow for the case where the user is going back to the
form to correct data entry errors, so no new record should be created. Does
this make sense?

Thanks
Dudley

Thanks
Dudley


Jeff Boyce said:
What I was attempting to point out is that a child (or a grandchild) record
would (and could) not be stored in a well-normalized relational database
design UNTIL AFTER the respective parent records were stored. Without the
parent(s) already in place, you only have an orphan.

Let's step back for a second and ask why? Why do you want to store a
sub-sub-record before knowing/recording its parent & grandparent? What will
having that child record stored allow you to do?

Regards

Jeff Boyce
Microsoft Access MVP

Dudley said:
Thanks for your help. As you will have gathered, my knowledge if Access
programming is limited, although I have always got there in the end with a
combination of trial and error and asking advice.

I have a structure in the back end of one parent, three child, and one of
the 'children' having a grandchild. They are linked by field
EnvelopeNumber.
I have tried an alternative approach of putting code in the command
buttons
opening the form and the following seems to work:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CapitalSubForm"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Forms!CAPITALSUBFORM.EnvelopeNumber = 0 Then
Forms!CAPITALSUBFORM.EnvelopeNumber = Me.EnvelopeNumber
Else: GoTo Capform
End If

Forms!CAPITALSUBFORM.ShareClass = "ORD"
Forms!CAPITALSUBFORM.ShareValue = "1"
Forms!CAPITALSUBFORM.PaidPerShare = "1"
Forms!CAPITALSUBFORM.Particulars = "Standard"
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

Capform:

DoCmd.Close acForm, "CapitalSubForm"

Does this seem OK?

Thanks
Dudley

Jeff Boyce said:
Dudley

I was referring to the parent and grandparent records, not the sub-form
and
form within which your sub-sub-form was embedded.

But perhaps I've assumed (?in error) that your use of main
form/sub-form/sub-sub-form reflects an underlying
grandparent/parent/child
relationship among the tables behind your forms...

Regards

Jeff Boyce
Microsoft Access MVP


The form is only opened by command buttons from other forms with code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

:

If a new sub-sub-form record is created, how is Access supposed to
know
who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

When I open a form with a sub-sub-form, I have code 'On Open' to go
to
the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form
but
not
in the subform, so I would also like the code to create and save a
record
in
the subform (if one does not already exist) with default data, and I
have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
I should add that the system works fine as long as the user puts data in the
parent record of the grandchild, and my current solution is to leave one of
the fields without default data in order to force the user to enter data, but
I do not like this as it annoying for the user and creates an unnecessary
opportunity for errors.

Dudley

Jeff Boyce said:
What I was attempting to point out is that a child (or a grandchild) record
would (and could) not be stored in a well-normalized relational database
design UNTIL AFTER the respective parent records were stored. Without the
parent(s) already in place, you only have an orphan.

Let's step back for a second and ask why? Why do you want to store a
sub-sub-record before knowing/recording its parent & grandparent? What will
having that child record stored allow you to do?

Regards

Jeff Boyce
Microsoft Access MVP

Dudley said:
Thanks for your help. As you will have gathered, my knowledge if Access
programming is limited, although I have always got there in the end with a
combination of trial and error and asking advice.

I have a structure in the back end of one parent, three child, and one of
the 'children' having a grandchild. They are linked by field
EnvelopeNumber.
I have tried an alternative approach of putting code in the command
buttons
opening the form and the following seems to work:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CapitalSubForm"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Forms!CAPITALSUBFORM.EnvelopeNumber = 0 Then
Forms!CAPITALSUBFORM.EnvelopeNumber = Me.EnvelopeNumber
Else: GoTo Capform
End If

Forms!CAPITALSUBFORM.ShareClass = "ORD"
Forms!CAPITALSUBFORM.ShareValue = "1"
Forms!CAPITALSUBFORM.PaidPerShare = "1"
Forms!CAPITALSUBFORM.Particulars = "Standard"
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

Capform:

DoCmd.Close acForm, "CapitalSubForm"

Does this seem OK?

Thanks
Dudley

Jeff Boyce said:
Dudley

I was referring to the parent and grandparent records, not the sub-form
and
form within which your sub-sub-form was embedded.

But perhaps I've assumed (?in error) that your use of main
form/sub-form/sub-sub-form reflects an underlying
grandparent/parent/child
relationship among the tables behind your forms...

Regards

Jeff Boyce
Microsoft Access MVP


The form is only opened by command buttons from other forms with code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

:

If a new sub-sub-form record is created, how is Access supposed to
know
who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

When I open a form with a sub-sub-form, I have code 'On Open' to go
to
the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the sub-sub-form
but
not
in the subform, so I would also like the code to create and save a
record
in
the subform (if one does not already exist) with default data, and I
have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
Sorry for the delay ...

.... and sorry, but I'm just not tracking the underlying business need that's
being solved. My spouse accuses me of being particularly 'literal' and
'sequential', and I need to understand a business need and the raw
underlying data in order to work out a "how"...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Dudley said:
I should add that the system works fine as long as the user puts data in
the
parent record of the grandchild, and my current solution is to leave one
of
the fields without default data in order to force the user to enter data,
but
I do not like this as it annoying for the user and creates an unnecessary
opportunity for errors.

Dudley

Jeff Boyce said:
What I was attempting to point out is that a child (or a grandchild)
record
would (and could) not be stored in a well-normalized relational database
design UNTIL AFTER the respective parent records were stored. Without
the
parent(s) already in place, you only have an orphan.

Let's step back for a second and ask why? Why do you want to store a
sub-sub-record before knowing/recording its parent & grandparent? What
will
having that child record stored allow you to do?

Regards

Jeff Boyce
Microsoft Access MVP

Dudley said:
Thanks for your help. As you will have gathered, my knowledge if Access
programming is limited, although I have always got there in the end
with a
combination of trial and error and asking advice.

I have a structure in the back end of one parent, three child, and one
of
the 'children' having a grandchild. They are linked by field
EnvelopeNumber.
I have tried an alternative approach of putting code in the command
buttons
opening the form and the following seems to work:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CapitalSubForm"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Forms!CAPITALSUBFORM.EnvelopeNumber = 0 Then
Forms!CAPITALSUBFORM.EnvelopeNumber = Me.EnvelopeNumber
Else: GoTo Capform
End If

Forms!CAPITALSUBFORM.ShareClass = "ORD"
Forms!CAPITALSUBFORM.ShareValue = "1"
Forms!CAPITALSUBFORM.PaidPerShare = "1"
Forms!CAPITALSUBFORM.Particulars = "Standard"
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

Capform:

DoCmd.Close acForm, "CapitalSubForm"

Does this seem OK?

Thanks
Dudley

:

Dudley

I was referring to the parent and grandparent records, not the
sub-form
and
form within which your sub-sub-form was embedded.

But perhaps I've assumed (?in error) that your use of main
form/sub-form/sub-sub-form reflects an underlying
grandparent/parent/child
relationship among the tables behind your forms...

Regards

Jeff Boyce
Microsoft Access MVP


The form is only opened by command buttons from other forms with
code;

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Capital"
stLinkCriteria = "[EnvelopeNumber]=" & Me![EnvelopeNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks
Dudley

:

If a new sub-sub-form record is created, how is Access supposed to
know
who
its "parent" and "grand-parent" is?

Regards

Jeff Boyce
Microsoft Access MVP

When I open a form with a sub-sub-form, I have code 'On Open' to
go
to
the
sub-sub-form:

Me.Capital_Subform.Form![SubscriberSubform].SetFocus

This works, but the user then creates a record in the
sub-sub-form
but
not
in the subform, so I would also like the code to create and save
a
record
in
the subform (if one does not already exist) with default data,
and I
have
not
been able to work out how to do this. Can anyone advise?

Thanks
Dudley
 
Back
Top