Populate textbox values after a combo box selection is made

[lhtan]

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[Piet Linden]

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?

Regards

if the data comes from the same tables as the recordsource for the
combobox, then you just include them in the recordsource. Then you
set the listwidth to 0 for every column you want to hide, so it might
look like this if you wanted to get data for 5 fields but show only
the second: 0;1;0;0;0
One thing to remember is that columns in Access are zero-based (so
subtract one from the ordinal position). Then set the control source
for the text boxes to =Me.MyCombo.Column(n) where n=the zero-based
position of the column you want to grab the data for.

 

[Klatuu]

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub

 

[lhtan]

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[Klatuu]

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[lhtan]

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[Klatuu]

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[lhtan]

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[Klatuu]

It probably means that EmployeeSubFormControl is the name of the form you are
using, not the name of the subform control on the main form.

The syntax breaks down like this:
With Me.EmployeeSubFormControl.Form.RecordsetClone

Me - Reference to the main form. Me is a "short cut" that referrs to the
form your code is in. (The form's code module).

EmployeeSubFormControl - This is the name of the subform control on the main
form, not the name of a form object.

Form - This is a reference to the form identified in the subform control's
Source Object property.

RecordsetClone - A copy of the recordset of the form being used as the
subform object.

So the actual reference chain is:

Forms Collection -> Form -> Subform Control -> Subform Control Source Object
-> Clone of the Recordset

--
Dave Hargis, Microsoft Access MVP

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[lhtan]

Thank you for the explanation.

I tried the method again but ran into another compilation error. It says
Type mismatch at the statement:
..FindFirst "[EmployeeID]=" & Me.cboName

I supposed it's because [EmployeeID] is an alphanumeric type while Name is a
String type. So instead of using [EmployeeID] in the subform, I changed it to
[Name]. Upon selecting a Name from the combo box, only the employee name
appear in the textbox of the subform. The rest of the employee details are
still not populated.

Regards

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[lhtan]

My latest progress for this problem is that I managed to get the employee
details populated into the textboxes but there is always a pop-up error after
I selected the Employee Name from the combo box.

It will open up the VB editor with the compile error. The message is:
The Method or data member not found.

....and it highlighted at "subFormEmployee" in the following statement:
With Me.subFormEmployee.Form.RecordsetClone

I'm sure that the name of the subform is correct but maybe there's more to
the problem. I'm not sure.

Regards

It probably means that EmployeeSubFormControl is the name of the form you are
using, not the name of the subform control on the main form.

The syntax breaks down like this:
With Me.EmployeeSubFormControl.Form.RecordsetClone

Me - Reference to the main form. Me is a "short cut" that referrs to the
form your code is in. (The form's code module).

EmployeeSubFormControl - This is the name of the subform control on the main
form, not the name of a form object.

Form - This is a reference to the form identified in the subform control's
Source Object property.

RecordsetClone - A copy of the recordset of the form being used as the
subform object.

So the actual reference chain is:

Forms Collection -> Form -> Subform Control -> Subform Control Source Object
-> Clone of the Recordset

--
Dave Hargis, Microsoft Access MVP

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[Klatuu]

What is the row source query for cboName?
Which is the Bound Column?
--
Dave Hargis, Microsoft Access MVP

Thank you for the explanation.

I tried the method again but ran into another compilation error. It says
Type mismatch at the statement:
.FindFirst "[EmployeeID]=" & Me.cboName

I supposed it's because [EmployeeID] is an alphanumeric type while Name is a
String type. So instead of using [EmployeeID] in the subform, I changed it to
[Name]. Upon selecting a Name from the combo box, only the employee name
appear in the textbox of the subform. The rest of the employee details are
still not populated.

Regards

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[lhtan]

row source query: qryEmployeeName

SELECT EmployeeID, Name, Company, office no, mobile no, email
FROM Employee
WHERE
(((Employee.Company)=IIf(IsNull(Forms!Legal_Masterlist!Company),[Company],Forms!Legal_Masterlist!Company)))
ORDER BY Employee.[Name];

Bound column of Employee Name combo box is [Name] from the
[Legal_Masterlist] Query which is the recordsource for the main form.

What is the row source query for cboName?
Which is the Bound Column?
--
Dave Hargis, Microsoft Access MVP

Thank you for the explanation.

I tried the method again but ran into another compilation error. It says
Type mismatch at the statement:
.FindFirst "[EmployeeID]=" & Me.cboName

I supposed it's because [EmployeeID] is an alphanumeric type while Name is a
String type. So instead of using [EmployeeID] in the subform, I changed it to
[Name]. Upon selecting a Name from the combo box, only the employee name
appear in the textbox of the subform. The rest of the employee details are
still not populated.

Regards

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

:

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[Klatuu]

So are you saying the Bound Column property of cboName is 2?
If so, your find first needs to have the correct syntax for a text field:

..FindFirst "[EmployeeID]=""" & Me.cboName & """"

Also, here is a bit shorter and easier to read version of your query. Note
I have added brackets to those names with evil spaces in them:

SELECT EmployeeID, Name, Company, [office no], [mobile no], email FROM
Employee WHERE Employee.Company = Nz(Forms!Legal_Masterlist!Company,
[Company]) ORDER BY Employee.[Name];

--
Dave Hargis, Microsoft Access MVP

row source query: qryEmployeeName

SELECT EmployeeID, Name, Company, office no, mobile no, email
FROM Employee
WHERE
(((Employee.Company)=IIf(IsNull(Forms!Legal_Masterlist!Company),[Company],Forms!Legal_Masterlist!Company)))
ORDER BY Employee.[Name];

Bound column of Employee Name combo box is [Name] from the
[Legal_Masterlist] Query which is the recordsource for the main form.

What is the row source query for cboName?
Which is the Bound Column?
--
Dave Hargis, Microsoft Access MVP

Thank you for the explanation.

I tried the method again but ran into another compilation error. It says
Type mismatch at the statement:
.FindFirst "[EmployeeID]=" & Me.cboName

I supposed it's because [EmployeeID] is an alphanumeric type while Name is a
String type. So instead of using [EmployeeID] in the subform, I changed it to
[Name]. Upon selecting a Name from the combo box, only the employee name
appear in the textbox of the subform. The rest of the employee details are
still not populated.

Regards

:

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

:

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards

 

[lhtan]

Yes, the Bound Column propery for cboName is 2.

However, I couldn't use the query that you had written below cos' the
Legal_Masterlist Table does not have a Company field. This is because
"Company" field is in a "Company" Table (Parent) linked to the "Employee"
Table (child). Each Employee has an EmployeeID that is in turned linked to
the Legal_Masterlist.

Seems like I have to think of an alternative method as the recordsource for
the main form (Legal_Masterlist) is on the many-side of a relationship while
the subform recordsource (Employee) is on the one-side.

Are there other ways to code the form without using a subform ?

Each Legal_Masterlist record has an employee which is hired by a company.
Each Company can have one or more employees.
Every time when user navigates through the main form, the GUI will display
the employee and other legal details.

The relationship for this database is as follows:

Table/Query Type Field 1 Field 2
Field 3
Legal_Masterlist Table EmployeeID A (Pri Key)
B
Company Table CompanyName (Pri Key)
Employee Table EmployeeID (Pri Key) Name
CompanyName

Regards

So are you saying the Bound Column property of cboName is 2?
If so, your find first needs to have the correct syntax for a text field:

.FindFirst "[EmployeeID]=""" & Me.cboName & """"

Also, here is a bit shorter and easier to read version of your query. Note
I have added brackets to those names with evil spaces in them:

SELECT EmployeeID, Name, Company, [office no], [mobile no], email FROM
Employee WHERE Employee.Company = Nz(Forms!Legal_Masterlist!Company,
[Company]) ORDER BY Employee.[Name];

--
Dave Hargis, Microsoft Access MVP

row source query: qryEmployeeName

SELECT EmployeeID, Name, Company, office no, mobile no, email
FROM Employee
WHERE
(((Employee.Company)=IIf(IsNull(Forms!Legal_Masterlist!Company),[Company],Forms!Legal_Masterlist!Company)))
ORDER BY Employee.[Name];

Bound column of Employee Name combo box is [Name] from the
[Legal_Masterlist] Query which is the recordsource for the main form.

What is the row source query for cboName?
Which is the Bound Column?
--
Dave Hargis, Microsoft Access MVP


:

Thank you for the explanation.

I tried the method again but ran into another compilation error. It says
Type mismatch at the statement:
.FindFirst "[EmployeeID]=" & Me.cboName

I supposed it's because [EmployeeID] is an alphanumeric type while Name is a
String type. So instead of using [EmployeeID] in the subform, I changed it to
[Name]. Upon selecting a Name from the combo box, only the employee name
appear in the textbox of the subform. The rest of the employee details are
still not populated.

Regards

:

I tried this method but it ran into a compile error:

"Method or data member not found"

BTW, why do we have to write "Form" in this statement ?
"With Me.EmployeeSubFormControl.Form.RecordsetClone"

The error was highlighted at the "EmployeeSubFormControl". I thought Form
should be at a higher hierarchy than the EmployeeSubFormControl. I've
substituted the latter to the name of the control, ie. a textbox name.

Regards

:

Actually, the correct way to do this would be to use a subform to show the
employee information. Use the After Update event of the combo box populate
the subform. A way to do that would be to use the employee table primary key
and the employee name in the combo, and position the subform current record
based on the selection in the combo:

With Me.EmployeeSubFormControl.Form.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboEmpSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP


:

The recordsource of the main form is bound to the "Legal Masterlist". Page 1
of the form displays details in this recordsource.

On Page 2 of the form, there is a "Company" (from Company Table) and
"Employee Name" (from Employee Table) combo boxes. There is a one-to-many
relationship between "Company" and "Employee" Tables. When I select a company
from the "Company" combo box, only Employee names from that company will
appear in the "Employee Name" combo box. I've managed to achieve that.

Now, I want to display only a particular employee's details like office no,
email address, etc upon selecting an Employee Name from the combo box. There
is a one-to-many relationship between the "Employee" (one) and "Legal
Masterlist" (many) Tables using the "EmployeeID" which is a Primary key in
the former.

The purpose of this is to avoid repetition entry of Employee details when a
new record is created for the "Legal Masterlist". However, for viewing of
records, these fields should still display the actual data.

Hope it doesn't sound too complicated

Regards

:

Are these text boxes you want to populate bound? If so, they will have to
have some relationship to the companies' employees. Or, it may be you need
to create a subform bound to the table that has the employee records. I
don't know enough about your data or what you are doing to answer your
question. Perhaps some more detailed description of the data you are working
with and where it resides would be helpful.
--
Dave Hargis, Microsoft Access MVP


:

But the problem I have here could be more complicated cos' the recordsource
is not the Employee table.

What happens is that my form has 3 pages and this is just page 2 that I'm
working on. The recordsource is tied to a "Legal Masterlist".

In page 2 of this form, I have a "Company" combo box that upon selecting a
value, will display all the Employee names in the "Name" combo box. This has
already been achieved. But now, I wanna select an Employee Name from the
combo box which will then populate all the details into the textboxes.

Not sure if this can be done. But I can't set the recordsource to be the
"Employee" Table cos' it has to be the "Legal Masterlist".

Regards

:

Are you wanting to search for existing employees?
Is the combo box a bound control?

The best way do search for existing records using a combo box is to use an
unbound combo box. If you use a bound combo, what happens when you are are
on Sam Smith and want to search for Judy Jones, you will end up changing the
name if the record, so then you would have Judy Jones name with Sam Smith's
data.

I normally put the search combo in the report header so it is not in the tab
order and is out of the way until you need it. Then you can use the After
Update event to move to the record you want. Assuming you have an autonumber
primary key field for the employee table, and you have separate fields for
first and last name (which you should), you would need a row source for the
combo something like:

SELECT dbo_Employee.EmployeeID, [EmployeeFirstName] & " " &
[EmployeeLastName] AS FullName, dbo_Employee.EmployeeNumber FROM dbo_Employee
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName];

Then to find the employee:

Private Sub cboSearch_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.cboSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.txtEmpFirstName.SetFocus
End Sub


--
Dave Hargis, Microsoft Access MVP


:

I have a form with one combo box for Employee Name and a few textboxes for
Employee details.

Upon a combo box selection, I want the details to be populated into the
textboxes.

How can I do so ?


Regards