Add new record to master and subform at the same time.

[Guest]

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons related to
another form, I need to ensure that a new record is added to the subform when
a new record is created in the master form. Obviously, I'm having an issue.
Setup: The forms are locked down. The user must click an custom Add button
to add a new record in the master form. I'm trying to fit the additional code
into this routine. The user can also click another Add button to manually add
a record into the subform, but I also need to do this automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it gets the
primary key value necessary for the related subform record. Then write code
to add record into subform. This almost works. It adds the master form
primary key value into a new subform record, but the subform record does not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the subform
to add a new record. This fails with error "2465: Application-defined or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we can also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[Guest]

APOLOGIES for the unintentional duplicate posting. I must have double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

 

[BruceM]

How are the two tables related? (I will call them tblMother and tblChild).
I take it that the mother's name is entered into the main record, and that
at the same time a child's record is entered into the subform record. If
so, I don't see the problem if tblMother and tblChild are properly related,
and the subform control is properly linked to the main form through the Link
Parent and Link Child properties of the subform control. You enter a record
into the main form, and then enter the necessary information into the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields, and with
the subform control linking properties established (it would be MotherID in
both tables), this should work, unless I completely misunderstand the
situation.

APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons related
to
another form, I need to ensure that a new record is added to the subform
when
a new record is created in the master form. Obviously, I'm having an
issue.
Setup: The forms are locked down. The user must click an custom Add
button
to add a new record in the master form. I'm trying to fit the additional
code
into this routine. The user can also click another Add button to manually
add
a record into the subform, but I also need to do this automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it gets
the
primary key value necessary for the related subform record. Then write
code
to add record into subform. This almost works. It adds the master form
primary key value into a new subform record, but the subform record does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the
subform
to add a new record. This fails with error "2465: Application-defined or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[Guest]

Thanks for the reply. The tables are related pretty much as you state. The
issue is NOT manually adding a related birth record into the subform. The
issue is programmatically adding the birth record during the process of
creating the master mother record. The end result is that the added birth
record would only have the primary key (birthID + momID). I have a routine
attached to a command button in the birth subform that adds a related birth
record, of course. But when I try to call the button's click event procedure
rom the master form, it fails (see version2 example in my original post). So
if you can correct or improve upon my attempts, I'd appreciate it!
George

How are the two tables related? (I will call them tblMother and tblChild).
I take it that the mother's name is entered into the main record, and that
at the same time a child's record is entered into the subform record. If
so, I don't see the problem if tblMother and tblChild are properly related,
and the subform control is properly linked to the main form through the Link
Parent and Link Child properties of the subform control. You enter a record
into the main form, and then enter the necessary information into the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields, and with
the subform control linking properties established (it would be MotherID in
both tables), this should work, unless I completely misunderstand the
situation.

APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons related
to
another form, I need to ensure that a new record is added to the subform
when
a new record is created in the master form. Obviously, I'm having an
issue.
Setup: The forms are locked down. The user must click an custom Add
button
to add a new record in the master form. I'm trying to fit the additional
code
into this routine. The user can also click another Add button to manually
add
a record into the subform, but I also need to do this automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it gets
the
primary key value necessary for the related subform record. Then write
code
to add record into subform. This almost works. It adds the master form
primary key value into a new subform record, but the subform record does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the
subform
to add a new record. This fails with error "2465: Application-defined or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[BruceM]

I don't get it. It sounds as if you want to create a record in the Child
table that consists solely of a primary key (BirthID) and a foreign key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not need to
insert a related record*. The relationship between the tables, and the
form/subform relationship (assuming the linking fields are properly
established) will take care of that. The existence of a related (subform)
record presupposes that the record contains data not found in the main
table. Presumably there is some reason for creating a birth record. What
is it? Let's say it is to add the child's name and DOB. Add controls bound
to those fields to the subform. Go to the mother record. Click into the
C_FName field on the subform. Add a name. Click the DOB field, and add a
date. Close the form. Open tblChild. There should be a record that
contains ChildID, MotherID, C_FName, and DOB.
If I have missed the point, please describe the real-world situation rather
than the database terminology. I appreciate that you have made an effort to
solve this, but I still do not quite know what you are trying to do. Note
that questions in my reply are not rhetorical. There is a reason I have
asked.

A few other things:
If the point of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
is to save the record you could just use: Me.Dirty = False
The complicated line of code sounds like something the wizard generated. I
don't know why the wizard still does it that way.
Why the code to allow edits, etc.? If the idea is that old records can't be
changed, you could set the form's Data Entry property to Yes, which allows
only new records to be added.

Thanks for the reply. The tables are related pretty much as you state. The
issue is NOT manually adding a related birth record into the subform. The
issue is programmatically adding the birth record during the process of
creating the master mother record. The end result is that the added birth
record would only have the primary key (birthID + momID). I have a routine
attached to a command button in the birth subform that adds a related
birth
record, of course. But when I try to call the button's click event
procedure
rom the master form, it fails (see version2 example in my original post).
So
if you can correct or improve upon my attempts, I'd appreciate it!
George

How are the two tables related? (I will call them tblMother and
tblChild).
I take it that the mother's name is entered into the main record, and
that
at the same time a child's record is entered into the subform record. If
so, I don't see the problem if tblMother and tblChild are properly
related,
and the subform control is properly linked to the main form through the
Link
Parent and Link Child properties of the subform control. You enter a
record
into the main form, and then enter the necessary information into the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields, and with
the subform control linking properties established (it would be MotherID
in
both tables), this should work, unless I completely misunderstand the
situation.

APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

:

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons
related
to
another form, I need to ensure that a new record is added to the
subform
when
a new record is created in the master form. Obviously, I'm having an
issue.
Setup: The forms are locked down. The user must click an custom Add
button
to add a new record in the master form. I'm trying to fit the
additional
code
into this routine. The user can also click another Add button to
manually
add
a record into the subform, but I also need to do this automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it
gets
the
primary key value necessary for the related subform record. Then write
code
to add record into subform. This almost works. It adds the master form
primary key value into a new subform record, but the subform record
does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the
subform
to add a new record. This fails with error "2465: Application-defined
or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we
can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[Guest]

My apologies for not being clear enough in my prior posts. I'm not trynig to
add a record because I think it needs to be there. The record has to be there
because there is also a visits table that the staff uses, which is linked to
the birth table. In order to record a visit, we have to link it to a birth.
Since visits can take place during the prenatal phase, we have to have the
birth record in place beforehand. Now, the staff can certainly add a birth
record, themselves. However, I just wanted to make sure the record was there
from the get-go, in case they forget. THanks for your reply and concern,
Bruce. Any ideas?

I don't get it. It sounds as if you want to create a record in the Child
table that consists solely of a primary key (BirthID) and a foreign key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not need to
insert a related record*. The relationship between the tables, and the
form/subform relationship (assuming the linking fields are properly
established) will take care of that. The existence of a related (subform)
record presupposes that the record contains data not found in the main
table. Presumably there is some reason for creating a birth record. What
is it? Let's say it is to add the child's name and DOB. Add controls bound
to those fields to the subform. Go to the mother record. Click into the
C_FName field on the subform. Add a name. Click the DOB field, and add a
date. Close the form. Open tblChild. There should be a record that
contains ChildID, MotherID, C_FName, and DOB.
If I have missed the point, please describe the real-world situation rather
than the database terminology. I appreciate that you have made an effort to
solve this, but I still do not quite know what you are trying to do. Note
that questions in my reply are not rhetorical. There is a reason I have
asked.

A few other things:
If the point of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
is to save the record you could just use: Me.Dirty = False
The complicated line of code sounds like something the wizard generated. I
don't know why the wizard still does it that way.
Why the code to allow edits, etc.? If the idea is that old records can't be
changed, you could set the form's Data Entry property to Yes, which allows
only new records to be added.

Thanks for the reply. The tables are related pretty much as you state. The
issue is NOT manually adding a related birth record into the subform. The
issue is programmatically adding the birth record during the process of
creating the master mother record. The end result is that the added birth
record would only have the primary key (birthID + momID). I have a routine
attached to a command button in the birth subform that adds a related
birth
record, of course. But when I try to call the button's click event
procedure
rom the master form, it fails (see version2 example in my original post).
So
if you can correct or improve upon my attempts, I'd appreciate it!
George

How are the two tables related? (I will call them tblMother and
tblChild).
I take it that the mother's name is entered into the main record, and
that
at the same time a child's record is entered into the subform record. If
so, I don't see the problem if tblMother and tblChild are properly
related,
and the subform control is properly linked to the main form through the
Link
Parent and Link Child properties of the subform control. You enter a
record
into the main form, and then enter the necessary information into the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields, and with
the subform control linking properties established (it would be MotherID
in
both tables), this should work, unless I completely misunderstand the
situation.

APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

:

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons
related
to
another form, I need to ensure that a new record is added to the
subform
when
a new record is created in the master form. Obviously, I'm having an
issue.
Setup: The forms are locked down. The user must click an custom Add
button
to add a new record in the master form. I'm trying to fit the
additional
code
into this routine. The user can also click another Add button to
manually
add
a record into the subform, but I also need to do this automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it
gets
the
primary key value necessary for the related subform record. Then write
code
to add record into subform. This almost works. It adds the master form
primary key value into a new subform record, but the subform record
does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the
subform
to add a new record. This fails with error "2465: Application-defined
or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we
can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[BruceM]

Yes, that is an important detail about the visits table. As I understand
it, each birth (which includes prenatal care) may have several visits
associated with it. That means there should be a tblVisit that is related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform within fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words. With this
structure you should be able to add a visit record simply by clicking into a
control on fsubVisit. As I said, the relationship between tblMother and
tblChild already establishes the necessary key fields. Clicking into a
subform causes the parent record to be saved, so the record in tblChild will
be saved without any extra code needed. Just set up the relationships and
the forms/subforms, go to a Mother record, and click into a field on
fsubVisit. You may want a placeholder in tblChild (Baby07 in the C_FName
field, or something) for ease of reference by humans, but that is up to you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

My apologies for not being clear enough in my prior posts. I'm not trynig
to
add a record because I think it needs to be there. The record has to be
there
because there is also a visits table that the staff uses, which is linked
to
the birth table. In order to record a visit, we have to link it to a
birth.
Since visits can take place during the prenatal phase, we have to have the
birth record in place beforehand. Now, the staff can certainly add a birth
record, themselves. However, I just wanted to make sure the record was
there
from the get-go, in case they forget. THanks for your reply and concern,
Bruce. Any ideas?

I don't get it. It sounds as if you want to create a record in the Child
table that consists solely of a primary key (BirthID) and a foreign key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not need to
insert a related record*. The relationship between the tables, and the
form/subform relationship (assuming the linking fields are properly
established) will take care of that. The existence of a related
(subform)
record presupposes that the record contains data not found in the main
table. Presumably there is some reason for creating a birth record.
What
is it? Let's say it is to add the child's name and DOB. Add controls
bound
to those fields to the subform. Go to the mother record. Click into the
C_FName field on the subform. Add a name. Click the DOB field, and add
a
date. Close the form. Open tblChild. There should be a record that
contains ChildID, MotherID, C_FName, and DOB.
If I have missed the point, please describe the real-world situation
rather
than the database terminology. I appreciate that you have made an effort
to
solve this, but I still do not quite know what you are trying to do.
Note
that questions in my reply are not rhetorical. There is a reason I have
asked.

A few other things:
If the point of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
is to save the record you could just use: Me.Dirty = False
The complicated line of code sounds like something the wizard generated.
I
don't know why the wizard still does it that way.
Why the code to allow edits, etc.? If the idea is that old records can't
be
changed, you could set the form's Data Entry property to Yes, which
allows
only new records to be added.

Thanks for the reply. The tables are related pretty much as you state.
The
issue is NOT manually adding a related birth record into the subform.
The
issue is programmatically adding the birth record during the process of
creating the master mother record. The end result is that the added
birth
record would only have the primary key (birthID + momID). I have a
routine
attached to a command button in the birth subform that adds a related
birth
record, of course. But when I try to call the button's click event
procedure
rom the master form, it fails (see version2 example in my original
post).
So
if you can correct or improve upon my attempts, I'd appreciate it!
George

:

How are the two tables related? (I will call them tblMother and
tblChild).
I take it that the mother's name is entered into the main record, and
that
at the same time a child's record is entered into the subform record.
If
so, I don't see the problem if tblMother and tblChild are properly
related,
and the subform control is properly linked to the main form through
the
Link
Parent and Link Child properties of the subform control. You enter a
record
into the main form, and then enter the necessary information into the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields, and
with
the subform control linking properties established (it would be
MotherID
in
both tables), this should work, unless I completely misunderstand the
situation.

message
APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

:

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons
related
to
another form, I need to ensure that a new record is added to the
subform
when
a new record is created in the master form. Obviously, I'm having
an
issue.
Setup: The forms are locked down. The user must click an custom Add
button
to add a new record in the master form. I'm trying to fit the
additional
code
into this routine. The user can also click another Add button to
manually
add
a record into the subform, but I also need to do this
automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it
gets
the
primary key value necessary for the related subform record. Then
write
code
to add record into subform. This almost works. It adds the master
form
primary key value into a new subform record, but the subform record
does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the
subform
to add a new record. This fails with error "2465:
Application-defined
or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we
can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[Guest]

This pretty much matches my design, though I had made the visit table
viewable thorugh a command button (for reasons of space). When a visit record
exists, there is no problem with the user creating a visit record, of course,
as long as a birth record exists. From a hands-on point of view, there is no
issue on this point. I'm sure that the users are not going to try and insert
a visit without a birth record, so I reckon trying to anticipate them by
auto-inserting the birth record ahead of time is probably not worth the
effort. Thanks for your patience and the information, Bruce.
George

Yes, that is an important detail about the visits table. As I understand
it, each birth (which includes prenatal care) may have several visits
associated with it. That means there should be a tblVisit that is related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform within fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words. With this
structure you should be able to add a visit record simply by clicking into a
control on fsubVisit. As I said, the relationship between tblMother and
tblChild already establishes the necessary key fields. Clicking into a
subform causes the parent record to be saved, so the record in tblChild will
be saved without any extra code needed. Just set up the relationships and
the forms/subforms, go to a Mother record, and click into a field on
fsubVisit. You may want a placeholder in tblChild (Baby07 in the C_FName
field, or something) for ease of reference by humans, but that is up to you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

My apologies for not being clear enough in my prior posts. I'm not trynig
to
add a record because I think it needs to be there. The record has to be
there
because there is also a visits table that the staff uses, which is linked
to
the birth table. In order to record a visit, we have to link it to a
birth.
Since visits can take place during the prenatal phase, we have to have the
birth record in place beforehand. Now, the staff can certainly add a birth
record, themselves. However, I just wanted to make sure the record was
there
from the get-go, in case they forget. THanks for your reply and concern,
Bruce. Any ideas?

I don't get it. It sounds as if you want to create a record in the Child
table that consists solely of a primary key (BirthID) and a foreign key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not need to
insert a related record*. The relationship between the tables, and the
form/subform relationship (assuming the linking fields are properly
established) will take care of that. The existence of a related
(subform)
record presupposes that the record contains data not found in the main
table. Presumably there is some reason for creating a birth record.
What
is it? Let's say it is to add the child's name and DOB. Add controls
bound
to those fields to the subform. Go to the mother record. Click into the
C_FName field on the subform. Add a name. Click the DOB field, and add
a
date. Close the form. Open tblChild. There should be a record that
contains ChildID, MotherID, C_FName, and DOB.
If I have missed the point, please describe the real-world situation
rather
than the database terminology. I appreciate that you have made an effort
to
solve this, but I still do not quite know what you are trying to do.
Note
that questions in my reply are not rhetorical. There is a reason I have
asked.

A few other things:
If the point of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
is to save the record you could just use: Me.Dirty = False
The complicated line of code sounds like something the wizard generated.
I
don't know why the wizard still does it that way.
Why the code to allow edits, etc.? If the idea is that old records can't
be
changed, you could set the form's Data Entry property to Yes, which
allows
only new records to be added.

Thanks for the reply. The tables are related pretty much as you state.
The
issue is NOT manually adding a related birth record into the subform.
The
issue is programmatically adding the birth record during the process of
creating the master mother record. The end result is that the added
birth
record would only have the primary key (birthID + momID). I have a
routine
attached to a command button in the birth subform that adds a related
birth
record, of course. But when I try to call the button's click event
procedure
rom the master form, it fails (see version2 example in my original
post).
So
if you can correct or improve upon my attempts, I'd appreciate it!
George

:

How are the two tables related? (I will call them tblMother and
tblChild).
I take it that the mother's name is entered into the main record, and
that
at the same time a child's record is entered into the subform record.
If
so, I don't see the problem if tblMother and tblChild are properly
related,
and the subform control is properly linked to the main form through
the
Link
Parent and Link Child properties of the subform control. You enter a
record
into the main form, and then enter the necessary information into the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields, and
with
the subform control linking properties established (it would be
MotherID
in
both tables), this should work, unless I completely misunderstand the
situation.

message
APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

:

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons
related
to
another form, I need to ensure that a new record is added to the
subform
when
a new record is created in the master form. Obviously, I'm having
an
issue.
Setup: The forms are locked down. The user must click an custom Add
button
to add a new record in the master form. I'm trying to fit the
additional
code
into this routine. The user can also click another Add button to
manually
add
a record into the subform, but I also need to do this
automatically.

What is the best way to accomplish this? All corrections and ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure it
gets
the
primary key value necessary for the related subform record. Then
write
code
to add record into subform. This almost works. It adds the master
form
primary key value into a new subform record, but the subform record
does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of the
subform
to add a new record. This fails with error "2465:
Application-defined
or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure we
can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[BruceM]

You could hide or unhide the visit subform (I assume you mean the form, not
the table) as you wish:
Me.subformControlName.Visible = Not Me.subformControlName
You may want to look into adding a tab control is space becomes a problem.
My point about adding the Visit record is that with the nested subform
structure and the proper table relationships, a Birth record should be
created as soon as a Visit record is created. As I mentioned, clicking into
a subform causes the parent record to be saved. Note the MotherID (from
tblMother) on a record with no Visit information (and no specific Child
information). Look through tblChild to see if there is a record with that
MotherID in the MotherID field. Now note the MotherID in a tblMother record
that has specific Child information (DOB or something). Again, search
through tblChild. You should find a record with that MotherID in the
MotherID field. Now note MotherID in a tblMother record that has no
specific Child information, but that has Visit information. Again, you
should find a record in tblChild with that MotherID in the MotherID field.
The easiest way to "note MotherID" is to place onto the form a temporary
text box bound to that field.
Be aware that a subform control has Link Parent and Link Child properties.
Understand that these are the same on every subform control, and have
nothing to do with your table names.

This pretty much matches my design, though I had made the visit table
viewable thorugh a command button (for reasons of space). When a visit
record
exists, there is no problem with the user creating a visit record, of
course,
as long as a birth record exists. From a hands-on point of view, there is
no
issue on this point. I'm sure that the users are not going to try and
insert
a visit without a birth record, so I reckon trying to anticipate them by
auto-inserting the birth record ahead of time is probably not worth the
effort. Thanks for your patience and the information, Bruce.
George

Yes, that is an important detail about the visits table. As I understand
it, each birth (which includes prenatal care) may have several visits
associated with it. That means there should be a tblVisit that is
related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform within
fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words. With
this
structure you should be able to add a visit record simply by clicking
into a
control on fsubVisit. As I said, the relationship between tblMother and
tblChild already establishes the necessary key fields. Clicking into a
subform causes the parent record to be saved, so the record in tblChild
will
be saved without any extra code needed. Just set up the relationships
and
the forms/subforms, go to a Mother record, and click into a field on
fsubVisit. You may want a placeholder in tblChild (Baby07 in the C_FName
field, or something) for ease of reference by humans, but that is up to
you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

My apologies for not being clear enough in my prior posts. I'm not
trynig
to
add a record because I think it needs to be there. The record has to be
there
because there is also a visits table that the staff uses, which is
linked
to
the birth table. In order to record a visit, we have to link it to a
birth.
Since visits can take place during the prenatal phase, we have to have
the
birth record in place beforehand. Now, the staff can certainly add a
birth
record, themselves. However, I just wanted to make sure the record was
there
from the get-go, in case they forget. THanks for your reply and
concern,
Bruce. Any ideas?

:

I don't get it. It sounds as if you want to create a record in the
Child
table that consists solely of a primary key (BirthID) and a foreign
key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not need
to
insert a related record*. The relationship between the tables, and
the
form/subform relationship (assuming the linking fields are properly
established) will take care of that. The existence of a related
(subform)
record presupposes that the record contains data not found in the main
table. Presumably there is some reason for creating a birth record.
What
is it? Let's say it is to add the child's name and DOB. Add controls
bound
to those fields to the subform. Go to the mother record. Click into
the
C_FName field on the subform. Add a name. Click the DOB field, and
add
a
date. Close the form. Open tblChild. There should be a record that
contains ChildID, MotherID, C_FName, and DOB.
If I have missed the point, please describe the real-world situation
rather
than the database terminology. I appreciate that you have made an
effort
to
solve this, but I still do not quite know what you are trying to do.
Note
that questions in my reply are not rhetorical. There is a reason I
have
asked.

A few other things:
If the point of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
is to save the record you could just use: Me.Dirty = False
The complicated line of code sounds like something the wizard
generated.
I
don't know why the wizard still does it that way.
Why the code to allow edits, etc.? If the idea is that old records
can't
be
changed, you could set the form's Data Entry property to Yes, which
allows
only new records to be added.

message
Thanks for the reply. The tables are related pretty much as you
state.
The
issue is NOT manually adding a related birth record into the
subform.
The
issue is programmatically adding the birth record during the process
of
creating the master mother record. The end result is that the added
birth
record would only have the primary key (birthID + momID). I have a
routine
attached to a command button in the birth subform that adds a
related
birth
record, of course. But when I try to call the button's click event
procedure
rom the master form, it fails (see version2 example in my original
post).
So
if you can correct or improve upon my attempts, I'd appreciate it!
George

:

How are the two tables related? (I will call them tblMother and
tblChild).
I take it that the mother's name is entered into the main record,
and
that
at the same time a child's record is entered into the subform
record.
If
so, I don't see the problem if tblMother and tblChild are properly
related,
and the subform control is properly linked to the main form through
the
Link
Parent and Link Child properties of the subform control. You enter
a
record
into the main form, and then enter the necessary information into
the
subform.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB
etc.

With a relationship established between the two MotherID fields,
and
with
the subform control linking properties established (it would be
MotherID
in
both tables), this should work, unless I completely misunderstand
the
situation.

message
APOLOGIES for the unintentional duplicate posting. I must have
double-clicked
the post button by mistake. Didn't mean to spam the forum!

I'm still looking for advise, though!

George

:

Using Access 2003 SP2.
I have a master form and linked subform. For procedural reasons
related
to
another form, I need to ensure that a new record is added to the
subform
when
a new record is created in the master form. Obviously, I'm
having
an
issue.
Setup: The forms are locked down. The user must click an custom
Add
button
to add a new record in the master form. I'm trying to fit the
additional
code
into this routine. The user can also click another Add button to
manually
add
a record into the subform, but I also need to do this
automatically.

What is the best way to accomplish this? All corrections and
ideas
appreciated.

I've tried a few approaches, with varying success:
1. When the master record is added, save it right away to ensure
it
gets
the
primary key value necessary for the related subform record. Then
write
code
to add record into subform. This almost works. It adds the
master
form
primary key value into a new subform record, but the subform
record
does
not
yet have its own primary key value.

2. Add master record as 1, but call the Add command routine of
the
subform
to add a new record. This fails with error "2465:
Application-defined
or
object-defined error"

Here is the core code for both approaches:

version 1:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new birth record in subform
Me![subtblhugbirths SubForm].Form.AllowAdditions = True
Me![subtblhugbirths SubForm].Form.AllowEdits = True
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

version 2:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec
' save record, then open for edit again. This is done to ensure
we
can
also
add
' new birth record at the same time.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Me.MotherFN.SetFocus
' insert new record birth subform/table
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

The core cmdAddBirth_Click code looks like this:
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.GoToRecord , , acNewRec

Thanks for taking the time to read this.

 

[Guest]

Bruce,
Thanks for the additional info. I rigged up a new set of tables, following
your example. In each case, the PK was only the single ID field of the table,
excluding the FK field. Now, it is true that I can enter a visit date into
the visit table and have it generate a visit PK value that can be saved.
However, the Birth FK field in the Visit table is not auto-filled, since
there is no Birth record.

The birth record in the form appears to show the linked mom FK value, but it
does not auto-generate a birth PK value, as the record has not been touched
(that is, after creating a mom record, I immediately created a vist record,
per your scenario). When I view the tables, there are records in the mom
table and visit table only. Here is my design, as you indicated.

tblMom tblBirth tblVisit
MomPK --------| BirthPK ------| VisitPK
MomLN |------- MomFK |-------BirthFK
MomFN DOB VisitDate

So it LOOKS like it is working, but when you view the tables, there is no
birth record and no birth key, without actually going into the birth record
at some point. Perhaps I missed something in all this.

BTW, I then modified the Birth table, making BirthPK+MomK a compound PK.
This also forced me to add the MomFK into the Visit table as part of the FK.
In this scenario, I could not create a visit record without first having a
Birth record. But it avoided creating child records with no null Fk fields.

George

 

[BruceM]

My mistake. I don't think I have ever tried to create a record with no data
except for linked fields. I experimented for a while, and discovered that
if you use code to set the value of BirthPK you can add a record to tblVisit
without adding any other information to tblBirth. Using Default Value to
set the value does not have the same effect. You could set BirthPK to a
Number field (Long Integer) in table design view, then use code like this in
the Current event for frmBirth (which functions as a subform in frmMother):

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

 

[Guest]

Now we're reading on the same page, Bruce. My problem is largely one of
syntax, I suspect: Adding the new birth record into the subform from a
procedure run in the main form. So this stuff fails (and variations in
syntax):
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec

and this (calling the subroutine in the birth form from the main mother form:
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

george

My mistake. I don't think I have ever tried to create a record with no data
except for linked fields. I experimented for a while, and discovered that
if you use code to set the value of BirthPK you can add a record to tblVisit
without adding any other information to tblBirth. Using Default Value to
set the value does not have the same effect. You could set BirthPK to a
Number field (Long Integer) in table design view, then use code like this in
the Current event for frmBirth (which functions as a subform in frmMother):

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

Bruce,
Thanks for the additional info. I rigged up a new set of tables, following
your example. In each case, the PK was only the single ID field of the
table,
excluding the FK field. Now, it is true that I can enter a visit date
into
the visit table and have it generate a visit PK value that can be saved.
However, the Birth FK field in the Visit table is not auto-filled, since
there is no Birth record.

The birth record in the form appears to show the linked mom FK value, but
it
does not auto-generate a birth PK value, as the record has not been
touched
(that is, after creating a mom record, I immediately created a vist
record,
per your scenario). When I view the tables, there are records in the mom
table and visit table only. Here is my design, as you indicated.

tblMom tblBirth tblVisit
MomPK --------| BirthPK ------| VisitPK
MomLN |------- MomFK |-------BirthFK
MomFN DOB VisitDate

So it LOOKS like it is working, but when you view the tables, there is no
birth record and no birth key, without actually going into the birth
record
at some point. Perhaps I missed something in all this.

BTW, I then modified the Birth table, making BirthPK+MomK a compound PK.
This also forced me to add the MomFK into the Visit table as part of the
FK.
In this scenario, I could not create a visit record without first having a
Birth record. But it avoided creating child records with no null Fk
fields.

George

 

[BruceM]

I am running out of ways to say this: you enter the record into tblBirth
via the subform by entering information into the record. You seem
determined to use a procedure on the main form to accomplish this, but I
don't know what you intend to accomplish that you could not accomplish by
the means I have suggested.

You may need to use a variant of the DMax code I suggested for fsubBirth to
create the Mother PK in tblMother. Autonumber may give you problems, in
that the ChildPK, when created via the code I suggested, is created as soon
as you create a new Mother record, but the FK in tblChild is not established
until you start typing information into the Mother record. By the way, the
DMax code should be:
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),0) + 1
I had a 1 where there is now a 0.

So, in the Current event for frmMother:

If Me.NewRecord Then
Me.MotherPK = Nz(DMax("[MotherPK]","[tblMother]"),0) + 1
End If

In the Current event for fsubChild:

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

I'm hearing that you don't want to create a birth record at all other than
the key fields, so that you can enter visit information without entering any
birth information at all. But now you seem to be saying you want to create
a new Birth record. Each Birth event happens once, and needs one record.
I'll leave it to you to decide how you plan to handle twins. For all I know
this database is for information about prenatal visits only, so the Birth
information is really pregnancy information, and the number of children is
irrelevant.
As soon as you create a Mother record there will be a related Birth record
if you use the code I have suggested. What do you need beyond that?

Now we're reading on the same page, Bruce. My problem is largely one of
syntax, I suspect: Adding the new birth record into the subform from a
procedure run in the main form. So this stuff fails (and variations in
syntax):
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec

and this (calling the subroutine in the birth form from the main mother
form:
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

george

My mistake. I don't think I have ever tried to create a record with no
data
except for linked fields. I experimented for a while, and discovered
that
if you use code to set the value of BirthPK you can add a record to
tblVisit
without adding any other information to tblBirth. Using Default Value to
set the value does not have the same effect. You could set BirthPK to a
Number field (Long Integer) in table design view, then use code like this
in
the Current event for frmBirth (which functions as a subform in
frmMother):

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

Bruce,
Thanks for the additional info. I rigged up a new set of tables,
following
your example. In each case, the PK was only the single ID field of the
table,
excluding the FK field. Now, it is true that I can enter a visit date
into
the visit table and have it generate a visit PK value that can be
saved.
However, the Birth FK field in the Visit table is not auto-filled,
since
there is no Birth record.

The birth record in the form appears to show the linked mom FK value,
but
it
does not auto-generate a birth PK value, as the record has not been
touched
(that is, after creating a mom record, I immediately created a vist
record,
per your scenario). When I view the tables, there are records in the
mom
table and visit table only. Here is my design, as you indicated.

tblMom tblBirth tblVisit
MomPK --------| BirthPK ------| VisitPK
MomLN |------- MomFK |-------BirthFK
MomFN DOB VisitDate

So it LOOKS like it is working, but when you view the tables, there is
no
birth record and no birth key, without actually going into the birth
record
at some point. Perhaps I missed something in all this.

BTW, I then modified the Birth table, making BirthPK+MomK a compound
PK.
This also forced me to add the MomFK into the Visit table as part of
the
FK.
In this scenario, I could not create a visit record without first
having a
Birth record. But it avoided creating child records with no null Fk
fields.

George

:

You could hide or unhide the visit subform (I assume you mean the
form,
not
the table) as you wish:
Me.subformControlName.Visible = Not Me.subformControlName
You may want to look into adding a tab control is space becomes a
problem.
My point about adding the Visit record is that with the nested subform
structure and the proper table relationships, a Birth record should be
created as soon as a Visit record is created. As I mentioned,
clicking
into
a subform causes the parent record to be saved. Note the MotherID
(from
tblMother) on a record with no Visit information (and no specific
Child
information). Look through tblChild to see if there is a record with
that
MotherID in the MotherID field. Now note the MotherID in a tblMother
record
that has specific Child information (DOB or something). Again, search
through tblChild. You should find a record with that MotherID in the
MotherID field. Now note MotherID in a tblMother record that has no
specific Child information, but that has Visit information. Again,
you
should find a record in tblChild with that MotherID in the MotherID
field.
The easiest way to "note MotherID" is to place onto the form a
temporary
text box bound to that field.
Be aware that a subform control has Link Parent and Link Child
properties.
Understand that these are the same on every subform control, and have
nothing to do with your table names.

message
This pretty much matches my design, though I had made the visit
table
viewable thorugh a command button (for reasons of space). When a
visit
record
exists, there is no problem with the user creating a visit record,
of
course,
as long as a birth record exists. From a hands-on point of view,
there
is
no
issue on this point. I'm sure that the users are not going to try
and
insert
a visit without a birth record, so I reckon trying to anticipate
them
by
auto-inserting the birth record ahead of time is probably not worth
the
effort. Thanks for your patience and the information, Bruce.
George

:

Yes, that is an important detail about the visits table. As I
understand
it, each birth (which includes prenatal care) may have several
visits
associated with it. That means there should be a tblVisit that is
related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform within
fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words.
With
this
structure you should be able to add a visit record simply by
clicking
into a
control on fsubVisit. As I said, the relationship between
tblMother
and
tblChild already establishes the necessary key fields. Clicking
into
a
subform causes the parent record to be saved, so the record in
tblChild
will
be saved without any extra code needed. Just set up the
relationships
and
the forms/subforms, go to a Mother record, and click into a field
on
fsubVisit. You may want a placeholder in tblChild (Baby07 in the
C_FName
field, or something) for ease of reference by humans, but that is
up
to
you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

message
My apologies for not being clear enough in my prior posts. I'm
not
trynig
to
add a record because I think it needs to be there. The record has
to
be
there
because there is also a visits table that the staff uses, which
is
linked
to
the birth table. In order to record a visit, we have to link it
to a
birth.
Since visits can take place during the prenatal phase, we have to
have
the
birth record in place beforehand. Now, the staff can certainly
add a
birth
record, themselves. However, I just wanted to make sure the
record
was
there
from the get-go, in case they forget. THanks for your reply and
concern,
Bruce. Any ideas?

:

I don't get it. It sounds as if you want to create a record in
the
Child
table that consists solely of a primary key (BirthID) and a
foreign
key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not
need
to
insert a related record*. The relationship between the tables,
and
the
form/subform relationship (assuming the linking fields are
properly
established) will take care of that. The existence of a related
(subform)
record presupposes that the record contains data not found in
the
main
table. Presumably there is some reason for creating a birth
record.
What
is it? Let's say it is to add the child's name and DOB. Add
controls
bound
to those fields to the subform. Go to the mother record. Click
into
the
C_FName field on the subform. Add a name. Click the DOB field,
and
add
a
date. Close the form. Open tblChild. There should be a record
that
contains ChildID, MotherID, C_FName, and DOB.
If I have missed the point, please describe the real-world
situation
rather
than the database terminology. I appreciate that you have made
an
effort
to
solve this, but I still do not quite know what you are trying to
do.
Note
that questions in my reply are not rhetorical. There is a
reason I
have
asked.

A few other things:
If the point of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
is to save the record you could just use: Me.Dirty = False
The complicated line of code sounds like something the wizard
generated.
I
don't know why the wizard still does it that way.
Why the code to allow edits, etc.? If the idea is that old
records
can't
be
changed, you could set the form's Data Entry property to Yes,
which
allows
only new records to be added.

message
Thanks for the reply. The tables are related pretty much as
you
state.
The
issue is NOT manually adding a related birth record into the
subform.
The
issue is programmatically adding the birth record during the
process
of
creating the master mother record. The end result is that the
added
birth
record would only have the primary key (birthID + momID). I
have
a
routine
attached to a command button in the birth subform that adds a
related
birth
record, of course. But when I try to call the button's click
event
procedure
rom the master form, it fails (see version2 example in my
original
post).
So
if you can correct or improve upon my attempts, I'd appreciate
it!
George

:

How are the two tables related? (I will call them tblMother
and
tblChild).
I take it that the mother's name is entered into the main
record,
and
that
at the same time a child's record is entered into the subform
record.
If
so, I don't see the problem if tblMother and tblChild are
properly
related,
and the subform control is properly linked to the main form
through
the
Link
Parent and Link Child properties of the subform control. You
enter
a
record
into the main form, and then enter the necessary information
into
the
subform.

 

[Guest]

We seem to be working at cross purposes. OF COURSE a record can be manually
entered into a birth subform. I didn't think that was a point of concern. At
least it wasn't , to me. In fact, I pointed that out in my initial post. My
only issue was a way to automatically create a birth record when a new mother
record was created. Nothing more and nothing less. Sure, the birth record
would initially be blank, save for the primary key; but that is enough for my
needs. I reckon we can just leave it at that and move on to other inquiries.

As for your question regarding how to record a multiple birth, it is not
your fault for not understanding, as there was no reason to explain other
aspects of the design. Babies are entered into a baby table linked to the
birth table: Mom-->Birth-->Babies

George

I am running out of ways to say this: you enter the record into tblBirth
via the subform by entering information into the record. You seem
determined to use a procedure on the main form to accomplish this, but I
don't know what you intend to accomplish that you could not accomplish by
the means I have suggested.

You may need to use a variant of the DMax code I suggested for fsubBirth to
create the Mother PK in tblMother. Autonumber may give you problems, in
that the ChildPK, when created via the code I suggested, is created as soon
as you create a new Mother record, but the FK in tblChild is not established
until you start typing information into the Mother record. By the way, the
DMax code should be:
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),0) + 1
I had a 1 where there is now a 0.

So, in the Current event for frmMother:

If Me.NewRecord Then
Me.MotherPK = Nz(DMax("[MotherPK]","[tblMother]"),0) + 1
End If

In the Current event for fsubChild:

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

I'm hearing that you don't want to create a birth record at all other than
the key fields, so that you can enter visit information without entering any
birth information at all. But now you seem to be saying you want to create
a new Birth record. Each Birth event happens once, and needs one record.
I'll leave it to you to decide how you plan to handle twins. For all I know
this database is for information about prenatal visits only, so the Birth
information is really pregnancy information, and the number of children is
irrelevant.
As soon as you create a Mother record there will be a related Birth record
if you use the code I have suggested. What do you need beyond that?

Now we're reading on the same page, Bruce. My problem is largely one of
syntax, I suspect: Adding the new birth record into the subform from a
procedure run in the main form. So this stuff fails (and variations in
syntax):
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec

and this (calling the subroutine in the birth form from the main mother
form:
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

george

My mistake. I don't think I have ever tried to create a record with no
data
except for linked fields. I experimented for a while, and discovered
that
if you use code to set the value of BirthPK you can add a record to
tblVisit
without adding any other information to tblBirth. Using Default Value to
set the value does not have the same effect. You could set BirthPK to a
Number field (Long Integer) in table design view, then use code like this
in
the Current event for frmBirth (which functions as a subform in
frmMother):

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

Bruce,
Thanks for the additional info. I rigged up a new set of tables,
following
your example. In each case, the PK was only the single ID field of the
table,
excluding the FK field. Now, it is true that I can enter a visit date
into
the visit table and have it generate a visit PK value that can be
saved.
However, the Birth FK field in the Visit table is not auto-filled,
since
there is no Birth record.

The birth record in the form appears to show the linked mom FK value,
but
it
does not auto-generate a birth PK value, as the record has not been
touched
(that is, after creating a mom record, I immediately created a vist
record,
per your scenario). When I view the tables, there are records in the
mom
table and visit table only. Here is my design, as you indicated.

tblMom tblBirth tblVisit
MomPK --------| BirthPK ------| VisitPK
MomLN |------- MomFK |-------BirthFK
MomFN DOB VisitDate

So it LOOKS like it is working, but when you view the tables, there is
no
birth record and no birth key, without actually going into the birth
record
at some point. Perhaps I missed something in all this.

BTW, I then modified the Birth table, making BirthPK+MomK a compound
PK.
This also forced me to add the MomFK into the Visit table as part of
the
FK.
In this scenario, I could not create a visit record without first
having a
Birth record. But it avoided creating child records with no null Fk
fields.

George

:

You could hide or unhide the visit subform (I assume you mean the
form,
not
the table) as you wish:
Me.subformControlName.Visible = Not Me.subformControlName
You may want to look into adding a tab control is space becomes a
problem.
My point about adding the Visit record is that with the nested subform
structure and the proper table relationships, a Birth record should be
created as soon as a Visit record is created. As I mentioned,
clicking
into
a subform causes the parent record to be saved. Note the MotherID
(from
tblMother) on a record with no Visit information (and no specific
Child
information). Look through tblChild to see if there is a record with
that
MotherID in the MotherID field. Now note the MotherID in a tblMother
record
that has specific Child information (DOB or something). Again, search
through tblChild. You should find a record with that MotherID in the
MotherID field. Now note MotherID in a tblMother record that has no
specific Child information, but that has Visit information. Again,
you
should find a record in tblChild with that MotherID in the MotherID
field.
The easiest way to "note MotherID" is to place onto the form a
temporary
text box bound to that field.
Be aware that a subform control has Link Parent and Link Child
properties.
Understand that these are the same on every subform control, and have
nothing to do with your table names.

message
This pretty much matches my design, though I had made the visit
table
viewable thorugh a command button (for reasons of space). When a
visit
record
exists, there is no problem with the user creating a visit record,
of
course,
as long as a birth record exists. From a hands-on point of view,
there
is
no
issue on this point. I'm sure that the users are not going to try
and
insert
a visit without a birth record, so I reckon trying to anticipate
them
by
auto-inserting the birth record ahead of time is probably not worth
the
effort. Thanks for your patience and the information, Bruce.
George

:

Yes, that is an important detail about the visits table. As I
understand
it, each birth (which includes prenatal care) may have several
visits
associated with it. That means there should be a tblVisit that is
related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform within
fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words.
With
this
structure you should be able to add a visit record simply by
clicking
into a
control on fsubVisit. As I said, the relationship between
tblMother
and
tblChild already establishes the necessary key fields. Clicking
into
a
subform causes the parent record to be saved, so the record in
tblChild
will
be saved without any extra code needed. Just set up the
relationships
and
the forms/subforms, go to a Mother record, and click into a field
on
fsubVisit. You may want a placeholder in tblChild (Baby07 in the
C_FName
field, or something) for ease of reference by humans, but that is
up
to
you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

message
My apologies for not being clear enough in my prior posts. I'm
not
trynig
to
add a record because I think it needs to be there. The record has
to
be
there
because there is also a visits table that the staff uses, which
is
linked
to
the birth table. In order to record a visit, we have to link it
to a
birth.
Since visits can take place during the prenatal phase, we have to
have
the
birth record in place beforehand. Now, the staff can certainly
add a
birth
record, themselves. However, I just wanted to make sure the
record
was
there
from the get-go, in case they forget. THanks for your reply and
concern,
Bruce. Any ideas?

:

I don't get it. It sounds as if you want to create a record in
the
Child
table that consists solely of a primary key (BirthID) and a
foreign
key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not
need
to
insert a related record*. The relationship between the tables,
and
the
form/subform relationship (assuming the linking fields are
properly
established) will take care of that. The existence of a related
(subform)
record presupposes that the record contains data not found in
the
main
table. Presumably there is some reason for creating a birth
record.

 

[Guest]

I forgot to mention that, yes, the primay keys are autonumber fields.Thanks
for your thoughts and suggestions, Bruce. Sorry that we were going in
different directions. Or at least, I was. Perhaps my inquiry is not
realistic, or as dubious as the new "multi-value" field in 2007, which looks
like microsoft giving the rasberry to normalization.

I am running out of ways to say this: you enter the record into tblBirth
via the subform by entering information into the record. You seem
determined to use a procedure on the main form to accomplish this, but I
don't know what you intend to accomplish that you could not accomplish by
the means I have suggested.

You may need to use a variant of the DMax code I suggested for fsubBirth to
create the Mother PK in tblMother. Autonumber may give you problems, in
that the ChildPK, when created via the code I suggested, is created as soon
as you create a new Mother record, but the FK in tblChild is not established
until you start typing information into the Mother record. By the way, the
DMax code should be:
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),0) + 1
I had a 1 where there is now a 0.

So, in the Current event for frmMother:

If Me.NewRecord Then
Me.MotherPK = Nz(DMax("[MotherPK]","[tblMother]"),0) + 1
End If

In the Current event for fsubChild:

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

I'm hearing that you don't want to create a birth record at all other than
the key fields, so that you can enter visit information without entering any
birth information at all. But now you seem to be saying you want to create
a new Birth record. Each Birth event happens once, and needs one record.
I'll leave it to you to decide how you plan to handle twins. For all I know
this database is for information about prenatal visits only, so the Birth
information is really pregnancy information, and the number of children is
irrelevant.
As soon as you create a Mother record there will be a related Birth record
if you use the code I have suggested. What do you need beyond that?

Now we're reading on the same page, Bruce. My problem is largely one of
syntax, I suspect: Adding the new birth record into the subform from a
procedure run in the main form. So this stuff fails (and variations in
syntax):
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec

and this (calling the subroutine in the birth form from the main mother
form:
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

george

My mistake. I don't think I have ever tried to create a record with no
data
except for linked fields. I experimented for a while, and discovered
that
if you use code to set the value of BirthPK you can add a record to
tblVisit
without adding any other information to tblBirth. Using Default Value to
set the value does not have the same effect. You could set BirthPK to a
Number field (Long Integer) in table design view, then use code like this
in
the Current event for frmBirth (which functions as a subform in
frmMother):

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

Bruce,
Thanks for the additional info. I rigged up a new set of tables,
following
your example. In each case, the PK was only the single ID field of the
table,
excluding the FK field. Now, it is true that I can enter a visit date
into
the visit table and have it generate a visit PK value that can be
saved.
However, the Birth FK field in the Visit table is not auto-filled,
since
there is no Birth record.

The birth record in the form appears to show the linked mom FK value,
but
it
does not auto-generate a birth PK value, as the record has not been
touched
(that is, after creating a mom record, I immediately created a vist
record,
per your scenario). When I view the tables, there are records in the
mom
table and visit table only. Here is my design, as you indicated.

tblMom tblBirth tblVisit
MomPK --------| BirthPK ------| VisitPK
MomLN |------- MomFK |-------BirthFK
MomFN DOB VisitDate

So it LOOKS like it is working, but when you view the tables, there is
no
birth record and no birth key, without actually going into the birth
record
at some point. Perhaps I missed something in all this.

BTW, I then modified the Birth table, making BirthPK+MomK a compound
PK.
This also forced me to add the MomFK into the Visit table as part of
the
FK.
In this scenario, I could not create a visit record without first
having a
Birth record. But it avoided creating child records with no null Fk
fields.

George

:

You could hide or unhide the visit subform (I assume you mean the
form,
not
the table) as you wish:
Me.subformControlName.Visible = Not Me.subformControlName
You may want to look into adding a tab control is space becomes a
problem.
My point about adding the Visit record is that with the nested subform
structure and the proper table relationships, a Birth record should be
created as soon as a Visit record is created. As I mentioned,
clicking
into
a subform causes the parent record to be saved. Note the MotherID
(from
tblMother) on a record with no Visit information (and no specific
Child
information). Look through tblChild to see if there is a record with
that
MotherID in the MotherID field. Now note the MotherID in a tblMother
record
that has specific Child information (DOB or something). Again, search
through tblChild. You should find a record with that MotherID in the
MotherID field. Now note MotherID in a tblMother record that has no
specific Child information, but that has Visit information. Again,
you
should find a record in tblChild with that MotherID in the MotherID
field.
The easiest way to "note MotherID" is to place onto the form a
temporary
text box bound to that field.
Be aware that a subform control has Link Parent and Link Child
properties.
Understand that these are the same on every subform control, and have
nothing to do with your table names.

message
This pretty much matches my design, though I had made the visit
table
viewable thorugh a command button (for reasons of space). When a
visit
record
exists, there is no problem with the user creating a visit record,
of
course,
as long as a birth record exists. From a hands-on point of view,
there
is
no
issue on this point. I'm sure that the users are not going to try
and
insert
a visit without a birth record, so I reckon trying to anticipate
them
by
auto-inserting the birth record ahead of time is probably not worth
the
effort. Thanks for your patience and the information, Bruce.
George

:

Yes, that is an important detail about the visits table. As I
understand
it, each birth (which includes prenatal care) may have several
visits
associated with it. That means there should be a tblVisit that is
related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform within
fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words.
With
this
structure you should be able to add a visit record simply by
clicking
into a
control on fsubVisit. As I said, the relationship between
tblMother
and
tblChild already establishes the necessary key fields. Clicking
into
a
subform causes the parent record to be saved, so the record in
tblChild
will
be saved without any extra code needed. Just set up the
relationships
and
the forms/subforms, go to a Mother record, and click into a field
on
fsubVisit. You may want a placeholder in tblChild (Baby07 in the
C_FName
field, or something) for ease of reference by humans, but that is
up
to
you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

message
My apologies for not being clear enough in my prior posts. I'm
not
trynig
to
add a record because I think it needs to be there. The record has
to
be
there
because there is also a visits table that the staff uses, which
is
linked
to
the birth table. In order to record a visit, we have to link it
to a
birth.
Since visits can take place during the prenatal phase, we have to
have
the
birth record in place beforehand. Now, the staff can certainly
add a
birth
record, themselves. However, I just wanted to make sure the
record
was
there
from the get-go, in case they forget. THanks for your reply and
concern,
Bruce. Any ideas?

:

I don't get it. It sounds as if you want to create a record in
the
Child
table that consists solely of a primary key (BirthID) and a
foreign
key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do not
need
to
insert a related record*. The relationship between the tables,
and
the
form/subform relationship (assuming the linking fields are
properly
established) will take care of that. The existence of a related
(subform)
record presupposes that the record contains data not found in
the
main
table. Presumably there is some reason for creating a birth
record.

 

[BruceM]

I have not used Access 2007, so have not confronted the multi-value field,
but the thought of it is rather troubling. Maybe I'm locked into a way of
thinking, but it doesn't seem right.
I believe you will find that using the manually incremented numbers rather
than autonumber will let you do what you need to do. No further code is
needed. When you create a Mother record, the Birth record will be created,
provided the relationships and the linking fields are set up properly. You
may enter Visit information immediately, or at another time when you return
to that Mother's record. You may manually enter information into the Birth
record (DueDate or whatever) at any time, or never, at your discretion.
The trouble with autonumber in yor situation seems to be that values
assigned as default values (including autonumber, even though it may be
inaccurate to refer to it as a default value) are not assigned unless the
record is saved, which will not occur unless information is entered manually
into another field. On the other hand, when a PK field's value is assigned
by code, the value immediately becomes the FK field's value in the related
table. On the other hand, if the number is assigned by code to the Birth
table (via the subform's current event), but the PK in tblMother is
autonumber, when you go to a new record in tblMother the PK field in
tblBirth is assigned immediately, before the PK field is generated in
tblMother. That means there is a FK field before there is a PK field, and
you get an error. If both PKs are assigned by code, the problems go away.

I forgot to mention that, yes, the primay keys are autonumber fields.Thanks
for your thoughts and suggestions, Bruce. Sorry that we were going in
different directions. Or at least, I was. Perhaps my inquiry is not
realistic, or as dubious as the new "multi-value" field in 2007, which
looks
like microsoft giving the rasberry to normalization.

I am running out of ways to say this: you enter the record into tblBirth
via the subform by entering information into the record. You seem
determined to use a procedure on the main form to accomplish this, but I
don't know what you intend to accomplish that you could not accomplish by
the means I have suggested.

You may need to use a variant of the DMax code I suggested for fsubBirth
to
create the Mother PK in tblMother. Autonumber may give you problems, in
that the ChildPK, when created via the code I suggested, is created as
soon
as you create a new Mother record, but the FK in tblChild is not
established
until you start typing information into the Mother record. By the way,
the
DMax code should be:
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),0) + 1
I had a 1 where there is now a 0.

So, in the Current event for frmMother:

If Me.NewRecord Then
Me.MotherPK = Nz(DMax("[MotherPK]","[tblMother]"),0) + 1
End If

In the Current event for fsubChild:

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

I'm hearing that you don't want to create a birth record at all other
than
the key fields, so that you can enter visit information without entering
any
birth information at all. But now you seem to be saying you want to
create
a new Birth record. Each Birth event happens once, and needs one record.
I'll leave it to you to decide how you plan to handle twins. For all I
know
this database is for information about prenatal visits only, so the Birth
information is really pregnancy information, and the number of children
is
irrelevant.
As soon as you create a Mother record there will be a related Birth
record
if you use the code I have suggested. What do you need beyond that?

Now we're reading on the same page, Bruce. My problem is largely one of
syntax, I suspect: Adding the new birth record into the subform from a
procedure run in the main form. So this stuff fails (and variations in
syntax):
Me.[subtblhugbirths SubForm].Form.cmdAddBirth.SetFocus
DoCmd.GoToRecord , , acNewRec

and this (calling the subroutine in the birth form from the main mother
form:
Me.[subtblhugbirths SubForm].Form.cmdAddBirth_Click

george
:

My mistake. I don't think I have ever tried to create a record with
no
data
except for linked fields. I experimented for a while, and discovered
that
if you use code to set the value of BirthPK you can add a record to
tblVisit
without adding any other information to tblBirth. Using Default Value
to
set the value does not have the same effect. You could set BirthPK to
a
Number field (Long Integer) in table design view, then use code like
this
in
the Current event for frmBirth (which functions as a subform in
frmMother):

If Me.NewRecord Then
Me.BirthPK = Nz(DMax("[BirthPK]","[tblBirth]"),1) + 1
End If

message
Bruce,
Thanks for the additional info. I rigged up a new set of tables,
following
your example. In each case, the PK was only the single ID field of
the
table,
excluding the FK field. Now, it is true that I can enter a visit
date
into
the visit table and have it generate a visit PK value that can be
saved.
However, the Birth FK field in the Visit table is not auto-filled,
since
there is no Birth record.

The birth record in the form appears to show the linked mom FK
value,
but
it
does not auto-generate a birth PK value, as the record has not been
touched
(that is, after creating a mom record, I immediately created a vist
record,
per your scenario). When I view the tables, there are records in the
mom
table and visit table only. Here is my design, as you indicated.

tblMom tblBirth tblVisit
MomPK --------| BirthPK ------| VisitPK
MomLN |------- MomFK |-------BirthFK
MomFN DOB VisitDate

So it LOOKS like it is working, but when you view the tables, there
is
no
birth record and no birth key, without actually going into the birth
record
at some point. Perhaps I missed something in all this.

BTW, I then modified the Birth table, making BirthPK+MomK a compound
PK.
This also forced me to add the MomFK into the Visit table as part of
the
FK.
In this scenario, I could not create a visit record without first
having a
Birth record. But it avoided creating child records with no null Fk
fields.

George

:

You could hide or unhide the visit subform (I assume you mean the
form,
not
the table) as you wish:
Me.subformControlName.Visible = Not Me.subformControlName
You may want to look into adding a tab control is space becomes a
problem.
My point about adding the Visit record is that with the nested
subform
structure and the proper table relationships, a Birth record should
be
created as soon as a Visit record is created. As I mentioned,
clicking
into
a subform causes the parent record to be saved. Note the MotherID
(from
tblMother) on a record with no Visit information (and no specific
Child
information). Look through tblChild to see if there is a record
with
that
MotherID in the MotherID field. Now note the MotherID in a
tblMother
record
that has specific Child information (DOB or something). Again,
search
through tblChild. You should find a record with that MotherID in
the
MotherID field. Now note MotherID in a tblMother record that has
no
specific Child information, but that has Visit information. Again,
you
should find a record in tblChild with that MotherID in the MotherID
field.
The easiest way to "note MotherID" is to place onto the form a
temporary
text box bound to that field.
Be aware that a subform control has Link Parent and Link Child
properties.
Understand that these are the same on every subform control, and
have
nothing to do with your table names.

message
This pretty much matches my design, though I had made the visit
table
viewable thorugh a command button (for reasons of space). When a
visit
record
exists, there is no problem with the user creating a visit
record,
of
course,
as long as a birth record exists. From a hands-on point of view,
there
is
no
issue on this point. I'm sure that the users are not going to try
and
insert
a visit without a birth record, so I reckon trying to anticipate
them
by
auto-inserting the birth record ahead of time is probably not
worth
the
effort. Thanks for your patience and the information, Bruce.
George

:

Yes, that is an important detail about the visits table. As I
understand
it, each birth (which includes prenatal care) may have several
visits
associated with it. That means there should be a tblVisit that
is
related
to tblBirth.

tblMother
MotherID (primary key, or PK)
M_FName
M_LName
etc.

tblChild
ChildID (PK)
MotherID (foreign key, or FK)
C_FName
C_LName (it may not always be the same as the mother's)
DOB

tblVisit
VisitID (PK)
ChildID (FK)
VisitReason

A subform (fsubVisit) based on tblVisit would by a subform
within
fsubChild
(the name I am giving to your Child subform for purposes of this
discussion). It is a subform within a subform, in other words.
With
this
structure you should be able to add a visit record simply by
clicking
into a
control on fsubVisit. As I said, the relationship between
tblMother
and
tblChild already establishes the necessary key fields. Clicking
into
a
subform causes the parent record to be saved, so the record in
tblChild
will
be saved without any extra code needed. Just set up the
relationships
and
the forms/subforms, go to a Mother record, and click into a
field
on
fsubVisit. You may want a placeholder in tblChild (Baby07 in
the
C_FName
field, or something) for ease of reference by humans, but that
is
up
to
you.
It doesn't affect the design.

Note the question in the previous post about Allow Edits, etc.

message
My apologies for not being clear enough in my prior posts. I'm
not
trynig
to
add a record because I think it needs to be there. The record
has
to
be
there
because there is also a visits table that the staff uses,
which
is
linked
to
the birth table. In order to record a visit, we have to link
it
to a
birth.
Since visits can take place during the prenatal phase, we have
to
have
the
birth record in place beforehand. Now, the staff can certainly
add a
birth
record, themselves. However, I just wanted to make sure the
record
was
there
from the get-go, in case they forget. THanks for your reply
and
concern,
Bruce. Any ideas?

:

I don't get it. It sounds as if you want to create a record
in
the
Child
table that consists solely of a primary key (BirthID) and a
foreign
key
(MomID). Are both of those fields autonumber?
I think you're missing something here, which is that you *do
not
need
to
insert a related record*. The relationship between the
tables,
and
the
form/subform relationship (assuming the linking fields are
properly
established) will take care of that. The existence of a
related
(subform)
record presupposes that the record contains data not found in
the
main
table. Presumably there is some reason for creating a birth
record.

 

[Rick A.B.]

George,

You could try something like this in the after update event of your
main form.

If IsNull(Forms!MainForm![SubMainForm.Form!SubFormField) Then
Me![SubMainForm].Form!SubFormField = "Whatever"
End If

As long as the main form and subform are linked that should create a
record in the subform table with out you having to touch it.