tabbed subform related new record.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you please help this amateur.

I have subforms on tabs and since linked by common id (InvNum) they display
and edit fine. I can add a new record to the first tab (jobs) successfully.
I am at a loss as to how to create the related records on adjacent tab for
(labor). All relate to InvNum (key field in each) and are in their own
tables. Job table is related one-to-one to labor, RI enforced, cascade
update/delete is true.

My subforms are DataEntry and Additions = No, so I set to Yes after setting
focus to the subform on the adjacent tab.

Code:
Me![BCfrm].SetFocus
Me![BCfrm].Form.DataEntry = True
Me![BCfrm].Form.AllowAdditions = True
RunCommand acCmdRecordsGoToNew
Me![BCfrm].Form!InvNum = Me![Jobfrm].Form!InvNum
Me![BCfrm].Form!txtholdFocus.SetFocus
DoCmd.Save

Have I entirely missed the boat on this?
 
Chris said:
I have subforms on tabs and since linked by common id (InvNum) they display
and edit fine. I can add a new record to the first tab (jobs) successfully.
I am at a loss as to how to create the related records on adjacent tab for
(labor). All relate to InvNum (key field in each) and are in their own
tables. Job table is related one-to-one to labor, RI enforced, cascade
update/delete is true.

My subforms are DataEntry and Additions = No, so I set to Yes after setting
focus to the subform on the adjacent tab.

Code:
Me![BCfrm].SetFocus
Me![BCfrm].Form.DataEntry = True
Me![BCfrm].Form.AllowAdditions = True
RunCommand acCmdRecordsGoToNew
Me![BCfrm].Form!InvNum = Me![Jobfrm].Form!InvNum
Me![BCfrm].Form!txtholdFocus.SetFocus
DoCmd.Save
Click to expand...


Well, I think you've lost me me. However there are a couple
of things that might help you.

1) DoCmd.Save save the active object's design. The active
object is normally the running form, so that line makes no
sense in this context. I'm pretty sure that you are trying
to save the new labor record, which can be done using:
Me![BCfrm].Form!Dirty = False

2) You can synchronize two subforms by adding a text box
(named txtLink) to the main form's header/footer section.
In the Jobs subform's Current event, set the text box to the
JobID field:
Parent.txtLink = Me.JobID
Then setting the labor subform control's Link Master
property to InvNum,txtLink and set Link Child to
InvNum,JobID

The latter point may not be relevant in your case, since you
said the tables are all related one-to-one, which seems more
than a little odd to me. Normally, I would expect an
invoice to related one-to-many to jobs and jobs one-to-many
to labor.
 
I am an amateur, so not a lot of this makes sense to me. No more DoCmd.Save

Still, I don't understand why this code will not create a new record.

Me![BCfrm].Form.DataEntry = True
Me![BCfrm].Form.AllowAdditions = True
RunCommand acCmdRecordsGoToNew
Me![BCfrm].Form!InvNum = Me![Jobfrm].Form!InvNum

I invoke the code via a button on Me![Jobfrm] (which successfully uses the
code modified to increment InvNum using DMax). I set the focus to [BCfrm].
The two subforms are on the same tab control,different tabs. My
relationships are Client - many jobs. Jobs - one BCfrm and one Laborfrm each
InvNum. I do not have multiple visits ([BCfrm]), nor multiple employees
([Laborfrm]) per InvNum -- they are always one record each.

Why would the code that works at [Jobfrm] not also work at the others? The
difference is only (as I see) the InvNum creation at [Jobfrm] and "input" on
the related records.

Please help me understand and solve this.

--
Thankyou,
Greg (Sharing problems with Chris)


Marshall Barton said:
Chris said:
I have subforms on tabs and since linked by common id (InvNum) they display
and edit fine. I can add a new record to the first tab (jobs) successfully.
I am at a loss as to how to create the related records on adjacent tab for
(labor). All relate to InvNum (key field in each) and are in their own
tables. Job table is related one-to-one to labor, RI enforced, cascade
update/delete is true.

My subforms are DataEntry and Additions = No, so I set to Yes after setting
focus to the subform on the adjacent tab.

Code:
Me![BCfrm].SetFocus
Me![BCfrm].Form.DataEntry = True
Me![BCfrm].Form.AllowAdditions = True
RunCommand acCmdRecordsGoToNew
Me![BCfrm].Form!InvNum = Me![Jobfrm].Form!InvNum
Me![BCfrm].Form!txtholdFocus.SetFocus
DoCmd.Save
Click to expand...


Well, I think you've lost me me. However there are a couple
of things that might help you.

1) DoCmd.Save save the active object's design. The active
object is normally the running form, so that line makes no
sense in this context. I'm pretty sure that you are trying
to save the new labor record, which can be done using:
Me![BCfrm].Form!Dirty = False

2) You can synchronize two subforms by adding a text box
(named txtLink) to the main form's header/footer section.
In the Jobs subform's Current event, set the text box to the
JobID field:
Parent.txtLink = Me.JobID
Then setting the labor subform control's Link Master
property to InvNum,txtLink and set Link Child to
InvNum,JobID

The latter point may not be relevant in your case, since you
said the tables are all related one-to-one, which seems more
than a little odd to me. Normally, I would expect an
invoice to related one-to-many to jobs and jobs one-to-many
to labor.
Click to expand...
 
Chris said:
I am an amateur, so not a lot of this makes sense to me. No more DoCmd.Save

Still, I don't understand why this code will not create a new record.

Me![BCfrm].Form.DataEntry = True
Me![BCfrm].Form.AllowAdditions = True
RunCommand acCmdRecordsGoToNew
Me![BCfrm].Form!InvNum = Me![Jobfrm].Form!InvNum

I invoke the code via a button on Me![Jobfrm] (which successfully uses the
code modified to increment InvNum using DMax). I set the focus to [BCfrm].
The two subforms are on the same tab control,different tabs. My
relationships are Client - many jobs. Jobs - one BCfrm and one Laborfrm each
InvNum. I do not have multiple visits ([BCfrm]), nor multiple employees
([Laborfrm]) per InvNum -- they are always one record each.

Why would the code that works at [Jobfrm] not also work at the others? The
difference is only (as I see) the InvNum creation at [Jobfrm] and "input" on
the related records.
Click to expand...


I am still not entirely clear about your arrangement, but
the above code will only manipulate the BCfrm if the code in
in the main form. I think you are saying that it is in the
jobs subform, in which case Me does not have the BCfrm
subform control and hence you get some kind of error. (Your
code would only be valid if the labor subform was on the
jobs subform.) Also note that you are referencing the
InvNum control on the jobs subform as if the code were on
the main form.

To get from one subform on a main form to another subform on
the main form use a reference like:

Parent![BCfrm].Form.DataEntry = True
Parent![BCfrm].Form.AllowAdditions = True
Parent![BCfrm].Form.SetFocus
RunCommand acCmdRecordsGoToNew
Parent![BCfrm].Form!InvNum = Me!InvNum

I think the labor subform needs the focus to indicate the
active object for acCmdRecordsGoToNew to operate on (I don't
do it this way so I'm not sure).

I'm pretty sure that you don't need to set the DataEntry
property, so try removing that line.
 
Thanks for helping me Marshall.

You make sense, but it fails. I'm getting a write conflict, changed by
another user....and when I select drop changes it fails 2465 can't find BCfrm.

Yes, code to create the new job is on [Jobsfrm] btn, as is code to create
new [BCfrm] record. BTW, the write conflict occurs any button on [Jobsfrm].
What could cause that?

Any ideas?

--
Thanks for your help,
Greg


Marshall Barton said:
Chris said:
I am an amateur, so not a lot of this makes sense to me. No more DoCmd.Save

Still, I don't understand why this code will not create a new record.

Me![BCfrm].Form.DataEntry = True
Me![BCfrm].Form.AllowAdditions = True
RunCommand acCmdRecordsGoToNew
Me![BCfrm].Form!InvNum = Me![Jobfrm].Form!InvNum

I invoke the code via a button on Me![Jobfrm] (which successfully uses the
code modified to increment InvNum using DMax). I set the focus to [BCfrm].
The two subforms are on the same tab control,different tabs. My
relationships are Client - many jobs. Jobs - one BCfrm and one Laborfrm each
InvNum. I do not have multiple visits ([BCfrm]), nor multiple employees
([Laborfrm]) per InvNum -- they are always one record each.

Why would the code that works at [Jobfrm] not also work at the others? The
difference is only (as I see) the InvNum creation at [Jobfrm] and "input" on
the related records.
Click to expand...


I am still not entirely clear about your arrangement, but
the above code will only manipulate the BCfrm if the code in
in the main form. I think you are saying that it is in the
jobs subform, in which case Me does not have the BCfrm
subform control and hence you get some kind of error. (Your
code would only be valid if the labor subform was on the
jobs subform.) Also note that you are referencing the
InvNum control on the jobs subform as if the code were on
the main form.

To get from one subform on a main form to another subform on
the main form use a reference like:

Parent![BCfrm].Form.DataEntry = True
Parent![BCfrm].Form.AllowAdditions = True
Parent![BCfrm].Form.SetFocus
RunCommand acCmdRecordsGoToNew
Parent![BCfrm].Form!InvNum = Me!InvNum

I think the labor subform needs the focus to indicate the
active object for acCmdRecordsGoToNew to operate on (I don't
do it this way so I'm not sure).

I'm pretty sure that you don't need to set the DataEntry
property, so try removing that line.
Click to expand...
 
Chris said:
You make sense, but it fails. I'm getting a write conflict, changed by
another user....and when I select drop changes it fails 2465 can't find BCfrm.

Yes, code to create the new job is on [Jobsfrm] btn, as is code to create
new [BCfrm] record. BTW, the write conflict occurs any button on [Jobsfrm].
What could cause that?
Click to expand...


That error can be caused by some other code also manipulting
the BCfrm form's data.

Did you remember to save the record after the previous edit?

Is there any code in the BCfrm form that changes a bound
control?

Make sure that you are referencing the subform **control**,
which may have a different name from the form object it
displays.
 
Thanks again Marshall.

I'm now getting failure on dirty.

I'd like to try a different approach toward resolution, please.

Given:
Main form -- clients

Subform on tab control -- Jobs [Jobfrm] (many for each client)
Button to add new record to jobs table -- need code.
Button to set focus to [BCfrm] and create new record in BCfrm table -- need
code.

Subform on Jobs tab -- Reservation [BCfrm] (one for each job)
Button to set focus to [Laborfrm] and create new record in Labor table--
need code.

Subform on Jobs tab -- Labor [Laborfrm] (one for each job)

New job -- InvNum is Key and ClientID is table-required
Reservation -- InvNum is Key
Labor -- InvNum is key

Job's ClientID and each subform's InvNum control sources are name the same
with prefix "txt" -- InvNum and txtInvNum.

I am certain this is a simple thing for professionals, I am not. I think I
am painted (of my own doing) into a mental corner. Pleas have pity.

Frankly, given the Jobs new record code and Reservation new record code, I
can do the Labor new record.

--
I appreciate your time.
Greg


Marshall Barton said:
Chris said:
You make sense, but it fails. I'm getting a write conflict, changed by
another user....and when I select drop changes it fails 2465 can't find BCfrm.

Yes, code to create the new job is on [Jobsfrm] btn, as is code to create
new [BCfrm] record. BTW, the write conflict occurs any button on [Jobsfrm].
What could cause that?
Click to expand...


That error can be caused by some other code also manipulting
the BCfrm form's data.

Did you remember to save the record after the previous edit?

Is there any code in the BCfrm form that changes a bound
control?

Make sure that you are referencing the subform **control**,
which may have a different name from the form object it
displays.
Click to expand...
 
Chris said:
I'm now getting failure on dirty.

I'd like to try a different approach toward resolution, please.

Given:
Main form -- clients

Subform on tab control -- Jobs [Jobfrm] (many for each client)
Button to add new record to jobs table -- need code.
Button to set focus to [BCfrm] and create new record in BCfrm table -- need
code.

Subform on Jobs tab -- Reservation [BCfrm] (one for each job)
Button to set focus to [Laborfrm] and create new record in Labor table--
need code.

Subform on Jobs tab -- Labor [Laborfrm] (one for each job)

New job -- InvNum is Key and ClientID is table-required
Reservation -- InvNum is Key
Labor -- InvNum is key

Job's ClientID and each subform's InvNum control sources are name the same
with prefix "txt" -- InvNum and txtInvNum.

I am certain this is a simple thing for professionals, I am not. I think I
am painted (of my own doing) into a mental corner.
Click to expand...


Sorry, but I just don't understand what's different now. It
seems like all you did was put all the subforms on the same
tab page, which will have no effect on how you do things.

I don't even understand why you feel that you must create
new records. This is is normally left for the user to do
and is automatic when the subforms are properly linked (see
my first reply) and a user enters something in any bound
control.
 
Huh? I don't understand at all that reply. Am I in circles? Help me
understand.

Marshall: It seems like all you did was put all the subforms on the same
tab page, which will have no effect on how you do things.

No effect? What effect? I am trying to learn how to do this.

Marshall: I don't even understand why you feel that you must create
new records.

How else would the other tables get the related record?

Marshall: This is is normally left for the user to do . . .

How would the user do this?

Marshall: ... and is automatic when the subforms are properly linked (see
my first reply) and a user enters something in any bound
control.

I believe my suborms are properly linked, so why am I having problems?

--
Thanks for your help,
Greg


Marshall Barton said:
Chris said:
I'm now getting failure on dirty.

I'd like to try a different approach toward resolution, please.

Given:
Main form -- clients

Subform on tab control -- Jobs [Jobfrm] (many for each client)
Button to add new record to jobs table -- need code.
Button to set focus to [BCfrm] and create new record in BCfrm table -- need
code.

Subform on Jobs tab -- Reservation [BCfrm] (one for each job)
Button to set focus to [Laborfrm] and create new record in Labor table--
need code.

Subform on Jobs tab -- Labor [Laborfrm] (one for each job)

New job -- InvNum is Key and ClientID is table-required
Reservation -- InvNum is Key
Labor -- InvNum is key

Job's ClientID and each subform's InvNum control sources are name the same
with prefix "txt" -- InvNum and txtInvNum.

I am certain this is a simple thing for professionals, I am not. I think I
am painted (of my own doing) into a mental corner.
Click to expand...


Sorry, but I just don't understand what's different now. It
seems like all you did was put all the subforms on the same
tab page, which will have no effect on how you do things.

I don't even understand why you feel that you must create
new records. This is is normally left for the user to do
and is automatic when the subforms are properly linked (see
my first reply) and a user enters something in any bound
control.
Click to expand...
 
After further review...
My dirty failure was due to ".form." versus ".form!" but that didn't solve
my record creation issue.

And after more searching...
I am now creating the records in related tables without any code. I guess
that's what you were attempting to tell me, but I was in a mindset,
otherwise.

I have reduced my issue of one of requery/refresh timing so I can use the
new record created in [BCfrm] to edit immediately.

This should be easy to resolve.

Thanks for your help.

--
Thanks for your help,
Greg


Chris said:
Huh? I don't understand at all that reply. Am I in circles? Help me
understand.

Marshall: It seems like all you did was put all the subforms on the same
tab page, which will have no effect on how you do things.

No effect? What effect? I am trying to learn how to do this.

Marshall: I don't even understand why you feel that you must create
new records.

How else would the other tables get the related record?

Marshall: This is is normally left for the user to do . . .

How would the user do this?

Marshall: ... and is automatic when the subforms are properly linked (see
my first reply) and a user enters something in any bound
control.

I believe my suborms are properly linked, so why am I having problems?

--
Thanks for your help,
Greg


Marshall Barton said:
Chris said:
I'm now getting failure on dirty.

I'd like to try a different approach toward resolution, please.

Given:
Main form -- clients

Subform on tab control -- Jobs [Jobfrm] (many for each client)
Button to add new record to jobs table -- need code.
Button to set focus to [BCfrm] and create new record in BCfrm table -- need
code.

Subform on Jobs tab -- Reservation [BCfrm] (one for each job)
Button to set focus to [Laborfrm] and create new record in Labor table--
need code.

Subform on Jobs tab -- Labor [Laborfrm] (one for each job)

New job -- InvNum is Key and ClientID is table-required
Reservation -- InvNum is Key
Labor -- InvNum is key

Job's ClientID and each subform's InvNum control sources are name the same
with prefix "txt" -- InvNum and txtInvNum.

I am certain this is a simple thing for professionals, I am not. I think I
am painted (of my own doing) into a mental corner.
Click to expand...


Sorry, but I just don't understand what's different now. It
seems like all you did was put all the subforms on the same
tab page, which will have no effect on how you do things.

I don't even understand why you feel that you must create
new records. This is is normally left for the user to do
and is automatic when the subforms are properly linked (see
my first reply) and a user enters something in any bound
control.
Click to expand...
Click to expand...
 
Chris said:
After further review...
My dirty failure was due to ".form." versus ".form!" but that didn't solve
my record creation issue.

And after more searching...
I am now creating the records in related tables without any code. I guess
that's what you were attempting to tell me, but I was in a mindset,
otherwise.

I have reduced my issue of one of requery/refresh timing so I can use the
new record created in [BCfrm] to edit immediately.
Click to expand...


You do not need to create a record. That will happen when a
user enters some stat in a new record. The only trick in
your situation is to turn off AllowAdditions when a record
already has already been created. This can be done by code
in the main form's Current event procedure:

With Me.subformcontrolname.Form
.AllowAdditions = (.Recordset.RecordCount = 0)
End With

To prevent users from adding two records in the same
session, turn it off in the subform's AfterInsert event:

Me.AllowAdditions = False
 
Thank you so much for helping.
--
Thanks for your help,
Chris


Marshall Barton said:
Chris said:
After further review...
My dirty failure was due to ".form." versus ".form!" but that didn't solve
my record creation issue.

And after more searching...
I am now creating the records in related tables without any code. I guess
that's what you were attempting to tell me, but I was in a mindset,
otherwise.

I have reduced my issue of one of requery/refresh timing so I can use the
new record created in [BCfrm] to edit immediately.
Click to expand...


You do not need to create a record. That will happen when a
user enters some stat in a new record. The only trick in
your situation is to turn off AllowAdditions when a record
already has already been created. This can be done by code
in the main form's Current event procedure:

With Me.subformcontrolname.Form
.AllowAdditions = (.Recordset.RecordCount = 0)
End With

To prevent users from adding two records in the same
session, turn it off in the subform's AfterInsert event:

Me.AllowAdditions = False
Click to expand...
 
Back
Top