textbox value to show a column

[umair434]

Hi,

i am using a simple public function to calculate the cost which is dependent on the inventory week. How do I design so that when the user enters a week number in a form. It uses only that column to calculate the cost.

Public Function PPC(week1 As Variant, CASE_CUBE As Variant)
PPC = CASE_CUBE * week1
End Function

i want to make the "week1" above a variable which is dependent on a value from the user in a form. Any ideas please?

 

[pbaldy]

You can refer to the form:

PPC = CASE_CUBE * Forms!FormName.TextboxName

 

[umair434]

thanks! But how do I declare the variables in the function then.

Alternatively, I was thinking to use dlookup. So when user enter a week number.

I make it equal to a string which equals one of the columns. Now that means the first part of the dlookup (the column name) becomes a variable:

DLookup([strweek], "[tblOutBoundData]", "[PROD#] = forms!frmEnterSku.combo13.value")

where strweek could be week1, week2, week3 etc. but this gives me an error. any ways to get pass this?

thanks alot!

 

[pbaldy]

What variables? You don't need a variable to get the value off the form.

Proper syntax of DLookup:

http://www.mvps.org/access/general/gen0018.htm

 

[pbaldy]

Or if you mean the week variable you would leave it out and just pass the other:

Public Function PPC(CASE_CUBE As Variant)