Automate field

[ladybug via AccessMonster.com]

I have a form that has a field with a week range. Below this field I have 7
tabs with a subform on each. These tabs and subforms are representing each
day of the week. I want the date field on the first tab to automate Sunday's
date from the date range above. The Second tab will have Monday's date, and
so on.

Example:

Week: 04/08/07 - 04/14/07

Sunday tab automates 04/08/07 Monday tab automates 04/09/07

How can I do this?

 

[kingston via AccessMonster.com]

If the start date in the range is always a Sunday and is always 8 characters
long, you can do something like this:

Me.Page1.Caption = "Sunday " & Left(Me.[WeekRange],8)
Me.Page2.Caption = "Monday " & CDate(Left(Me.[WeekRange],8))+1
Me.Page3.Caption = "Tuesday " & CDate(Left(Me.[WeekRange],8))+2
...

You'd do something similar to restrict the data for each subform via a query
or filter.

 

[ladybug via AccessMonster.com]

Where does this code go? Sorry, I have very little knowledge of vba.

If the start date in the range is always a Sunday and is always 8 characters
long, you can do something like this:

Me.Page1.Caption = "Sunday " & Left(Me.[WeekRange],8)
Me.Page2.Caption = "Monday " & CDate(Left(Me.[WeekRange],8))+1
Me.Page3.Caption = "Tuesday " & CDate(Left(Me.[WeekRange],8))+2
...

You'd do something similar to restrict the data for each subform via a query
or filter.

I have a form that has a field with a week range. Below this field I have 7
tabs with a subform on each. These tabs and subforms are representing each

[quoted text clipped - 9 lines]

How can I do this?

 

[kingston via AccessMonster.com]

It depends on how the week range is set; how and when does this data get onto
your form? For example, if you have a combobox where the user selects the
week range, use the combobox's AfterUpdate event.

Where does this code go? Sorry, I have very little knowledge of vba.

If the start date in the range is always a Sunday and is always 8 characters
long, you can do something like this:

[quoted text clipped - 12 lines]

 

[ladybug via AccessMonster.com]

There is a form with a combo box where the user selects from a list of week
ranges. Then they click a command button that opens another form where the
date range they selected is captured at the top.
Below are 7 tabs. On each tab is a subform for each day of the week. At the
top of each subform there is a date field. If the date range is 04/01/2007 -
04/07/2007. The first tab will have a
field that automatically enters 04/01/2007, second tab 04/02/2007, third tab
04/03/2007, etc.
Sorry, I don't know proper programming language so as descriptive as you can
get would be greatly appreciated!

It depends on how the week range is set; how and when does this data get onto
your form? For example, if you have a combobox where the user selects the
week range, use the combobox's AfterUpdate event.

Where does this code go? Sorry, I have very little knowledge of vba.

[quoted text clipped - 3 lines]

 

[kingston via AccessMonster.com]

OK, let's try a different approach. Just to verify - your command button
opens a second form with 7 tabs and a control that contains the date range.

Assuming the date range always starts with a Sunday we can minimize the
amount of coding you need to do, which will be zero. Label the tabs Sunday,
Monday...Saturday. In the body of the first tab (make sure you select the
individual tab), add a textbox with the formula =Left(Me.DateRange,8); change
DateRange to the proper control name. In the next tab add a control that
references the previous tab's date control as such =Me.SundayDate + 1 where
SundayDate is the name of the control you just added in the first tab. Do
this for the rest of the tabs. If the results don't give you dates, try the
function CDate(), e.g. CDate(Me.SundayDate).

Now create seven queries based on the data table you want to populate or
display. The criteria for each query will be something like [FieldDate] =
Forms!TabFormName!SundayDate. Save each query and then drag and drop each of
them into their respective tabs in form design mode. Subforms will be made
automatically. Just be careful to select each tab first and to drop the
correct query into the body of the tab.

hth

There is a form with a combo box where the user selects from a list of week
ranges. Then they click a command button that opens another form where the
date range they selected is captured at the top.
Below are 7 tabs. On each tab is a subform for each day of the week. At the
top of each subform there is a date field. If the date range is 04/01/2007 -
04/07/2007. The first tab will have a
field that automatically enters 04/01/2007, second tab 04/02/2007, third tab
04/03/2007, etc.
Sorry, I don't know proper programming language so as descriptive as you can
get would be greatly appreciated!

It depends on how the week range is set; how and when does this data get onto
your form? For example, if you have a combobox where the user selects the

[quoted text clipped - 5 lines]

 

[ladybug via AccessMonster.com]

It didn't work. Well, at least I couldn't get it to work. It just says
#Name?

OK, let's try a different approach. Just to verify - your command button
opens a second form with 7 tabs and a control that contains the date range.

Assuming the date range always starts with a Sunday we can minimize the
amount of coding you need to do, which will be zero. Label the tabs Sunday,
Monday...Saturday. In the body of the first tab (make sure you select the
individual tab), add a textbox with the formula =Left(Me.DateRange,8); change
DateRange to the proper control name. In the next tab add a control that
references the previous tab's date control as such =Me.SundayDate + 1 where
SundayDate is the name of the control you just added in the first tab. Do
this for the rest of the tabs. If the results don't give you dates, try the
function CDate(), e.g. CDate(Me.SundayDate).

Now create seven queries based on the data table you want to populate or
display. The criteria for each query will be something like [FieldDate] =
Forms!TabFormName!SundayDate. Save each query and then drag and drop each of
them into their respective tabs in form design mode. Subforms will be made
automatically. Just be careful to select each tab first and to drop the
correct query into the body of the tab.

hth

There is a form with a combo box where the user selects from a list of week
ranges. Then they click a command button that opens another form where the

[quoted text clipped - 12 lines]

 

[kingston via AccessMonster.com]

I'm sorry, I gave you some bad advice (more than once actually), but I need a
little more information from you. What didn't work? Did anything work?
Where are you seeing #Name? Are you seeing this on a control I suggested you
build or is something you started out with not working?

Anyway, ignore what I said about using =Me.SundayDate + 1 and setting the
rest of the date controls that way on the other tabs. Instead, continue to
use =Left(Me.DateRange,8) + 1, =Left(Me.DateRange,8) + 2, and so forth. Again,
you may need to use the function CDate() and replace Me.DateRange with the
correct control name.

Instead of making seven queries, create a subform with the proper
Master/Child links. In form design mode, select a tab, drag and drop the
table into the tab, and set Link Child Fields and Link Master Fields in the
properties window for the newly created subform control (e.g. TableDate and
Tab1Date). Copy and paste this control into the other tabs and set the Link
Master Fields property to each tab's date.

This WILL work. If something doesn't work, try to be as descriptive as
possible. I apologize for any confusion I may have caused with my previous
posts.

It didn't work. Well, at least I couldn't get it to work. It just says
#Name?

OK, let's try a different approach. Just to verify - your command button
opens a second form with 7 tabs and a control that contains the date range.

[quoted text clipped - 23 lines]

 

[ladybug via AccessMonster.com]

It's not you that should apoligize. I think may be in over my head. I so do
not understand code. I will try to explain what I have already. If you
can't help, I understand. I have tried reading books and online reports, but
it is just like reading Russian!

The form has a date range at the top (04/01/2007 -04/07/2007) This date
range field's control source is from a table that includes a list of date
ranges.

Now under this date range is a series of tabs. There are 7 tabs, each
already labeled Sunday, Monday, etc. Under this label is a subform on each
tab. The subform has a text field with the date in it. Automatically the
first day of that date range is already in this field (04/01/2007).

I tried essentially copying the Sunday's subform and creating a new subform
for Monday. However, 04/01/2007 is still showing up in the date field for
Monday. So I guess I just need to know what code I put in for Mondays date
so that it is always the next day after whatever date shows on the Sunday
date. Next question is where do I put that code?

The first subform name is Sunday subform. The date field is named Date.

Again, thank you for being so patient. I hope I have been as descriptive as
possible.

I'm sorry, I gave you some bad advice (more than once actually), but I need a
little more information from you. What didn't work? Did anything work?
Where are you seeing #Name? Are you seeing this on a control I suggested you
build or is something you started out with not working?

Anyway, ignore what I said about using =Me.SundayDate + 1 and setting the
rest of the date controls that way on the other tabs. Instead, continue to
use =Left(Me.DateRange,8) + 1, =Left(Me.DateRange,8) + 2, and so forth. Again,
you may need to use the function CDate() and replace Me.DateRange with the
correct control name.

Instead of making seven queries, create a subform with the proper
Master/Child links. In form design mode, select a tab, drag and drop the
table into the tab, and set Link Child Fields and Link Master Fields in the
properties window for the newly created subform control (e.g. TableDate and
Tab1Date). Copy and paste this control into the other tabs and set the Link
Master Fields property to each tab's date.

This WILL work. If something doesn't work, try to be as descriptive as
possible. I apologize for any confusion I may have caused with my previous
posts.

It didn't work. Well, at least I couldn't get it to work. It just says
#Name?

[quoted text clipped - 4 lines]

 

[kingston via AccessMonster.com]

I'll try to break things down a little more and you tell me where there is a
problem.

Step 1
In each tab, create a text control (outside of the subform). Set the Default
Values to =CDate(Left([DateRange],10)), =CDate(Left([DateRange],10))+1,
=CDate(Left([DateRange],10))+2, etc. for Sunday, Monday, Tuesday...
respectively.
This is based on the control you have on the main form that contains the date
range. If it is not called DateRange, substitute the correct name in the
formulas. At this point, you should see the correct date for each tab.

Step 2
In design mode of the main form, select the first tab. Drag and drop the
table (or query) you want to display into the body of the tab. The subform
wizard will help you create a subform.

Step 3
In design mode of the main form, select the subform control (make sure you
don't select or enter the subform itself). Open the Properties window of the
subform control. Go to the Data tab.

Step 4
For Link Child Fields, type the name of the date field in the subform: e.g.
[TableDate].
For Link Master Fields, type the name of the text control you created in Step
1 for that tab: e.g. [txtSundayDate]. Note: don't use reserved names like
Date.

Step 5
Copy the subform control to the other six tabs and change the Link Master
Fields property to the correct text control.

Check to see if things work after each step and tell me exactly what happens
if you encounter a problem.

It's not you that should apoligize. I think may be in over my head. I so do
not understand code. I will try to explain what I have already. If you
can't help, I understand. I have tried reading books and online reports, but
it is just like reading Russian!

The form has a date range at the top (04/01/2007 -04/07/2007) This date
range field's control source is from a table that includes a list of date
ranges.

Now under this date range is a series of tabs. There are 7 tabs, each
already labeled Sunday, Monday, etc. Under this label is a subform on each
tab. The subform has a text field with the date in it. Automatically the
first day of that date range is already in this field (04/01/2007).

I tried essentially copying the Sunday's subform and creating a new subform
for Monday. However, 04/01/2007 is still showing up in the date field for
Monday. So I guess I just need to know what code I put in for Mondays date
so that it is always the next day after whatever date shows on the Sunday
date. Next question is where do I put that code?

The first subform name is Sunday subform. The date field is named Date.

Again, thank you for being so patient. I hope I have been as descriptive as
possible.

I'm sorry, I gave you some bad advice (more than once actually), but I need a
little more information from you. What didn't work? Did anything work?

[quoted text clipped - 23 lines]

 

[ladybug via AccessMonster.com]

So I did step one and the field just shows up as #Name?. Is there an example
of what I am trying to do out on the web that I can follow? I hate to waste
anymore of your time.

I'll try to break things down a little more and you tell me where there is a
problem.

Step 1
In each tab, create a text control (outside of the subform). Set the Default
Values to =CDate(Left([DateRange],10)), =CDate(Left([DateRange],10))+1,
=CDate(Left([DateRange],10))+2, etc. for Sunday, Monday, Tuesday...
respectively.
This is based on the control you have on the main form that contains the date
range. If it is not called DateRange, substitute the correct name in the
formulas. At this point, you should see the correct date for each tab.

Step 2
In design mode of the main form, select the first tab. Drag and drop the
table (or query) you want to display into the body of the tab. The subform
wizard will help you create a subform.

Step 3
In design mode of the main form, select the subform control (make sure you
don't select or enter the subform itself). Open the Properties window of the
subform control. Go to the Data tab.

Step 4
For Link Child Fields, type the name of the date field in the subform: e.g.
[TableDate].
For Link Master Fields, type the name of the text control you created in Step
1 for that tab: e.g. [txtSundayDate]. Note: don't use reserved names like
Date.

Step 5
Copy the subform control to the other six tabs and change the Link Master
Fields property to the correct text control.

Check to see if things work after each step and tell me exactly what happens
if you encounter a problem.

It's not you that should apoligize. I think may be in over my head. I so do
not understand code. I will try to explain what I have already. If you

[quoted text clipped - 26 lines]

 

[kingston via AccessMonster.com]

From your earlier post:

"The form has a date range at the top (04/01/2007 -04/07/2007) This date
range field's control source is from a table that includes a list of date
ranges."

What type of control is this? What is the name of this control?

To demonstrate how this works, just create a text box in your main form.
Call it DateRange (or something else if you've already used this name) and
put this as the Default Value "04/01/2007 -04/07/2007". Do the text boxes on
the tabs work now? Just go through the rest of the steps to see how things
work. Then you can change it to meet your needs.

So I did step one and the field just shows up as #Name?. Is there an example
of what I am trying to do out on the web that I can follow? I hate to waste
anymore of your time.

I'll try to break things down a little more and you tell me where there is a
problem.

[quoted text clipped - 37 lines]