need some help displaying some data properly

[Herc]

This is more than likely a newb question:
On a data entry form I have a couple of fields you make change to and on the
same form it it displays what you entered in. now i have a field called
Employee ID which is a drop down combo box that references a table called
Employees this fields row source is "SELECT Employees.EmployeeID,
Employees.Initials FROM Employees; " and it displays the initials. In the
field it fills in it shows the autonumber EmployeeID and not the initals. how
do i get it to show the initials in the text field that is generated? Also,
if that employee has gotten dismissed ( in the employee database I specified
Active or Dismissed field) how do I stop the old employees from showing up in
the list without actually deleting the employee from the database and
possibly corrupting the whole database?

 

[Duane Hookom]

This should all be done in a form with a combo box. Your combo box Column
Widths property can be set to hide the first column of the Row Source:

Column Widths: 0"; .5"

The Row Source of the combo box can use a sql statement like:
SELECT Employees.EmployeeID, Employees.Initials
FROM Employees
WHERE [StatusField]="Active"
ORDER BY Employees.Initials;

I will sometimes use a Row Source that displays active employees first.
SELECT EmployeeID, Initials , [StatusField]
FROM Employees
WHERE [StatusField]="Active"
ORDER BY [StatusField], Employees.Initials;

I then use the After Update event to check the status field value
"Me.cboEmployeeID.Column(2)" to make sure the selected employee is active.
This requires you to set the column count to 3 and the column widths to:
0",.5",.6"

 

[Herc]

Thank you very much for solving that display of active employees for me. It
was very helpful. I still need to know how to get the initials from the
employee table to input into the generated field. I can get the employee
number to show up but not the initilas. any ideas?

This should all be done in a form with a combo box. Your combo box Column
Widths property can be set to hide the first column of the Row Source:

Column Widths: 0"; .5"

The Row Source of the combo box can use a sql statement like:
SELECT Employees.EmployeeID, Employees.Initials
FROM Employees
WHERE [StatusField]="Active"
ORDER BY Employees.Initials;

I will sometimes use a Row Source that displays active employees first.
SELECT EmployeeID, Initials , [StatusField]
FROM Employees
WHERE [StatusField]="Active"
ORDER BY [StatusField], Employees.Initials;

I then use the After Update event to check the status field value
"Me.cboEmployeeID.Column(2)" to make sure the selected employee is active.
This requires you to set the column count to 3 and the column widths to:
0",.5",.6"


--
Duane Hookom
MS Access MVP
--

This is more than likely a newb question:
On a data entry form I have a couple of fields you make change to and on
the
same form it it displays what you entered in. now i have a field called
Employee ID which is a drop down combo box that references a table called
Employees this fields row source is "SELECT Employees.EmployeeID,
Employees.Initials FROM Employees; " and it displays the initials. In the
field it fills in it shows the autonumber EmployeeID and not the initals.
how
do i get it to show the initials in the text field that is generated?
Also,
if that employee has gotten dismissed ( in the employee database I
specified
Active or Dismissed field) how do I stop the old employees from showing up
in
the list without actually deleting the employee from the database and
possibly corrupting the whole database?

 

[Duane Hookom]

Are you expecting to store both the ID and the initials in the table? If
your column widths are set as I suggested, you wont' see the ID.

--
Duane Hookom
MS Access MVP
--

Thank you very much for solving that display of active employees for me.
It
was very helpful. I still need to know how to get the initials from the
employee table to input into the generated field. I can get the employee
number to show up but not the initilas. any ideas?

This should all be done in a form with a combo box. Your combo box Column
Widths property can be set to hide the first column of the Row Source:

Column Widths: 0"; .5"

The Row Source of the combo box can use a sql statement like:
SELECT Employees.EmployeeID, Employees.Initials
FROM Employees
WHERE [StatusField]="Active"
ORDER BY Employees.Initials;

I will sometimes use a Row Source that displays active employees first.
SELECT EmployeeID, Initials , [StatusField]
FROM Employees
WHERE [StatusField]="Active"
ORDER BY [StatusField], Employees.Initials;

I then use the After Update event to check the status field value
"Me.cboEmployeeID.Column(2)" to make sure the selected employee is
active.
This requires you to set the column count to 3 and the column widths to:
0",.5",.6"


--
Duane Hookom
MS Access MVP
--

This is more than likely a newb question:
On a data entry form I have a couple of fields you make change to and
on
the
same form it it displays what you entered in. now i have a field called
Employee ID which is a drop down combo box that references a table
called
Employees this fields row source is "SELECT Employees.EmployeeID,
Employees.Initials FROM Employees; " and it displays the initials. In
the
field it fills in it shows the autonumber EmployeeID and not the
initals.
how
do i get it to show the initials in the text field that is generated?
Also,
if that employee has gotten dismissed ( in the employee database I
specified
Active or Dismissed field) how do I stop the old employees from showing
up
in
the list without actually deleting the employee from the database and
possibly corrupting the whole database?

 

[Herc]

Duane,
I apologize. I am phrasing my words incorrectly. The "generated field" is
not going to be entered into the table at all. it is for display purposes
only. For example, in the combo box they select their initials. when they do,
the text box only shows what initials they put in. if an order was put in
today the field would display JC03222006"some autonumber". The display of the
date and the autonumber workfine, but I cannot get the initials to show. what
i get now is 24032220066025. The code I have for the box is =[EmployeeID] &
DatePart("m",[OrderDate],1) & DatePart("d",[OrderDate],1) &
DatePart("yyyy",[OrderDate],1) & [OrderID]. Where it says [EmployeeID] the
code needs to be fixed to show initials. It is only to display and it is
driving my nuts. The reason for this is that I was told that on the form the
"generated field" would then be turned around and written on paper to give
the customer or printed out. Hopefully this explanation fleshes out more of
the idea.

 

[Duane Hookom]

If you don't want to show or use the EmployeeID then why even have the
EmployeeID in the Row Source of the combo box? You can set the combo box:
Name: cboEmployeeInit
Row Source: SELECT Initials FROM Employees;
Column Count: 1
Bound Column: 1
Column Widths: 1"

Then add a text box with a control source like:
=cboEmployeeInit & Format(OrderDate,"mmddyyyy") & OrderID

 

[Herc]

Duane,
Thank you for all your help. It solved my issues. Now I just gotta fix the
reports. LOL
They were working, but with the code change, they decided to not work now.
Easy enough to fix. You are a life saver or at least a code saver. :-D

Take care and keep up the great work.

If you don't want to show or use the EmployeeID then why even have the
EmployeeID in the Row Source of the combo box? You can set the combo box:
Name: cboEmployeeInit
Row Source: SELECT Initials FROM Employees;
Column Count: 1
Bound Column: 1
Column Widths: 1"

Then add a text box with a control source like:
=cboEmployeeInit & Format(OrderDate,"mmddyyyy") & OrderID

--
Duane Hookom
MS Access MVP
--

Duane,
I apologize. I am phrasing my words incorrectly. The "generated field" is
not going to be entered into the table at all. it is for display purposes
only. For example, in the combo box they select their initials. when they
do,
the text box only shows what initials they put in. if an order was put in
today the field would display JC03222006"some autonumber". The display of
the
date and the autonumber workfine, but I cannot get the initials to show.
what
i get now is 24032220066025. The code I have for the box is =[EmployeeID]
&
DatePart("m",[OrderDate],1) & DatePart("d",[OrderDate],1) &
DatePart("yyyy",[OrderDate],1) & [OrderID]. Where it says [EmployeeID] the
code needs to be fixed to show initials. It is only to display and it is
driving my nuts. The reason for this is that I was told that on the form
the
"generated field" would then be turned around and written on paper to give
the customer or printed out. Hopefully this explanation fleshes out more
of
the idea.