Combo Box - Haven't got a clue

[Guest]

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Guest]

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards

 

[Guest]

Thanks Pete. I think I understand that the name doesn't have to be on the
form for the user since it's connected in the tables. I'm trying to list
the name on the form, because I think it will help the user that is entering
the data since the employeeID isn't known typically and won't be on the paper
form that she's entering from. If I hide the key (employeeID) it only shows
the last name. I tried entering the text in the Row Source and it didn't
change the outcome.

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Guest]

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display the
users full name as "Schmidt, Peter", although the underlying table stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK

Thanks Pete. I think I understand that the name doesn't have to be on the
form for the user since it's connected in the tables. I'm trying to list
the name on the form, because I think it will help the user that is entering
the data since the employeeID isn't known typically and won't be on the paper
form that she's entering from. If I hide the key (employeeID) it only shows
the last name. I tried entering the text in the Row Source and it didn't
change the outcome.

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Guest]

Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display the
users full name as "Schmidt, Peter", although the underlying table stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK

Thanks Pete. I think I understand that the name doesn't have to be on the
form for the user since it's connected in the tables. I'm trying to list
the name on the form, because I think it will help the user that is entering
the data since the employeeID isn't known typically and won't be on the paper
form that she's entering from. If I hide the key (employeeID) it only shows
the last name. I tried entering the text in the Row Source and it didn't
change the outcome.

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Douglas J Steele]

No, he means do it as a computed field in a query, and use the query as the
row source for the combobox.

See what he's suggested using as the Row Source in his earlier post?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display the
users full name as "Schmidt, Peter", although the underlying table stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK

Thanks Pete. I think I understand that the name doesn't have to be on the
form for the user since it's connected in the tables. I'm trying to list
the name on the form, because I think it will help the user that is entering
the data since the employeeID isn't known typically and won't be on the paper
form that she's entering from. If I hide the key (employeeID) it only shows
the last name. I tried entering the text in the Row Source and it didn't
change the outcome.

:

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Guest]

First, thanks to both of you for responding...again my apologies for not
being a savvy user. Clearly that 3 day training class only got me prepared
to open the software. =)

With that said, in the properties tab of the combobox under Data/Row Source
I tried to input what Pete provided:

Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " & tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

and got a message Syntax Error in Query Expression 'tblEmployee].LastName &
", " & tblEmployee.FirstName]'

No, he means do it as a computed field in a query, and use the query as the
row source for the combobox.

See what he's suggested using as the Row Source in his earlier post?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display the
users full name as "Schmidt, Peter", although the underlying table stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK


:

Thanks Pete. I think I understand that the name doesn't have to be on the
form for the user since it's connected in the tables. I'm trying to list
the name on the form, because I think it will help the user that is entering
the data since the employeeID isn't known typically and won't be on the paper
form that she's entering from. If I hide the key (employeeID) it only shows
the last name. I tried entering the text in the Row Source and it didn't
change the outcome.

:

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Guest]

Slight error with the brackets, try
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee.LastName &
", " & tblEmployee.FirstName AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

First, thanks to both of you for responding...again my apologies for not
being a savvy user. Clearly that 3 day training class only got me prepared
to open the software. =)

With that said, in the properties tab of the combobox under Data/Row Source
I tried to input what Pete provided:

Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " & tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

and got a message Syntax Error in Query Expression 'tblEmployee].LastName &
", " & tblEmployee.FirstName]'

No, he means do it as a computed field in a query, and use the query as the
row source for the combobox.

See what he's suggested using as the Row Source in his earlier post?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

:

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display the
users full name as "Schmidt, Peter", although the underlying table stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK


:

Thanks Pete. I think I understand that the name doesn't have to be on the
form for the user since it's connected in the tables. I'm trying to list
the name on the form, because I think it will help the user that is entering
the data since the employeeID isn't known typically and won't be on the paper
form that she's entering from. If I hide the key (employeeID) it only shows
the last name. I tried entering the text in the Row Source and it didn't
change the outcome.

:

Hi Dedrie

Strictly speaking you have no need to store the name in the travel details
table as you can derive this by linking the employeeID to the employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and hide by
setting their width. Therefore if you setup the Employee combo on your form
to have 2 columns (Column Count on Format tab) and set the Column Widths to
1cm; (only specify the first column width, the second will auto size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName & ", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information - EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel end date,
as well as Employee info - ID, FirstName, LastName and State traveled to.

Since the Employee info is in another table I was trying to use a combo box
so that the person doing the data entry in the form could just select the
Employee and a the field would populate the other items attached to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to calculate
the amount of US Source Income for each trip for each person by month for
reporting to the IRS. That is, of course, if I'm doing this correctly.

1.) Is a combo box the right was to achieve the display of information on my
form?
2.) Is there a way to have the "display" on the combo box display all three
columns - currently in the drop down you see EmployeeID, FirstName, LastName,
but once you select the person, only the EmployeeID displays on the form.

Any and all suggestions are welcome!

 

[Ken Snell \(MVP\)]

In addition to schasteen's solution, it's not a good idea to use Name as the
name of a field. It's a reserved word in ACCESS. See these Knowledge Base
articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>

Slight error with the brackets, try
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee.LastName &
", " & tblEmployee.FirstName AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " & tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

 

[Guest]

Okay, I see that it now shows the Lastname, Firstname on the Form. (Thanks
much!)
I tried to replicate this on the Report so that it would show either the
firstname & lastname columns or the combined name - but I haven't figured it
out. Any suggestions?

Slight error with the brackets, try
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee.LastName &
", " & tblEmployee.FirstName AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

First, thanks to both of you for responding...again my apologies for not
being a savvy user. Clearly that 3 day training class only got me prepared
to open the software. =)

With that said, in the properties tab of the combobox under Data/Row Source
I tried to input what Pete provided:

Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " & tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

and got a message Syntax Error in Query Expression 'tblEmployee].LastName &
", " & tblEmployee.FirstName]'

No, he means do it as a computed field in a query, and use the query as the
row source for the combobox.

See what he's suggested using as the Row Source in his earlier post?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

:

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display
the
users full name as "Schmidt, Peter", although the underlying table
stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK


:

Thanks Pete. I think I understand that the name doesn't have to be on
the
form for the user since it's connected in the tables. I'm trying to
list
the name on the form, because I think it will help the user that is
entering
the data since the employeeID isn't known typically and won't be on
the paper
form that she's entering from. If I hide the key (employeeID) it only
shows
the last name. I tried entering the text in the Row Source and it
didn't
change the outcome.

:

Hi Dedrie

Strictly speaking you have no need to store the name in the travel
details
table as you can derive this by linking the employeeID to the
employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and
hide by
setting their width. Therefore if you setup the Employee combo on
your form
to have 2 columns (Column Count on Format tab) and set the Column
Widths to
1cm; (only specify the first column width, the second will auto
size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens
that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information -
EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel
end date,
as well as Employee info - ID, FirstName, LastName and State
traveled to.

Since the Employee info is in another table I was trying to use a
combo box
so that the person doing the data entry in the form could just
select the
Employee and a the field would populate the other items attached
to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to
calculate
the amount of US Source Income for each trip for each person by
month for
reporting to the IRS. That is, of course, if I'm doing this
correctly.

1.) Is a combo box the right was to achieve the display of
information on my
form?
2.) Is there a way to have the "display" on the combo box display
all three
columns - currently in the drop down you see EmployeeID,
FirstName, LastName,
but once you select the person, only the EmployeeID displays on
the form.

Any and all suggestions are welcome!

 

[Guest]

Base your report on a query. The query should include both tables with a
join on the employee ID. You can then include the first name and last name
in you report.

Or

Create a combo box and duplicate what you have on the form.

Okay, I see that it now shows the Lastname, Firstname on the Form. (Thanks
much!)
I tried to replicate this on the Report so that it would show either the
firstname & lastname columns or the combined name - but I haven't figured it
out. Any suggestions?

Slight error with the brackets, try
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee.LastName &
", " & tblEmployee.FirstName AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

First, thanks to both of you for responding...again my apologies for not
being a savvy user. Clearly that 3 day training class only got me prepared
to open the software. =)

With that said, in the properties tab of the combobox under Data/Row Source
I tried to input what Pete provided:

Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " & tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

and got a message Syntax Error in Query Expression 'tblEmployee].LastName &
", " & tblEmployee.FirstName]'





:

No, he means do it as a computed field in a query, and use the query as the
row source for the combobox.

See what he's suggested using as the Row Source in his earlier post?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

:

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display
the
users full name as "Schmidt, Peter", although the underlying table
stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK


:

Thanks Pete. I think I understand that the name doesn't have to be on
the
form for the user since it's connected in the tables. I'm trying to
list
the name on the form, because I think it will help the user that is
entering
the data since the employeeID isn't known typically and won't be on
the paper
form that she's entering from. If I hide the key (employeeID) it only
shows
the last name. I tried entering the text in the Row Source and it
didn't
change the outcome.

:

Hi Dedrie

Strictly speaking you have no need to store the name in the travel
details
table as you can derive this by linking the employeeID to the
employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and
hide by
setting their width. Therefore if you setup the Employee combo on
your form
to have 2 columns (Column Count on Format tab) and set the Column
Widths to
1cm; (only specify the first column width, the second will auto
size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens
that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information -
EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel
end date,
as well as Employee info - ID, FirstName, LastName and State
traveled to.

Since the Employee info is in another table I was trying to use a
combo box
so that the person doing the data entry in the form could just
select the
Employee and a the field would populate the other items attached
to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to
calculate
the amount of US Source Income for each trip for each person by
month for
reporting to the IRS. That is, of course, if I'm doing this
correctly.

1.) Is a combo box the right was to achieve the display of
information on my
form?
2.) Is there a way to have the "display" on the combo box display
all three
columns - currently in the drop down you see EmployeeID,
FirstName, LastName,
but once you select the person, only the EmployeeID displays on
the form.

Any and all suggestions are welcome!

 

[Guest]

Got it. It works perfectly. Thanks much!

Base your report on a query. The query should include both tables with a
join on the employee ID. You can then include the first name and last name
in you report.

Or

Create a combo box and duplicate what you have on the form.

Okay, I see that it now shows the Lastname, Firstname on the Form. (Thanks
much!)
I tried to replicate this on the Report so that it would show either the
firstname & lastname columns or the combined name - but I haven't figured it
out. Any suggestions?

Slight error with the brackets, try
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee.LastName &
", " & tblEmployee.FirstName AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

:

First, thanks to both of you for responding...again my apologies for not
being a savvy user. Clearly that 3 day training class only got me prepared
to open the software. =)

With that said, in the properties tab of the combobox under Data/Row Source
I tried to input what Pete provided:

Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " & tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;

and got a message Syntax Error in Query Expression 'tblEmployee].LastName &
", " & tblEmployee.FirstName]'





:

No, he means do it as a computed field in a query, and use the query as the
row source for the combobox.

See what he's suggested using as the Row Source in his earlier post?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Do you mean create a new field "name" in the main table that combines
FirstName & LastName? I'm sorry I'm so confused.

:

If you use

FirstName & ", " & LastName as Name

as I suggested you combine the fields into 1 so the form will display
the
users full name as "Schmidt, Peter", although the underlying table
stores
their employeeID
--
Peter Schmidt
Ross-on-Wye, UK


:

Thanks Pete. I think I understand that the name doesn't have to be on
the
form for the user since it's connected in the tables. I'm trying to
list
the name on the form, because I think it will help the user that is
entering
the data since the employeeID isn't known typically and won't be on
the paper
form that she's entering from. If I hide the key (employeeID) it only
shows
the last name. I tried entering the text in the Row Source and it
didn't
change the outcome.

:

Hi Dedrie

Strictly speaking you have no need to store the name in the travel
details
table as you can derive this by linking the employeeID to the
employee table
in queries used for your reports.

A combo box can have multiple columns of data that you can show and
hide by
setting their width. Therefore if you setup the Employee combo on
your form
to have 2 columns (Column Count on Format tab) and set the Column
Widths to
1cm; (only specify the first column width, the second will auto
size)

On the data tab set the properties as follows:
Control Source: EmployeeID
Row Source Type: Table/Query
Row Source: SELECT tblEmployee.EmployeeID, tblEmployee].LastName &
", " &
tblEmployee.FirstName] AS Name FROM tblEmployee ORDER BY
tblEmployee.LastName;
Bound Column: 1

Regards
--
Peter Schmidt
Ross-on-Wye, UK


:

I'm trying to set up a travel database for our Non-US citizens
that travel to
the US. (We're located in Bermuda)

I have imported a table with all the employee information -
EmployeeID,
FirstName, LastName, title, salary, department, etc...

I created a table with all the states and the state abbreviations.

I have another table that I want to use a form to input the data.

The data needing to be input would be travel start date and travel
end date,
as well as Employee info - ID, FirstName, LastName and State
traveled to.

Since the Employee info is in another table I was trying to use a
combo box
so that the person doing the data entry in the form could just
select the
Employee and a the field would populate the other items attached
to that
employee record.

Once the form/travelinfo table is populated, I'll use queries to
calculate
the amount of US Source Income for each trip for each person by
month for
reporting to the IRS. That is, of course, if I'm doing this
correctly.

1.) Is a combo box the right was to achieve the display of
information on my
form?
2.) Is there a way to have the "display" on the combo box display
all three
columns - currently in the drop down you see EmployeeID,
FirstName, LastName,
but once you select the person, only the EmployeeID displays on
the form.

Any and all suggestions are welcome!