Calculation on hidden amounts on a combo box (1 Viewer)

munkeyroot

Registered User.
Local time
Today, 07:01
Joined
Jan 13, 2011
Messages
76
Hi Everyone

hope you can help i am a little stuck on a project
i have a text box called percent - i don't want to use the amount as a customer may see this so i have a table with, and created a query for it.
Grade | Percent
A = 10
B = 15
C = 20
and made the Percent text box a combo box. and set the row source to the query.

on another text box i have the below code that i need to multiply the percent by.
=Nz(([CostofInstallation]*[Percent]),0)

how do i the code to choose the percent amount from the grade i enter.

hope this make sense

cheers
Munk
 

isladogs

MVP / VIP
Local time
Today, 07:01
Joined
Jan 14, 2017
Messages
18,186
Assuming that percent is the bound column, you could use Forms!FormName.Comboboxname.
Or set a variable/tempvar equal to the combo value then reference that
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:01
Joined
Feb 28, 2001
Messages
27,001
Adding to Colin's comment, which focused on the bound column...

Forms!FormName.Comboboxname.Column(n) is the content of the nth column if it happens that the hidden value isn't also the bound column. You CAN hide the bound column in a combo and it still works just fine.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:01
Joined
May 21, 2018
Messages
8,463
Code:
is the content of the nth column
Actually would say the (n-1)th column since this is a zero based index. The first column would have an index of 0.
 

isladogs

MVP / VIP
Local time
Today, 07:01
Joined
Jan 14, 2017
Messages
18,186
Unless my memory is playing tricks, you can't refer to .Column(1) etc in query criteria.
So if you do need anything other than the bound column in query criteria, you would need to set a variable or tempvar equal to that value
 

Cronk

Registered User.
Local time
Today, 18:01
Joined
Jul 4, 2013
Messages
2,770
you would need to set a variable or tempvar equal to that value


...or a function returning the value of the required column.


Colin, I think the OP has the query as the row source of the combo. The required percentage column is being used for the text box


ie me.textbox =Nz(([CostofInstallation] * me.combo.column(1)),0)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:01
Joined
Feb 28, 2001
Messages
27,001
True that, MajP, on the fact that column is zero-based. Most things in Access ARE 0 to n-1, but some are not. Sometimes I forget which. Then I try it, find it doesn't work, and smack my forehead in a true V8 moment.
 

isladogs

MVP / VIP
Local time
Today, 07:01
Joined
Jan 14, 2017
Messages
18,186
..or a function returning the value of the required column.

Agreed - in fact I regularly use a function for that purpose - never use tempvars myself

Colin, I think the OP has the query as the row source of the combo. The required percentage column is being used for the text box

ie me.textbox =Nz(([CostofInstallation] * me.combo.column(1)),0)

Yes I saw that but picked up on the previous sentence
and made the Percent text box a combo box. and set the row source to the query.
Perhaps I misread what that meant but that was the reason I referred solely to the bound column to try & avoid any issue with references to Column(1) etc

Here, as is so often the case, several replies can occur before the OP returns to clarify meanings
 

munkeyroot

Registered User.
Local time
Today, 07:01
Joined
Jan 13, 2011
Messages
76
Hi guys apologies i have just got back to you all thank you soo much for your input. i have attached a screenshot hope this will help.

yes so on the Revenue from Rental per annum Text box "TYRRetalRev_3YC".
i am using the control source to populate the maths from the combo box "TYRPercentage_PA" depending on which once is selected.

i hope this helps

cheers again munk
 

Attachments

  • dropdown_issue.jpg
    dropdown_issue.jpg
    105.4 KB · Views: 52

isladogs

MVP / VIP
Local time
Today, 07:01
Joined
Jan 14, 2017
Messages
18,186
Screenshot isn't that helpful IMO
Suggest you read through & then try out previous suggestions & identify which is appropriate for your situation
 

munkeyroot

Registered User.
Local time
Today, 07:01
Joined
Jan 13, 2011
Messages
76
Ahhh i think i have cracked it...well maybe a bit clunky but it works
i have my qry set on Grade | Percent. so because the 1st column is Grade as in A B C etc the code in the control source was picking up the letter .....so i switched them in the qry to be Percent | Grade so the C.Box shows the number. .. on the ComboBox made it a 2 column, hidden the Percent column by setting to 0cm and the grade to 2cm.

so thoughts guys?

cheers munk
 

isladogs

MVP / VIP
Local time
Today, 07:01
Joined
Jan 14, 2017
Messages
18,186
Or you could leave the combobox order as it was originally & set the bound column to column 2 (Percent).
Both methods should work as long as the values are unique
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:01
Joined
Sep 12, 2006
Messages
15,614
Just one further observation. Combo box values are always strings, and you may need to use a conversion routine to change a text value into a numeric value. Occasionally I have found that I had to specifically change a text value into a numeric, although I am pretty sure that that sometimes it's automatic also. If you appear to get an issue with the data, you may need to do some conversion.
 

Users who are viewing this thread

Top Bottom