Access 2007 query into textbox

I'm new to Access so i hope this question isn't too basic.
This is basically a modification to the "sales pipeline" database (comes with Access 2007) since it had similarities to what i wanted to write.  I have an access table called "Employees" where there is a field called "comission_rate", which allows me to log each employees commission rate.
I have a second table called "keystoneopportunities" (to log sales opportunity data) where there is a field called "sales1", which documents who the primary salesman is.
On the form "OpportunityInput", which is the input form for a sales opportunity, i have a combobox for the purpose of selecting a salesman.  The control source is "sales1" mentioned earlier.  The rowsource is:

SELECT [Employees Extended].ID, [Employees Extended].[Employee Name] FROM [Employees Extended] ORDER BY [Employees Extended].[Employee Name];

this sets the items i see in the combobox dropdown.

Adjacent to the above combobox where i can select an employee, i would like to have their commission rate (which will be used in some calculations later).  I'm assuming i would place a textbox for this result to be displayed.  Can anyone help me with how to get commission rate to query into this text box?  Also, if the commission rate/% is displayed in a textbox, will it be treated as a number (and not text) so that i can use it in calculations?
GTC-KTXAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

private sub comboName_afterupdate()

me.text1=cdbl(nz(dlookup("comission_rate","Employees","[ID]= " & me.comboName),0)

end sub
0
 
Rey Obrero (Capricorn1)Commented:
you can use the following in the afterupdate event of the combo

private sub comboName_afterupdate()

me.text1=cdbl(dlookup("comission_rate","Employees","[ID]= " & me.comboName))

end sub
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Add the Commission rate to the combobox row source:

SELECT [Employees Extended].ID, [Employees Extended].[Employee Name],  [Employees Extended].[EmployeeCommissionRate]
FROM [Employees Extended] ORDER BY [Employees Extended].[Employee Name];


Then drop a textbox on the form and set the control source to:

    =YourComboBox.Column(2)

JeffCoachman
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GTC-KTXAuthor Commented:
Thanks for both replies.  i didn't really want the commision rates to show up in the combobox, so i tried to implement the first solution (from Capricorn1).  This seems to work fine except when an employee is chosen that doesn't have a commission value in the table.  I get Run-time error  '94' (Invalid use of null).
Any suggestions?
0
 
Jeffrey CoachmanMIS LiasonCommented:
GTC-KTX,

These are two techniques to do the same thing.

If you set the Column widths property of the combobox, you can hide any column you like.

Jeff
0
 
GTC-KTXAuthor Commented:
Thanks Guys!  Appreciate the quick response.
0
All Courses

From novice to tech pro — start learning today.