Lookup table only works as a standalone form

  • Thread starter Thread starter MAC
  • Start date Start date
M

MAC

Dear list,

I have a database regarding meal plans called "MealPlan" with the fields
ID, MealPlan, Price and PlanCost. I have a corresponding form, also called
"MealPlan", in which the user will select the meal plan from a dropdown. I
have a procedure that will insert the price of the corresponding meal plan
in the field PlanCost AFTER selection is made from the MealPlan dropdown.
From subform "Orders" in the Northwind database, I adapted the following
procedure:

Dim strFilter As String
'Evaluate filter before it's passed to Dlook up function
strFilter = "Price = " & Me!Price

'Look up price & assign it to Meal Price control
Me!PlanCost = DLookup("Price", "MealPlan", strFilter)

Exit_Choice_AfterUpdate:
Exit Sub

Err_Choice_AfterUpdate:
MsgBox Err.Description
Resume Exit_Choice_AfterUpdate

1) It works fine as a standalone form (that is, once I select the mealplan,
its corresponding price is inserted in the PlanCost field), but when I
insert this form as a subform, linking by the field "ID" (for each unique
user), it can't evaluate the function, saying that there's a missing
operator in Price. What am I doing wrong?

2) The MealPlan lookup table should not be changed but just populate the
field PlanCost, but because it's linked to the ID field, it keeps adding ids
and choices to the lookup table.

Thank you in advance for any assitance!!!
 
MAC said:
Dear list,

I have a database regarding meal plans called "MealPlan" with the fields
ID, MealPlan, Price and PlanCost. I have a corresponding form, also called
"MealPlan", in which the user will select the meal plan from a dropdown. I
have a procedure that will insert the price of the corresponding meal plan
in the field PlanCost AFTER selection is made from the MealPlan dropdown.
From subform "Orders" in the Northwind database, I adapted the following
procedure:

Dim strFilter As String
'Evaluate filter before it's passed to Dlook up function
strFilter = "Price = " & Me!Price

'Look up price & assign it to Meal Price control
Me!PlanCost = DLookup("Price", "MealPlan", strFilter)

Exit_Choice_AfterUpdate:
Exit Sub

Err_Choice_AfterUpdate:
MsgBox Err.Description
Resume Exit_Choice_AfterUpdate

1) It works fine as a standalone form (that is, once I select the mealplan,
its corresponding price is inserted in the PlanCost field), but when I
insert this form as a subform, linking by the field "ID" (for each unique
user), it can't evaluate the function, saying that there's a missing
operator in Price. What am I doing wrong?

2) The MealPlan lookup table should not be changed but just populate the
field PlanCost, but because it's linked to the ID field, it keeps adding ids
and choices to the lookup table.

Thank you in advance for any assitance!!!
Click to expand...
Hi MAC

I don't know the answer to your questions but your code is very confusing:

strFilter = "Price = " & Me!Price
Me!PlanCost = DLookup("Price", "MealPlan", strFilter)

You are looking up the value of the field "Price" where "Price = " &
Me!Price.
If "Price = " & Me!Price then you already know the value of "Price" - it is
the same as Me!Price.
So what are you gaining or trying to achieve by using the DLookup?

Regards - Joe
 
Hello Joe:

I got this code from the form "orders" in the Northwind sample database,
advised by someone in the list here. As a standalone form it does work,
though. When I select from the dropdown menu (with the meal choices), it
retrieves the price for the corresponding meal plan in the database and
inserts in the PlanCost field. I'm having problems using this form as a
subform in my main form where each user will select the meal plan.

Maybe another method would be better. Here's what I have:

A table with 4 fields: ID, Choice, Price & PlanCost. Each Choice has a
different Price. The PlanCost field is the one that needs to be colonized,
depending on the Choice (so that I can use it in reports etc on each user)
selected by students. The information in this database is something like
this:

ID Choice Price PlanCost
Full Extra-Large $3,075.00
Full Large $2,875.00
Full Regular $2,675.00
Full Small $2,475.00
Reduced Extra-Large $2,425.00
Full Light $2,250.00
Reduced Large $2,225.00
Reduced Regular $2,025.00
Reduced Small $1,825.00
Reduced Light $1,600.00

Any suggestions for alternatives? Any help would greatly appreciated!

Marco
 
MAC said:
Hello Joe:

I got this code from the form "orders" in the Northwind sample database,
advised by someone in the list here. As a standalone form it does work,
though. When I select from the dropdown menu (with the meal choices), it
retrieves the price for the corresponding meal plan in the database and
inserts in the PlanCost field. I'm having problems using this form as a
subform in my main form where each user will select the meal plan.

Maybe another method would be better. Here's what I have:

A table with 4 fields: ID, Choice, Price & PlanCost. Each Choice has a
different Price. The PlanCost field is the one that needs to be colonized,
depending on the Choice (so that I can use it in reports etc on each user)
selected by students. The information in this database is something like
this:

ID Choice Price PlanCost
Full Extra-Large $3,075.00
Full Large $2,875.00
Full Regular $2,675.00
Full Small $2,475.00
Reduced Extra-Large $2,425.00
Full Light $2,250.00
Reduced Large $2,225.00
Reduced Regular $2,025.00
Reduced Small $1,825.00
Reduced Light $1,600.00

Any suggestions for alternatives? Any help would greatly appreciated!

Marco
Click to expand...
Hi MAC

I am struggling to understand your design choices.
In your first post you said that the table MealPlan is a lookup table. If it
is a lookup table, why has it got a field called "PlanCost" which needs to
be "colonized"? If it was colonized, wouldn't it just be colonized with the
value of the "Price" field in the same record (which seems a bit pointless).
You also state something about a selecton made by students yet there is no
mention of a student table.

From the limited information you have supplied, I would design the tables
like this (for the sake of simplicity I am assuming that an individual
student can only select one meal plan)

tblMealPlan
MealPlanID
MealPlan
Price

tblStudent
StudentID
FirstName
LastName
MealPlanID

The last field in tblStudent is important. By storing the MealPlanID in
tblStudent, you can retrieve the MealPlan and Price for it by using a query
which joins tblStudent and tblMealPlan. This means that when you update a
Price or MealPlan in tblMealPlan, when you run the query, it will allways
display the current Price etc. for that MealPlanID.

If you do not want the value of Price or MealPlan to change once it is
assigned to a student, you would store the actual values instead of the id
in which case the table would look like this:
tblStudent
StudentID
FirstName
LastName
MealPlan
PlanCost

The above scenarios do not require the use of a subform.

If you want a student to be able to make more than one MealPlan selection
then instead of a one-to-many relationship as above, you would have a
many-to-many relationship which would require a "joining" table like:

tblMealPlan
MealPlanID
MealPlan
Price

tblStudent
StudentID
FirstName
LastName

tblStudentMealPlan <-- joining table
StudentMealPlanID
StudentID
MealPlanID (or MealPlan , PlanCost)

A subform could be used for this type of relationship. The main form would
be the based on tblStudent, the subform would be based on tblStudentMealPlan
linked to the main form by StudentID. tblMealPlan would be the lookup table
for the MealPlanID field of the tblStudentMealPlan, probably using a combo
box to select the MealPlan. The combo box would be bound to the MealPlanID
field or if storing the PlanCost because you don't want it to change if the
value changes in the lookup table, that is where you would update the
PlanCost using code in the after update event of the combo box.

If I have understood correctly (or not) what you are trying to do, please
let me know and I will give you more information on how to implement it.

Regards - Joe
 
Dear Joe:

Yes, I know that code is little odd. I was advised to use it in the table
design newsgroup and it comes from MS Northwind database. For some
reason, it works in the Northwind database.

Here's what I need: I have an automation code that will write expense
letters for international students, based on their selections in the letter
request database. They will interact with this database using a predesigned
form & answer questions regarding all kinds of personal expenses, on and
off-campus. One of the pieces of information they will need to answer is
how much a they spent on food. They don't know how much or they don't have
the exact figures. But I do (the price grid in my last posting). Once we
have all the info on their expenses my code will write the letters,
colonizing the right fields in Word etc etc.

They don't know the exact figures, but they sure know the meal plan they
picked (and royally paid!). Based on the price grid I mentioned in my last
posting, I need to come up with a pull down menu in my Expense Database,
with the 10 choices for their meal plan (the grid in my last posting). Once
selected (and since the meal plan prices don't usually change or change only
once a year), I need the corresponding amount (from the price grid in last
posting) to be put in the plan cost field. Yes, each student will have a
somewhat different amount in the plan cost field, because it depends on
individual choices. My main database ("Expenses") has information on each
student and is, of course, organized by their student number.

I will use the same method you advise me to use with the meal grid for other
types of expenses in the same database, such as "accommodation".

Since in my office relies mostly on "starving" volunteer students, with
hardly any computer skills, my databases have to be interactive & foolproof.

So, looking at my price grid, what do you suggest?? I really appreciate
your help!!!!

Mark
 
MAC said:
Dear Joe:

Yes, I know that code is little odd. I was advised to use it in the table
design newsgroup and it comes from MS Northwind database. For some
reason, it works in the Northwind database.

Here's what I need: I have an automation code that will write expense
letters for international students, based on their selections in the letter
request database. They will interact with this database using a predesigned
form & answer questions regarding all kinds of personal expenses, on and
off-campus. One of the pieces of information they will need to answer is
how much a they spent on food. They don't know how much or they don't have
the exact figures. But I do (the price grid in my last posting). Once we
have all the info on their expenses my code will write the letters,
colonizing the right fields in Word etc etc.

They don't know the exact figures, but they sure know the meal plan they
picked (and royally paid!). Based on the price grid I mentioned in my last
posting, I need to come up with a pull down menu in my Expense Database,
with the 10 choices for their meal plan (the grid in my last posting). Once
selected (and since the meal plan prices don't usually change or change only
once a year), I need the corresponding amount (from the price grid in last
posting) to be put in the plan cost field. Yes, each student will have a
somewhat different amount in the plan cost field, because it depends on
individual choices. My main database ("Expenses") has information on each
student and is, of course, organized by their student number.

I will use the same method you advise me to use with the meal grid for other
types of expenses in the same database, such as "accommodation".

Since in my office relies mostly on "starving" volunteer students, with
hardly any computer skills, my databases have to be interactive & foolproof.

So, looking at my price grid, what do you suggest?? I really appreciate
your help!!!!

Mark
Click to expand...

If you want to make your database available for me to download from a
website somewhere I will look at it for you.
(Don't post it to the newsgroup as this is frowned upon)

Regards - Joe
 
Joe, I shrunk & zipped the db to 39 KB. Can I e-mail it to you? If you
want to give me your e-mail in private, my e-mail is (e-mail address removed).

I tried to send it to (e-mail address removed) but it bounced back ("mailbox
unavailable").

Regards,
Mark
 
Back
Top