Combo box on report

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

My form (frmMain) has a combo box (cboName) for Name. The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last] & ", " &
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a three
column query. The first column is EmployeeID, the second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format. The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format. Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the names, so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users are
not limited to the list. There is a good reason for that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields as
tblMain. Text box txtName on the report has as its record
source the field [Name]. A name selected from cboName on
the form will appear as Jones, Johnny, and the typed name
will appear as Mary Martin, or exactly as typed. In order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="",[Forms]!
[frmMain]![cboName],[Forms]![frmMain]![cboName].Column(2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at all if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query. However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.
Sorry this went on for so long. There is just no way to
sum this up in a sentence or two. I feel like I am very
close, but I can't quite get there.
 
Bruce said:
My form (frmMain) has a combo box (cboName) for Name. The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last] & ", " &
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a three
column query. The first column is EmployeeID, the second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format. The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format. Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the names, so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users are
not limited to the list. There is a good reason for that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields as
tblMain. Text box txtName on the report has as its record
source the field [Name]. A name selected from cboName on
the form will appear as Jones, Johnny, and the typed name
will appear as Mary Martin, or exactly as typed. In order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="",[Forms]!
[frmMain]![cboName],[Forms]![frmMain]![cboName].Column(2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at all if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query. However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.
Click to expand...


You should get the name info from the query.

I have no idea what the syntax [Tables]![tblMain]![Name] is
supposed to mean and I suspect that the prompt is an
indicator that it's not legal.

If [Name] is a field in the report's record source
table/query, then the text box can refer to it simply by
[Name]. But if that doesn't wotk, it's because you've used
a reserved word in a context where Access can't figure out
what you intended (you really should change that to
something else).
 
Thank you for the reply. To summarize what I was trying
to do, on the form (frmMain) is a combo box (cboName).
The row source for cboName is a SQL statement. Its
records source is [Name] from the form's underlying
table. Its Limit to List property is set to No, since
sometimes other names must be used. It has three columns,
with widths 0";1";0".
The SQL statement in datasheet view shows three columns,
EmployeeID, LastFirst and FirstLast. For example:
12345 Jones, Johnny Johnny Jones
The user selects Jones, Johnny from the list, but Johnny
Jones is to show up on the report. If Mary Martin is
typed in, that is to show up on the report. The record
source for the report (rptMain) is tblMain (actually, it
is a query derived from tblMain).
The best I could come up with was a text box (txtName) on
rptMain with the control source:
=IIf(IsNull([Forms]![frmMain]![cboName].Column(2)),[Name],
[Forms]![frmMain]![cboName].Column(2)). I referenced
Column(2) because it would be blank when the name is typed
in rather than being selected from the combo box list.
The problem I had been having before was that instead of
IsNull I was using = "". I am still trying to sort out
the difference between an empty string and a null value.
-----Original Message-----
Bruce said:
My form (frmMain) has a combo box (cboName) for Name. The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last] & ", " &
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a three
column query. The first column is EmployeeID, the second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format. The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format. Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the names, so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users are
not limited to the list. There is a good reason for that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields as
tblMain. Text box txtName on the report has as its record
source the field [Name]. A name selected from cboName on
the form will appear as Jones, Johnny, and the typed name
will appear as Mary Martin, or exactly as typed. In order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="",[Forms]!
[frmMain]![cboName],[Forms]![frmMain]![cboName].Column (2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at all if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query. However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.
Click to expand...


You should get the name info from the query.

I have no idea what the syntax [Tables]![tblMain]![Name] is
supposed to mean and I suspect that the prompt is an
indicator that it's not legal.

If [Name] is a field in the report's record source
table/query, then the text box can refer to it simply by
[Name]. But if that doesn't wotk, it's because you've used
a reserved word in a context where Access can't figure out
what you intended (you really should change that to
something else).
Click to expand...
 
There is another way to check if the combo box had an item
selected from the row source or if something was typed in.
I'm still not clear on where [Name] is coming from, but I
think this might do what you want??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName], [Name])

or maybe this??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName],
[Forms]![frmMain]![cboName.Column(2))
--
Marsh
MVP [MS Access]


Thank you for the reply. To summarize what I was trying
to do, on the form (frmMain) is a combo box (cboName).
The row source for cboName is a SQL statement. Its
records source is [Name] from the form's underlying
table. Its Limit to List property is set to No, since
sometimes other names must be used. It has three columns,
with widths 0";1";0".
The SQL statement in datasheet view shows three columns,
EmployeeID, LastFirst and FirstLast. For example:
12345 Jones, Johnny Johnny Jones
The user selects Jones, Johnny from the list, but Johnny
Jones is to show up on the report. If Mary Martin is
typed in, that is to show up on the report. The record
source for the report (rptMain) is tblMain (actually, it
is a query derived from tblMain).
The best I could come up with was a text box (txtName) on
rptMain with the control source:
=IIf(IsNull([Forms]![frmMain]![cboName].Column(2)),[Name],
[Forms]![frmMain]![cboName].Column(2)). I referenced
Column(2) because it would be blank when the name is typed
in rather than being selected from the combo box list.
The problem I had been having before was that instead of
IsNull I was using = "". I am still trying to sort out
the difference between an empty string and a null value.
-----Original Message-----
Bruce said:
My form (frmMain) has a combo box (cboName) for Name. The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last] & ", " &
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a three
column query. The first column is EmployeeID, the second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format. The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format. Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the names, so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users are
not limited to the list. There is a good reason for that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields as
tblMain. Text box txtName on the report has as its record
source the field [Name]. A name selected from cboName on
the form will appear as Jones, Johnny, and the typed name
will appear as Mary Martin, or exactly as typed. In order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="",[Forms]!
[frmMain]![cboName],[Forms]![frmMain]![cboName].Column (2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at all if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query. However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.
Click to expand...


You should get the name info from the query.

I have no idea what the syntax [Tables]![tblMain]![Name] is
supposed to mean and I suspect that the prompt is an
indicator that it's not legal.

If [Name] is a field in the report's record source
table/query, then the text box can refer to it simply by
[Name]. But if that doesn't wotk, it's because you've used
a reserved word in a context where Access can't figure out
what you intended (you really should change that to
something else).
Click to expand...
Click to expand...
 
It is rather unclear, isn't it? frmMain is to manage the
table (tblMain) that lists training sessions. Each record
contains Subject, Department, Instructor, etc. Instructor
is the [Name] field in my example. Most training is done
on the fly, so that if for instance a manufacturing
procedure is modified the affected employees receive
training. Instructors in such cases are other employees,
such as Supervisors. Some other types of training are
performed by outside instructors.
There is an Employees table, including a Yes/No field if
an Employee is also an Instructor. A query (qryName)
lists those for whom Instructor is checked. qryName
contains [EmployeeID], [Last,First], and [FirstLast].
These last two are concatenated values. qryName is the
Row Source for cboName on frmMain.
If training were conducted solely by a designated list of
instructors, the Instructor field in tblMain would
probably be the PK from an Instructor table. However,
outside instructors (e.g. First Aid instructors) would
need to be added to the Employee table, or to a separate
Instructor table, or something like that. Instead, I
decided to store the Instructor name (as text) in
tblMain. The problem was that elsewhere in the database
names are selected from a list in Last, First format, so I
wanted to keep it the same for selecting Instructor (when
the instructor is also an employee, and therefore on the
combo box list). At the same time, I wanted the name to
appear in the report in First Last format. I also wanted
a name typed into the combo box to appear on the report
exactly as typed. IN assembling the report, my thinking
was that Column(2) from the combo box would contain a
value only if a name is selected from the list. Otherwise
Column(2) contains a Null value, and the value typed into
the combo box would be used.
I am writing all of this even though I have the thing
working because it is possible that List Index is a better
way of doing things. If not, it is certainly something I
can use in the future, so I am tring to understand it
now. If I understand correctly, ListIndex = -1 is a way
of saying that the item is not in the combo box row
source: IIf(The combo box entry is not in the row source,
Use the typed value, Otherwise use Column(2)).
-----Original Message-----
There is another way to check if the combo box had an item
selected from the row source or if something was typed in.
I'm still not clear on where [Name] is coming from, but I
think this might do what you want??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName], [Name])

or maybe this??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName],
[Forms]![frmMain]![cboName.Column(2))
--
Marsh
MVP [MS Access]


Thank you for the reply. To summarize what I was trying
to do, on the form (frmMain) is a combo box (cboName).
The row source for cboName is a SQL statement. Its
records source is [Name] from the form's underlying
table. Its Limit to List property is set to No, since
sometimes other names must be used. It has three columns,
with widths 0";1";0".
The SQL statement in datasheet view shows three columns,
EmployeeID, LastFirst and FirstLast. For example:
12345 Jones, Johnny Johnny Jones
The user selects Jones, Johnny from the list, but Johnny
Jones is to show up on the report. If Mary Martin is
typed in, that is to show up on the report. The record
source for the report (rptMain) is tblMain (actually, it
is a query derived from tblMain).
The best I could come up with was a text box (txtName) on
rptMain with the control source:
=IIf(IsNull([Forms]![frmMain]![cboName].Column(2)), [Name],
[Forms]![frmMain]![cboName].Column(2)). I referenced
Column(2) because it would be blank when the name is typed
in rather than being selected from the combo box list.
The problem I had been having before was that instead of
IsNull I was using = "". I am still trying to sort out
the difference between an empty string and a null value.
-----Original Message-----
Bruce wrote:

My form (frmMain) has a combo box (cboName) for Name. The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last]
Click to expand...
& ", "
&
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a three
column query. The first column is EmployeeID, the second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format. The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format. Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the names, so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users are
not limited to the list. There is a good reason for that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields as
tblMain. Text box txtName on the report has as its record
source the field [Name]. A name selected from cboName on
the form will appear as Jones, Johnny, and the typed name
will appear as Mary Martin, or exactly as typed. In order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="",[Forms]!
[frmMain]![cboName],[Forms]![frmMain]![cboName].Column (2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at all if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query. However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.


You should get the name info from the query.

I have no idea what the syntax [Tables]![tblMain]!
Click to expand...
[Name]
is
supposed to mean and I suspect that the prompt is an
indicator that it's not legal.

If [Name] is a field in the report's record source
table/query, then the text box can refer to it simply by
[Name]. But if that doesn't wotk, it's because you've used
a reserved word in a context where Access can't figure out
what you intended (you really should change that to
something else).
Click to expand...
Click to expand...

.
Click to expand...
 
I think maybe I mightprefer the separate instructors table,
but I'm not familiar with your entire set of needs.

Regardless, you are correct the ListIndex = -1 when the
entry in the combo box is not in its row source. This is an
alternative to using the NotInList event to detect that
situation.

Glad you got it working and good luck on the rest of your
project.
--
Marsh
MVP [MS Access]


It is rather unclear, isn't it? frmMain is to manage the
table (tblMain) that lists training sessions. Each record
contains Subject, Department, Instructor, etc. Instructor
is the [Name] field in my example. Most training is done
on the fly, so that if for instance a manufacturing
procedure is modified the affected employees receive
training. Instructors in such cases are other employees,
such as Supervisors. Some other types of training are
performed by outside instructors.
There is an Employees table, including a Yes/No field if
an Employee is also an Instructor. A query (qryName)
lists those for whom Instructor is checked. qryName
contains [EmployeeID], [Last,First], and [FirstLast].
These last two are concatenated values. qryName is the
Row Source for cboName on frmMain.
If training were conducted solely by a designated list of
instructors, the Instructor field in tblMain would
probably be the PK from an Instructor table. However,
outside instructors (e.g. First Aid instructors) would
need to be added to the Employee table, or to a separate
Instructor table, or something like that. Instead, I
decided to store the Instructor name (as text) in
tblMain. The problem was that elsewhere in the database
names are selected from a list in Last, First format, so I
wanted to keep it the same for selecting Instructor (when
the instructor is also an employee, and therefore on the
combo box list). At the same time, I wanted the name to
appear in the report in First Last format. I also wanted
a name typed into the combo box to appear on the report
exactly as typed. IN assembling the report, my thinking
was that Column(2) from the combo box would contain a
value only if a name is selected from the list. Otherwise
Column(2) contains a Null value, and the value typed into
the combo box would be used.
I am writing all of this even though I have the thing
working because it is possible that List Index is a better
way of doing things. If not, it is certainly something I
can use in the future, so I am tring to understand it
now. If I understand correctly, ListIndex = -1 is a way
of saying that the item is not in the combo box row
source: IIf(The combo box entry is not in the row source,
Use the typed value, Otherwise use Column(2)).
-----Original Message-----
There is another way to check if the combo box had an item
selected from the row source or if something was typed in.
I'm still not clear on where [Name] is coming from, but I
think this might do what you want??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName], [Name])

or maybe this??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName],
[Forms]![frmMain]![cboName.Column(2))
--
Marsh
MVP [MS Access]


Thank you for the reply. To summarize what I was trying
to do, on the form (frmMain) is a combo box (cboName).
The row source for cboName is a SQL statement. Its
records source is [Name] from the form's underlying
table. Its Limit to List property is set to No, since
sometimes other names must be used. It has three columns,
with widths 0";1";0".
The SQL statement in datasheet view shows three columns,
EmployeeID, LastFirst and FirstLast. For example:
12345 Jones, Johnny Johnny Jones
The user selects Jones, Johnny from the list, but Johnny
Jones is to show up on the report. If Mary Martin is
typed in, that is to show up on the report. The record
source for the report (rptMain) is tblMain (actually, it
is a query derived from tblMain).
The best I could come up with was a text box (txtName) on
rptMain with the control source:
=IIf(IsNull([Forms]![frmMain]![cboName].Column(2)), [Name],
[Forms]![frmMain]![cboName].Column(2)). I referenced
Column(2) because it would be blank when the name is typed
in rather than being selected from the combo box list.
The problem I had been having before was that instead of
IsNull I was using = "". I am still trying to sort out
the difference between an empty string and a null value.
-----Original Message-----
Bruce wrote:

My form (frmMain) has a combo box (cboName) for Name.
The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last] & ", "
&
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a
three
column query. The first column is EmployeeID, the
second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format.
The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format.
Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the names, so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users
are
not limited to the list. There is a good reason for
that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields
as
tblMain. Text box txtName on the report has as its
record
source the field [Name]. A name selected from cboName
on
the form will appear as Jones, Johnny, and the typed
name
will appear as Mary Martin, or exactly as typed. In
order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="",[Forms]!
[frmMain]![cboName],[Forms]![frmMain]![cboName].Column
(2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at all if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query.
However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.


You should get the name info from the query.

I have no idea what the syntax [Tables]![tblMain]! [Name]
is
supposed to mean and I suspect that the prompt is an
indicator that it's not legal.

If [Name] is a field in the report's record source
table/query, then the text box can refer to it simply by
[Name]. But if that doesn't wotk, it's because you've
used
a reserved word in a context where Access can't figure out
what you intended (you really should change that to
something else).
Click to expand...

.
Click to expand...
Click to expand...
 
Regarding the use of an Instructors table, an employee
could be an instructor for one training session and a
trainee for another. Everybody is a trainee at some time
during the year, but only 10% or so are also instructors.
An Instructors table would require a query, I think (an
append query?) based on the Employees table (to find those
for whom the Instructor box is checked), and would also
need to include names of people who are not employees.
Double entry of employee information is, of course, not a
sound option. The problem is that the Instructors table
would, ten years from now, still include the name of the
person who came in once to teach CPR. I could probably
filter that in some way, but it starts to become pretty
convoluted. In the end I decided that since I am storing
instructor information in a single field, storing the name
(instead of the PK) is a reasonable choice. Not as clean
as I would have liked, but I dont' see a problem.
Thanks again for your help and your insights on
ListIndex. I know I will be using that.
-----Original Message-----
I think maybe I mightprefer the separate instructors table,
but I'm not familiar with your entire set of needs.

Regardless, you are correct the ListIndex = -1 when the
entry in the combo box is not in its row source. This is an
alternative to using the NotInList event to detect that
situation.

Glad you got it working and good luck on the rest of your
project.
--
Marsh
MVP [MS Access]


It is rather unclear, isn't it? frmMain is to manage the
table (tblMain) that lists training sessions. Each record
contains Subject, Department, Instructor, etc. Instructor
is the [Name] field in my example. Most training is done
on the fly, so that if for instance a manufacturing
procedure is modified the affected employees receive
training. Instructors in such cases are other employees,
such as Supervisors. Some other types of training are
performed by outside instructors.
There is an Employees table, including a Yes/No field if
an Employee is also an Instructor. A query (qryName)
lists those for whom Instructor is checked. qryName
contains [EmployeeID], [Last,First], and [FirstLast].
These last two are concatenated values. qryName is the
Row Source for cboName on frmMain.
If training were conducted solely by a designated list of
instructors, the Instructor field in tblMain would
probably be the PK from an Instructor table. However,
outside instructors (e.g. First Aid instructors) would
need to be added to the Employee table, or to a separate
Instructor table, or something like that. Instead, I
decided to store the Instructor name (as text) in
tblMain. The problem was that elsewhere in the database
names are selected from a list in Last, First format, so I
wanted to keep it the same for selecting Instructor (when
the instructor is also an employee, and therefore on the
combo box list). At the same time, I wanted the name to
appear in the report in First Last format. I also wanted
a name typed into the combo box to appear on the report
exactly as typed. IN assembling the report, my thinking
was that Column(2) from the combo box would contain a
value only if a name is selected from the list. Otherwise
Column(2) contains a Null value, and the value typed into
the combo box would be used.
I am writing all of this even though I have the thing
working because it is possible that List Index is a better
way of doing things. If not, it is certainly something I
can use in the future, so I am tring to understand it
now. If I understand correctly, ListIndex = -1 is a way
of saying that the item is not in the combo box row
source: IIf(The combo box entry is not in the row source,
Use the typed value, Otherwise use Column(2)).
-----Original Message-----
There is another way to check if the combo box had an item
selected from the row source or if something was typed in.
I'm still not clear on where [Name] is coming from, but I
think this might do what you want??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName], [Name])

or maybe this??

=IIf([Forms]![frmMain]![cboName].ListIndex = -1,
[Forms]![frmMain]![cboName],
[Forms]![frmMain]![cboName.Column(2))
--
Marsh
MVP [MS Access]



Bruce wrote:

Thank you for the reply. To summarize what I was trying
to do, on the form (frmMain) is a combo box (cboName).
The row source for cboName is a SQL statement. Its
records source is [Name] from the form's underlying
table. Its Limit to List property is set to No, since
sometimes other names must be used. It has three columns,
with widths 0";1";0".
The SQL statement in datasheet view shows three columns,
EmployeeID, LastFirst and FirstLast. For example:
12345 Jones, Johnny Johnny Jones
The user selects Jones, Johnny from the list, but Johnny
Jones is to show up on the report. If Mary Martin is
typed in, that is to show up on the report. The record
source for the report (rptMain) is tblMain (actually, it
is a query derived from tblMain).
The best I could come up with was a text box (txtName) on
rptMain with the control source:
=IIf(IsNull([Forms]![frmMain]![cboName].Column(2)), [Name],
[Forms]![frmMain]![cboName].Column(2)). I referenced
Column(2) because it would be blank when the name is typed
in rather than being selected from the combo box list.
The problem I had been having before was that instead of
IsNull I was using = "". I am still trying to sort out
the difference between an empty string and a null value.
-----Original Message-----
Bruce wrote:

My form (frmMain) has a combo box (cboName) for Name.
The
record source for frmMain is tblMain. The record source
for cboName is [Name]. The row source is the SQL
statement SELECT qryInstructor.EmployeeID, [Last] & ", "
&
[First] AS LastFirst, [First] & " " & [Last] AS FullName
FROM qryInstructor. In query design view, that is a
three
column query. The first column is EmployeeID, the
second
is Last, First and the third column is First Last. The
second column is visible in the combo box, so that the
users select name from a list in Last, First format.
The
reason for the third column in the combo box is that on
the report the names need to appear as, for instance,
Johnny Jones rather than Jones, Johnny. However,
elsewhere in the database names are selected from combo
box lists where they appear in Last, First format.
Users
were complaining about the First Last format in just one
place on the form.
For purposes of a report I can concatenate the
Click to expand...
names,
so
that Jones, Johnny will appear on the report as Johnny
Jones. However, in the case explained above the users
are
not limited to the list. There is a good reason for
that,
but let it suffice to say that when Mary Martin is typed
into the combo box, her name needs to appear on the
report. The report (rptMain) has as its record source
qryMain, which is derived from and has the same fields
as
tblMain. Text box txtName on the report has as its
record
source the field [Name]. A name selected from cboName
on
the form will appear as Jones, Johnny, and the typed
name
will appear as Mary Martin, or exactly as typed. In
order
to have Johnny appear as Johnny Jones, I tried the
following code in txtName on the report:
=IIf([Forms]![frmMain]![cboName].Column(2)="", [Forms]!
[frmMain]![cboName],[Forms]![frmMain]! [cboName].Column
(2)).
To sum it up, [cboName].Column(2), or the literal third
column, is Johnny Jones, and [cboName] appears as Jones,
Johnny. However, Mary Martin does not show up at
Click to expand...
all
if
her name is typed in. In fact, the text box (on the
report) is set to be invisible when it contains a null
value, and it is invisible when a name is typed into the
combo box (on the form). Again, remember that her name
does show up when the control source for txtName is the
field [Name] from the report's underlying query.
However,
attempts to reference that field from within the
expression have so far failed. References to the field
[Name] in either qryMain or tblMain (e.g. [Tables]!
[tblMain]![Name],) produce a parameter dialog box asking
for Tables.


You should get the name info from the query.

I have no idea what the syntax [Tables]![tblMain]! [Name]
is
supposed to mean and I suspect that the prompt is an
indicator that it's not legal.

If [Name] is a field in the report's record source
table/query, then the text box can refer to it simply by
[Name]. But if that doesn't wotk, it's because you've
used
a reserved word in a context where Access can't
Click to expand...
figure
out
what you intended (you really should change that to
something else).

.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top