Populate Form and subForms based on the user's selection from a combo box
in [Form]
|
Prev: Populate Form and subForms based on the user's selection from a combo box
Next: Extract a 3 digit day from a date field into its own field.
From: John W. Vinson on 18 Jan 2010 19:48 On Mon, 18 Jan 2010 15:42:37 -0800 (PST), Frank <frank.q.gg(a)gmail.com> wrote: >On Jan 14, 5:42�pm, John W. Vinson ><jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: >> On Thu, 14 Jan 2010 13:40:04 -0800 (PST), Frank <frank.q...(a)gmail.com> wrote: >> >I have created the Form and subform using Form and subform relation, >> >but when the combox was added to select employee name, the application >> >can not work anymore. Any suggestion is appreciated. >> >> Delete the combo box, open the form in design view, and click the magic wand >> icon on the toolbar so that it's selected. Then use the Combo Box icon. The >> combo box wizard will open; one of the options is "use this combo box to find >> a record". Choose that option. >> >> Basically you need an *UNBOUND* combo box with some VBA code (or, in 2007, a >> Macro) which will find the desired record. The wizard will set this up for >> you. >> >> If it doesn't work, post back with a description of what you did and with the >> AfterUpdate event of the combo box. >> -- >> >> � � � � � � �John W. Vinson [MVP] > >Thank you, John! I created a form, then used the subform/subreport >toolbar to created three subforms. Form and suform work well. Then I >created 'UNBOUND' combo box. But I can't find the magic wand icon in >the toolbar. >Where is it? It's in the tool*box* - I have no idea where it is in your installation, it can be moved. I'm also not sure if it's handled the same in 2007 (or what version you have). -- John W. Vinson [MVP]
From: John W. Vinson on 20 Jan 2010 12:53 On Wed, 20 Jan 2010 09:27:45 -0800 (PST), Frank <frank.q.gg(a)gmail.com> wrote: >Hi Jonh, > >I have created the 'UNBOUND' combo. Would you let me know VA code >associated in order to work? > >Thanks Not without knowing more about your database: if you'll post the SQL view of the Form's Recordsource and the combo box's RowSource I might be able to help. -- John W. Vinson [MVP]
From: John W. Vinson on 20 Jan 2010 18:05 On Wed, 20 Jan 2010 14:14:56 -0800 (PST), Frank <frank.q.gg(a)gmail.com> wrote: >On Jan 20, 9:53�am, John W. Vinson ><jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: >> On Wed, 20 Jan 2010 09:27:45 -0800 (PST), Frank <frank.q...(a)gmail.com> wrote: >> >Hi Jonh, >> >> >I have created the 'UNBOUND' combo. Would you let me know VA code >> >associated in order to work? >> >> >Thanks >> >> Not without knowing more about your database: if you'll post the SQL view of >> the Form's Recordsource and the combo box's RowSource I might be able to help. >> >> -- >> >> � � � � � � �John W. Vinson [MVP] > >The Form's Recordsource is Employee (a table). The combo box's >RowSource is SELECT Employee.EMPLOYEE FROM Employee; >Basically, I have Form of Employee and subform Training and subform >Audit. Pls let me know if you need additional info. > >Thanks Jonh for your help! > >Frank Do you have an EmployeeID? Or is the employee table just one field with "G. Gordon Liddy" in the EMPLOYEE field? If so, what will you do if you have an employee change their name (say by marriage), or get two employees who happen to have the same name? I once worked at a university where there was a Professor John W. Vinson... he got a much bigger salary than I did at the time. That said, you can put code in the Combo's AfterUpdate event such as: Private Sub cboFindEmployee_AfterUpdate() Dim rs As DAO.Recordset Set rs = Me.RecordsetClone rs.FindFirst "Employee = """ & Me!cboFindEmployee & """" If rs.NoMatch Then MsgBox "Something wrong, employee not found!" Else Me.Bookmark = rs.Bookmark End If Set rs = Nothing End Sub -- John W. Vinson [MVP]
From: John W. Vinson on 20 Jan 2010 19:50
On Wed, 20 Jan 2010 15:44:25 -0800 (PST), Frank <frank.q.gg(a)gmail.com> wrote: >I created the VB code similar to the code you suggested in combo box >(see code below), But it does not work. When an employee name is >selected from combox box, the form and subforms are not refreshed. > >Frank > > >Private Sub Select_Employee_AfterUpdate() > >Dim rs As DAO.Recordset >Set rs = Me.RecordsetClone >rs.FindFirst "Employee = """ & Me!Select_Employee & """" >If rs.NoMatch Then > MsgBox "Something wrong, employee not found!" >Else > Me.Bookmark = rs.Bookmark >End If >Set rs = Nothing >End Sub > > > Is Employee a Lookup field? If so, it's stored in the table as a numeric ID. What is the datatype of EMPLOYEE in the table? What's on its lookup tab? Do you in fact have just a full name in the field (that's unwise, you should have separate fields for last and first names)? What (if anything) is the Primary Key of the employee table? -- John W. Vinson [MVP] |