populate textbox with calculated value taking values from database and form fields. (1 Viewer)

[pinky]

Hi,
I am new to ms access. Trying to build a form with lost of functionality.

I have to calculate a value and display in text box. Its calculation is something like this.

"[amount+Number(additional_amount)]*3"

where Income is queried from database table Income_table. It has columns year, amount, additional_amount. According to the year, its needs to pick the amount and additional amount.(Select amount, additional_amount from income where year = @year(provided in form Yeartxt field).

Get the Number from form Numbertxt field.

Substitute amount, additional_amount,number in above formula and display it in calculatedtxt filed in form.

Please can anyone help me with a event procedure or macro or module for this scenario.

Thanks,
Pinky

 

[jzwp22]

Is the form bound to the income table or is the form unbound or bound to another table?

Assuming that the form is not bound to the income table, then you will need to use the DLookup() function to get each value you need from the income table.

Also the word "year" is a reserved word in Access so I strongly suggest that you change your field name to something else.

The DLookup function would look something like this assuming that your year field is numeric. You have to substitute your actual field, table, form and form control names etc.

DLookup("amount","Income_table", "year=" & forms!yourformname!yeartext)

You will need a second Dlookup function for the additional amount.

 

[pinky]

Thank you very much for replying.
I have taken Text203 and in it control source as dlookup expression. It is working ie I am retrieving database value according to year entered in Yeartxt. But when I type 2008 in Yeartxt then text203 is not populated by itself. When I switch from formview to design view and back to form view then i could see the values that i expect. It is not triggering by itself and populating.
What may be the reason behind this?

Thanks,
Pinky

 

[jzwp22]

It should populate once control moves from the year textbox to another textbox control on the form. Make sure to save the form and exit design view. Conduct your test in form view.

 

[pinky]

I came upto the point where I could generate values in text box as required. Now I have yeartxt, amounttxt, addtxt which are retrived from databas and displayed in Calculatedtxt. I have comboperiod which has drop down annual, monthly. I have to link comboperiod to calculatedtxt such as when annual is selected in comboperiod, the value in calculatedtxt should not be changed and when monthly is selected, the value in calculatedtxt should be divided by 12.

Can you guide me with this?
Thanks,
Pinky.

 

[jzwp22]

You would just continue the expression

=(first part of expression) / IIF(comboboxname="Annual",1,12)

 

[pinky]

Thank you very much! But small enhancement.

This is limited to two arguments. But I need to calculate weekly and bi weekly also. How to approach if there are more than two arguments.

Thanks,
Pinky

 

[jzwp22]

The other option is to set up the factor as the bound field in the combo box

factor|Term
1|Annual
12|Monthly
26|Bi-weekly
52|Weekly

=(first part of expression) / comboboxname