435,254 Members | 2,365 Online
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

# How do I...Calculate a field based on two other values entered in a form

 P: 7 Hey, I have created an access form, in which I have a drop down with employee names. These are the steps I would like to happen: 1. A user will enter a number in FIELD A 2. The user will choose an employee name from the drop down 3. A number should calculate based on the name chosen and number entered in FIELD B. Each employee should have a number tied to them, (ie Jane = 1.3, John = 5.0). My problem is, I do not know how to a) tie a number to an employee name so that it can be used in a calculation and b) I do not know how to calculate the third field based on the previous two (employee name and entered number). I apologize if this is confusing but I have no other way to explain it (I tried to break it down into steps) I appreciate any help…thanks! Aug 22 '06 #1
14 Replies

 100+ P: 179 How are the numbers assigned to the employees? Are they done in the VB programming? I.e Select Case Me.textbox1 Case 1.3 Me.textbox2="Jane" Case 5.0 Me.textbox2="John" End Select Or is the number a field in a table somewhere? comteck Aug 22 '06 #2

 Expert Mod 10K+ P: 14,534 I assume your drop down or Combo box is currently set to get a list of employees from the employee table. For the purposes of this example I will assume you have set this to EmployeeID, EmployeeName with the EmployeeID field hidden. Add another field to your table, let's call it EmployeeRate (you may already have a field defined). Make the field a Number type with a Decimal format. Then change the value for your combo (drop down) field to show EmployeeID, EmployeeName, EmployeeRate as follows: Me.Combo1.RecordSourceType = "Table/Query" Me.Combo1.RecordSource = "SELECT EmployeeID, EmployeeName, EmployeeRate FROM tblEmployees" Me.Combo1.BoundColumn = 3 ' the value returned from the field will be the EmployeeRate Me.Combo1.ColumnWidths = "0 cm; 5 cm; 0 cm" Then in the After Update event of the Combobox (drop down), set the following code: (It will run after an employee is selected ...) Private Sub Combo0_AfterUpdate() Dim result as Long ' create a number variable to store the result result = Me.Combo1.Value * [Other Number Field] ' do something with the result End Sub Hey, I have created an access form, in which I have a drop down with employee names. These are the steps I would like to happen: 1. A user will enter a number in FIELD A 2. The user will choose an employee name from the drop down 3. A number should calculate based on the name chosen and number entered in FIELD B. Each employee should have a number tied to them, (ie Jane = 1.3, John = 5.0). My problem is, I do not know how to a) tie a number to an employee name so that it can be used in a calculation and b) I do not know how to calculate the third field based on the previous two (employee name and entered number). I apologize if this is confusing but I have no other way to explain it (I tried to break it down into steps) I appreciate any help…thanks! Aug 22 '06 #3

 P: 7 Thanks for the quick replies! I do have a table as mmccarthy suggested, however using the code in the above post, I am getting a compile error on the "RecordSourceType" (it says that the method or data member is not found). I am relatively new to VB, however I do program so I should be able to pick up on it easily. Thanks again! Aug 23 '06 #4

 Expert Mod 10K+ P: 14,534 I only used Combo1 as the name for the example. Check out the name of your drop down/Combo box. Go to the Properties and under the Other tab check the Name. Thanks for the quick replies! I do have a table as mmccarthy suggested, however using the code in the above post, I am getting a compile error on the "RecordSourceType" (it says that the method or data member is not found). I am relatively new to VB, however I do program so I should be able to pick up on it easily. Thanks again! Aug 23 '06 #5

 P: 7 Hi mmccarthy... I did change the name (I beleive mine was Combo0), however it highlights "RecordSourceType" when displaying the error. Am I to change "Table/Query" to my table name, or leave it as is? I have tried both but still to no avail... Thanks Aug 23 '06 #6

 Expert Mod 10K+ P: 14,534 The code is correct, I'm not sure why you are having a problem. When you type Me. do you get a list of options including Combo0 and if you do when you type Me.Combo0. do you get a list of items including RowSouceType. If you don't get Combo0 on the list when you type Me. then you are not writing your code behind the form. If you write the code elsewhere the reference is different. However, if the problem is that you don't get RowSouceType on the list of items when you type Me.Combo0 the problem is with your libraries. If so, go to Tools - References in the VBA Editor Screen and let me know what libraries are ticked. Hi mmccarthy... I did change the name (I beleive mine was Combo0), however it highlights "RecordSourceType" when displaying the error. Am I to change "Table/Query" to my table name, or leave it as is? I have tried both but still to no avail... Thanks Aug 23 '06 #7

 100+ P: 179 What is the name of the underlying table or query? comteck Aug 23 '06 #8

 P: 7 Well now I have no errors but nothing seems to be happening when I make a choice in the drop down. When I type Me. I get Combo0 and then Recordset or RowSourceType are the only similar options, I do not get RecordSource, or RecordSourceType as in your example above. In the libraries the first 20 or so listed are checked off. Thanks! Aug 24 '06 #9

 Expert Mod 10K+ P: 14,534 My sincerest apologies. I just looked at the original code I sent you again. Where it says RecordSourceType and RecordSource. It should say RowSourceType and RowSource. I'm so sorry I checked out the code a few times and didn't see what I had done. Well now I have no errors but nothing seems to be happening when I make a choice in the drop down. When I type Me. I get Combo0 and then Recordset or RowSourceType are the only similar options, I do not get RecordSource, or RecordSourceType as in your example above. In the libraries the first 20 or so listed are checked off. Thanks! Aug 24 '06 #10

 P: 7 Oh, okay! Well that makes more sense. No worries. My only issue now is that I do not think I am placing the code in the right place. Do I put it in BeforeUpdate, or somewhere else? You said to change the value of my combo box to display this, but I don't know where to put it (I tried "value" too, but it doesnt seem to be working). Just another quick question..how do I create a "search" field so that when someone enters "122" it would take them to that record as opposed to changing the data in that field for the record selected. Thanks! Aug 24 '06 #11

 Expert Mod 10K+ P: 14,534 Based on your original spec. 1. A user will enter a number in FIELD A 2. The user will choose an employee name from the drop down 3. A number should calculate based on the name chosen and number entered in FIELD B You want the calculation to happen after the user chooses an employee from the dropdown box (Combo0). Create an After Update event as follows: Private Sub Combo0_AfterUpdate() ' check that a value has been entered into FieldA If Not IsNull(Me.FieldA.Value) Then Me.FieldB.Value = Me.Combo0.Value * Me.FieldA.Value Else ' otherwise Me.Combo0 = Null MsgBox "You must enter a value in Field A first" Me.FieldA.SetFocus End If End Sub To set your combo box correctly put the following code in the On Load event of the form. Alternatively, you could hard code the combo box by going into its properites and resetting them as described below: Private Sub Form_Load() Me.Combo0.RowSourceType = "Table/Query" Me.Combo0.RowSource = "SELECT EmployeeID, EmployeeName, EmployeeRate FROM tblEmployees" Me.Combo0.BoundColumn = 3 ' the value returned from the field will be the EmployeeRate Me.Combo0.ColumnWidths = "0 cm; 5 cm; 0 cm" End Sub To create a search facility put a new textbox on the form and call it 'Search' for convenience. For the purposes of this example I am going to assume you are searching for an employee record using the employeeID. Create an After Update event on the textbox something like this: Private Sub Search_AfterUpdate() Dim stLinkCriteria As String DoCmd.EmployeeID.SetFocus ' set focus to the field being searched DoCmd.FindRecord Me.Search.Value, acEntire, , acSearchAll, , acCurrent End Sub This assumes that the Record Source of the form is the Employee table or query. If this doesn't work let me know. Oh, okay! Well that makes more sense. No worries. My only issue now is that I do not think I am placing the code in the right place. Do I put it in BeforeUpdate, or somewhere else? You said to change the value of my combo box to display this, but I don't know where to put it (I tried "value" too, but it doesnt seem to be working). Just another quick question..how do I create a "search" field so that when someone enters "122" it would take them to that record as opposed to changing the data in that field for the record selected. Thanks! Aug 24 '06 #12

 P: 7 Yes! The calculation works!! Thank you so much.. However the search box doesn't work, it stops me on the "DoCmd.EmployeeID" part... Aug 24 '06 #13

 Expert Mod 10K+ P: 14,534 Sorry, don't know what's wrong with me lately. Working too hard I guess. Line should read. Me.EmployeeID.SetFocus ' set focus to the field being searched Yes! The calculation works!! Thank you so much.. However the search box doesn't work, it stops me on the "DoCmd.EmployeeID" part... Aug 24 '06 #14

 P: 7 Hey, Sorry for the delay...it's now giving me an error on "SetFocus"..the only option that comes up is "Value". Thanks Aug 31 '06 #15