Sub-Form data entry

[John S]

I have a sub-form (continuous forms) and on the main form there is a button
that opens a seperate Data Entry Form. On the Data Entry Form there is a
standard Close button.

Once the Data Entry Form closes - the underlying table doesn't update. Is
this because the button that opens the Data Entry Form is on the main form
instead of the sub-form? If so, can anyone suggest a solution (other than
moving the button that opens the Data Entry Form)?

Thanks in anticipation.

Regards,
John.

 

[Arvin Meyer [MVP]]

In the Data Entry Form make some changes in the code behind the Close
button. Add the following before the form closes:


If Me.Dirty = True Then Me.Dirty = False 'saves the record
Forms!MainFormName!SubformControlName.Form.Requery

of course you will need to substitute your main form name and your subform
control name.

 

[John S]

Thanks Arvin:

I tried that - I'm still getting the message "You cannot edit or change a
record because a related record is required in [mainform]".

Regards,
John.

 

[Jeff Boyce]

Hmmm? Did you mention that message before?

Access is telling you that you can't enter a "child" record until you've
indicated its "parent".

Are you using the "parent" and "child" properties of the subform control on
your main form to tell the subform the "parent" to which the "child" records
belong?

--

Regards

Jeff Boyce
Microsoft Access MVP

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

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

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

Thanks Arvin:

I tried that - I'm still getting the message "You cannot edit or change a
record because a related record is required in [mainform]".

Regards,
John.

In the Data Entry Form make some changes in the code behind the Close
button. Add the following before the form closes:


If Me.Dirty = True Then Me.Dirty = False 'saves the record
Forms!MainFormName!SubformControlName.Form.Requery

of course you will need to substitute your main form name and your
subform
control name.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com





.

 

[John S]

Jeff,

Sorry - yes - the sub-form is the many side of a one-to-many relationship.
You've hit the nail on the head!

Is there an easy way to update the 'one' side field (ie main form/table)
from my Data Entry form?

Regards,
John.

Hmmm? Did you mention that message before?

Access is telling you that you can't enter a "child" record until you've
indicated its "parent".

Are you using the "parent" and "child" properties of the subform control on
your main form to tell the subform the "parent" to which the "child" records
belong?

--

Regards

Jeff Boyce
Microsoft Access MVP

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

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

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

Thanks Arvin:

I tried that - I'm still getting the message "You cannot edit or change a
record because a related record is required in [mainform]".

Regards,
John.

In the Data Entry Form make some changes in the code behind the Close
button. Add the following before the form closes:


If Me.Dirty = True Then Me.Dirty = False 'saves the record
Forms!MainFormName!SubformControlName.Form.Requery

of course you will need to substitute your main form name and your
subform
control name.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I have a sub-form (continuous forms) and on the main form there is a
button
that opens a seperate Data Entry Form. On the Data Entry Form there is
a
standard Close button.

Once the Data Entry Form closes - the underlying table doesn't update.
Is
this because the button that opens the Data Entry Form is on the main
form
instead of the sub-form? If so, can anyone suggest a solution (other
than
moving the button that opens the Data Entry Form)?

Thanks in anticipation.

Regards,
John.


.

.

 

[Arvin Meyer [MVP]]

Jeff,

Sorry - yes - the sub-form is the many side of a one-to-many relationship.
You've hit the nail on the head!

Is there an easy way to update the 'one' side field (ie main form/table)
from my Data Entry form?

If the data entry form is bound to the subform's table, as is the usual
case, the answer is no. Why would you want to enter data on the many-side
without a one-side record? That's like a doctor creating an operation when
he hasn't contacted the patient yet.

 

[John S]

Arvin,

You may have misunderstood what I am trying to do.

If you enter a new record direct into a sub-form control then the 'one' side
of the relationship is automatically updated.

When using a seperate Data Entry Form (bound to the sub-form's table) MS
Access doesn't know that the 'one' side needs to be updated. Presumably
because there is no Link Child Fields and Link Master Fields properties on
the Data Entry Form.

In short, I need to tell the Data Entry Form to relate to the current main
form record. Does anyone know how?

Regards,
John.

 

[Jeff Boyce]

Using the parent and child link fields is how you tell Access to use the
current main form that the (new) subform record belongs to it.

--

Regards

Jeff Boyce
Microsoft Access MVP

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

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

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

 

[Arvin Meyer [MVP]]

If you have created a mainform record, it is saved the moment you focus on
the subform. If you are not focusing on the subform first, you must
explicitly save the mainform's record before moving to the "Data Entry Form"
You can do that in the button which opens the data entry form.

Also, by using a default value in the foreign key field of the "Data Entry
Form", which refers to the primary key in the main form, you can assure that
field will be properly filled. It is imperative, however, that the main form
be open, or you'll be prompted for the value. Set the foreign key field's
textbox's DefaultValue to something like:

=Forms!MainFormName!PrimaryKeyTextboxName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John S]

Arvin,

Many thanks. I had overlooked the DefaultValue. All now works fine.

Regards,
John.