PC Review


Reply
Thread Tools Rate Thread

searching and finding records from separate tables

 
 
Masoud
Guest
Posts: n/a
 
      13th May 2010
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

 
Reply With Quote
 
 
 
 
Masoud
Guest
Posts: n/a
 
      16th May 2010
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.



"PieterLinden via AccessMonster.com" wrote:

> 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.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/201005/1
>
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
designing form for inserting records into separate tables Masoud Microsoft Access Database Table Design 0 17th May 2010 07:39 AM
Store similar types of records all in one table or separate tables? tryit Microsoft Access Database Table Design 2 6th Nov 2009 10:07 PM
updating info in separate tables in separate databases timglass via AccessMonster.com Microsoft Access External Data 1 1st Nov 2005 09:20 PM
VBA code for searching and appending data from linked tables to unlinked tables Pete Straman Straman via AccessMonster.com Microsoft Access External Data 1 17th Feb 2005 08:40 PM
Searching for records across two tables using a form alan newton Microsoft Access Forms 0 2nd Feb 2004 12:30 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:53 PM.