Re: Display result of combo box selection on form?

From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 11/30/04 

Date: Tue, 30 Nov 2004 08:15:44 -0600

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" <MCB@discussions.microsoft.com> wrote in message 
news:CE2B5112-7E76-4ECC-A255-139357A43ADE@microsoft.com...
>
>
> "Marshall Barton" wrote:
>
>>
>> >
>> >> 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?
>> >>
>> >>
>> >"Marshall Barton" wrote:
>> >> 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.
>> >
>> MCB wrote:
>> >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.
>>
>>
>> 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.
>
> 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 Data*** View, records are displayed. If I add tblUser to the query, 
> then
> switch to Data*** 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.