Display result of combo box selection on form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I found an answer on another question that I think is getting me closer, but
I am still getting errors.

Currently, on frmBugs, for the field CreatedBy, the user selects from names
displayed in a combo box (cboCreatedBy). The source of those names is: SELECT
[tblUsers].[UserID], [tblUsers].[User] FROM tblUsers;

I want the name selected in CreatedBy to appear on a report, but currently I
can only get the UserID to show up, or get an error.

I tried =[Forms]![frmBugs]![cboCreatedBy].Column(1) but it's returning an
error. None of the assorted DLookup attempts I've made work, either. Help?
 
MCB said:
I found an answer on another question that I think is getting me closer, but
I am still getting errors.

Currently, on frmBugs, for the field CreatedBy, the user selects from names
displayed in a combo box (cboCreatedBy). The source of those names is: SELECT
[tblUsers].[UserID], [tblUsers].[User] FROM tblUsers;

I want the name selected in CreatedBy to appear on a report, but currently I
can only get the UserID to show up, or get an error.

I tried =[Forms]![frmBugs]![cboCreatedBy].Column(1) but it's returning an
error. None of the assorted DLookup attempts I've made work, either. Help?
Click to expand...


Create a query to use as the report's RecordSource. Add
both the main table the report is currently based on AND
table tblUsers. Drag the main table field user ID field to
the UserID field in tblUsers. This should result in a
connecting line between the tow table/fields. If the main
table user ID field might not be specified, then right click
on the line and select Join Type from the little pop up
menu. Select the option Select all from table <main table>
and any matching records in tblUsers.

Now drag all the needed fields in the main table to the
field list and drag the User field from tblUsers down to the
query's field list so you can use it in the report.
 
Marshall Barton said:
MCB said:
I found an answer on another question that I think is getting me closer, but
I am still getting errors.

Currently, on frmBugs, for the field CreatedBy, the user selects from names
displayed in a combo box (cboCreatedBy). The source of those names is: SELECT
[tblUsers].[UserID], [tblUsers].[User] FROM tblUsers;

I want the name selected in CreatedBy to appear on a report, but currently I
can only get the UserID to show up, or get an error.

I tried =[Forms]![frmBugs]![cboCreatedBy].Column(1) but it's returning an
error. None of the assorted DLookup attempts I've made work, either. Help?
Click to expand...


Create a query to use as the report's RecordSource. Add
both the main table the report is currently based on AND
table tblUsers. Drag the main table field user ID field to
the UserID field in tblUsers. This should result in a
connecting line between the tow table/fields. If the main
table user ID field might not be specified, then right click
on the line and select Join Type from the little pop up
menu. Select the option Select all from table <main table>
and any matching records in tblUsers.

Now drag all the needed fields in the main table to the
field list and drag the User field from tblUsers down to the
query's field list so you can use it in the report.
Click to expand...

I've already tried to do that. The report's RecordSource is
qryProjectsReport. The report isn't based on one main table; it's based on
three: tblProjects, tblFunctionSets, and tblBugs (the theory being a
cascading one - a project can have multiple functions sets, and each function
set can have multiple bugs). The field I was asking about (CreatedBy) is in
tblBugs. All three tables are in the query.

I have tried adding tblUsers to the query, but when I do it "breaks" (i.e.
no records show up at all). Currently there are three fields that pull their
combo box records from tblUsers (CreatedBy, Owner, and Signoff). I thought
that's what was breaking it, but I haven't been able to get it to work when I
change the relationship so it only involves one of the three, e.g.
[tblUsers].[UserID] joined to [tblBugs].[CreatedBy], but that doesn't work,
either.
 
MCB wrote:
MCB said:
I found an answer on another question that I think is getting me closer, but
I am still getting errors.

Currently, on frmBugs, for the field CreatedBy, the user selects from names
displayed in a combo box (cboCreatedBy). The source of those names is: SELECT
[tblUsers].[UserID], [tblUsers].[User] FROM tblUsers;

I want the name selected in CreatedBy to appear on a report, but currently I
can only get the UserID to show up, or get an error.

I tried =[Forms]![frmBugs]![cboCreatedBy].Column(1) but it's returning an
error. None of the assorted DLookup attempts I've made work, either. Help?
Click to expand...
Click to expand...
Marshall Barton said:
Create a query to use as the report's RecordSource. Add
both the main table the report is currently based on AND
table tblUsers. Drag the main table field user ID field to
the UserID field in tblUsers. This should result in a
connecting line between the tow table/fields. If the main
table user ID field might not be specified, then right click
on the line and select Join Type from the little pop up
menu. Select the option Select all from table <main table>
and any matching records in tblUsers.

Now drag all the needed fields in the main table to the
field list and drag the User field from tblUsers down to the
query's field list so you can use it in the report.
Click to expand...
Click to expand...
MCB said:
I've already tried to do that. The report's RecordSource is
qryProjectsReport. The report isn't based on one main table; it's based on
three: tblProjects, tblFunctionSets, and tblBugs (the theory being a
cascading one - a project can have multiple functions sets, and each function
set can have multiple bugs). The field I was asking about (CreatedBy) is in
tblBugs. All three tables are in the query.

I have tried adding tblUsers to the query, but when I do it "breaks" (i.e.
no records show up at all). Currently there are three fields that pull their
combo box records from tblUsers (CreatedBy, Owner, and Signoff). I thought
that's what was breaking it, but I haven't been able to get it to work when I
change the relationship so it only involves one of the three, e.g.
[tblUsers].[UserID] joined to [tblBugs].[CreatedBy], but that doesn't work,
either.
Click to expand...


Sorry, but "doesn't work" just isn't much to go on. No
records at all strikes me as an indicator that the Join is
not set up properly, either it's the wrong field, wrong type
or the bracketing is inappropriate. Can you get it to work
with just the project(?) (or is it bugs?) and user tables?

The only significant difference between using a just a table
and and using a query is the you have more Joins to keep
straight. I might be able to help sort that out if I knew
what each table's primary key, foreign keys and important
data fields are.
 
Marshall Barton said:
MCB wrote:
MCB wrote:
I found an answer on another question that I think is getting me closer, but
I am still getting errors.

Currently, on frmBugs, for the field CreatedBy, the user selects from names
displayed in a combo box (cboCreatedBy). The source of those names is: SELECT
[tblUsers].[UserID], [tblUsers].[User] FROM tblUsers;

I want the name selected in CreatedBy to appear on a report, but currently I
can only get the UserID to show up, or get an error.

I tried =[Forms]![frmBugs]![cboCreatedBy].Column(1) but it's returning an
error. None of the assorted DLookup attempts I've made work, either. Help?
Click to expand...
Marshall Barton said:
Create a query to use as the report's RecordSource. Add
both the main table the report is currently based on AND
table tblUsers. Drag the main table field user ID field to
the UserID field in tblUsers. This should result in a
connecting line between the tow table/fields. If the main
table user ID field might not be specified, then right click
on the line and select Join Type from the little pop up
menu. Select the option Select all from table <main table>
and any matching records in tblUsers.

Now drag all the needed fields in the main table to the
field list and drag the User field from tblUsers down to the
query's field list so you can use it in the report.
Click to expand...
Click to expand...
MCB said:
I've already tried to do that. The report's RecordSource is
qryProjectsReport. The report isn't based on one main table; it's based on
three: tblProjects, tblFunctionSets, and tblBugs (the theory being a
cascading one - a project can have multiple functions sets, and each function
set can have multiple bugs). The field I was asking about (CreatedBy) is in
tblBugs. All three tables are in the query.

I have tried adding tblUsers to the query, but when I do it "breaks" (i.e.
no records show up at all). Currently there are three fields that pull their
combo box records from tblUsers (CreatedBy, Owner, and Signoff). I thought
that's what was breaking it, but I haven't been able to get it to work when I
change the relationship so it only involves one of the three, e.g.
[tblUsers].[UserID] joined to [tblBugs].[CreatedBy], but that doesn't work,
either.
Click to expand...


Sorry, but "doesn't work" just isn't much to go on. No
records at all strikes me as an indicator that the Join is
not set up properly, either it's the wrong field, wrong type
or the bracketing is inappropriate. Can you get it to work
with just the project(?) (or is it bugs?) and user tables?

The only significant difference between using a just a table
and and using a query is the you have more Joins to keep
straight. I might be able to help sort that out if I knew
what each table's primary key, foreign keys and important
data fields are.
Click to expand...

By "doesn't work", what I mean is that if I am in Design View of the query
when only tblProjects, tblFunctionSets, and tblBugs are included, then switch
to Datasheet View, records are displayed. If I add tblUser to the query, then
switch to Datasheet View, no records are displayed, not even a single blank
one. When I add tblUser to the query, the existing joins are there. There is
a one-to-many relationship between [tblUsers].[UserID] and
[tblBugs].[CreatedBy], [tblBugs].[Owner], and [tbl.Bugs].[Signoff]. When I
look at the join properties in the query, the radio button checked is "Only
include rows where the joined fields from both tables are equal".

I have not had any success with any query arrangement involving tblUsers. At
best I get an "ambiguous outer joins" error, and I'm not sure where to begin
fixing that.
 
PMFJI (and Top Posting)
Did you add tblusers in only once and attempt to join it to 3 different
tables? With 3 different joins, you need 3 copies of tblUsers.

--
Duane Hookom
MS Access MVP
--

MCB said:
Marshall Barton said:
MCB wrote:
MCB wrote:
I found an answer on another question that I think is getting me
closer, but
I am still getting errors.

Currently, on frmBugs, for the field CreatedBy, the user selects from
names
displayed in a combo box (cboCreatedBy). The source of those names
is: SELECT
[tblUsers].[UserID], [tblUsers].[User] FROM tblUsers;

I want the name selected in CreatedBy to appear on a report, but
currently I
can only get the UserID to show up, or get an error.

I tried =[Forms]![frmBugs]![cboCreatedBy].Column(1) but it's
returning an
error. None of the assorted DLookup attempts I've made work, either.
Help?


:
Create a query to use as the report's RecordSource. Add
both the main table the report is currently based on AND
table tblUsers. Drag the main table field user ID field to
the UserID field in tblUsers. This should result in a
connecting line between the tow table/fields. If the main
table user ID field might not be specified, then right click
on the line and select Join Type from the little pop up
menu. Select the option Select all from table <main table>
and any matching records in tblUsers.

Now drag all the needed fields in the main table to the
field list and drag the User field from tblUsers down to the
query's field list so you can use it in the report.
Click to expand...
MCB said:
I've already tried to do that. The report's RecordSource is
qryProjectsReport. The report isn't based on one main table; it's based
on
three: tblProjects, tblFunctionSets, and tblBugs (the theory being a
cascading one - a project can have multiple functions sets, and each
function
set can have multiple bugs). The field I was asking about (CreatedBy) is
in
tblBugs. All three tables are in the query.

I have tried adding tblUsers to the query, but when I do it "breaks"
(i.e.
no records show up at all). Currently there are three fields that pull
their
combo box records from tblUsers (CreatedBy, Owner, and Signoff). I
thought
that's what was breaking it, but I haven't been able to get it to work
when I
change the relationship so it only involves one of the three, e.g.
[tblUsers].[UserID] joined to [tblBugs].[CreatedBy], but that doesn't
work,
either.
Click to expand...


Sorry, but "doesn't work" just isn't much to go on. No
records at all strikes me as an indicator that the Join is
not set up properly, either it's the wrong field, wrong type
or the bracketing is inappropriate. Can you get it to work
with just the project(?) (or is it bugs?) and user tables?

The only significant difference between using a just a table
and and using a query is the you have more Joins to keep
straight. I might be able to help sort that out if I knew
what each table's primary key, foreign keys and important
data fields are.
Click to expand...

By "doesn't work", what I mean is that if I am in Design View of the query
when only tblProjects, tblFunctionSets, and tblBugs are included, then
switch
to Datasheet View, records are displayed. If I add tblUser to the query,
then
switch to Datasheet View, no records are displayed, not even a single
blank
one. When I add tblUser to the query, the existing joins are there. There
is
a one-to-many relationship between [tblUsers].[UserID] and
[tblBugs].[CreatedBy], [tblBugs].[Owner], and [tbl.Bugs].[Signoff]. When I
look at the join properties in the query, the radio button checked is
"Only
include rows where the joined fields from both tables are equal".

I have not had any success with any query arrangement involving tblUsers.
At
best I get an "ambiguous outer joins" error, and I'm not sure where to
begin
fixing that.
Click to expand...
 
MCB wrote:
[snip]
I have not had any success with any query arrangement involving tblUsers. At
best I get an "ambiguous outer joins" error, and I'm not sure where to begin
fixing that.
Click to expand...


Duane raises an excellent point here. Did you try that?
 
Back
Top