Linked data on forms

[Peter Ramshaw]

Since posting I tried running the 'Customer Extended" query mentioned below and it DOES find all the records so my initial work arounds may be misconceived.
I suppose it must be an oject that is evoked after bthat query is run that is causing my proble.
In case it helps, here's the 'Row Source' expression tied to the actual 'Customer' field generated by the query:

SELECT [Customers Extended].ID, [Customers Extended].[Customer Name] FROM [Customers Extended] ORDER BY [Customers Extended].[Customer Name];

Any ideas?
Peter R

==================
Hi guys and gals, I hope someone can point me in the right direction.
I have two tables in my database that are linked by an ID field.
One table (Cases) holds case names and comments and status that link back to the contacts details in my main database (in fact the database uses the links in the MS sample database for Customer service or something so I'm not claiming I wrote it.
In this database a form called 'Case List' can be opened. There you can add new cases by searching for conbact's names and assignbing a staus and case name to that record.
Trouble is, the query that generates the 'Case List' (see expression used below) sometimes fails to pick up some name whjich we know are in our main table (Contacts). To get around this I need to either a) modify the lookup below so all records are displayed in the 'Case List' form or b) somehow place a field from Table:Cases (say the Staus one) on a form that does display all our records so I can make that field 'Active' which is then picked up by the query roiutine for the 'Case List' datasheet.
Now no matter how I attempt this, as soon as I put the 'Status' field on a form connected to my 'Contacts' table that form only displays records where there is a status active (like 48 active records out of the 30,000 in the database. Obviously, when I take the field off my normal form, it displays all 30,000+ records again.
There's got to be a simple way around this (I tried chaging all of the fields atributes on the 'normal' form but to no effect.

Anyway, here's the expressions used in the query that runs on opening the 'Case List' datasheet. Somehow it is missing records and I can't work out why. Prob ablky has somethignt o do with Null or "" fields but I can't get past that. It's called 'Cust,mers Extended' and used in a lot of those sample dBases.

Field 1:
File As: IIf(IsNull([Surname]),IIf(IsNull([First Name(s)]),[Company or Group],[First Name(s)]),IIf(IsNull([First Name(s)]),[Surname],[Surname] & ", " & [First Name(s)]))

Field 2:
Customer Name: IIf(IsNull([Surname]),IIf(IsNull([First Name(s)]),[Company or Group],[First Name(s)]),IIf(IsNull([First Name(s)]),[Surname],[First Name(s)] & " " & [Surname]))

Field 3:
Contacts.*

Cheers
Peter R

 

[boblarson]

What is the entire SQL of the query Customers Extended?

 

[Peter Ramshaw]

Thankis Bob. its:
SELECT IIf(IsNull([Surname]),IIf(IsNull([First Name(s)]),[Company or Group],[First Name(s)]),IIf(IsNull([First Name(s)]),[Surname],[Surname] & ", " & [First Name(s)])) AS [File As], IIf(IsNull([Surname]),IIf(IsNull([First Name(s)]),[Company or Group],[First Name(s)]),IIf(IsNull([First Name(s)]),[Surname],[First Name(s)] & " " & [Surname])) AS [Customer Name], Contacts.*
FROM Contacts
ORDER BY IIf(IsNull([Surname]),IIf(IsNull([First Name(s)]),[Company or Group],[First Name(s)]),IIf(IsNull([First Name(s)]),[Surname],[Surname] & ", " & [First Name(s)])), IIf(IsNull([Surname]),IIf(IsNull([First Name(s)]),[Company or Group],[First Name(s)]),IIf(IsNull([First Name(s)]),[Surname],[First Name(s)] & " " & [Surname]));

But, as I indicated it actually does pick up the data I need but when I type a name into the 'Customer' field on a form, there are records in the database that are not returned.

Cheers
Peter R

 

[boblarson]

But, as I indicated it actually does pick up the data I need but when I type a name into the 'Customer' field on a form, there are records in the database that are not returned.

That statement doesn't make sense given what you have provided earlier. And you don't type into a customer field on a form you type into a control on a form which can be bound to a field. If it is bound to a field why is it? You don't type into a bound control in order to search for something. You type, or select from, an UNBOUND control and there must be something to return and something which uses that as the criteria. But you have not provided anything with any criteria on it. And you have not provided the process which you are going through and how it is supposed to search for the particular record.