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

[Frank]

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.

 

[John W. Vinson]

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.

 

[Frank]

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 withthe
AfterUpdate event of the combo box.

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

 

[John W. Vinson]

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).

 

[Frank]

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

 

[Frank]

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

 

[John W. Vinson]

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.

 

[Frank]

Not without knowing more about your database: if you'll post the SQL viewof
the Form's Recordsource and the combo box's RowSource I might be able to help.

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

 

[John W. Vinson]

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

 

[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

 

[John W. Vinson]

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?