Listbox Columns, Queries & Relationships 1

[Najemikon]

Hi all,

Well, that's it! I give up & freely admit I haven't got a clue how to build a listbox correctly when I'm using related tables. This is despite the fact I've done it quite a few times. Usually I stumble over the answer, but I can never see what I did, nor recreate it.

I have a form with a listbox. Behind the listbox is a query from a table. Two fields on the table are lookup fields linked to two other tables:

MainTable query: CustomerID / ItemID
CustomerTable: CustomerID / CustomerName
ItemTable: ItemID / ItemName

So, CustomerID & ItemID on MainTable are actually the primary keys / record IDs of the related tables. Here is the basis of my problem: when I run the query on its own, it displays the text; when I run the listbox, it just displays the numbers! For example:

MainTable query: CustomerID= "Name of Customer" / ItemID= "Name of Item"
Listbox running MainTable query: CustomerID= 27 / ItemID= 521

It's frustrating that the query works, surely showing my relationships are correct, but the listbox won't show the same. I must be missing something simple!


Jon

 

[PHV]

What is the SQL code of the listbox's rowsource ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Najemikon]

Hi PHV,

For simplicity in the previous post I used generic names. These are the actual. The listbox SQL is:

SELECT [tblApplications].[appRecord], [tblApplications].[appCustomer], [tblApplications].[appName] FROM [tblApplications];


Additionally, this is the SQL behind the look up field (appName) from the table tblApplications: SELECT tblSoftware.sfwRecord, tblSoftware.sfwName FROM tblSoftware

And appCustomer on the same table: SELECT tblCustomers.CustomerNumber, tblCustomers.CustomerName FROM tblCustomers

 

[PHV]

And what about something like this for the listbox's RowSource ?
SELECT A.appRecord, C.CustomerName, S.sfwName
FROM (tblApplications AS A
INNER JOIN tblCustomers AS C ON A.appCustomer = C.CustomerNumber)
INNER JOIN tblSoftware AS S ON A.appName = S.sfwRecord;

BTW, hopefully you didn't use LookUp Fields !

http://www.mvps.org/access/lookupfields.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Najemikon]

Inner Join! Never really understood how to use it & therein lies the secret. Worked straight away, thank you.

Two points arise from this. First, I thought your SQL statement was abbreviated ("A", "C" & "S") for ease of typing the post & I started to replace them with the actual table names until I realised it was doing that itself. I'm very impressed! I never knew you could do that. Makes the statement much shorter & easier to read. In another db I use VBA to specify the SQl & the statements are massive because I use a lot more than two fields. So that's a great tip in itself!

Second though, I followed your link about Look Ups? I used them here & all over the place! Always have. I didn't realise it could bloat the db, that's for sure, but I've only ever had a problem with displaying on the listbox; all the queries, reports & subforms display properly. But does that imply my relationships are working overtime & bloating?

I'll certainly review. Thank you so much for showing me that so quickly.


Jon

 

[PHV]

Inner Join! Never really understood how to use it
Yet another usefull link for you:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886