Changing an expr field to a string

[Afrosheen via AccessMonster.com]

I'm trying to change an expr string which consist of [lname]&", " & [fname]
to make it into a text string so I can store that information in another
field which is a text field. Every time I try and do this I get an error
saying that the field can not be edited. I tried putting in the error code
number like:

After I put in the break points and hover my cursor over the strtxt and
combo41 line it will show the correct information. When I hover over the
strtxt after the Me!Supervisor it will show nothing.

I'm really lost here some where and could use some help.

Private Sub Combo41_AfterUpdate()
Dim strtxt As String
10 On Error GoTo Combo41_AfterUpdate_Error

20 strtxt = Combo41
30 Me!Supervisor = strtxt

40 On Error GoTo 0
50 Exit Sub

Combo41_AfterUpdate_Error:
60 If Err.Number <> -2147352567 Or 3327 Then
70 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure Combo41_AfterUpdate of VBA Document Form_Copy of frmSupervisor"
80 End If
End Sub

 

[pietlinden]

Why? This is bad design. If you need to use the two together, create
a query.

 

[Jeanette Cunningham]

This should work

Private Sub Combo41_AfterUpdate()
On Error GoTo Combo41_AfterUpdate_Error

If Not IsNull(Me.Combo41) Then
Me!Supervisor = Me.Combo41
End If

SubExit:
Exit Sub

Combo41_AfterUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")
End Sub



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

I'm trying to change an expr string which consist of [lname]&", " & [fname]
to make it into a text string so I can store that information in another
field which is a text field.

Ummm...

No.

You almost certainly do NOT want to do this. It's redundant!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you have some good reason to do so, e.g. you want a record with LName
"Jones" and FName "Mike" to default to "Mike Jones" but to be able to edit it
to "Alouisious P. Snodgrass" in the other field, please explain the logic.

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me.
What I'm trying to accomplish is this.
I have 3 fields. One with the expr. One with Super1, and one with Super2.
What I'm trying to do is pick a name from the exp and place it either in
Super1 or Super2.. For example:

Exp Super1 Super2
Jones, John Thomas, Fred Jackson, George
Thomas, Fred
Jackson, George

The first column [exp] is the employee. The second and third columns are for
the supervisors. John Jones [exp] is an employee. His supervisor is Fred
Thomas [super1]. Fred Thomas' supervisor is George Jackson [super2] and so
forth up the chain of command. I only need the two supervisors. Fred Thomas
in the next [exp] is the employee. Then George Jackson is his supervisor
[super1]

If there's an easier way of doing this any help would be appreciated.

I'm trying to change an expr string which consist of [lname]&", " & [fname]
to make it into a text string so I can store that information in another
field which is a text field.

Ummm...

No.

You almost certainly do NOT want to do this. It's redundant!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you have some good reason to do so, e.g. you want a record with LName
"Jones" and FName "Mike" to default to "Mike Jones" but to be able to edit it
to "Alouisious P. Snodgrass" in the other field, please explain the logic.

 

[John W. Vinson]

I have 3 fields. One with the expr. One with Super1, and one with Super2.
What I'm trying to do is pick a name from the exp and place it either in
Super1 or Super2.. For example:

Exp Super1 Super2
Jones, John Thomas, Fred Jackson, George
Thomas, Fred
Jackson, George

The first column [exp] is the employee. The second and third columns are for
the supervisors. John Jones [exp] is an employee. His supervisor is Fred
Thomas [super1]. Fred Thomas' supervisor is George Jackson [super2] and so
forth up the chain of command. I only need the two supervisors. Fred Thomas
in the next [exp] is the employee. Then George Jackson is his supervisor
[super1]

If there's an easier way of doing this any help would be appreciated.

There is indeed. Consider a table structure like:

Employees
EmployeeID <Autonumber, Primary Key>
LastName
FirstName
<other biographical data as needed>
SupervisorID <Number, Long Integer>

You only need the direct supervisor, and you do NOT need (or want!!) the
person's name in the Employees table. The SupervisorID is a "self join" link
to the EmployeeID of the supervisor.

In a Form you can easily enter this by using a Combo Box bound to the
SupervisorID field. This combo box would be based on a query of Employees
selecting only those employees who are supervisors (you might want to add a
Yes/No field for that purpose).

To get the supervisor's names you could use a Query:

SELECT Employees.LastName & ", " & Employees.FirstName AS EmpName,
Sups1.LastName & ", " & Sups1.FirstName AS Super1, Sups2.LastName & ", " &
Sups2.FirstName AS Super2
FROM (Employees INNER JOIN Employees AS Sups1
ON Sups1.EmployeeID = Employees.SupervisorID)
INNER JOIN Employees AS Sups2
ON Sups2.EmployeeID = Sups1.SupervisorID);

 

[Afrosheen via AccessMonster.com]

Thanks John for the suggestion. I did a change in the query I built to
supervisor staffId and when I need them. Then I do a dlookup on the record.

txtAssist = cmboSupervisor.Column(3)
txtman = cmboSupervisor.Column(4)
txtmail = Nz(DLookup("Email", "querytest2", "StaffId ='" & txtAssist & _
"'"), vbNullString)
tmail = Nz(DLookup("lname +', '+ fname", "querytest2", "StaffId ='" &
txtAssist & _
"'"), vbNullString)

The cmboSupervisor.Column(3) holds the supervisors staffid like (svt01)
and the Column(4) like wise holds a supervisor staffid like (bw04).
Column(1) is a supervisor also. Example

Column(1) John a supervisor
Column(3) John's supervisor
Column(4) John's supervisor and Column(3) supervisor

Am I correct what your suggesting is to set up a separate table and relation
ship?

What I'm dealing with is one supervisor may have 10 subordinates.

I have 3 fields. One with the expr. One with Super1, and one with Super2.
What I'm trying to do is pick a name from the exp and place it either in

[quoted text clipped - 13 lines]

If there's an easier way of doing this any help would be appreciated.

There is indeed. Consider a table structure like:

Employees
EmployeeID <Autonumber, Primary Key>
LastName
FirstName
<other biographical data as needed>
SupervisorID <Number, Long Integer>

You only need the direct supervisor, and you do NOT need (or want!!) the
person's name in the Employees table. The SupervisorID is a "self join" link
to the EmployeeID of the supervisor.

In a Form you can easily enter this by using a Combo Box bound to the
SupervisorID field. This combo box would be based on a query of Employees
selecting only those employees who are supervisors (you might want to add a
Yes/No field for that purpose).

To get the supervisor's names you could use a Query:

SELECT Employees.LastName & ", " & Employees.FirstName AS EmpName,
Sups1.LastName & ", " & Sups1.FirstName AS Super1, Sups2.LastName & ", " &
Sups2.FirstName AS Super2
FROM (Employees INNER JOIN Employees AS Sups1
ON Sups1.EmployeeID = Employees.SupervisorID)
INNER JOIN Employees AS Sups2
ON Sups2.EmployeeID = Sups1.SupervisorID);

 

[John W. Vinson]

The cmboSupervisor.Column(3) holds the supervisors staffid like (svt01)
and the Column(4) like wise holds a supervisor staffid like (bw04).
Column(1) is a supervisor also. Example

Column(1) John a supervisor
Column(3) John's supervisor
Column(4) John's supervisor and Column(3) supervisor

Am I correct what your suggesting is to set up a separate table and relation
ship?

What I'm dealing with is one supervisor may have 10 subordinates.

No.

I am not suggesting multiple fields (which you are doing).
I am not suggesting multiple tables (which would be even worse).

I am suggesting a "self join" query. Each record in the table would have a
SupervisorID field indicating *that person's supervisor*. The supervisor is
also an employee (right???), so the supervisor has a record in the table; the
SupervisorID is a link to that supervisor's record.

If Mary has ten subordinates, then all ten records for those people would have
Mary's EmployeeID stored in their SupervisorID field. So there is no limit on
the number of reports.

You can "cascade up" - if Joe works for Mary, and Mary works for Alfredo, you
can create a query linking from Joe's record to Mary's, and from Mary's to
Alfredo's (and so on).

 

[Afrosheen via AccessMonster.com]

I have an employee field, a staffid, and a Super1, a Super2, and a SuperCk
field among others.

When I click on the SuperCk [check box] field, that tells the table that they
are a supervisor and adds that name or could be the staffid to a query called
Supervisors.

When an employee has no supervisor and one has to be selected, then a combo
box based on the Supervisor query is displayed and I can select that
supervisor and the results which is the staffid is placed in the Super1 field.


I hope I'm not being redundant. It's been one of those bad Access days, but
it's better than physical work.

Thanks for your help. It's really appreciated.

The cmboSupervisor.Column(3) holds the supervisors staffid like (svt01)
and the Column(4) like wise holds a supervisor staffid like (bw04).

[quoted text clipped - 8 lines]

What I'm dealing with is one supervisor may have 10 subordinates.

No.

I am not suggesting multiple fields (which you are doing).
I am not suggesting multiple tables (which would be even worse).

I am suggesting a "self join" query. Each record in the table would have a
SupervisorID field indicating *that person's supervisor*. The supervisor is
also an employee (right???), so the supervisor has a record in the table; the
SupervisorID is a link to that supervisor's record.

If Mary has ten subordinates, then all ten records for those people would have
Mary's EmployeeID stored in their SupervisorID field. So there is no limit on
the number of reports.

You can "cascade up" - if Joe works for Mary, and Mary works for Alfredo, you
can create a query linking from Joe's record to Mary's, and from Mary's to
Alfredo's (and so on).

 

[John W. Vinson]

I have an employee field, a staffid, and a Super1, a Super2, and a SuperCk
field among others.

Sorry... but let me emphasize: the Super2 field *SHOULD NOT EXIST*. It's not
necessary, and it becomes a real hassle; if the second-level supervisor
"leaves to pursue other opportunities" after being caught with his hand in the
till, you would have to update not only his direct reports but all of THEIR
reports' records.

You can instead use a Self Join query to cascade up and find any employee's
supervisor, or that person's supervisor, or THAT person's supervisor - as many
levels as needed.

When I click on the SuperCk [check box] field, that tells the table that they
are a supervisor and adds that name or could be the staffid to a query called
Supervisors.

That query could easily be used as the rosource of your combo.

When an employee has no supervisor and one has to be selected, then a combo
box based on the Supervisor query is displayed and I can select that
supervisor and the results which is the staffid is placed in the Super1 field.

Exactly what I was suggesting.

I hope I'm not being redundant. It's been one of those bad Access days, but
it's better than physical work.

<g> I'm not altogether sure of that some days but... yes.

What specific problem are you still having? It sounds like you should have
enough information in your table structure to display any level of
supervisor's name; for example, you could display the direct supervisor's name
on a Form with a textbox with a control source

=DLookUp("[LastName] & ', ' & [FirstName]", "Employees", "[EmployeeID] = " &
[SupervisorID])

and the second level supervisor with a somewhat more complex expression

=DLookUp("[LastName] & ', ' & [FirstName]", "Employees", "[EmployeeID] = " &
DLookUp("[SupervisorID]", "Employees", "[EmployeeID] = " & [SupervisorID]))

You can also create specific queries to return the name based on the
supervisor (first level, second level, third level, whatever...)

 

[Afrosheen via AccessMonster.com]

Sorry for getting back so slow. The reason for the super2 is for the super1
supervisor. What is wanted by the people having me do this program is to send
out an email. One to the supervisor, super1 & super2 in that order.

I'll talk to them about eliminating the super 2 field. I don't think that
would be a problem

Thanks for your help.

I have an employee field, a staffid, and a Super1, a Super2, and a SuperCk
field among others.

Sorry... but let me emphasize: the Super2 field *SHOULD NOT EXIST*. It's not
necessary, and it becomes a real hassle; if the second-level supervisor
"leaves to pursue other opportunities" after being caught with his hand in the
till, you would have to update not only his direct reports but all of THEIR
reports' records.

You can instead use a Self Join query to cascade up and find any employee's
supervisor, or that person's supervisor, or THAT person's supervisor - as many
levels as needed.

When I click on the SuperCk [check box] field, that tells the table that they
are a supervisor and adds that name or could be the staffid to a query called
Supervisors.

That query could easily be used as the rosource of your combo.

When an employee has no supervisor and one has to be selected, then a combo
box based on the Supervisor query is displayed and I can select that
supervisor and the results which is the staffid is placed in the Super1 field.

Exactly what I was suggesting.

I hope I'm not being redundant. It's been one of those bad Access days, but
it's better than physical work.

<g> I'm not altogether sure of that some days but... yes.

What specific problem are you still having? It sounds like you should have
enough information in your table structure to display any level of
supervisor's name; for example, you could display the direct supervisor's name
on a Form with a textbox with a control source

=DLookUp("[LastName] & ', ' & [FirstName]", "Employees", "[EmployeeID] = " &
[SupervisorID])

and the second level supervisor with a somewhat more complex expression

=DLookUp("[LastName] & ', ' & [FirstName]", "Employees", "[EmployeeID] = " &
DLookUp("[SupervisorID]", "Employees", "[EmployeeID] = " & [SupervisorID]))

You can also create specific queries to return the name based on the
supervisor (first level, second level, third level, whatever...)

 

[John W. Vinson]

Sorry for getting back so slow. The reason for the super2 is for the super1
supervisor. What is wanted by the people having me do this program is to send
out an email. One to the supervisor, super1 & super2 in that order.

And I have told you - twice now - exactly how you can do so.

It is NOT necessary to denormalize your table in order to meet this (very
reasonable) business requirement. Or are you assuming that all of the data
must be stored in the same table in order to generate an email??? It's not
necessary to do so!

 

[Afrosheen via AccessMonster.com]

Thanks for getting back to me. I wanted to tell you that I was sorry. I
didn't realize about Join Queries. I did do some research and found out about
them. Now with the help and direction I received from you, I think I can move
on. I guess this is what you call Lack of experience and knowledge. Sorry no
MVP here.