Recipe form

[DF59]

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

 

[John W. Vinson]

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.

 

[DF59]

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]

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?

 

[DF59]

I also need to test if the record I am working on is a new record

thanks again
Dominic

 

[DF59]

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,

 

[DF59]

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

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]

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.

 

[DF59]

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]

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.

 

[DF59]

Thank you, perfect. Code wokred beautifully

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]

Thank you, perfect. Code wokred beautifully

Glad to have been of help. Payment in the form of a good vegetarian recipe
would be welcome... <g>