Combo box query (1 Viewer)

[sstasiak]

I have a combo box that gets its values from a query. The query only shows those records whose yes/no field[ClosedIssue] value is equal to no.

This works, but where the description should show up in the combo box, I get the ID number associated with that description from its original table[IssueType]. How can I make it so that instead of the number being displayed, the actual text is displayed?

 

[rainman89]

adjust your column widths set the first column width to 0" and the one with the desciption to mayb 1" or bigger

 

[sstasiak]

I don't think I explained correctly....

In my main form, frmIssues, I have 3 combo boxes among other fields. With these 3 combo boxes, you can select:
1. AnalystName
2. SystemName
3. IssueType

These combo boxes each get their values from 3 seperate tables: tblAnalyst, tblSystem, and tblIssueType. The data entered into frmIssues is stored in my main table, tblMain. When I create a record, my main table holds the ID# associated with the AnalystName, SystemName, and IssueType instead of the actual text. There is also a yes/no field on my main form and table that dictates if a service ticket is closed or not.

I created a combo box to get its values from a query that should only display records where that yes/no field is "No", or issues that are not closed. The query gives me the right records in the combo box, but for the AnalystName, SystemName, and IssueType columns, I get only the ID stored in tblMain, and not the text. I want the text there instead of the ID number.

I could change the bound column of the 3 combo boxes, but then I'd have the text being stored in the main table. I don't really want to do this.

Does this make sense

 

[rainman89]

right!

you should not store the txt.

if you want to get the value from the Id try a Dlookup

 

[pbaldy]

I agree with Ray about storing the ID rather than the text, but the more efficient way to display the text is a query that joins the two tables rather than a DLookup.

 

[sstasiak]

OK, so if I have my main table, tblMain, with the 3 fields in question: AnalystName(data in tblAnalyst with fields[AnalystID] and [AnalystName]), SystemName(data in tblSystem:[SystemID] and [SystemName]), and IssueType(data in tblIssue:[IssueID] and [IssueName])

What would that join query look like given the above data?

 

[rainman89]

use the query builder and add both tables selecting ID# from the main table and name from the analyst table. that should show u what values are associated with what name..

I get only the ID stored in tblMain, and not the text. I want the text there instead of the ID number.

As long as you can reference it later and get the proper values. what is stored in the table should not matter seeing as you should not be seeing tables anyways

 

[sstasiak]

Ray

This doesn't seem to work. It shows the correct ID for the analyst assigned to the CURRENT record, but the actual name stays the same no matter what the ID is. Not sure how to do this....

I need the 3 fields I mentioned above to ALL be in the new combo box. When I click the drop down arrow, I want to see all records whose yes/no field in the main table is set to "NO". Once those are filtered out, I want to see the TicketID(in main table), AnalystName(the actual name, not the ID# that's stored in the main table), SystemName(again, the text, not the ID#), and IssueType(text, not ID#). Not sure how to create a query to do ALL these things...

 

[rainman89]

can u post a sample of your db? itll probably be easier to show you then to explain it here

 

[sstasiak]

Do you want screenshots, or can I actually attach the DB somehow?

 

[spasticus]

if you create a query in design view and add the tables

-tblAnalyst
-tblMain

question - what value do you want to store with the combo box? i mean, do you want to store the TicketID or AnalystID? don't think it matters i was wondering if you needed the TicketID field in the query.

from tblMain add TicketID, AnalystID, and ClosedIssue
then
from tblAnalyst add AnalystName
then
in the criteria for ClosedIssue put False
save it.

open your form and draw a new combo box.
set the record source as that query.
hide AnalystID and ClosedIssue.

finish and see if that does what you wanted.

-- i think thats what rainman89 was getting at.

 

[rainman89]

That IS what i meant.. i was just feeling lazy and didnt wanna type!

 

[sstasiak]

spasticus

In the combo box, I need the ticketID, AnalystName, System name, and Issue description. right now I'm getting the ID's for all those fields and not the text. haven't tried your way yet....doing it now.

 

[rainman89]

if you are geting IDs then just add all the names to the query also

 

[rainman89]

I think this is what u mean?

 

[sstasiak]

I followed the instructions above, and in my combo box I'm now seeing the ticketID, of the one open test ticket, but it's showing the ticketID 8 times with ALL 8 analyst names corresponding to ticket #2. It needs to show just the one analyst assigned to the open ticket

 

[rainman89]

see the sample that i posted for ya! maybe thats what u are looking for

 

[sstasiak]

Thanks for the help guys....I got it working finally. I had to make some corrections in my field types for it to work, but that's something I missed when I first created the tables.