If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Recipe form
I have a recipe database I am building or my wife. I have the categories table and my recipes table with the relationshi set u between them.
My wife would like a field on the recippe form displaying what number recipe the item is within the category it is asigned to. For example if have 10 meat recipes and meatloaf is the fifth one, she wants the number 5 display. I am asuming I need some kind of count query or something like it for my field, but cannot figure out how to do this. thank you Dominic Fontana |
#2
|
|||
|
|||
Recipe form
On Mon, 1 Feb 2010 13:19:16 -0500, "DF59" wrote:
I have a recipe database I am building or my wife. I have the categories table and my recipes table with the relationshi set u between them. My wife would like a field on the recippe form displaying what number recipe the item is within the category it is asigned to. For example if have 10 meat recipes and meatloaf is the fifth one, she wants the number 5 display. I am asuming I need some kind of count query or something like it for my field, but cannot figure out how to do this. Well, we can't either, given the information you've posted. Is there a "recipe number" field in the table? If not, what do you mean by the term? Remeber that records in a table have no defined order; they're like potatoes in a sack of potatoes... there is no "fifth recipe" in that regard. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Recipe form
Ok, thanks for the info I've rethought this and trying to figure out how to
do it. Wouldthis be posible: Add a new field to my recipe table that would give my wife what she wants sch as if a recie is the 5th within category CrockPot it would become 5. Here is what I am thinking could be done: 1) Create a field which will be an autonumber named my desired name 2) have the value set to the count of recipe within its category + 1 I just can't seem to figure how I would do this either. I will start this and see what I come u with but any help would be appreciated. Thank you again "John W. Vinson" wrote in message ... On Mon, 1 Feb 2010 13:19:16 -0500, "DF59" wrote: I have a recipe database I am building or my wife. I have the categories table and my recipes table with the relationshi set u between them. My wife would like a field on the recippe form displaying what number recipe the item is within the category it is asigned to. For example if have 10 meat recipes and meatloaf is the fifth one, she wants the number 5 display. I am asuming I need some kind of count query or something like it for my field, but cannot figure out how to do this. Well, we can't either, given the information you've posted. Is there a "recipe number" field in the table? If not, what do you mean by the term? Remeber that records in a table have no defined order; they're like potatoes in a sack of potatoes... there is no "fifth recipe" in that regard. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Recipe form
On Wed, 3 Feb 2010 11:37:25 -0500, "DF59" wrote:
Ok, thanks for the info I've rethought this and trying to figure out how to do it. Wouldthis be posible: Add a new field to my recipe table that would give my wife what she wants sch as if a recie is the 5th within category CrockPot it would become 5. Here is what I am thinking could be done: 1) Create a field which will be an autonumber named my desired name 2) have the value set to the count of recipe within its category + 1 I just can't seem to figure how I would do this either. I will start this and see what I come u with but any help would be appreciated. An Autonumber will NOT meet your needs. You cannot edit or control the value it assigns, and it will be unique within the entire recipe table, not within the category. I'm perplexed at the purpose of this number! What benefit do you get from knowing that the beef brisket and carrots and potatoes stew was the fifth, or eighth, or seventeenth crockpot recipe entered into the database? It's a meaningless number, based on the arbitrary chance order of entry into the database. Secondly, surely a recipe can be in many categories - it's a crockpot dish, it's a main dish, it's a stew, it's a hearty winter dish; would you need four different sequence numbers for the four categories? What purpose does this number serve? -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Recipe form
I also need to test if the record I am working on is a new record
thanks again Dominic "John W. Vinson" wrote in message ... On Mon, 1 Feb 2010 13:19:16 -0500, "DF59" wrote: I have a recipe database I am building or my wife. I have the categories table and my recipes table with the relationshi set u between them. My wife would like a field on the recippe form displaying what number recipe the item is within the category it is asigned to. For example if have 10 meat recipes and meatloaf is the fifth one, she wants the number 5 display. I am asuming I need some kind of count query or something like it for my field, but cannot figure out how to do this. Well, we can't either, given the information you've posted. Is there a "recipe number" field in the table? If not, what do you mean by the term? Remeber that records in a table have no defined order; they're like potatoes in a sack of potatoes... there is no "fifth recipe" in that regard. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Recipe form
I agree, it does seem useless and meaningless but the wife wants it, so I am
trying my best. I set the field to be a number instead of autonumber with a default value that has the following formula: =Count([FoodCategoryID]=[recipes]![FoodCategoryID]) I am getting close, but as my record does have the Category defined yet yields a 1, "John W. Vinson" wrote in message ... On Wed, 3 Feb 2010 11:37:25 -0500, "DF59" wrote: Ok, thanks for the info I've rethought this and trying to figure out how to do it. Wouldthis be posible: Add a new field to my recipe table that would give my wife what she wants sch as if a recie is the 5th within category CrockPot it would become 5. Here is what I am thinking could be done: 1) Create a field which will be an autonumber named my desired name 2) have the value set to the count of recipe within its category + 1 I just can't seem to figure how I would do this either. I will start this and see what I come u with but any help would be appreciated. An Autonumber will NOT meet your needs. You cannot edit or control the value it assigns, and it will be unique within the entire recipe table, not within the category. I'm perplexed at the purpose of this number! What benefit do you get from knowing that the beef brisket and carrots and potatoes stew was the fifth, or eighth, or seventeenth crockpot recipe entered into the database? It's a meaningless number, based on the arbitrary chance order of entry into the database. Secondly, surely a recipe can be in many categories - it's a crockpot dish, it's a main dish, it's a stew, it's a hearty winter dish; would you need four different sequence numbers for the four categories? What purpose does this number serve? -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Recipe form
I think this may take care of my concern:
If I set the after update property of the food category ID to set the value of the caegory count field to be the count of how many recies are within that category plus 1. I looked under functions in the expression builder but did not see setval function, is it only available via code builder? thanks again "DF59" wrote in message ... I agree, it does seem useless and meaningless but the wife wants it, so I am trying my best. I set the field to be a number instead of autonumber with a default value that has the following formula: =Count([FoodCategoryID]=[recipes]![FoodCategoryID]) I am getting close, but as my record does have the Category defined yet yields a 1, "John W. Vinson" wrote in message ... On Wed, 3 Feb 2010 11:37:25 -0500, "DF59" wrote: Ok, thanks for the info I've rethought this and trying to figure out how to do it. Wouldthis be posible: Add a new field to my recipe table that would give my wife what she wants sch as if a recie is the 5th within category CrockPot it would become 5. Here is what I am thinking could be done: 1) Create a field which will be an autonumber named my desired name 2) have the value set to the count of recipe within its category + 1 I just can't seem to figure how I would do this either. I will start this and see what I come u with but any help would be appreciated. An Autonumber will NOT meet your needs. You cannot edit or control the value it assigns, and it will be unique within the entire recipe table, not within the category. I'm perplexed at the purpose of this number! What benefit do you get from knowing that the beef brisket and carrots and potatoes stew was the fifth, or eighth, or seventeenth crockpot recipe entered into the database? It's a meaningless number, based on the arbitrary chance order of entry into the database. Secondly, surely a recipe can be in many categories - it's a crockpot dish, it's a main dish, it's a stew, it's a hearty winter dish; would you need four different sequence numbers for the four categories? What purpose does this number serve? -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Recipe form
On Wed, 3 Feb 2010 13:15:49 -0500, "DF59" wrote:
I think this may take care of my concern: If I set the after update property of the food category ID to set the value of the caegory count field to be the count of how many recies are within that category plus 1. I looked under functions in the expression builder but did not see setval function, is it only available via code builder? SetVal (or is it SetValue?) is a Macro action. I'm more familiar with VBA code where you can just use Me!controlname = some expression If you'll post your current table fieldnames and code someone can help you write VBA code for this, it's a pretty common request. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Recipe form
ok, my conrolname is CatCount and the field names of my Recipes table ar as
follows: 1) RecipeID Autonumber 2) CatCount Number 3) RecipeName Text 4) RecieDescription Text 5) FoodCategoryID Number 6) TimetoPrepare Text 7) CookTime Text 8) NumberofServings Number 9) Ingredients Memo 10) Instructions Memo 11) BookIncluded YesNo 12) Picture Ole Object If I read John's last reply correctly, my code should look something like this: Me!CatCount = Count(CatCount = Tables!Recipes!{FoodCategoryID] ) + 1 I tried a setval macro to do this but the objects were not resolved correctly. Any hel on his code will be appreciated. Yhanks again "John W. Vinson" wrote in message ... On Wed, 3 Feb 2010 13:15:49 -0500, "DF59" wrote: I think this may take care of my concern: If I set the after update property of the food category ID to set the value of the caegory count field to be the count of how many recies are within that category plus 1. I looked under functions in the expression builder but did not see setval function, is it only available via code builder? SetVal (or is it SetValue?) is a Macro action. I'm more familiar with VBA code where you can just use Me!controlname = some expression If you'll post your current table fieldnames and code someone can help you write VBA code for this, it's a pretty common request. -- John W. Vinson [MVP] |
#10
|
|||
|
|||
Recipe form
On Wed, 3 Feb 2010 18:45:01 -0500, "DF59" wrote:
ok, my conrolname is CatCount and the field names of my Recipes table ar as follows: 1) RecipeID Autonumber 2) CatCount Number 3) RecipeName Text 4) RecieDescription Text 5) FoodCategoryID Number 6) TimetoPrepare Text 7) CookTime Text 8) NumberofServings Number 9) Ingredients Memo 10) Instructions Memo 11) BookIncluded YesNo 12) Picture Ole Object If I read John's last reply correctly, my code should look something like this: Me!CatCount = Count(CatCount = Tables!Recipes!{FoodCategoryID] ) + 1 I tried a setval macro to do this but the objects were not resolved correctly. There are a few ways to do this, but I'd suggest using VBA code, not a Macro; for a one-user system (where you won't have different users competing for new ID numbers) you could use the AfterUpdate event of the FoodCategoryID combo box. View its Properties; on the After Event property on the Events tab click the ... icon and choose Code Builder. Edit the code to Private Sub FoodCategoryID_AfterUpdate Me!CatCount = NZ(DMax("CatCount", "Recipes", _ "FoodCategoryID = " & Me!FoodCategoryID)) + 1 End Sub This will look up the largest existing CatCount for this food category (and get a 0 if there aren't any yet); add 1, and store the result in the CatCount field. I like this better than counting records because that count will be off if you should ever delete a recipe. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|