If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combo box to fill text boxes..
A form has one combo box and 3 text boxes. This form has the Record
Source set to a table containing employee scoring information. The combo box contains employee ID numbers. Based on the selection from the combo box the three text boxes will display the employee name, employee area, and an evaluation score. Currently the combo box has a Row Source: SELECT ScoresTbl.EmpNum, ScoresTbl.EmpName, ScoresTbl.EmpArea, ScoresTbl.OverAllEval FROM ScoresTbl ORDER BY ScoresTbl.EmpNum; Once the EmpNum is selected or an AfterUpdate event on the combo box, then the appropriate text boxes are filled with the matching information. Unfortunately, this somehow modifies the ScoresTbl. How does the ScoresTbl get modified? Is there a way to perform this task by using a query? I tried setting the Record Source to a query that contained the four needed fields from the ScoreTbl, but was unsure how to get the information back to the text boxes in the form. Any help is greatly appreciated. Thank you, George |
#2
|
|||
|
|||
Combo box to fill text boxes..
George -
If you are using the combo box to select a value, make sure it is not bound to the recordsource of the form. If it is bound to the form, then you will over-write the current form record with what you select. Also, if you are populating the text boxes from the combo box, then these values will also update the record on the form. If you are using a combo box to select a record to display, then you don't want the combo box to be bound. The AfterUpdate event should then add a filter to the current form to restrict which records are being shown, rather than updating the data in the field. -- Daryl S "George" wrote: A form has one combo box and 3 text boxes. This form has the Record Source set to a table containing employee scoring information. The combo box contains employee ID numbers. Based on the selection from the combo box the three text boxes will display the employee name, employee area, and an evaluation score. Currently the combo box has a Row Source: SELECT ScoresTbl.EmpNum, ScoresTbl.EmpName, ScoresTbl.EmpArea, ScoresTbl.OverAllEval FROM ScoresTbl ORDER BY ScoresTbl.EmpNum; Once the EmpNum is selected or an AfterUpdate event on the combo box, then the appropriate text boxes are filled with the matching information. Unfortunately, this somehow modifies the ScoresTbl. How does the ScoresTbl get modified? Is there a way to perform this task by using a query? I tried setting the Record Source to a query that contained the four needed fields from the ScoreTbl, but was unsure how to get the information back to the text boxes in the form. Any help is greatly appreciated. Thank you, George . |
#3
|
|||
|
|||
Combo box to fill text boxes..
Daryl S wrote:
George - If you are using the combo box to select a value, make sure it is not bound to the recordsource of the form. If it is bound to the form, then you will over-write the current form record with what you select. Also, if you are populating the text boxes from the combo box, then these values will also update the record on the form. If you are using a combo box to select a record to display, then you don't want the combo box to be bound. The AfterUpdate event should then add a filter to the current form to restrict which records are being shown, rather than updating the data in the field. I just finished the "Access 97 Bible", what other material would be recommended for more advanced reference? Thenks |
#4
|
|||
|
|||
Combo box to fill text boxes..
On Fri, 26 Feb 2010 10:57:25 -0500, George
wrote: I just finished the "Access 97 Bible", what other material would be recommended for more advanced reference? Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Combo box to fill text boxes..
George:
There are two ways you can handle this: 1 Using a bound form. 1.1 Using a bound form as you are at the moment make the combo box unbound by deleting its ControlSource property, which is presumably EmpNum at present, which is why the data in the underlying table is changing. 1.2 Change the Name property of the combo box if necessary so its not the same name as the field, e.g. to cboEmployee 1.3 The ControlSource properties of the three text boxes should be EmpName, EmpArea and OverAllEval respectively. 1.4 In the AfterUpdate event procedure of the combo box put the following code to navigate to the selected employee: Dim rst As Object Dim ctrl As Control Set rst = Me.Recordset.Clone Set ctrl = Me.ActiveControl With rst .FindFirst "EmpNum = " & ctrl If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With This assumes EmpNum is a number data type, If it’s a text data type amend the code to: .FindFirst "EmpNum = """ & ctrl & """" If you are unfamiliar with entering code in event procedures you do this by selecting the control in form design view and opening its properties sheet if its not already open. Then select the After Update event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the lines of code between these two existing lines. 1.5 In the Form's AfterUpdate event procedure put: Me.cboEmployee.Requery Me.cboEmployee = Me.EmpNum This will update the combo box's list if you change any of the data in the text boxes or add a new record via the form. 1.6 An added refinement is to put the following in the form's Current event procedu Me.cboEmployee = Me.EmpNum This will keep the combo box in sync with the form's current record if you navigate to another record via the navigation bar etc. 2 Using an unbound form 2.1 Delete the form's RecordSource property so its unbound. 2.2 Keep the combo box exactly as above. 2.3 Change the ControlSource properties of the three text boxes to: =cboEmployee.Column(1) =cboEmployee.Column(2) =cboEmployee.Column(3) The Column property is zero-based, so Column(1) is the second column of the combo box's RowSource and so on. The big difference between these two approaches is that in the case of the first, using a bound form you can edit the data in the text boxes or even add a new record, and therefore update the data in the underlying table, whereas in the second you can only view the data and not edit it or add a new record. Ken Sheridan Stafford, England George wrote: A form has one combo box and 3 text boxes. This form has the Record Source set to a table containing employee scoring information. The combo box contains employee ID numbers. Based on the selection from the combo box the three text boxes will display the employee name, employee area, and an evaluation score. Currently the combo box has a Row Source: SELECT ScoresTbl.EmpNum, ScoresTbl.EmpName, ScoresTbl.EmpArea, ScoresTbl.OverAllEval FROM ScoresTbl ORDER BY ScoresTbl.EmpNum; Once the EmpNum is selected or an AfterUpdate event on the combo box, then the appropriate text boxes are filled with the matching information. Unfortunately, this somehow modifies the ScoresTbl. How does the ScoresTbl get modified? Is there a way to perform this task by using a query? I tried setting the Record Source to a query that contained the four needed fields from the ScoreTbl, but was unsure how to get the information back to the text boxes in the form. Any help is greatly appreciated. Thank you, George -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
Thread Tools | |
Display Modes | |
|
|