Get more than one value from DB

[Dartagnan]

I am trying to retrieve all records that match my criteria. Unfortunately, the DLookup only returns the first records and so does a nester query.

How do I get Access to bring back all records?

Thank you in advance.

 

[Norie]

Could you explain your data structure and also what's a 'nester' query?

 

[Dartagnan]

Sorry meant to say nested query.

The following query is nested.

(select staffid from tblstaffspecialty where specialty = [forms]![application log]![combo145])

The combo box is a list of specialties like ABD or SSO. Once this is passed to the nested query I get a list of staff ids with that specialty. Unfortunately, the nested query can only return one value. The problem I have is more than one staff member can have the same specialty. As a result the query breaks because more than one is found.

I tried a dlookup as well, but the dlookup only returns the top value.

 

[Norie]

I don't see how such a query would only return 1 value.

But then I don't know how your data is structured.

 

[Dartagnan]

Here is the entire sql behind my form.

SELECT [Application Log].Index, [Application Log].ApplicationDate, [Application Log].[Date Assigned to ET], [Application Log].[Date Received], [Application Log].[Case Number], [Application Log].[Case Action], [Application Log].[Case LName], [Application Log].[Case FName], [Application Log].Program, [Application Log].ET, [Application Log].StaffID, [Application Log].[Application Type], [Application Log].[Delivery Method], [Application Log].[Re-Application], Staff.tbl1stSpokenLanguage, Staff.tbl2ndSpokenLanguage, Staff.tbl3rdSpokenLanguage, Staff.tblClassification, [Application Log].IDT, [Application Log].MC38, [Application Log].Caseload, Staff.tblOffice, [Application Log].EnteredBy, Staff.MCLog
FROM [Application Log] INNER JOIN (Caseworker INNER JOIN Staff ON Caseworker.StaffID = Staff.StaffID) ON [Application Log].ET = Caseworker.ET
WHERE ((([Application Log].[Case Number]) Is Null) AND (([Application Log].Program)="MC") AND ((Staff.tblOffice)=(SELECT Staff.tbloffice FROM Staff WHERE (((Staff.Login)=Environ("username"))))) AND ((Staff.MCLog)=Yes) AND ((([Application Log].[ET]) Like [forms]![application log]![combo148]) Is Null) AND ((([Application Log].[StaffID]) Like [forms]![application log]![combo145]) Is Null)) OR ((([Application Log].[Case Number]) Is Null) AND (([Application Log].Program)="MC") AND (([Application Log].StaffID)=(select staffid from tblstaffspecialty where specialty = [forms]![application log]![combo145])) AND ((Staff.tblOffice)=(SELECT Staff.tbloffice FROM Staff WHERE (((Staff.Login)=Environ("username"))))) AND ((Staff.MCLog)=Yes)) OR ((([Application Log].[Case Number]) Is Null) AND (([Application Log].Program)="MC") AND (([Application Log].ET)=[forms]![application log]![combo148]) AND ((Staff.tblOffice)=(SELECT Staff.tbloffice FROM Staff WHERE (((Staff.Login)=Environ("username"))))) AND ((Staff.MCLog)=Yes))
ORDER BY [Application Log].Index;

 

[Richard Lane]

If I understand this---you have put the SQL into the form to drive the functionality of the form. All D functions such as DLookup will only return one value. As far as I'm concerned, you should be using a query --this query should be used by the form as its recordsource. Use QBF (Query By Form) in the query to use data from the form to drive the query as well.