Trouble passing named subqueries

D

digitalshepard

Registered User.
Local time
Today, 14:43
Joined
Jul 20, 2011
Messages
32
I'm sure this is a simple issue, but I don't know what terms to search for.

I want to display all fields and records from tbl_Document that meet the criteria specified from qry_Documents. I'm trying to do this using a WHERE statement, but every time I run the query I'm asked to provide a value for SortList. I'm looking at similar code and not sure what the issue is.

Code: 
SELECT
            (SELECT Doc_ID FROM qry_Documents) as SortList,* 
FROM tbl_Document 
WHERE (SortList)=(Doc_ID);
 
An inner join would work for that specific purpose, but has unintended consequences in my database. I'm writing qry_documents in lieu of a VBA built search function that feeds a form recordset. The form has subform fields containing multiple entries linked to the main record. I currently use inner joins to integrate those linked tables visible in the subforms to the main table, but in the process the subforms go blank. My thinking is if I can feed a list of IDs to pull from my main table and use that to feed the form recordset, those subform entries will again become visible. Using an inner join at this point would negate the main purpose.

So I suppose the question is can I do this without using inner joins. :)
 
To be honest I can't visualise your setup from what you've just explained. In any case, whether you feed it values from a subquery or you create an INNER JOIN, one way or the other you're still creating a join between the source table and the subquery.

I think you should re-think your approach.
 
vbaInet - I took your advice and am rethinking my approach. I am still trying to avoid using Join queries as that breaks my subforms, however I did come with something that almost works using RecordSet.

The following code works, but only returns 1 result. I want to return all results but have yet to find the syntax. Any ideas?
varSQL = "SELECT Doc_ID FROM qry_Documents"
Dim dbsCurrent As Database
Dim rsCount As Recordset

Set dbsCurrent = CurrentDb
Set rsCount = dbsCurrent.OpenRecordset(varSQL)
BuildFilter = "SELECT * FROM tbl_Document Where [Doc_ID]= " & rsCount![Doc_ID]
rsCount.Close
Click to expand...

(FYI, BuildFilter is the record source for a form)
 
How does it break your subform? If your subform is coming up blank for some records then it simply means it can't find a match in the parent record.

I still cannot visualise what you're doing with that recordset code. I don't even see the need for it. Note that the code you write is only pointing to the first record in the recordset.
 
I'll try again to describe what's happening with my database that is leading me to stay away from join statements.

The level 1 parent form contains search criteria and buttons which trigger the VBA search function.

The level 2 subform displays results from tbl_Document based on the level 1 search form. This works great.

I also have level 3 subforms embedded in the level 2 subform which pull data lookup tables linked to tbl_Document based on Doc_ID because some fields in tbl_Document have multiple records.

So the challenge is how to select search terms for those lookup tables in the level 1 parent form. Inner joins statements in the search builder work in selecting the correct documents, but when I inner join those lookup tables to tbl_Document, the level 3 subforms no longer show any data. It seems like once the lookup table is joined to the main table, Access no longer sees that table and can't get data from it.

I know the recordset code I currently have only pulls one record, but as I'm just learning how to use it I figure there should be a way to pull all the records I need. Its a creative way to bypass the inner joins, and when using that method the level 3 subforms DO show data.

Does that make more sense?
 
I have come full circle in my approach, and am no closer to solving the issue.

I created a temp table to hold Doc_ID returned from a search query. Then I tried to pull records from tbl_Document where the Doc_ID match. One method was using INNER JOIN. However, when I try INNER JOIN the level 2 subform shows no results even with no search criteria. My take away is you can pull data into a form from a query that contains an inner join.

So the original question stands - how can I pull records from a table where the Doc_ID of one table equals the Doc_ID of another without using a JOIN statement?
 
digitalshepard said:
So the original question stands - how can I pull records from a table where the Doc_ID of one table equals the Doc_ID of another without using a JOIN statement?
Click to expand...
I don't understand why you seek another solution when an Inner Join is the most effecient.
 
vbaInet, I appriciate your continued help and patience.

I would use the inner join method, except that when I do the subforms displaying information from the tables that were inner joined no longer display any information, even through the correct records are returned on the main form. That is why I don't think I can use inner joins.

So far, the closest I've come to making this work is using the method from http://www.access-programmers.co.uk/forums/showpost.php?p=1121397&postcount=5 The only issue there is figuring out how to pull all the records from the recordset, not just the first one. Perhaps some sort of script would accomplish that?
 
Well, if you don't think the Inner Joins will not work or are having doubts, the first thing you do is ask and don't just discount it. Using an Inner Join is the best kind of join that you use with subqueries.

Can you show me a sample db of the problems you're facing.
 
I have uploaded a samble db. Notice how the 'color' field shows no records when searched.

Again, thanks for your help.
 

Attachments

So what forms and/or queries should I be looking at?

What are the steps for replicating the problem?
 
To replicate the issue:
In the frmSearch window that pops up you will see 7 controls which build a query in VBA, which in turn generates results in the subform frmsubClients. One of those 7 controls is a color drop down - this is the control where the issue is. Pick a color to search from, and then look at the results in the subform frmsubClients. You will notice that in the subform, ClientID and Colors are blank although the records returned are correct for the color you picked.

The inner join query in question is located in the VBA code from frmSearch in:
Public Function BuildFilter ->
varfrom = "(" & varfrom & "INNER JOIN linktbl ON " & varSource & ".ClientID = linktbl.ClientID) "
 
Attached is how you would do it using an INNER JOIN.

I stipped off some redundant code too.
 

Attachments

Thank you vbaInet. As it turns out, all my code was missing was DISTINCTROW. The reason my from query strings in the vba had extra text was to support multiple Inner Joins. All works well now. I can't thank you enough!
 
Well, the primary thing your code was missing was DISTINCTROW but they were other things that would have prevented your code from functioning properly which I amended. Compare your code with mine and you will notice the differences. Start from the Variables Initialization part.

Happy developing!
 
You must log in or register to reply here.

Similar threads

A
Query for Pass/Fail/Totals
Replies
5
Views
673
ashleymac
A
P
Automation 'constants'
Replies
13
Views
368
MarkK
MarkK
D
Need help with SQL in an Access query that has UNION for the non-relational queries/tables
2
Replies
26
Views
975
dlhappy
D
K
Query to to exclude records with a date within last 4 months, and show records where most recent date is older than that
Replies
9
Views
587
kevin_maher
K
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
812
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom