searching and finding records from separate tables

Discussion in 'Microsoft Access VBA Modules' started by Masoud, May 13, 2010.

  1. Masoud

    Masoud Guest

    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
     
    Masoud, May 13, 2010
    #1
    1. Advertisements

  2. Masoud

    Masoud Guest

    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 Type:DW
    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/Forums.aspx/access-modules/201005/1
    >
    > .
    >
     
    Masoud, May 16, 2010
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. daniel

    Open a form after searching for records

    daniel, Sep 5, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    165
    Allen Browne
    Sep 6, 2003
  2. Guest

    Finding Results and Pasting Into Separate Field

    Guest, Apr 22, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    3
    Views:
    165
    Guest
    Apr 22, 2004
  3. Guest

    Script for Searching and copying tables

    Guest, Jun 28, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    142
    Guest
    Jun 29, 2007
  4. Steve Sanford

    RE: Finding a value and then finding a value from that

    Steve Sanford, Aug 6, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    9
    Views:
    326
    Steve Sanford
    Aug 14, 2009
  5. Masoud

    designing form for inserting records into separate table

    Masoud, May 17, 2010, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    368
    Masoud
    May 22, 2010
Loading...

Share This Page