OK.I'll try to put in enough details to represent what I'm doing. For
the top half I've changed the names to convey what I'm doing in
familiar terms. (The database is not about books and dealers, etc, but
it could be.)
TABLES: Books, Authors, Dealers, Subjects
Join Tables: jtblBookByAuthor, jtblBookBySubejct, jtblBookByDealers
tblBooks
-BookID PK
-BookName
-AuthorsID FK
-BookTitle
-BookDate
tblAuthors
-AuthorID PK
-FirstName
-LastName
tblDealers
-DealerID PK
-DealerName
tblSubjects
-SubjectID PK
-SubjectName
-SubjectDescription
jtblBookByAuthor
-BookID FK--------\_____PK
-AuthorID FK------/
(All the join tables have the same setup as the one above.
FORM: frmSearch controls:
-cbxAuthor unbound combobox {rowsource="SELECT [tblAuthor].[AuthorID],
[Author].[FirstName] & " " & [tblAuthor]![LastName] AS FirstSpaceLast
FROM tbAuthor}
-lbxDealers unbound Multi-selection(extended) List Box
{rowsource="SELECT [tblDealer].[DealerID], [tblDealer].[DealerName]
FROM tbDealer}
(other controls mimic the 2 above)
The user makes selections in the controls, then clicks on a "Search"
command button. Code parses all of the control values to create WHERE
clauses for a SQL statement against their respective join tables. i.e.
For Dealers it would be: "WHERE (((jtbBookBylDealer.DealerID)=5 OR
(jtblBookByDealer.DealerID)=25)"
The where clauses are connect to their respective SELECT clause. i.e.
Dealer would be: "SELECT [jtblBookByDealer].[BookID],
[jtblBookByDealer].[DealerID]
FROM jtbBookByDealer WHERE (((jtbBookBylDealer.DealerID)=5 OR
(jtblBookByDealer.DealerID)=25)"
My thoughts were to get a recordset of BookIDs from each join-table
that met the criteria slected in the search form controls.
I created a long UNION stamen joining all of the SELECT statements
similar to the one above. (FYI, I have a total of 6 SELECT stamens
UNION'd together.)
SELECT jtbBookByDealer.BookID, jtbBookByDealer.DealerID FROM
jtblBookByDealer WHERE (((jtbBookBylDealer.DealerID)=5 OR
(jtblBookByDealer.DealerID)=25)
UNION
SELECT jtblBookBySubject.BookID, jtbBookBySubject.SubjectID FROM
jtbBookBySubject WHERE (((jtblBookBySubject.DealerID)=151))
UNION
....etc.
----------Actual code------no name changes
Code in Search button CLICK event:
Private Sub cmbSearch_Click()
On Error GoTo Err_cmbSearch_Click
Dim strSQLDelete As String
Dim strSQL As String
Dim strDetailsSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsKeys As DAO.Recordset
Dim i As Integer
Dim strSQLSearchKeys
----------create an SQL to retreive the BookID values that meet the
search criteria.------
strSQLSearchKeys = "SELECT tblSearchResults.tblOPLID,
tblSearchResults.tblSearchResults FROM tblSearchResults;"
'create an SQL that clears the records returned from the last search
strSQLDelete = "DELETE tblSearchResults.tblOPLID,
tblSearchResults.tblSearchResults" & _
" FROM tblSearchResults;"
---------create an SQL to combine the BookID's with Book
details.------
strDetailsSQL = "SELECT DISTINCT tblSearchResults.tblOPLID,
tblOPL.tblOPLNumber, tblOPL.tblOPLDate, tblOPL.tblOPLTitle,
tblOPL.tblDepartmentID, tblOPL.tblOPLLine,
tblDepartment.tblDepartmentName, tblOPL.tblOPLInactive FROM
tblDepartment INNER JOIN (tblOPL INNER JOIN tblSearchResults ON
tblOPL.tblOPLID = tblSearchResults.tblOPLID) ON
tblDepartment.tblDepartmentID = tblOPL.tblDepartmentID WHERE("
--------(This section of code limits the records shown by date
according to 2 unbound text boxes on the search form)---------
If IsDate([Forms]![frmMain]![tbxStart]) And
IsDate([Forms]![frmMain]![tbxEnd]) Then
strDetailsSQL = strDetailsSQL & "((tblOPL.tblOPLDate)>=#" &
[Forms]![frmMain]![tbxStart] & "# And " & "(tblOPL.tblOPLDate)<=#" &
[Forms]![frmMain]![tbxEnd] & "#) AND "
Else:
If Not IsDate([Forms]![frmMain]![tbxStart]) And
IsDate([Forms]![frmMain]![tbxEnd]) Then
strDetailsSQL = strDetailsSQL &
"((tblOPL.tblOPLDate)>=#01/01/1900# And " & "(tblOPL.tblOPLDate)<=#"&
[Forms]![frmMain]![tbxEnd] & "#) AND "
End If
If IsDate([Forms]![frmMain]![tbxStart]) And Not
IsDate([Forms]![frmMain]![tbxEnd]) Then
strDetailsSQL = strDetailsSQL &
"((tblOPL.tblOPLDate)>=#" & [Forms]![frmMain]![tbxStart] & "# And " &
"(tblOPL.tblOPLDate)<=#" & Format(Now(), "mm/dd/yyyy") & "#) AND "
End If
End If
------------------------------end of date section-----------------
-----complete the SQL text.--------
strDetailsSQL = strDetailsSQL &
"((tblOPL.tblOPLInactive)=0));"
-------Call the function that creates the UNION SQL statement---------
--------fBuildKeyQuery creates the SELECT stamen-----------
strSQL = fBuildSearchQuery(fBuildKeyQuery("jtblOPLPreparer",
Me.cbxPreparer), fBuildKeyQuery("tblOPL", Me.cbxDepartment),
fBuildKeyQuery("jtblOPLLine", Me.lbxLines),
fBuildKeyQuery("jtblOPLRamp", Me.lbxRamps))
----------Delete previous search results
DoCmd.RunSQL strSQLDelete
------------Execute the Union Query
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
---------Save the results into a table
Set rsKeys = db.OpenRecordset(strSQLSearchKeys)
With rsKeys
rs.MoveLast
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
While Not (rs.EOF)
.AddNew
![tblOPLID] = rs.Fields(0)
![tblSearchResults] = rs.Fields(1)
.Update
rs.MoveNext
Wend
Else:
MsgBox "The current search returned 0 records.", vbOKOnly,
"Search Results"
End If
'Debug.Print rs.RecordCount
End With
Debug.Print "strDetailsSQL=" & strDetailsSQL
--------update the search form's record source
Me.RecordSource = strDetailsSQL
Me.Requery
Exit_cmbSearch_Click:
Exit Sub
Everything works great, but it's not the results I need. I need to
display only those records that meet ALL the criteria, IOW I need the
criteria to be logical "AND" not "OR".
As you said, Union joins the records end to end. I need an
"intersection" of the individual SELECT statements.
If you would like help, give us some help to be able to do so: post theSQL
view of the queries. We can't fix what we can't see!
A UNION query is probably not what you want - it stacks recordsets end to end,
rather than each new query further restricting the first - but I have no idea
what you've done or even any clear idea what you're trying to accomplish.
THANKS!
David G.
