How do I add a calendar drop-down in Access?

Disregard my last response about the 2nd step! I was apparently making it
much more complicated than it was! I figured it out and it works!!!!

Thank you so much for your help.

"MikeJohnB" wrote:

Angela, I am really sorry, I have used Steven Lebans solutions on many
occasions but this was not one of them so I applogise for misdirecting you.

In fact the solution I used for this item was by Allen Browne
http://allenbrowne.com/ser-51.html

With his solution you download the caledar form and buttons. You simply copy
the calendar form to your forms area and copy the sample button to your form
where you place it next to the control where you want to enter the date. You
change the OnClick event to reflect your controls name.

=CalendarFor([Date_of_Enquiry],"Set the Enquiry Date")

(My controls name is [Date_of_Enquiry] as you can see above, the text in
quotes is a comment for notes)

Then when you select the date on the Calendar, it pops the date selected
straight into the control on your form. Copy the button as many time as you
have date fields and just change the on click event to the new field name.

I'm really sorry if you have had problems, in future I will either check
first or keep my mouth shut. Hope I have now put the record straight and
helped you?

Regards

Mike

"Angela" wrote:

I have continued to try this. I can't get past a "Compile Error: Ambiguous
name deteced: DialogFont". I've made several attempts to fix this, but I
just can't figure it out. Can you help with this?

thanks

"MikeJohnB" wrote:

Can recommend http/www.lebans.com/monthcalendar.htm even I was able to set it
up by following the destructions.

"Ed Robichaud" wrote:

Look at:

http://www.fontstuff.com/access/acctut09.htm

and/or

http://www.mvps.org/access/forms/frm0057.htm

or

http://allenbrowne.com/ser-51.html

or my favorite-

http://www.lebans.com/monthcalendar.htm

-Ed


"Angela" wrote in message
...
I have a table to track compliance issues in which there are 3 dates (date
issue received, date started, date closed). I would like to use drop-down
calendars for each of these fields. Is this possible? If so, please
explain
in the simplest of terms since I have not used Access in this way before.

Thank you so much!

My Pleasue Angela, I'm really into Excel VBA and like you I have to ask the
odd question on here but there again, that is what this site is for. There
are some really clever people on here who will help if they can.

Really glad to have been able to put right your struggle but as I said, I
have also used some of Steve's solutions and they have worked well. Hope you
have as much fun at this game as I do, pity I'm too old in the tooth to pick
this game up as quickly as some. All the very best wishes

Mike.

"Angela" wrote:

Disregard my last response about the 2nd step! I was apparently making it
much more complicated than it was! I figured it out and it works!!!!

Thank you so much for your help.

"MikeJohnB" wrote:

Angela, I am really sorry, I have used Steven Lebans solutions on many
occasions but this was not one of them so I applogise for misdirecting you.

In fact the solution I used for this item was by Allen Browne
http://allenbrowne.com/ser-51.html

With his solution you download the caledar form and buttons. You simply copy
the calendar form to your forms area and copy the sample button to your form
where you place it next to the control where you want to enter the date. You
change the OnClick event to reflect your controls name.

=CalendarFor([Date_of_Enquiry],"Set the Enquiry Date")

(My controls name is [Date_of_Enquiry] as you can see above, the text in
quotes is a comment for notes)

Then when you select the date on the Calendar, it pops the date selected
straight into the control on your form. Copy the button as many time as you
have date fields and just change the on click event to the new field name.

I'm really sorry if you have had problems, in future I will either check
first or keep my mouth shut. Hope I have now put the record straight and
helped you?

Regards

Mike

"Angela" wrote:

I have continued to try this. I can't get past a "Compile Error: Ambiguous
name deteced: DialogFont". I've made several attempts to fix this, but I
just can't figure it out. Can you help with this?

thanks

"MikeJohnB" wrote:

Can recommend http/www.lebans.com/monthcalendar.htm even I was able to set it
up by following the destructions.

"Ed Robichaud" wrote:

Look at:

http://www.fontstuff.com/access/acctut09.htm

and/or

http://www.mvps.org/access/forms/frm0057.htm

or

http://allenbrowne.com/ser-51.html

or my favorite-

http://www.lebans.com/monthcalendar.htm

-Ed


"Angela" wrote in message
...
I have a table to track compliance issues in which there are 3 dates (date
issue received, date started, date closed). I would like to use drop-down
calendars for each of these fields. Is this possible? If so, please
explain
in the simplest of terms since I have not used Access in this way before.

Thank you so much!

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

My Pleasue Angela, I'm really into Excel VBA and like you I have to ask the
odd question on here but there again, that is what this site is for. There
are some really clever people on here who will help if they can.

Really glad to have been able to put right your struggle but as I said, I
have also used some of Steve's solutions and they have worked well. Hope you
have as much fun at this game as I do, pity I'm too old in the tooth to pick
this game up as quickly as some. All the very best wishes

Mike.

"Angela" wrote:

Disregard my last response about the 2nd step! I was apparently making it
much more complicated than it was! I figured it out and it works!!!!

Thank you so much for your help.

"MikeJohnB" wrote:

Angela, I am really sorry, I have used Steven Lebans solutions on many
occasions but this was not one of them so I applogise for misdirecting you.

In fact the solution I used for this item was by Allen Browne
http://allenbrowne.com/ser-51.html

With his solution you download the caledar form and buttons. You simply copy
the calendar form to your forms area and copy the sample button to your form
where you place it next to the control where you want to enter the date. You
change the OnClick event to reflect your controls name.

=CalendarFor([Date_of_Enquiry],"Set the Enquiry Date")

(My controls name is [Date_of_Enquiry] as you can see above, the text in
quotes is a comment for notes)

Then when you select the date on the Calendar, it pops the date selected
straight into the control on your form. Copy the button as many time as you
have date fields and just change the on click event to the new field name.

I'm really sorry if you have had problems, in future I will either check
first or keep my mouth shut. Hope I have now put the record straight and
helped you?

Regards

Mike

"Angela" wrote:

I have continued to try this. I can't get past a "Compile Error: Ambiguous
name deteced: DialogFont". I've made several attempts to fix this, but I
just can't figure it out. Can you help with this?

thanks

"MikeJohnB" wrote:

Can recommend http/www.lebans.com/monthcalendar.htm even I was able to set it
up by following the destructions.

"Ed Robichaud" wrote:

Look at:

http://www.fontstuff.com/access/acctut09.htm

and/or

http://www.mvps.org/access/forms/frm0057.htm

or

http://allenbrowne.com/ser-51.html

or my favorite-

http://www.lebans.com/monthcalendar.htm

-Ed


"Angela" wrote in message
...
I have a table to track compliance issues in which there are 3 dates (date
issue received, date started, date closed). I would like to use drop-down
calendars for each of these fields. Is this possible? If so, please
explain
in the simplest of terms since I have not used Access in this way before.

Thank you so much!

The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.

set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.

HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues

1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)

This will give a selection of the Categories and will save it in the form
where you tell it to.

Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.

Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!

With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.

As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]

This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.

So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.

I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.

Perhaps someone else can assist but I hope this helps

Best Wishes, Mike

"Angela" wrote:

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

My Pleasue Angela, I'm really into Excel VBA and like you I have to ask the
odd question on here but there again, that is what this site is for. There
are some really clever people on here who will help if they can.

Really glad to have been able to put right your struggle but as I said, I
have also used some of Steve's solutions and they have worked well. Hope you
have as much fun at this game as I do, pity I'm too old in the tooth to pick
this game up as quickly as some. All the very best wishes

Mike.

"Angela" wrote:

Disregard my last response about the 2nd step! I was apparently making it
much more complicated than it was! I figured it out and it works!!!!

Thank you so much for your help.

"MikeJohnB" wrote:

Angela, I am really sorry, I have used Steven Lebans solutions on many
occasions but this was not one of them so I applogise for misdirecting you.

In fact the solution I used for this item was by Allen Browne
http://allenbrowne.com/ser-51.html

With his solution you download the caledar form and buttons. You simply copy
the calendar form to your forms area and copy the sample button to your form
where you place it next to the control where you want to enter the date. You
change the OnClick event to reflect your controls name.

=CalendarFor([Date_of_Enquiry],"Set the Enquiry Date")

(My controls name is [Date_of_Enquiry] as you can see above, the text in
quotes is a comment for notes)

Then when you select the date on the Calendar, it pops the date selected
straight into the control on your form. Copy the button as many time as you
have date fields and just change the on click event to the new field name.

I'm really sorry if you have had problems, in future I will either check
first or keep my mouth shut. Hope I have now put the record straight and
helped you?

Regards

Mike

"Angela" wrote:

I have continued to try this. I can't get past a "Compile Error: Ambiguous
name deteced: DialogFont". I've made several attempts to fix this, but I
just can't figure it out. Can you help with this?

thanks

"MikeJohnB" wrote:

Can recommend http/www.lebans.com/monthcalendar.htm even I was able to set it
up by following the destructions.

"Ed Robichaud" wrote:

Look at:

http://www.fontstuff.com/access/acctut09.htm

and/or

http://www.mvps.org/access/forms/frm0057.htm

or

http://allenbrowne.com/ser-51.html

or my favorite-

http://www.lebans.com/monthcalendar.htm

-Ed


"Angela" wrote in message
...
I have a table to track compliance issues in which there are 3 dates (date
issue received, date started, date closed). I would like to use drop-down
calendars for each of these fields. Is this possible? If so, please
explain
in the simplest of terms since I have not used Access in this way before.

Thank you so much!

Sorry Angela, forgot to tell you, in the tables need a unique value to link
them. Auto number in TblCategories "UniqNum" lets say for Categories set to
indexed Yes No Duplicates and "RecNum" in TblIssues, set to indexed
duplicates ok.

In relationships, show both tables and drag a link between TblCategories
"UniqNum" to TblIssues "RecNum" Right click on the link line and select
enforce referential integrity, cascade up and down adds and deletions are
also selected. This will give you a one to many table link.

Alternatively, send me you e mail and I will send a demo if its easier. I'm
sorry if I teaching you things you already know though. PS if you posy your
email address, dont post it in the normal manner something like Angela at
hotmail dot com will make sure auto e mail pickers cannot get you for scam.



"MikeJohnB" wrote:

The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.

set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.

HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues

1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)

This will give a selection of the Categories and will save it in the form
where you tell it to.

Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.

Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!

With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.

As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]

This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.

So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.

I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.

Perhaps someone else can assist but I hope this helps

Best Wishes, Mike

"Angela" wrote:

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

My Pleasue Angela, I'm really into Excel VBA and like you I have to ask the
odd question on here but there again, that is what this site is for. There
are some really clever people on here who will help if they can.

Really glad to have been able to put right your struggle but as I said, I
have also used some of Steve's solutions and they have worked well. Hope you
have as much fun at this game as I do, pity I'm too old in the tooth to pick
this game up as quickly as some. All the very best wishes

Mike.

"Angela" wrote:

Disregard my last response about the 2nd step! I was apparently making it
much more complicated than it was! I figured it out and it works!!!!

Thank you so much for your help.

"MikeJohnB" wrote:

Angela, I am really sorry, I have used Steven Lebans solutions on many
occasions but this was not one of them so I applogise for misdirecting you.

In fact the solution I used for this item was by Allen Browne
http://allenbrowne.com/ser-51.html

With his solution you download the caledar form and buttons. You simply copy
the calendar form to your forms area and copy the sample button to your form
where you place it next to the control where you want to enter the date. You
change the OnClick event to reflect your controls name.

=CalendarFor([Date_of_Enquiry],"Set the Enquiry Date")

(My controls name is [Date_of_Enquiry] as you can see above, the text in
quotes is a comment for notes)

Then when you select the date on the Calendar, it pops the date selected
straight into the control on your form. Copy the button as many time as you
have date fields and just change the on click event to the new field name.

I'm really sorry if you have had problems, in future I will either check
first or keep my mouth shut. Hope I have now put the record straight and
helped you?

Regards

Mike

"Angela" wrote:

I have continued to try this. I can't get past a "Compile Error: Ambiguous
name deteced: DialogFont". I've made several attempts to fix this, but I
just can't figure it out. Can you help with this?

thanks

"MikeJohnB" wrote:

Can recommend http/www.lebans.com/monthcalendar.htm even I was able to set it
up by following the destructions.

"Ed Robichaud" wrote:

Look at:

http://www.fontstuff.com/access/acctut09.htm

and/or

http://www.mvps.org/access/forms/frm0057.htm

or

http://allenbrowne.com/ser-51.html

or my favorite-

http://www.lebans.com/monthcalendar.htm

-Ed


"Angela" wrote in message
...
I have a table to track compliance issues in which there are 3 dates (date
issue received, date started, date closed). I would like to use drop-down
calendars for each of these fields. Is this possible? If so, please
explain
in the simplest of terms since I have not used Access in this way before.

Thank you so much!

Mike,

I am just not getting this! Let me be more detailed because I can not get
this to work the way I need it to.

Here's how I want it to work:

"Category" drop-down list "Issue" drop-down list

Retailiation By Management
By Co-Worker(s)
Other

Training Failure to Complete
Scheculing Issue
Request for Speaker
Accuracy of Materials
Other

Duty to Report Failure to Report
Bad Faith Report
Concern Ignored by Management
Other

Acknowledgement Failure to Complete
Incomplete
Disclosure
Request for Contact

and so on.....

These are just some of the categories in the 1st drop-down list. As you
can see, the user would select from the category list first. Then depending
on their choice, I want the long list of issues narrowed down to just the
items that apply to that particular category. There are no identical items
in the 2 lists and the user needs to be able to choose "other" from issues
regardless of the category.

I will continue to play with this, but if you could send me an example, that
would be great. Send it to amj41563 at yahoo dot com.

Thanks.



"MikeJohnB" wrote:

Sorry Angela, forgot to tell you, in the tables need a unique value to link
them. Auto number in TblCategories "UniqNum" lets say for Categories set to
indexed Yes No Duplicates and "RecNum" in TblIssues, set to indexed
duplicates ok.

In relationships, show both tables and drag a link between TblCategories
"UniqNum" to TblIssues "RecNum" Right click on the link line and select
enforce referential integrity, cascade up and down adds and deletions are
also selected. This will give you a one to many table link.

Alternatively, send me you e mail and I will send a demo if its easier. I'm
sorry if I teaching you things you already know though. PS if you posy your
email address, dont post it in the normal manner something like Angela at
hotmail dot com will make sure auto e mail pickers cannot get you for scam.



"MikeJohnB" wrote:

The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.

set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.

HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues

1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)

This will give a selection of the Categories and will save it in the form
where you tell it to.

Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.

Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!

With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.

As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]

This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.

So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.

I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.

Perhaps someone else can assist but I hope this helps

Best Wishes, Mike

"Angela" wrote:

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

Will do Angela, what version of Access you using? 97. 2k or 2k3?

"Angela" wrote:

Mike,

I am just not getting this! Let me be more detailed because I can not get
this to work the way I need it to.

Here's how I want it to work:

"Category" drop-down list "Issue" drop-down list

Retailiation By Management
By Co-Worker(s)
Other

Training Failure to Complete
Scheculing Issue
Request for Speaker
Accuracy of Materials
Other

Duty to Report Failure to Report
Bad Faith Report
Concern Ignored by Management
Other

Acknowledgement Failure to Complete
Incomplete
Disclosure
Request for Contact

and so on.....

These are just some of the categories in the 1st drop-down list. As you
can see, the user would select from the category list first. Then depending
on their choice, I want the long list of issues narrowed down to just the
items that apply to that particular category. There are no identical items
in the 2 lists and the user needs to be able to choose "other" from issues
regardless of the category.

I will continue to play with this, but if you could send me an example, that
would be great. Send it to amj41563 at yahoo dot com.

Thanks.



"MikeJohnB" wrote:

Sorry Angela, forgot to tell you, in the tables need a unique value to link
them. Auto number in TblCategories "UniqNum" lets say for Categories set to
indexed Yes No Duplicates and "RecNum" in TblIssues, set to indexed
duplicates ok.

In relationships, show both tables and drag a link between TblCategories
"UniqNum" to TblIssues "RecNum" Right click on the link line and select
enforce referential integrity, cascade up and down adds and deletions are
also selected. This will give you a one to many table link.

Alternatively, send me you e mail and I will send a demo if its easier. I'm
sorry if I teaching you things you already know though. PS if you posy your
email address, dont post it in the normal manner something like Angela at
hotmail dot com will make sure auto e mail pickers cannot get you for scam.



"MikeJohnB" wrote:

The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.

set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.

HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues

1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)

This will give a selection of the Categories and will save it in the form
where you tell it to.

Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.

Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!

With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.

As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]

This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.

So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.

I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.

Perhaps someone else can assist but I hope this helps

Best Wishes, Mike

"Angela" wrote:

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

Mike,

I'm using 2K3.

Thanks so much!

"MikeJohnB" wrote:

Will do Angela, what version of Access you using? 97. 2k or 2k3?

"Angela" wrote:

Mike,

I am just not getting this! Let me be more detailed because I can not get
this to work the way I need it to.

Here's how I want it to work:

"Category" drop-down list "Issue" drop-down list

Retailiation By Management
By Co-Worker(s)
Other

Training Failure to Complete
Scheculing Issue
Request for Speaker
Accuracy of Materials
Other

Duty to Report Failure to Report
Bad Faith Report
Concern Ignored by Management
Other

Acknowledgement Failure to Complete
Incomplete
Disclosure
Request for Contact

and so on.....

These are just some of the categories in the 1st drop-down list. As you
can see, the user would select from the category list first. Then depending
on their choice, I want the long list of issues narrowed down to just the
items that apply to that particular category. There are no identical items
in the 2 lists and the user needs to be able to choose "other" from issues
regardless of the category.

I will continue to play with this, but if you could send me an example, that
would be great. Send it to amj41563 at yahoo dot com.

Thanks.



"MikeJohnB" wrote:

Sorry Angela, forgot to tell you, in the tables need a unique value to link
them. Auto number in TblCategories "UniqNum" lets say for Categories set to
indexed Yes No Duplicates and "RecNum" in TblIssues, set to indexed
duplicates ok.

In relationships, show both tables and drag a link between TblCategories
"UniqNum" to TblIssues "RecNum" Right click on the link line and select
enforce referential integrity, cascade up and down adds and deletions are
also selected. This will give you a one to many table link.

Alternatively, send me you e mail and I will send a demo if its easier. I'm
sorry if I teaching you things you already know though. PS if you posy your
email address, dont post it in the normal manner something like Angela at
hotmail dot com will make sure auto e mail pickers cannot get you for scam.



"MikeJohnB" wrote:

The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.

set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.

HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues

1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)

This will give a selection of the Categories and will save it in the form
where you tell it to.

Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.

Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!

With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.

As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]

This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.

So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.

I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.

Perhaps someone else can assist but I hope this helps

Best Wishes, Mike

"Angela" wrote:

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

Have sent you demo Angela

"Angela" wrote:

Mike,

I'm using 2K3.

Thanks so much!

"MikeJohnB" wrote:

Will do Angela, what version of Access you using? 97. 2k or 2k3?

"Angela" wrote:

Mike,

I am just not getting this! Let me be more detailed because I can not get
this to work the way I need it to.

Here's how I want it to work:

"Category" drop-down list "Issue" drop-down list

Retailiation By Management
By Co-Worker(s)
Other

Training Failure to Complete
Scheculing Issue
Request for Speaker
Accuracy of Materials
Other

Duty to Report Failure to Report
Bad Faith Report
Concern Ignored by Management
Other

Acknowledgement Failure to Complete
Incomplete
Disclosure
Request for Contact

and so on.....

These are just some of the categories in the 1st drop-down list. As you
can see, the user would select from the category list first. Then depending
on their choice, I want the long list of issues narrowed down to just the
items that apply to that particular category. There are no identical items
in the 2 lists and the user needs to be able to choose "other" from issues
regardless of the category.

I will continue to play with this, but if you could send me an example, that
would be great. Send it to amj41563 at yahoo dot com.

Thanks.



"MikeJohnB" wrote:

Sorry Angela, forgot to tell you, in the tables need a unique value to link
them. Auto number in TblCategories "UniqNum" lets say for Categories set to
indexed Yes No Duplicates and "RecNum" in TblIssues, set to indexed
duplicates ok.

In relationships, show both tables and drag a link between TblCategories
"UniqNum" to TblIssues "RecNum" Right click on the link line and select
enforce referential integrity, cascade up and down adds and deletions are
also selected. This will give you a one to many table link.

Alternatively, send me you e mail and I will send a demo if its easier. I'm
sorry if I teaching you things you already know though. PS if you posy your
email address, dont post it in the normal manner something like Angela at
hotmail dot com will make sure auto e mail pickers cannot get you for scam.



"MikeJohnB" wrote:

The way I have done this is with two combo boxes, you would need to set up a
table to hold the value of Category and one for Issue with a 1 to many link
between the two tables. In other words Category "HR" will have many Issues.

set up a query where the value for both tables resides (Select both tables
when setting up the query and providing you have the link, this should work)
Table Category should show a link to Table Issues with 1 to many (Lets Call
this QryLink) Opening the query, you should see lots of Categories of the
same name with a few issues against each category if you have done it right.

HR Issue1
HR issue1a
HR Issue2
etc with the Category changing to show another many selection of issues

1st combo (Combo 1)
when you set up a combo box by dragging a combo box to your form based on
the main table by using the tools menu, you get a wizard, choose option 1
find a value in a table or query (Or something like that). The wizard takes
you through the table or query to choose and where to save the value once it
has been selected in the combo box. Normally this would be the field where
you want to store the value selected. Base the Combo box source on your query
(ie from "QryLink" Select "Category" and store this value in the field of the
form where you want the CategoryValue stored)

This will give a selection of the Categories and will save it in the form
where you tell it to.

Combo 2
Exactly the same as combo 1 exept From "QryLink" Select "Issues" and store
that value in the forms Main Table field where you want the value IssueValue
to be stored.

Now you have two Combo Boxes which don't relate to each other, you will see
al the Categories in Combo 1 and all the issues in Combo 2, I know this is
not what you want but!

With your form open in design view
In the properties of Combo 2, you will see that Access has set a Select
Query in the Row Source, Click in the Row Source and click on the far right
box which pops up. (marked "...") This will take you direct to the select
query in design view.

As you have based the Combo box on the query QryLink, you will see both
Categories and issues inthe query table, right click in the crietria row of
"Categories" and select build. You with see the expression builder pop up. In
the left hand column, select forms and navigate to you main form through all
open forms. Select from your main form Combo1 and in the right box, select
value. this should give you something like this
[Forms]![Frm_MainFormName]![Combo1]

This means that the result of the query is based on the value selected in
the first Combo1 control on your form. I use this typically to select
Different Contacts from the same company.

So, what have we? if you select "HR" from Combo1, you should only see issues
which relate to "HR" Issue 1, Issue 2, Issue 3 etc in Combo2, you should not
see anything relating to any other Category.

I'm sorry if this is a long drawn out method, there are easier ways to do
this but as yet I don't know them.

Perhaps someone else can assist but I hope this helps

Best Wishes, Mike

"Angela" wrote:

You were so helpful with this issue, I wonder if you could help me with
another one in the same database. I have 2 fields that I need to nest
together. The first field is "Category" which has a drop-down list of
categories. The 2nd field "Issue" and needs to be dependent on the Category
field.

Ex: If the category is "HR Issues", then the drop-down in the issue field
would contain a list of issues to choose from. Each category would have
differerent issues that would relate.

Is it possible to do something like this? Do I need to create additional
tables?

Any help you can offer on this would be great!!

Thanks so much.

"MikeJohnB" wrote:

For your combo boxes

Have found this link

http://support.microsoft.com/?id=289670

Also have a look at the question posed by tiger and the answer by Larry
Linson for the same subject in Access General Questions, a much simpler
answer give in this thread.

HTH Mike


"Angela" wrote:

I have a table to track compliance issues in which there are 3 dates (date
issue received, date started, date closed). I would like to use drop-down
calendars for each of these fields. Is this possible? If so, please explain
in the simplest of terms since I have not used Access in this way before.

Thank you so much!