Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    15

    Unanswered: Passing info to query from multiple tables

    Information that is input through combo boxes and a text box need to be passed to the query (qryOpenQryTrans) from which the form (frmSubOpenQryTrans) is drawn.

    Information is drawn from one of nineteen tables, as defined by combo box one (cboPipeline1). Combo box 2 (cboReportingType1) determines the column within combo box 1, modified by variables <, >, =, etc (cboVariable1) and a text box for an alphanumeric keyword search (txtValue1).

    I have a main table, tblQryTrans, which can connect to the other nineteen by means of a one-to-many function, but is this the best way to go about it? Also, what would the code that parses those tables look like?

    Private Sub cmdRun_Click()
    On Error GoTo Err_cmdRun_Click
    Dim strSQL As String, strPipe As String, strTypeA As String, strReport As String, strTypeB As String, strVariable As String, strValue As String, strOp As String, strCaption As String
    Dim strSQL1 As String, strSQL2 As String, strFilter As String, strErr As String, strInput As String
    Dim intX As Integer, intY As Integer
    Dim blnOp As Boolean, blnValid As Boolean
    Dim bln1 As Boolean, bln2 As Boolean, bln3 As Boolean, bln4 As Boolean, bln5 As Boolean

    intY = 0
    blnValid = False
    For intX = 1 To 5
    Select Case intX

    Case 1
    strPipe = Nz(Me.cboPipe1.Value, "000")
    strReport = Nz(Me.cboReport1.Value, "000")
    strTypeA = Nz(Me.txtTypeA1.Value, "000")
    strTypeB = Nz(Me.txtTypeB1.Value, "000")
    strVariable = Nz(Me.cboVariable1.Value, "000")
    strValue = Nz(Me.txtValue1.Value, "000")
    If intY > 0 Then
    strOp = ""
    End If

    Case 2
    strPipe = Nz(Me.cboPipe2.Value, "000")
    strReport = Nz(Me.cboReport2.Value, "000")
    strTypeA = Nz(Me.txtTypeA2.Value, "000")
    strTypeB = Nz(Me.txtTypeB2.Value, "000")
    strVariable = Nz(Me.cboVariable2.Value, "000")
    strValue = Nz(Me.txtValue2.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp1.Value, "000")
    Else
    strOp = ""
    End If

    Case 3
    strPipe = Nz(Me.cboPipe3.Value, "000")
    strReport = Nz(Me.cboReport3.Value, "000")
    strTypeA = Nz(Me.txtTypeA3.Value, "000")
    strTypeB = Nz(Me.txtTypeB3.Value, "000")
    strVariable = Nz(Me.cboVariable3.Value, "000")
    strValue = Nz(Me.txtValue3.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp2.Value, "000")
    Else
    strOp = ""
    End If

    Case 4
    strPipe = Nz(Me.cboPipe4.Value, "000")
    strReport = Nz(Me.cboReport4.Value, "000")
    strTypeA = Nz(Me.txtTypeA4.Value, "000")
    strTypeB = Nz(Me.txtTypeB4.Value, "000")
    strVariable = Nz(Me.cboVariable4.Value, "000")
    strValue = Nz(Me.txtValue4.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp3.Value, "000")
    Else
    strOp = ""
    End If

    Case 5
    strPipe = Nz(Me.cboPipe5.Value, "000")
    strReport = Nz(Me.cboReport5.Value, "000")
    strTypeA = Nz(Me.txtTypeA5.Value, "000")
    strTypeB = Nz(Me.txtTypeB5.Value, "000")
    strVariable = Nz(Me.cboVariable5.Value, "000")
    strValue = Nz(Me.txtValue5.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp4.Value, "000")
    Else
    strOp = ""
    End If

    End Select

    If strPipe <> "000" And strPipe <> "" Then
    strErr = CheckValid(strPipe, strTypeA, strReport, strTypeB, strVariable, strValue, strOp, intY)
    strInput = strVariable & " " & strValue

    If strErr = "" Then
    'strFilter = GetCriteria(strPipe, strReport, strInput)
    strFilter = BuildCriteria("depthpercent", dbText, strInput)
    If Not IsNull(strFilter) And strFilter <> "" Then
    intY = intY + 1
    End If

    If intY > 0 Then
    strSQL2 = strSQL2 & " " & strOp & " " & strFilter
    Else
    strSQL2 = strSQL2 & " " & strFilter
    End If
    Else
    MsgBox strErr

    End If
    End If
    Next

    'strSQL = "SELECT ArnaudvilleToBayouSale330, BayouSaleToNapoleonvilleStation, GulfportToFlorenvilleStation, " _
    ' & "GulfportToMobile300, Longview391, Luling291, MarchardJctToStRose270, MarchandJctToScottlandville270, " _
    ' & "MarksvilleToArnaudville330, MontpelierToMcComb130, OllaToMarksville330, OllaToWestMonroe330, " _
    ' & "OuachitaRiverToSterlington129, StRoseToFlorenville300, SugarBowlToMontpelierMainLine, SugarBowlToMP85-130Loop, " _
    ' & "TerreboneJctToLuling300, WhistlerJctToBigCreek311, WhistlerToHurricane308 " _
    ' & "FROM qryOpenQryTrans "

    strSQL2 = "SELECT Pipeline, Feature, WheelCountFT, WheelCountIN, StationNumberFT, DepthPERCENT, WTIN, LengthFT, LengthIN, " _
    & "InteriorExterior, OrientationCLOCKWISE, BurstModB31G, RPRModB31G, BurstEffArea, RPREffArea, SMYS, DistFromUSWeldFT, " _
    & "DistFromUSWeldIN, DistFromDSWeldFT, DistFromDSWeldIN, DistToUSAGMIN, DistToUSAGMIN, DistToDSAGMFT, " _
    & "DistToDSAGMIN, JointLengthFT, JointLengthIN, Comments, Latitude, Longitude, Elevation, AdditionalComments " _
    & "FROM qryOpenQryTrans "

    '& "FROM Luling291"

    If intY > 0 Then
    strSQL = strSQL & " WHERE " & strSQL2
    End If

    'Me.txtSQL.Value = strSQL
    'Me.frmSubQryTrans.Form.RecordSource = strSQL

    Me.txtSQL2.Value = strSQL2
    Me.frmSubOpenQryTrans.Form.RecordSource = strSQL2

    Close_cmdRun_Click:
    Exit Sub

    Err_cmdRun_Click:
    MsgBox Err.Description
    Resume Close_cmdRun_Click

    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Passing info to query from multiple tables

    Originally posted by limolnar
    Information that is input through combo boxes and a text box need to be passed to the query (qryOpenQryTrans) from which the form (frmSubOpenQryTrans) is drawn.

    Information is drawn from one of nineteen tables, as defined by combo box one (cboPipeline1). Combo box 2 (cboReportingType1) determines the column within combo box 1, modified by variables <, >, =, etc (cboVariable1) and a text box for an alphanumeric keyword search (txtValue1).

    I have a main table, tblQryTrans, which can connect to the other nineteen by means of a one-to-many function, but is this the best way to go about it? Also, what would the code that parses those tables look like?

    Private Sub cmdRun_Click()
    On Error GoTo Err_cmdRun_Click
    Dim strSQL As String, strPipe As String, strTypeA As String, strReport As String, strTypeB As String, strVariable As String, strValue As String, strOp As String, strCaption As String
    Dim strSQL1 As String, strSQL2 As String, strFilter As String, strErr As String, strInput As String
    Dim intX As Integer, intY As Integer
    Dim blnOp As Boolean, blnValid As Boolean
    Dim bln1 As Boolean, bln2 As Boolean, bln3 As Boolean, bln4 As Boolean, bln5 As Boolean

    intY = 0
    blnValid = False
    For intX = 1 To 5
    Select Case intX

    Case 1
    strPipe = Nz(Me.cboPipe1.Value, "000")
    strReport = Nz(Me.cboReport1.Value, "000")
    strTypeA = Nz(Me.txtTypeA1.Value, "000")
    strTypeB = Nz(Me.txtTypeB1.Value, "000")
    strVariable = Nz(Me.cboVariable1.Value, "000")
    strValue = Nz(Me.txtValue1.Value, "000")
    If intY > 0 Then
    strOp = ""
    End If

    Case 2
    strPipe = Nz(Me.cboPipe2.Value, "000")
    strReport = Nz(Me.cboReport2.Value, "000")
    strTypeA = Nz(Me.txtTypeA2.Value, "000")
    strTypeB = Nz(Me.txtTypeB2.Value, "000")
    strVariable = Nz(Me.cboVariable2.Value, "000")
    strValue = Nz(Me.txtValue2.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp1.Value, "000")
    Else
    strOp = ""
    End If

    Case 3
    strPipe = Nz(Me.cboPipe3.Value, "000")
    strReport = Nz(Me.cboReport3.Value, "000")
    strTypeA = Nz(Me.txtTypeA3.Value, "000")
    strTypeB = Nz(Me.txtTypeB3.Value, "000")
    strVariable = Nz(Me.cboVariable3.Value, "000")
    strValue = Nz(Me.txtValue3.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp2.Value, "000")
    Else
    strOp = ""
    End If

    Case 4
    strPipe = Nz(Me.cboPipe4.Value, "000")
    strReport = Nz(Me.cboReport4.Value, "000")
    strTypeA = Nz(Me.txtTypeA4.Value, "000")
    strTypeB = Nz(Me.txtTypeB4.Value, "000")
    strVariable = Nz(Me.cboVariable4.Value, "000")
    strValue = Nz(Me.txtValue4.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp3.Value, "000")
    Else
    strOp = ""
    End If

    Case 5
    strPipe = Nz(Me.cboPipe5.Value, "000")
    strReport = Nz(Me.cboReport5.Value, "000")
    strTypeA = Nz(Me.txtTypeA5.Value, "000")
    strTypeB = Nz(Me.txtTypeB5.Value, "000")
    strVariable = Nz(Me.cboVariable5.Value, "000")
    strValue = Nz(Me.txtValue5.Value, "000")
    If intY > 0 Then
    strOp = Nz(Me.cboOp4.Value, "000")
    Else
    strOp = ""
    End If

    End Select

    If strPipe <> "000" And strPipe <> "" Then
    strErr = CheckValid(strPipe, strTypeA, strReport, strTypeB, strVariable, strValue, strOp, intY)
    strInput = strVariable & " " & strValue

    If strErr = "" Then
    'strFilter = GetCriteria(strPipe, strReport, strInput)
    strFilter = BuildCriteria("depthpercent", dbText, strInput)
    If Not IsNull(strFilter) And strFilter <> "" Then
    intY = intY + 1
    End If

    If intY > 0 Then
    strSQL2 = strSQL2 & " " & strOp & " " & strFilter
    Else
    strSQL2 = strSQL2 & " " & strFilter
    End If
    Else
    MsgBox strErr

    End If
    End If
    Next

    'strSQL = "SELECT ArnaudvilleToBayouSale330, BayouSaleToNapoleonvilleStation, GulfportToFlorenvilleStation, " _
    ' & "GulfportToMobile300, Longview391, Luling291, MarchardJctToStRose270, MarchandJctToScottlandville270, " _
    ' & "MarksvilleToArnaudville330, MontpelierToMcComb130, OllaToMarksville330, OllaToWestMonroe330, " _
    ' & "OuachitaRiverToSterlington129, StRoseToFlorenville300, SugarBowlToMontpelierMainLine, SugarBowlToMP85-130Loop, " _
    ' & "TerreboneJctToLuling300, WhistlerJctToBigCreek311, WhistlerToHurricane308 " _
    ' & "FROM qryOpenQryTrans "

    strSQL2 = "SELECT Pipeline, Feature, WheelCountFT, WheelCountIN, StationNumberFT, DepthPERCENT, WTIN, LengthFT, LengthIN, " _
    & "InteriorExterior, OrientationCLOCKWISE, BurstModB31G, RPRModB31G, BurstEffArea, RPREffArea, SMYS, DistFromUSWeldFT, " _
    & "DistFromUSWeldIN, DistFromDSWeldFT, DistFromDSWeldIN, DistToUSAGMIN, DistToUSAGMIN, DistToDSAGMFT, " _
    & "DistToDSAGMIN, JointLengthFT, JointLengthIN, Comments, Latitude, Longitude, Elevation, AdditionalComments " _
    & "FROM qryOpenQryTrans "

    '& "FROM Luling291"

    If intY > 0 Then
    strSQL = strSQL & " WHERE " & strSQL2
    End If

    'Me.txtSQL.Value = strSQL
    'Me.frmSubQryTrans.Form.RecordSource = strSQL

    Me.txtSQL2.Value = strSQL2
    Me.frmSubOpenQryTrans.Form.RecordSource = strSQL2

    Close_cmdRun_Click:
    Exit Sub

    Err_cmdRun_Click:
    MsgBox Err.Description
    Resume Close_cmdRun_Click

    End Sub
    Man, that's a lot of information to process without seeing what you are seeing. When you refer to a one to many function are you refering to relationships between the tables or a function that you have created?

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    15
    I apologise for not being clear. I was referring to one-to-many with tables.

    Thank you for taking the time to review my query!

  4. #4
    Join Date
    Oct 2003
    Posts
    15
    The main problem I have is with passing the combo box selections as parameters that the query can use to access different tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •