Don't Know How

  • Thread starter Thread starter iami
  • Start date Start date
I

iami

This is a tough one for a part time Access user...
But I know this is the place to get an answer if it has one.
Access 2007/ Vista/ 4 meg ram
I have a form that has a StartDate filed formatted at Date, dd-mmm.
The form also has a Combo field 'DaySet' formatted as text. The
DaySet combo field has values of:
Mon-Wed
Mon-Fri
Monday
Tue-Fri
Tue-Sat
Tuesday
etc
The table/form is used to schedule classes at our Arts & Crafts
Center.

I would like to have the DaySet field values limited to
days that correspond with StartDate, If Startdate = a Monday, then
the only lines showing in DaySet would be those that start with Mon,
if Startdate is a Tuesday then the only lines showing in DaySet would
be those that start with Tue.

Thanks, for all the help in the past and for any guidance that can be
provided on this one.

Tom Rector
tomrector @ svsatscott.com
 
you could try this, not simplest, but I think most flexible solution;

the values you have in your combobox, should go in a table. The table should
have 8 fields;
field 1: "DaySet" and contains values "Mon-Wed", "Mon-Fri", etc., type Text
field 2-8 should be named "DayX" where X is 0 to 6, type Yes/No
Basically, for each "DaySet" you specify which days of the week should be
included

Now in your combobox, you list the contents of the table (just field1)
Put a RowSource in your combobox as follows in the Form_Open event:
me.dayset.rowsource = "SELECT [tblYourTableName].* WHERE [Day" &
Weekday(me.StartDate) & "] = true"
me.dayset.requery

you may have to check the exact syntax. And ofcourse, everytime you update
"StartDate" you should renew the combobox settings as well

hope this helps.
 
iami,
See the Weekday function in help...
Add a second "hidden" column (ex. DayNo) to your DaySet combo that
represents the weekday number of the first "text" value in Column(1)
Mon-Fri 2
Monday 2
Tue-Fri 3
Tue-Sat 3
Wednesday 4
Etc...
Use a criteria against DayNo...
= WeekDay(StartDate)
Using the AfterUpdate event of StartDate to Requery the DaySet combo.
If StartDate is a Tuesday, only selections with a DayNo = 2 will appear.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Thanks for the help, I don't have it working yet, but am farther
than I was.
I created a SetDay2 table with fileds Control and DayWk
Contol is 1 thur 7 corresponding to the day of the week.
DayWk is the DaySet of the class ie: Mon Only; Mon-Tue, etc)

I created a a text field on the form- text72 =Weekday
([StartDate]) results in day number of the week 1,2,3,4,5,6,7
I used the combo field DaySet, Row Siources Type as Table Query
I used Row source as (exactly) SELECT SetDay2.DayWk FROM SetDay2 WHERE
(((SetDay2.Control)=Form.BClass.[Text72]));
Bound Col 1
(BClass is the name of the form)

This opens a enter parameter box for Form.BClass.Text72
If I put the day number in, it displays the correct DayWk slection
options.\

So how do I get the querry to complete, so I don't have to enter the
parameter ?
 
iami,
Your making this more difficult than need be...
Please don't <snip> my previous response/s. We need to see the total
"flow" of the problem. Just let them chain...
Drop the text72 control, it's not really needed.

Given the combo setup I used...
DayWeek Control
--------------------------------
Mon-Fri 2
Monday 2
Tue-Fri 3
Tue-Sat 3
Wednesday 4
Etc...

Use this criteria against Control in your combo
= Weekday(StartDate)

When StartDate is updated (AfterUpdate event), Requery the combo, and
only the appropriate DayWeek choices should be displayed for selection.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
iami,
    Your making this more difficult than need be...
    Pleasedon't<snip> my previous response/s.  We need to see the total
"flow" of the problem.  Just let them chain...
    Drop the text72 control, it's not really needed.

    Given the combo setup I used...
        DayWeek        Control
--------------------------------
        Mon-Fri              2
        Monday              2
        Tue-Fri               3
        Tue-Sat              3
        Wednesday        4
        Etc...

     Use this criteria against Control in your combo
        = Weekday(StartDate)

     When StartDate is updated (AfterUpdate event), Requery the combo, and
only the appropriate DayWeek choices should be displayed for selection.
--
    hth
    Al Campagna
    Microsoft Access MVP
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."




Thanks for the help, Idon'thave  it working yet, but am farther
than I was.
I created a SetDay2 table  with fileds Control and  DayWk
Contol is 1 thur 7 corresponding to the day of the week.
DayWk is the DaySet of the class   ie:  Mon Only; Mon-Tue,  etc)
Click to expand...
I created a a text field on the form-  text72    =Weekday
([StartDate])   results in  day number of the week  1,2,3,4,5,6,7
I used the combo field DaySet, Row Siources Type as Table Query
I used Row source as (exactly) SELECT SetDay2.DayWk FROM SetDay2 WHERE
(((SetDay2.Control)=Form.BClass.[Text72]));
Bound  Col 1
(BClass is the name of the form)
Click to expand...
This opens a enter parameter box for   Form.BClass.Text72
If I put the day number in, it displays the correct DayWk slection
options.\
Click to expand...
So how do I get the querry to complete,  so Idon'thave to enter the
parameter ?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Sorry for sniping the code......
I don't understand where how to put a hidden column in a combo box.
I have the StartDate set to dd-mmm
StartDate. AfterUpdate set to [DaySet].[Requery]
DaySet control source set to =WeekDay[StartDate]
Row Source=Saturday Only;Sunday Only;Monday Only;Mon & Tue;Mon &
Wed;Thus & Fri;Tue & Fri;Tue & Wed; etc
Row source type=Value List
 
iami.,
Another minor point... if a responder "top" posts, please do the same.
As you can see, the sequence of the conversation can get pretty messy if I
top post, and you bottom post.
Let's try the following.... (we won't bother about hiding the DayNo
column in this example)
I'll be using names that make sense to me... you use whatever suits you.

A combo box RowSource uses a table, or query, or a value list... to
display values for the user to select.
It appears that you are using a Value List. I would recommend a query
instead

Create a table called tblDayRanges, with two fields, DayRange = text
and DayNo = numeric byte.
It would look like this in table view....
DayRange DayNo
--------------------------------
Mon-Fri 2
Monday 2
Tue-Fri 3
Tue-Sat 3
Wednesday 4
Etc...

You could use this table as the rowsource to "feed" the cboDaySet combo,
but... most programmers prefer a query, because it's more flexible than a
raw table.
Create a new query, and bring tblDayRanges into the Query Design Grid,
and place [DayRange] in Column1 and [DayNo] in Column2
Sort the query by DayRange (ascending).
In the DayNo column use this criteria...
= Weekday(StartDate)
Make this query the Rowsource for your cboDayRange combo

Now, set up these combobox properties...
Column Count = 2
Column Widths = .75";.5"
List Width = 1.25"
Bound Column = 1
Name = cboDayRange
Control Source = the name of the field in your form's table where
you want to store the DayRange selected from cboDaySet. (example DayRange)

OK... now we enter a [StartDate] (ex. a date that is a Monday) and... on
the AfterUpdate of [StartDate]...
Me.cboDayRange.Requery
The query rowsource behind cboDayRange uses the Weekday(StartDate),
which equals 2, to filter the query for only values where DayNo = 2. In our
example, the combo would display "Mon" and "Mon-Fri" for the user to select.
I know this appears to be complicated, but it really isn't. Just follow
the steps one by one.
You might find it best to use my object names and examples, and when you
have it working, you can use your own names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

iami,
Your making this more difficult than need be...
Pleasedon't<snip> my previous response/s. We need to see the total
"flow" of the problem. Just let them chain...
Drop the text72 control, it's not really needed.

Given the combo setup I used...
DayWeek Control
--------------------------------
Mon-Fri 2
Monday 2
Tue-Fri 3
Tue-Sat 3
Wednesday 4
Etc...

Use this criteria against Control in your combo
= Weekday(StartDate)

When StartDate is updated (AfterUpdate event), Requery the combo, and
only the appropriate DayWeek choices should be displayed for selection.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




Thanks for the help, Idon'thave it working yet, but am farther
than I was.
I created a SetDay2 table with fileds Control and DayWk
Contol is 1 thur 7 corresponding to the day of the week.
DayWk is the DaySet of the class ie: Mon Only; Mon-Tue, etc)
Click to expand...
I created a a text field on the form- text72 =Weekday
([StartDate]) results in day number of the week 1,2,3,4,5,6,7
I used the combo field DaySet, Row Siources Type as Table Query
I used Row source as (exactly) SELECT SetDay2.DayWk FROM SetDay2 WHERE
(((SetDay2.Control)=Form.BClass.[Text72]));
Bound Col 1
(BClass is the name of the form)
Click to expand...
This opens a enter parameter box for Form.BClass.Text72
If I put the day number in, it displays the correct DayWk slection
options.\
Click to expand...
So how do I get the querry to complete, so Idon'thave to enter the
parameter ?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Sorry for sniping the code......
I don't understand where how to put a hidden column in a combo box.
I have the StartDate set to dd-mmm
StartDate. AfterUpdate set to [DaySet].[Requery]
DaySet control source set to =WeekDay[StartDate]
Row Source=Saturday Only;Sunday Only;Monday Only;Mon & Tue;Mon &
Wed;Thus & Fri;Tue & Fri;Tue & Wed; etc
Row source type=Value List
 
iami.,
    Another minor point... if a responder "top" posts, please do the same.
As you can see, the sequence of the conversation can get pretty messy if I
top post, and you bottom post.
    Let's try the following....  (we won't bother about hiding the DayNo
column in this example)
    I'll be using names that make sense to me... you use whatever suits you.

    A combo box RowSource uses a table, or query, or a value list... to
display values for the user to select.
    It appears that you are using a Value List.  I would recommend a query
instead

    Create a table called tblDayRanges, with two fields, DayRange =text
and   DayNo = numeric byte.
    It would look like this in table view....
        DayRange        DayNo
        --------------------------------
        Mon-Fri                2
        Monday                2
        Tue-Fri                 3
        Tue-Sat                3
        Wednesday           4
        Etc...

    You could use this table as the rowsource to "feed" the cboDaySetcombo,
but... most programmers prefer a query, because it's more flexible than a
raw table.
    Create a new query, and bring tblDayRanges into the Query Design Grid,
and place [DayRange] in Column1 and [DayNo] in Column2
    Sort the query by DayRange (ascending).
    In the DayNo column use this criteria...
        = Weekday(StartDate)
    Make this query the Rowsource for your cboDayRange combo

    Now, set up these combobox properties...
        Column Count = 2
        Column Widths = .75";.5"
        List Width = 1.25"
        Bound Column = 1
        Name = cboDayRange
        Control Source = the name of the field in your form's table where
you want to store the DayRange selected from cboDaySet. (example DayRange)

    OK... now we enter a [StartDate] (ex. a date that is a Monday) and... on
the AfterUpdate of [StartDate]...
        Me.cboDayRange.Requery
    The query rowsource behind cboDayRange uses the Weekday(StartDate),
which equals 2, to filter the query for only values where DayNo = 2.  In our
example, the combo would display "Mon" and "Mon-Fri" for the user to select.
    I know this appears to be complicated, but it really isn't.  Just follow
the steps one by one.
    You might find it best to use my object names and examples, and when you
have it working, you can use your own names.
--
    hth
    Al Campagna
    Microsoft Access MVP
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."


iami,
Your making this more difficult than need be...
Pleasedon't<snip> my previous response/s. We need to see the total
"flow" of the problem. Just let them chain...
Drop the text72 control, it's not really needed.
Click to expand...
Given the combo setup I used...
DayWeek Control
--------------------------------
Mon-Fri 2
Monday 2
Tue-Fri 3
Tue-Sat 3
Wednesday 4
Etc...
Click to expand...
Use this criteria against Control in your combo
= Weekday(StartDate)
Click to expand...
When StartDate is updated (AfterUpdate event), Requery the combo, and
only the appropriate DayWeek choices should be displayed for selection.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
Click to expand...
"Find a job that you love... and you'll never work a day in your life."
Click to expand...
news:[email protected]...
Click to expand...
Thanks for the help, Idon'thave it working yet, but am farther
than I was.
I created a SetDay2 table with fileds Control and DayWk
Contol is 1 thur 7 corresponding to the day of the week.
DayWk is the DaySet of the class ie: Mon Only; Mon-Tue, etc)
I created a a text field on the form- text72 =Weekday
([StartDate]) results in day number of the week 1,2,3,4,5,6,7
I used the combo field DaySet, Row Siources Type as Table Query
I used Row source as (exactly) SELECT SetDay2.DayWk FROM SetDay2 WHERE
(((SetDay2.Control)=Form.BClass.[Text72]));
Bound Col 1
(BClass is the name of the form)
This opens a enter parameter box for Form.BClass.Text72
If I put the day number in, it displays the correct DayWk slection
options.\
So how do I get the querry to complete, so Idon'thave to enter the
parameter ?- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Sorry for sniping the code......
 I don't understand where how to put a hidden column in a combo box.
I have the StartDate  set to dd-mmm
StartDate.  AfterUpdate set to [DaySet].[Requery]
DaySet control source set to =WeekDay[StartDate]
   Row Source=Saturday Only;Sunday Only;Monday Only;Mon & Tue;Mon &
Wed;Thus & Fri;Tue & Fri;Tue & Wed;  etc
   Row source type=Value List- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks, It is now working, I used my field names since they made
sense to me...
I was confused about the two columns issue. I did go with the query
route since I had tried that
previously , when I didn't know about the 2 columns.
Long story short, Thanks for sticking with me ! Made my week !
 
Back
Top