Populate Form and subForms based on the user's selection from a combo box
in [Form]
|
From: Frank on 18 Jan 2010 18:42 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? Frank
From: Frank on 20 Jan 2010 12:10 On Jan 18, 4:48 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Mon, 18 Jan 2010 15:42:37 -0800 (PST), Frank <frank.q...(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]- Hide quoted text - > > - Show quoted text - I use MS access2002. Thanks
From: Frank on 20 Jan 2010 12:27 On Jan 20, 9:10 am, Frank <frank.q...(a)gmail.com> wrote: > On Jan 18, 4:48 pm, John W. Vinson > > > > > > <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > > On Mon, 18 Jan 2010 15:42:37 -0800 (PST), Frank <frank.q...(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]- Hide quoted text - > > > - Show quoted text - > > I use MS access2002. Thanks- Hide quoted text - > > - Show quoted text - Hi Jonh, I have created the 'UNBOUND' combo. Would you let me know VA code associated in order to work? Thanks Frank
From: Frank on 20 Jan 2010 17:14 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
From: Frank on 20 Jan 2010 18:44 On Jan 20, 3:05 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Wed, 20 Jan 2010 14:14:56 -0800 (PST), Frank <frank.q...(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]- Hide quoted text - > > - Show quoted text - 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
|
Pages: 1 Prev: Datasheet View Next: Populate Form and subForms based on the user's selection from a combo box |