Closing records with a relationship

[Jessica]

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

 

[Jessica]

Yes i have the close date under the parents info in a form and them a
subform with all their children's information.

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

Yes i have the close date under the parents info in a form and them a
subform with all their children's information.

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[Jessica]

Cj,

Is not working. I have both Queries link by the parent ID. I enter the
criteria under the parents ID and it did not work. Then i enter the one under
the closing date and is asking for a parameter.

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

Yes i have the close date under the parents info in a form and them a
subform with all their children's information.

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[Jessica]

I need the closing date from the parents table to automatica populat in the
childrens table. That way i just have to enter it once. So that means i would
need closind date field in the children's table correct? But i'm not sure
what to use to make it populate from one to another?

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

Yes i have the close date under the parents info in a form and them a
subform with all their children's information.

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

Ok...there's actually two comments I'm responding to...here's the first:

"Is not working. I have both Queries link by the parent ID. I enter the
criteria under the parents ID and it did not work. Then i enter the one under
the closing date and is asking for a parameter."

--> For this one, maybe the CLOSE event is not the right event, because you
may not be closing the form right after you update the field. Instead, try
putting the query call in the closing date field's AFTER UPDATE property.

Regarding your second comment (the one below), yes, you will need something
in the children's table, so you can flag the record as closed. What we're
doing is running an Update Query that looks up the child's record (based on
the parent's ID number) and marks it as closed (either by passing a closed
date or flagging the record in some other manner you've designed).

I am assuming that you put the closing date in a field in each child's
record to flag it as closed, but it you use a yes/no flag to show the record
is closed, then change the query "Update To" field to TRUE (or whatever value
you use to flag the record closed). Unfortunately, without seeing how you
flag your children's records as closed, I cannot give you the exact format of
what you need.

If you're new to using Update Queries, you can experiment by:
1. Open the form and fill-in the close date
2. Open the query in design mode
3. Click the datasheet view button. The query should display the closing
date (or True, or whatever) without actually updating the tables.
4. Switch back and forth between the views until you're satisfied that the
query is working correctly.

Just be advised that if you run the query from the VBA Call statement (or
double-click the query in the Navigation Pane), it will run the query and
actually update the table.

---cj

I need the closing date from the parents table to automatica populat in the
childrens table. That way i just have to enter it once. So that means i would
need closind date field in the children's table correct? But i'm not sure
what to use to make it populate from one to another?

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

Yes i have the close date under the parents info in a form and them a
subform with all their children's information.
:

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[Jessica]

In regards to the second comment, I do have closing Date field in both
Queries, as soon as i enter a closed date in the parents form i want it to
automatically populate in the children's sub form

Ok...there's actually two comments I'm responding to...here's the first:

"Is not working. I have both Queries link by the parent ID. I enter the
criteria under the parents ID and it did not work. Then i enter the one under
the closing date and is asking for a parameter."

--> For this one, maybe the CLOSE event is not the right event, because you
may not be closing the form right after you update the field. Instead, try
putting the query call in the closing date field's AFTER UPDATE property.

Regarding your second comment (the one below), yes, you will need something
in the children's table, so you can flag the record as closed. What we're
doing is running an Update Query that looks up the child's record (based on
the parent's ID number) and marks it as closed (either by passing a closed
date or flagging the record in some other manner you've designed).

I am assuming that you put the closing date in a field in each child's
record to flag it as closed, but it you use a yes/no flag to show the record
is closed, then change the query "Update To" field to TRUE (or whatever value
you use to flag the record closed). Unfortunately, without seeing how you
flag your children's records as closed, I cannot give you the exact format of
what you need.

If you're new to using Update Queries, you can experiment by:
1. Open the form and fill-in the close date
2. Open the query in design mode
3. Click the datasheet view button. The query should display the closing
date (or True, or whatever) without actually updating the tables.
4. Switch back and forth between the views until you're satisfied that the
query is working correctly.

Just be advised that if you run the query from the VBA Call statement (or
double-click the query in the Navigation Pane), it will run the query and
actually update the table.

---cj

I need the closing date from the parents table to automatica populat in the
childrens table. That way i just have to enter it once. So that means i would
need closind date field in the children's table correct? But i'm not sure
what to use to make it populate from one to another?

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

:


Yes i have the close date under the parents info in a form and them a
subform with all their children's information.
:

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

To do that, you either need to have a field on the children's form where the
date can be set directly using VBA, or else you need to run a query that will
update it in the table directly. If you don't want the date on the
children's form, then you will have to set it on teh back end with a query.

Do you have a Closing Date field on the children's subform?

--------

In regards to the second comment, I do have closing Date field in both
Queries, as soon as i enter a closed date in the parents form i want it to
automatically populate in the children's sub form

Ok...there's actually two comments I'm responding to...here's the first:

"Is not working. I have both Queries link by the parent ID. I enter the
criteria under the parents ID and it did not work. Then i enter the one under
the closing date and is asking for a parameter."

--> For this one, maybe the CLOSE event is not the right event, because you
may not be closing the form right after you update the field. Instead, try
putting the query call in the closing date field's AFTER UPDATE property.

Regarding your second comment (the one below), yes, you will need something
in the children's table, so you can flag the record as closed. What we're
doing is running an Update Query that looks up the child's record (based on
the parent's ID number) and marks it as closed (either by passing a closed
date or flagging the record in some other manner you've designed).

I am assuming that you put the closing date in a field in each child's
record to flag it as closed, but it you use a yes/no flag to show the record
is closed, then change the query "Update To" field to TRUE (or whatever value
you use to flag the record closed). Unfortunately, without seeing how you
flag your children's records as closed, I cannot give you the exact format of
what you need.

If you're new to using Update Queries, you can experiment by:
1. Open the form and fill-in the close date
2. Open the query in design mode
3. Click the datasheet view button. The query should display the closing
date (or True, or whatever) without actually updating the tables.
4. Switch back and forth between the views until you're satisfied that the
query is working correctly.

Just be advised that if you run the query from the VBA Call statement (or
double-click the query in the Navigation Pane), it will run the query and
actually update the table.

---cj

I need the closing date from the parents table to automatica populat in the
childrens table. That way i just have to enter it once. So that means i would
need closind date field in the children's table correct? But i'm not sure
what to use to make it populate from one to another?

:

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

:


Yes i have the close date under the parents info in a form and them a
subform with all their children's information.
:

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[Jessica]

Yes i have the closing date field on both the main forma and the children's
subform. I just want to be able to type it in the main form and them for it
to automatically populate in the subform

To do that, you either need to have a field on the children's form where the
date can be set directly using VBA, or else you need to run a query that will
update it in the table directly. If you don't want the date on the
children's form, then you will have to set it on teh back end with a query.

Do you have a Closing Date field on the children's subform?

--------

In regards to the second comment, I do have closing Date field in both
Queries, as soon as i enter a closed date in the parents form i want it to
automatically populate in the children's sub form

Ok...there's actually two comments I'm responding to...here's the first:

"Is not working. I have both Queries link by the parent ID. I enter the
criteria under the parents ID and it did not work. Then i enter the one under
the closing date and is asking for a parameter."

--> For this one, maybe the CLOSE event is not the right event, because you
may not be closing the form right after you update the field. Instead, try
putting the query call in the closing date field's AFTER UPDATE property.

Regarding your second comment (the one below), yes, you will need something
in the children's table, so you can flag the record as closed. What we're
doing is running an Update Query that looks up the child's record (based on
the parent's ID number) and marks it as closed (either by passing a closed
date or flagging the record in some other manner you've designed).

I am assuming that you put the closing date in a field in each child's
record to flag it as closed, but it you use a yes/no flag to show the record
is closed, then change the query "Update To" field to TRUE (or whatever value
you use to flag the record closed). Unfortunately, without seeing how you
flag your children's records as closed, I cannot give you the exact format of
what you need.

If you're new to using Update Queries, you can experiment by:
1. Open the form and fill-in the close date
2. Open the query in design mode
3. Click the datasheet view button. The query should display the closing
date (or True, or whatever) without actually updating the tables.
4. Switch back and forth between the views until you're satisfied that the
query is working correctly.

Just be advised that if you run the query from the VBA Call statement (or
double-click the query in the Navigation Pane), it will run the query and
actually update the table.

---cj

:

I need the closing date from the parents table to automatica populat in the
childrens table. That way i just have to enter it once. So that means i would
need closind date field in the children's table correct? But i'm not sure
what to use to make it populate from one to another?

:

If you don't have a button to process the record, try putting the code in the
form's CLOSE event so it runs whenever the form is closed.

(BTW - There's other ways to do this by building SQL strings in VBA, but
this is the easiest way I can think of.)

---cj

:


Yes i have the close date under the parents info in a form and them a
subform with all their children's information.
:

Sorry, but I have to make the assumption that you are entering this close
date on some kind of form, instead of directly in a table; and that they have
to press a button to process the transaction (or close the form):

Create an update query with the [Parent's ID] and [closing date].

Remove the "show" check from the Parent's ID field, and under "Criteria",
enter =Forms![formname]![Parents ID fieldname].

In the "Update to" field for closing date, enter =Forms![formname]![closing
date fieldname]

In the code for the command button, enter the following code to update the
children's table before closing the form:

DoCmd.OpenQuery "queryname", acNormal, acEdit

where "queryname" is the query you just created.

:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

Okay...let's start fresh, and toss out the query idea, because we can update
this directly using the AfterUpdate property of your Closing Date field.
Keep in mind that you will need to substitute your form and field names in my
example below.

Let's assume that you have a form (frmParents). Open this form in design
view. Locate the Closing Date box, which I will call "txtClosingDate" for
this example.

This form has a subform showing the children's records, in a field named
"txtKidsClosedDate". Click on the subform, and open the Properties dialog
box. In the example I am going to give you below, I'll refer to the
subform's source object as a form named frmChildren in the Navigation Pane,
and it's named ChildrenSubform in the Properties box.

Select the closing date box, and scroll down the Properties list until you
see "After Update". Click on it and open a Code window in VBA. Enter the
following code:
---
Dim datClosingDate as Date

datClosingDate = Forms![frmParents]![txtClosingDate].Value

Me!ChildrenSubform.Form.txtKidsClosedDate = datClosingDate

 

[Jessica]

is not working, isn't populating to the childs case close field.

Okay...let's start fresh, and toss out the query idea, because we can update
this directly using the AfterUpdate property of your Closing Date field.
Keep in mind that you will need to substitute your form and field names in my
example below.

Let's assume that you have a form (frmParents). Open this form in design
view. Locate the Closing Date box, which I will call "txtClosingDate" for
this example.

This form has a subform showing the children's records, in a field named
"txtKidsClosedDate". Click on the subform, and open the Properties dialog
box. In the example I am going to give you below, I'll refer to the
subform's source object as a form named frmChildren in the Navigation Pane,
and it's named ChildrenSubform in the Properties box.

Select the closing date box, and scroll down the Properties list until you
see "After Update". Click on it and open a Code window in VBA. Enter the
following code:
---
Dim datClosingDate as Date

datClosingDate = Forms![frmParents]![txtClosingDate].Value

Me!ChildrenSubform.Form.txtKidsClosedDate = datClosingDate

---

This should update the date, like you wanted.

---cj

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

Is the case closed field a date field?

Also, are you getting any error messages or anything?

---cj

is not working, isn't populating to the childs case close field.

Okay...let's start fresh, and toss out the query idea, because we can update
this directly using the AfterUpdate property of your Closing Date field.
Keep in mind that you will need to substitute your form and field names in my
example below.

Let's assume that you have a form (frmParents). Open this form in design
view. Locate the Closing Date box, which I will call "txtClosingDate" for
this example.

This form has a subform showing the children's records, in a field named
"txtKidsClosedDate". Click on the subform, and open the Properties dialog
box. In the example I am going to give you below, I'll refer to the
subform's source object as a form named frmChildren in the Navigation Pane,
and it's named ChildrenSubform in the Properties box.

Select the closing date box, and scroll down the Properties list until you
see "After Update". Click on it and open a Code window in VBA. Enter the
following code:
---
Dim datClosingDate as Date

datClosingDate = Forms![frmParents]![txtClosingDate].Value

Me!ChildrenSubform.Form.txtKidsClosedDate = datClosingDate

---

This should update the date, like you wanted.

---cj

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[Jessica]

yes it is date field and no i'm not getting any messages is just not
transfering the closing date to the children subform

Is the case closed field a date field?

Also, are you getting any error messages or anything?

---cj

is not working, isn't populating to the childs case close field.

Okay...let's start fresh, and toss out the query idea, because we can update
this directly using the AfterUpdate property of your Closing Date field.
Keep in mind that you will need to substitute your form and field names in my
example below.

Let's assume that you have a form (frmParents). Open this form in design
view. Locate the Closing Date box, which I will call "txtClosingDate" for
this example.

This form has a subform showing the children's records, in a field named
"txtKidsClosedDate". Click on the subform, and open the Properties dialog
box. In the example I am going to give you below, I'll refer to the
subform's source object as a form named frmChildren in the Navigation Pane,
and it's named ChildrenSubform in the Properties box.

Select the closing date box, and scroll down the Properties list until you
see "After Update". Click on it and open a Code window in VBA. Enter the
following code:
---
Dim datClosingDate as Date

datClosingDate = Forms![frmParents]![txtClosingDate].Value

Me!ChildrenSubform.Form.txtKidsClosedDate = datClosingDate

---

This should update the date, like you wanted.

---cj


:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[Jessica]

Yes is a date field, i'm not getting an error message is just not transfering
to the subform

Is the case closed field a date field?

Also, are you getting any error messages or anything?

---cj

is not working, isn't populating to the childs case close field.

Okay...let's start fresh, and toss out the query idea, because we can update
this directly using the AfterUpdate property of your Closing Date field.
Keep in mind that you will need to substitute your form and field names in my
example below.

Let's assume that you have a form (frmParents). Open this form in design
view. Locate the Closing Date box, which I will call "txtClosingDate" for
this example.

This form has a subform showing the children's records, in a field named
"txtKidsClosedDate". Click on the subform, and open the Properties dialog
box. In the example I am going to give you below, I'll refer to the
subform's source object as a form named frmChildren in the Navigation Pane,
and it's named ChildrenSubform in the Properties box.

Select the closing date box, and scroll down the Properties list until you
see "After Update". Click on it and open a Code window in VBA. Enter the
following code:
---
Dim datClosingDate as Date

datClosingDate = Forms![frmParents]![txtClosingDate].Value

Me!ChildrenSubform.Form.txtKidsClosedDate = datClosingDate

---

This should update the date, like you wanted.

---cj


:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?

 

[CJ Mora]

I'm sorry, Jessica, but I can't figure out why it's not working. When I try
the code on my end, it has no problem updating the values between the main
form and the subform.

Frst of all, try double-checking the format of your statement so that all
the "!" and "." placements match what I've given in the example below. Also,
double-check your names (form and control names) to make sure you're
referencing them riby the right names. 99% of the time, it's the simple
things (like typos) that get ya.

If it's not anything simple, maybe you could set a break in the code and
watch it walk through each step using F8; make sure the variable
datClosingDate is getting filled with the correct information from the parent
form by hovering over it after that step has been processed.

---cj

Yes is a date field, i'm not getting an error message is just not transfering
to the subform

Is the case closed field a date field?

Also, are you getting any error messages or anything?

---cj

is not working, isn't populating to the childs case close field.

:

Okay...let's start fresh, and toss out the query idea, because we can update
this directly using the AfterUpdate property of your Closing Date field.
Keep in mind that you will need to substitute your form and field names in my
example below.

Let's assume that you have a form (frmParents). Open this form in design
view. Locate the Closing Date box, which I will call "txtClosingDate" for
this example.

This form has a subform showing the children's records, in a field named
"txtKidsClosedDate". Click on the subform, and open the Properties dialog
box. In the example I am going to give you below, I'll refer to the
subform's source object as a form named frmChildren in the Navigation Pane,
and it's named ChildrenSubform in the Properties box.

Select the closing date box, and scroll down the Properties list until you
see "After Update". Click on it and open a Code window in VBA. Enter the
following code:
---
Dim datClosingDate as Date

datClosingDate = Forms![frmParents]![txtClosingDate].Value

Me!ChildrenSubform.Form.txtKidsClosedDate = datClosingDate

---

This should update the date, like you wanted.

---cj


:

I have two queries one with parens information and one with their childrens
information both queries are link. When i enter a closing date on the parents
record i need for the childs record to automatically close. How can i do it?
Do i need a closing date field under the children table and close it manually?