searching and finding records from separate tables

[Masoud]

Hello

I have 1 form that there are some text boxes (parameters) and a button
search and its code is like below, for finding records in tbldocuments(
[DocumentNo] is the primary key).
Now I want to add another table, that there is not any relation between this
and tbldocuments. Just some fields between tables are same and another fiels
are different. Now I need the code for searching 2 tables. And in the output
records just there are same fields from both tables. For example:

tblDocuments (table1) contains.

DocumentNo
Title
Originator
Document Type

Table 2 contains:

DocumentNo (Vendor Doc)
Title
Vendor name
Document Type

Now I like when search with (Vendor name) it searches through table2 and
output records
DocumentNo
Title
Document Type

Or when search with (DocumentNo) it goes through both tables, and find
records from each table and then add them in out put records.

Best regards.



-----------------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim varWhere As Variant, varDateSearch As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null
varDateSearch = Null




If Not IsNothing(Me.txtDocumentNo) Then

varWhere = "[DocumentNo] LIKE '" & Me.txtDocumentNo & "*'"
End If


If Not IsNothing(Me.txtTitle) Then

varWhere = (varWhere + " AND ") & "[Title] LIKE '" & Me.txtTitle &
"*'"
End If


If Not IsNothing(Me.cmbOriginator) Then

varWhere = (varWhere + " AND ") & "[Originator] LIKE '" &
Me.cmbOriginator & "*'"
End If

If Not IsNothing(Me.CmbDiscipline) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & _
"[DocumentNo] IN (SELECT DocumentNo FROM qryDocuments " & _
"WHERE qryDocuments.Discipline LIKE '" & Me.CmbDiscipline & "*')"
End If
' Do Document Type next
If Not IsNothing(Me.CmbType) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "[Document Type] LIKE '" &
Me.CmbType & "*'"
End If
' Do civil unit next
If Not IsNothing(Me.CmbUnit) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "[unit] LIKE '" & CmbUnit & "*'"
End If

If Not IsNothing(Me.CmbVendorName) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "[VendorName] LIKE '" &
CmbVendorName & "*'"
End If

If Not IsNothing(Me.CmbStatus) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & _
"[DocumentNo] IN (SELECT DocumentNo FROM
qryDocumentSummaryLetterPOGC " & _
"WHERE qryDocumentSummaryLetterPOGC.POGCReply LIKE '" &
Me.CmbStatus & "*')"
End If

If Not IsNothing(CmbPurposeofIssue) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & _
"[DocumentNo] IN (SELECT DocumentNo FROM
qryDocumentSummaryLetterPOGC " & _
"WHERE qryDocumentSummaryLetterPOGC.PurposeofIssue LIKE '" &
Me.CmbPurposeofIssue & "*')"
End If

If Not IsNothing(Me.txtTransmittal) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[DocumentNo] IN (SELECT DocumentNo FROM tblTransmittalls " & _
"WHERE tblTransmittalls.Transmittal LIKE '" & Me.txtTransmittal
& "*')"
End If
If Not IsNothing(Me.txtTransmittaltoPOGC) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[DocumentNo] IN (SELECT DocumentNo FROM tblTransmittalsPOGC " & _
"WHERE tblTransmittalsPOGC.TransmittaltoPOGC LIKE '" &
Me.txtTransmittaltoPOGC & "*')"
End If

If Not IsNothing(Me.txtLetterfromPOGC) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[DocumentNo] IN (SELECT DocumentNo FROM tblDocLettersPOGC " & _
"WHERE tblDocLettersPOGC.LetterNoPOGC LIKE '" &
Me.txtLetterfromPOGC & "*')"
End If





' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, gstrAppTitle
Exit Sub
End If

' Open a recordset to see if any rows returned with this filter
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblDocuments WHERE
" & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "No Documents meet your criteria.", vbInformation, gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rst.MoveLast
' If 5 or less or frmContacts already open,
If IsFormLoaded("frmDocuments") Then
DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmDocuments.SetFocus

Else
' Ask if they want to see a summary list first
If (Me.txtRevision = "All") Then
If vbYes = MsgBox("Your search found " & rst.RecordCount & "
Documents. " & _
"Do you want to see a summary list first?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
'MsgBox "Your search found " & rst.RecordCount & " Documents.", 4,
"Search Results"
' Show the summary
DoCmd.OpenReport "rptDocumentslist", acPreview, , varWhere
Else
' Show the full contacts info filtered
DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmDocuments.SetFocus

End If
Else
If (Me.txtRevision = "Last") Then
If vbYes = MsgBox("Your search found " & rst.RecordCount & "
Documents. " & _
"Do you want to see a summary list first?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
DoCmd.OpenForm "frmDocumentSummary", WhereCondition:=varWhere
' Make sure focus is on contact summary
Forms!frmDocumentSummary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmDocuments.SetFocus

End If
End If
End If
End If
' Done
DoCmd.Close acForm, Me.Name
' Clean up recordset
rst.Close
Set rst = Nothing

End Sub

 

[PieterLinden via AccessMonster.com]

Masoud,

Put all the records in ONE table. If you need to, add another field to the
table to differentiate the records that would have been in table1 or table2.
Then index that column and proceed as before.

 

[Masoud]

Hi,
Thanks for your answer

I like to inform you difference between tables are not just one field, at
least 5 fields are totally difference, other fields that are the same like
DOC NO, but they have been made of concatenated of difference fields in the
each table when I am inserting records in each form. for more description,
table2 contains fields about QC documents. Like below
Proj id: 2042
Field code: COM
Discipline: MG
Doc Type: PR
Serial: 0002
Doc No: concatenated by Proj id+ Field code+ Discipline+ Doc Type+ Serial
and result should be some thing : 2042-COM-MG-PR-0002
Value of Doc No has been made by coding when inserting records in the form.

Table 1 contains fiels about engineering documents like below:
Projid: 2042
Doc TypeW
Unit: 114
Material Code: 1431
Serial: 0002
Doc No: Doc Type+ Projid+ Unit+ Material Code+ Serial and result should be
some thing like: DW-2042-114-1431-0002
Value of Doc No has been made by coding when inserting records in the form.
i am waiting for your answer, just I explained more because I liked inform
more information about the job I want to do maybe now you will have another
solution, any way if your bet is adding field in one table, I will do it.

thanks in advanced.