Populate Form and subForms based on the user's selection from acombo box

Hi,

I would like to create a MS access form that have form that shows
employee info (such as employee name, phone, manage, etc) and a
subform that have employee's traning records. The datasource of the
Form is from table Employee and the datasource of subform is from
table traming. I would like to have a combo box on the top of the Form
for user to select Employee name. When the employee name is selected
from combo box, the employee info can show in the form and the
employee training infor can show in subform.

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.

On Thu, 14 Jan 2010 13:40:04 -0800 (PST), Frank 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]

On Jan 14, 5:42*pm, John W. Vinson
wrote:
On Thu, 14 Jan 2010 13:40:04 -0800 (PST), Frank 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

On Mon, 18 Jan 2010 15:42:37 -0800 (PST), Frank wrote:

On Jan 14, 5:42*pm, John W. Vinson
wrote:
On Thu, 14 Jan 2010 13:40:04 -0800 (PST), Frank 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]

On Jan 18, 4:48*pm, John W. Vinson
wrote:
On Mon, 18 Jan 2010 15:42:37 -0800 (PST), Frank wrote:
On Jan 14, 5:42*pm, John W. Vinson
wrote:
On Thu, 14 Jan 2010 13:40:04 -0800 (PST), Frank 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

On Jan 20, 9:10*am, Frank wrote:
On Jan 18, 4:48*pm, John W. Vinson





wrote:
On Mon, 18 Jan 2010 15:42:37 -0800 (PST), Frank wrote:
On Jan 14, 5:42*pm, John W. Vinson
wrote:
On Thu, 14 Jan 2010 13:40:04 -0800 (PST), Frank 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

On Wed, 20 Jan 2010 09:27:45 -0800 (PST), Frank 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]

On Jan 20, 9:53*am, John W. Vinson
wrote:
On Wed, 20 Jan 2010 09:27:45 -0800 (PST), Frank 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

On Wed, 20 Jan 2010 14:14:56 -0800 (PST), Frank wrote:

On Jan 20, 9:53*am, John W. Vinson
wrote:
On Wed, 20 Jan 2010 09:27:45 -0800 (PST), Frank 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]

On Jan 20, 3:05*pm, John W. Vinson
wrote:
On Wed, 20 Jan 2010 14:14:56 -0800 (PST), Frank wrote:
On Jan 20, 9:53*am, John W. Vinson
wrote:
On Wed, 20 Jan 2010 09:27:45 -0800 (PST), Frank 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