Diff combo box for same field

[Radhika]

I am creating a database that uses a combo box for the filed 'Date' in a
table called 'tbl_Clinician'. I have created a form called 'frm_Clincian'
that consists of 12 pages with 12 subforms, one for each month of the year.
Each subform comes from the same table 'tbl_Clinician'. However, I want the
'Date' drop down box to display only the dates for each month in each months'
subform. The Dates must still be stored in the same table 'tbl_Clinician'.
How can I go about doing this. Is there an IIf statement i can write in a
query?

 

[Wayne-I-M]

1st - 12 forms with 12 subforms just sounds wrong. I can't see your
application but there "must" be a better way to lay this out.

Oh well

To filter the combo just point to the date control on your form (point the
criteria row of the query the combo is based on).

Dpon't forget the add the year to the DateSerial criteria or you will get
the same month from each year.
So it will be
DateSerial(Year(Date()), Month(Date()), 1) AND DateField <
DateSerial(Year(Date()), Month(Date()) + 1, 1)

 

[Allen Browne]

Firstly, Date is a reserved word, so will give you grief. I'll assume you
have changed the name to something like ClinDate.

Secondly, if you match each subform like that based on the month, any record
where ClinDate is null will disappear (not show in any subform.) To avoid
this, you might want to set the Required proeprty to Yes for this field
(bottom pane of table design.)

Next, you want all the January events in the first subform, the Feb ones in
the second, and so on. I wonder if you've thought about how this will look
after a couple of years? You will have the January 2008, 2009, and 2010
appointments in the first subform together? I'm not sure that's a great
idea.

Create a query using tbl_Clinician.
Type an expression like this into the Field row:
TheMonth: Month([ClinDate])
This returns 1 for Jan, 2 for Feb, and so on.
Save the query.
Set the RecordSource of your subfom to this query.

On your main form, add a text box with these properties:
Control Source =1
Name txtMonth1
Visible No
The first subform control will have properties like this
Link Master Fields [ClientID]
Link Child Fields [ClientID]
Add another to the end of these properties to filter the month as well, so
it becomes:
Link Master Fields [ClientID]; [txtMonth1]
Link Child Fields [ClientID]; [TheMonth]
This subform now filters for January.

Create a second text box named txtMonth2, with ControlSource =2, and so on
for the remaining months.

 

[Radhika]

Thank you!

I do have a concern and was hoping you could help me. I created a qry with
the expression you recommended TheMonth: Month([ClinDate]) and saved the qry
as the record source for the form and subfrom. However, when I try to add a
field in the subform, i get a message saying 'Field cannot be updated' when i
try to add a new date. Is there anything I can do to fix this?

Thanks,
Radhika

Firstly, Date is a reserved word, so will give you grief. I'll assume you
have changed the name to something like ClinDate.

Secondly, if you match each subform like that based on the month, any record
where ClinDate is null will disappear (not show in any subform.) To avoid
this, you might want to set the Required proeprty to Yes for this field
(bottom pane of table design.)

Next, you want all the January events in the first subform, the Feb ones in
the second, and so on. I wonder if you've thought about how this will look
after a couple of years? You will have the January 2008, 2009, and 2010
appointments in the first subform together? I'm not sure that's a great
idea.

Create a query using tbl_Clinician.
Type an expression like this into the Field row:
TheMonth: Month([ClinDate])
This returns 1 for Jan, 2 for Feb, and so on.
Save the query.
Set the RecordSource of your subfom to this query.

On your main form, add a text box with these properties:
Control Source =1
Name txtMonth1
Visible No
The first subform control will have properties like this
Link Master Fields [ClientID]
Link Child Fields [ClientID]
Add another to the end of these properties to filter the month as well, so
it becomes:
Link Master Fields [ClientID]; [txtMonth1]
Link Child Fields [ClientID]; [TheMonth]
This subform now filters for January.

Create a second text box named txtMonth2, with ControlSource =2, and so on
for the remaining months.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am creating a database that uses a combo box for the filed 'Date' in a
table called 'tbl_Clinician'. I have created a form called 'frm_Clincian'
that consists of 12 pages with 12 subforms, one for each month of the
year.
Each subform comes from the same table 'tbl_Clinician'. However, I want
the
'Date' drop down box to display only the dates for each month in each
months'
subform. The Dates must still be stored in the same table 'tbl_Clinician'.
How can I go about doing this. Is there an IIf statement i can write in a
query?

 

[Allen Browne]

So the user is trying to type in the text box that is bound to the
calculated query field? That can't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you!

I do have a concern and was hoping you could help me. I created a qry with
the expression you recommended TheMonth: Month([ClinDate]) and saved the
qry
as the record source for the form and subfrom. However, when I try to add
a
field in the subform, i get a message saying 'Field cannot be updated'
when i
try to add a new date. Is there anything I can do to fix this?

Thanks,
Radhika

Firstly, Date is a reserved word, so will give you grief. I'll assume you
have changed the name to something like ClinDate.

Secondly, if you match each subform like that based on the month, any
record
where ClinDate is null will disappear (not show in any subform.) To avoid
this, you might want to set the Required proeprty to Yes for this field
(bottom pane of table design.)

Next, you want all the January events in the first subform, the Feb ones
in
the second, and so on. I wonder if you've thought about how this will
look
after a couple of years? You will have the January 2008, 2009, and 2010
appointments in the first subform together? I'm not sure that's a great
idea.

Create a query using tbl_Clinician.
Type an expression like this into the Field row:
TheMonth: Month([ClinDate])
This returns 1 for Jan, 2 for Feb, and so on.
Save the query.
Set the RecordSource of your subfom to this query.

On your main form, add a text box with these properties:
Control Source =1
Name txtMonth1
Visible No
The first subform control will have properties like this
Link Master Fields [ClientID]
Link Child Fields [ClientID]
Add another to the end of these properties to filter the month as well,
so
it becomes:
Link Master Fields [ClientID]; [txtMonth1]
Link Child Fields [ClientID]; [TheMonth]
This subform now filters for January.

Create a second text box named txtMonth2, with ControlSource =2, and so
on
for the remaining months.

I am creating a database that uses a combo box for the filed 'Date' in a
table called 'tbl_Clinician'. I have created a form called
'frm_Clincian'
that consists of 12 pages with 12 subforms, one for each month of the
year.
Each subform comes from the same table 'tbl_Clinician'. However, I want
the
'Date' drop down box to display only the dates for each month in each
months'
subform. The Dates must still be stored in the same table
'tbl_Clinician'.
How can I go about doing this. Is there an IIf statement i can write in
a
query?

 

[Radhika]

I am trying to type in a field (Date) bound to the query which has the
calculation (TheMonth). The Month on the subform is bound to txMonth1 on the
Main form (text box).

If this does not work, is there any other way in which I can achieve the
following task?:

I have one table tbl_Clinician, with the field 'Date' (I do realize this is
a bad name)

I have a form with 12 pages, one for each month of the year. Each of these
pages has the same subform that carries all the information from
tbl_Clinician.

However, for the Date field on each subform, I want only the dates
corresponding to the months to be entered and saved.

I am having a hard time doing this using one table.

I attempted the procedure you mentioned earlier, but it gives me an error
msg saying 'Field cannot be udpated'.

Is there anyway in which I can achieve this task.

Thank you for your help!

So the user is trying to type in the text box that is bound to the
calculated query field? That can't work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you!

I do have a concern and was hoping you could help me. I created a qry with
the expression you recommended TheMonth: Month([ClinDate]) and saved the
qry
as the record source for the form and subfrom. However, when I try to add
a
field in the subform, i get a message saying 'Field cannot be updated'
when i
try to add a new date. Is there anything I can do to fix this?

Thanks,
Radhika

Firstly, Date is a reserved word, so will give you grief. I'll assume you
have changed the name to something like ClinDate.

Secondly, if you match each subform like that based on the month, any
record
where ClinDate is null will disappear (not show in any subform.) To avoid
this, you might want to set the Required proeprty to Yes for this field
(bottom pane of table design.)

Next, you want all the January events in the first subform, the Feb ones
in
the second, and so on. I wonder if you've thought about how this will
look
after a couple of years? You will have the January 2008, 2009, and 2010
appointments in the first subform together? I'm not sure that's a great
idea.

Create a query using tbl_Clinician.
Type an expression like this into the Field row:
TheMonth: Month([ClinDate])
This returns 1 for Jan, 2 for Feb, and so on.
Save the query.
Set the RecordSource of your subfom to this query.

On your main form, add a text box with these properties:
Control Source =1
Name txtMonth1
Visible No
The first subform control will have properties like this
Link Master Fields [ClientID]
Link Child Fields [ClientID]
Add another to the end of these properties to filter the month as well,
so
it becomes:
Link Master Fields [ClientID]; [txtMonth1]
Link Child Fields [ClientID]; [TheMonth]
This subform now filters for January.

Create a second text box named txtMonth2, with ControlSource =2, and so
on
for the remaining months.

I am creating a database that uses a combo box for the filed 'Date' in a
table called 'tbl_Clinician'. I have created a form called
'frm_Clincian'
that consists of 12 pages with 12 subforms, one for each month of the
year.
Each subform comes from the same table 'tbl_Clinician'. However, I want
the
'Date' drop down box to display only the dates for each month in each
months'
subform. The Dates must still be stored in the same table
'tbl_Clinician'.
How can I go about doing this. Is there an IIf statement i can write in
a
query?

 

[Allen Browne]

If you really can't get the linking to work, you could create 12 queries
(one that returns each month), and 12 subforms (one bound to each query.)

Again, I'm not sure I follow what's going on here, so perhaps I'm not
getting it. (It's not intuitive to me that I would look for Jan 2005, Jan
2006, and Jan 2007 in one subform, but Feb 2005 in a different place.)