List box showing numbers instead of text

[Milothicus]

I'm having the exact same problem as this person. I looked through 20 pages of threads and couldn't find any help for a list box with more than 2 columns... mine has 12.

I guess i'm not as perceptive. I understand i need to include the list of possible values (they're in another table), but do i include thetwo ID fields as well? I think i've tried all possibilities with those, unless i need some kind of code to equate them.

how is this done?

also, i was trying to use the 'show' checkbox in the query design, but it just seemed to delete the columns for the next time i opened the query. how does this work and how is it used?

 

[KeithWilliams]

Hi Milothicus,

I've looked at the thread you linked to, and I'm not exactly sure what problem you are having. Would you post the SQL from your query, and describe what you see in form view, and what is not working.

Thanks,
Keith.

 

[Milothicus]

I think it's easier to describe than copy SQL's because i have the same problem 6 time over.

the data:
i have an 'industry' table that lists industry sectors for reference in an 'input' table as a lookup. of course, each sector has an autonumber associated with it, as a link to the field in the 'input' table. so...if i'm editing the 'input' table, i can use a droplist to choose one of the available sectors.


i have a query which selects info from the 'input' table, and (among other things) displays the industry sector for the filtered records. all that works fine. when i look at the query screen, the sector column displays the proper sector names, but when i set up a list box sourcing the query, the sector column displays the data from the autonumber column of tblIndSec instead of the sector column. it's the right data, but displaying the wrong column.

I have this same problem with 4 other columns in the list box.

in case you really want to read it, here's the SQL from the Search Query:

SELECT Input.Enquiry_Number, Input.Enquirer, Client_Info.Client_Name, Input.Project_Name, Input.Project_Detail, Input.Enquiry_Type, Input.Job_Type, Input.Medium, Input.Industry_Sector, Input.Date_Received, Input.Job_Number, Input.Quote_Date, Input.kw, Input.Value
FROM Client_Info INNER JOIN (Contact_Info INNER JOIN [Input] ON Contact_Info.Contact_ID = Input.Contact_ID) ON Client_Info.Client_ID = Contact_Info.Client_FK
WHERE (((Input.Enquirer) Like fCboSrch([Forms].[Enquiry]![frmSearch].[form]![cboEnqrrSrch])) AND ((Client_Info.Client_Name) Like "*" & fCboSrch([forms].[Enquiry]![frmsearch].[form]![txtClntSrch]) & "*") AND ((Input.Project_Name) Like "*" & fCboSrch([forms].[Enquiry]![frmsearch].[form]![txtNmSrch]) & "*") AND ((Input.Project_Detail) Like "*" & fCboSrch([forms].[Enquiry]![frmsearch].[form]![txtDtSrch]) & "*") AND ((Input.Enquiry_Type) Like fCboSrch([Forms].[Enquiry]![frmSearch].[form]![cboEnqyTypSrch])) AND ((Input.Job_Type) Like fCboSrch([Forms].[Enquiry]![frmSearch].[form]![cboJbTypSrch])) AND ((Input.Medium) Like fCboSrch([Forms].[Enquiry]![frmSearch].[form]![cboMedSrch])) AND ((Input.Industry_Sector) Like fCboSrch([Forms].[Enquiry]![frmSearch].[form]![cboSctrSrch])) AND ((Input.kw) Like fCboSrch([Forms].[Enquiry]![frmSearch].[form]![cboKwSrch])));

And the SQL for the listbox:

SELECT qrySrch.*
FROM qrySrch;

but.... the problem isn't in the query, as it displays fine when i open it. just the listbox won't display properly.

 

[KeithWilliams]

Hi,

Are you expecting that Input.Industry_Sector will display the text description from tblIndSec? I'm confused. Is the database small enough to include as an attachment? Or can you create a cut-down version that illustrates the problem, that you could attach?

Sorry, I can't understand the problem based on your description.

Keith.

 

[Milothicus]

Industry_Sector is a standard lookup that's referenced to a table of values. it actually stores a number, but displays a text string which is stored in its own table (tblIndSec), and they're linked through an ID in tblIndSec.

when i view the input table, or a form, or the query itself, it knows to look up the value in tblIndSec, but the listbox doesn't seem to know to look in tblIndSec for its value, and just displays the ID.

tblIndSec:

ID Value
1 | Education
2 | Industrial
3 | Government

etc.


in 'input' table:

Industry_Sector
Education
Government

in query:

Industry_Sector
Education
Government

in list box:

Industry_Sector
1
3

 

[KeithWilliams]

I presume you have defined the lookup property of Industry_Sector in Input to reference tblIndSec. Perhaps the lookup property is simply not used to determine the display of a listbox and you need to include tblIndSec in the query that populates the listbox. I'm still confused and could do with seeing it as an attachment.

Keith.

 

[Milothicus]

here's a stripped down version including gibberish data. it'll open to the form i'm having trouble with.

 

[KeithWilliams]

Hi Milothicus,

OK, got it. Because you have included the Industry Sector column as part of the row source for a listbox, Access has not used the lookup properties defined in the Input table definition. You need to amend the query you used as the row source for your listbox as follows, to join to tblIndSec:

SELECT [qrySrch].[Enquiry_Number], [qrySrch].[Enquirer], [qrySrch].[Client_Name], [qrySrch].[Project_Name], [qrySrch].[Project_Detail], [qrySrch].[Enquiry_Type], [qrySrch].[Job_Type], [qrySrch].[Medium], [tblIndSec].[IndSec], [qrySrch].[Date_Received], [qrySrch].[Job_Number], [qrySrch].[Quote_Date], [qrySrch].[kw], [qrySrch].[Value] FROM qrySrch INNER JOIN tblIndSec ON [qrySrch].[Industry_Sector]=[tblIndSec].[IndSecID];

You also need to increase the column count to 14, as I think you miscounted the columns in the query.

An alternative to achieve what you are doing with the Enquiry form would be to use a subform, perhaps with a datasheet format, to get a similar look and feel to what you are getting.

Hope that helps,
Keith.

 

[Milothicus]

thanks for the advice. The reason i went with the listbox was that i found some code on this forum for linking to a record with a single click. will that work as easily with a datasheet subform? I'll try that now, as i'm starting ove r with my forms anyway.....

 

[KeithWilliams]

Hi Milothicus,

To jump to a record when the user clicks on a row in the datasheet, you will need to write code in the On Click event of each column in the datasheet, I think. To avoid duplicating code, write a sub for the form to open the record you want based on the values in the current datasheet row. Then call the sub from the On Click event of each column.

I suspect you might find it less work to continue with the listbox method, if you can make that work for you and you like how it looks. I've never tried that.

Keith.

 

[Milothicus]

I've decided not to go with the listbox anymore because it seems easier to write a macro to open a certain record, than to 'innerjoin' my query to 6 different tables for all my combobox issues......

 

[Milothicus]

Changed my mind again. rather than coding all the INNER JOINs that i didn't know how to code anyway, I used the design view for the query and it gave me the following ugly code that works:

SELECT qrySrch2.Enquiry_Number, tblEnqrr.Enqrr, qrySrch2.Client_Name, qrySrch2.Project_Name, qrySrch2.Project_Detail, tblEnqTyp.EnqTyp, tblJobTyp.JobTyp, tblMedium.Medium, tblIndSec.IndSec, qrySrch2.Date_Received, qrySrch2.Job_Number, qrySrch2.Quote_Date, tblKw.kW, qrySrch2.Value
FROM tblKw INNER JOIN (tblIndSec INNER JOIN (tblMedium INNER JOIN (tblJobTyp INNER JOIN (tblEnqTyp INNER JOIN (tblEnqrr INNER JOIN qrySrch2 ON tblEnqrr.EnqrrID = qrySrch2.Enquirer) ON tblEnqTyp.EnqTypID = qrySrch2.Enquiry_Type) ON tblJobTyp.JobTypID = qrySrch2.Job_Type) ON tblMedium.MedID = qrySrch2.Medium) ON tblIndSec.IndSecID = qrySrch2.Industry_Sector) ON tblKw.KwID = qrySrch2.kw;