Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    19

    Unanswered: Access SQL defining

    I am learning Access and came across a problem that if solved would make my life easier.

    I want to define the SQL from a form. I would like users to be able to use check boxes to define what they would like to query, then use a command button to run the query. Any suggestions. Your help is appreciated.

    Any other suggestions for allowing the user to query the database without them dealing with the query process.

  2. #2
    Join Date
    May 2003
    Location
    Maryland
    Posts
    11

    Re: Access SQL defining

    Im Doing a Simalar thing for a project at work, and Im reusing a solution I came up with for
    another access application at the same workplace.

    Ok It flow charts like this,
    On cmdSearch_Click Access polls all the text boxes on the search form an loads their values into
    the appropriate variables.

    Then It Calls Build search. Build Search does 2 things.
    It gets the SQL statement from the Query Def that is currently active in my application and stored in the globle stSQL.
    This becomes the BaseSQL. Before it can be used I have to trim the ; that marks the end of the SQL statement from
    the returned string.

    Then it runs a series of calls to BLDW building the Where statement.
    Finally and most Importantly for you.

    Let stSQL = BaseSQL & BLDS & ";"
    Debug.Print stSQL
    Let db.QueryDefs!qrySearch.SQL = stSQL

    This little bit of code attaches the SQL we have built to the the QueryDef("qrySearch")
    This Query is the Record source of our frmSearchResults, So you just open the SearchResults form normally now and
    It will come up with the record set we need.

    For my project I need to be able to access records from 3 seperate systems, whos parts lists need to be kept seperate,
    but the DB tables all share the same structure, because they where created by the same supply guy.

    The stSQL Global allows me to keep track of which of 3 possible Query defs is active at a given time, and manipulate the RecordSources of the main form under the hood with VBA, and Manipulate the SQL of the Search form as we have seen.
    The result is a little little VBA heavy, but I prefer to create fewer permenent objects forms and QueryDefs if I can reuse ones that exist already with a little help from the VBA.

    Oh BTW, DAO Ref 3.6 is on.

    Private Sub cmdSearch_Click()
    'Clicking the Command Button Launches the search - obviously.
    Dim stDocName As String
    Dim stLinkCriteria As String

    'Rem CollectSearch Loads the Varibles for the Where string.
    'Build Search Builds a Where statement based on those varibles.
    'Then loads resulting SQL string into the SQL Property of the
    'Search results form.


    Dim stPart As String, stMNum As String, stSERNum As String
    Dim stMan As String, stTag As String
    Call CollectSearch(stPart, stMNum, stSERNum, stMan, stTag)
    'Debug.Print stPart
    Call BuildSearch(stPart, stMNum, stSERNum, stMan, stTag)
    Debug.Print stPart

    stDocName = "frmSearchResults"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    End Sub

    Sub CollectSearch(stPart, stMNum, stSERNum, stMan, stTag)
    ' A textbox must have the focus for you to read it, so this
    ' allows me to read them all at once.

    txtMFG.SetFocus
    Let stMan = txtMFG.Text
    txtMODEL.SetFocus
    Let stMNum = txtMODEL.Text
    txtPART_NUM.SetFocus
    Let stPart = txtPART_NUM.Text
    txtSERIAL_NUM.SetFocus
    Let stSERNum = txtSERIAL_NUM.Text
    txtTag.SetFocus
    Let stTag = txtTag.Text

    End Sub

    Sub BuildSearch(stPart, stMNum, stSERNum, stMan, stTag)

    Dim db As Database
    Dim QDef1 As QueryDef
    Dim BaseSQL As String
    Dim Search4me As String, stFld As String, BLDS As String, stSQL As String
    Set db = CurrentDb

    Dim stBug: Let stBug = "BuildSearch": Debug.Print stBug

    'It took some tweeking to get this right, I still have not cleared
    'my debug traps from the underlying code.
    Debug.Print "stTag = "; stTag


    'Debug.Print stQRY
    Set QDef1 = db.QueryDefs(stQRY)
    Let BaseSQL = QDef1.SQL:
    Call PrepBaseSQL(BaseSQL)
    Let Search4me = stPart: Let stFld = "Comsat.[Part_Num]"
    Call BLDW(stFld, BLDS, Search4me)
    Let Search4me = stMNum: Let stFld = "[Model_Num]"
    Call BLDW(stFld, BLDS, Search4me)
    Let Search4me = stSERNum: Let stFld = "[SERIAL_Num]"
    Call BLDW(stFld, BLDS, Search4me)
    Let Search4me = stMan: Let stFld = "[MFG]"
    Call BLDW(stFld, BLDS, Search4me)
    Let Search4me = stTag: Let stFld = "[TAG_NUM]"
    Call BLDW(stFld, BLDS, Search4me)


    Let stSQL = BaseSQL & BLDS & ";"
    Debug.Print stSQL
    Let db.QueryDefs!qrySearch.SQL = stSQL
    Dim stSearchBug: Let stSearchBug = "Search4 Me"
    'Debug.Print stSQL
    QDef1.Close
    db.Close

    End Sub





    Sub BLDW(stFld, BLDS, Search4me)
    'The value from each text box for each searchable field
    'is feed to this routine one at a time, to slowly build
    'the where statement.


    If Search4me = "" Then Exit Sub

    If BLDS = "" Then
    Let BLDS = "WHERE " & stFld & " = '" & Search4me & "'"
    Else
    Let BLDS = BLDS & " And " & stFld & " = '" & Search4me & "'"

    End If
    Dim stBug: Let stBug = "BLDS = " & BLDS
    Debug.Print stBug


    End Sub

    Sub PrepBaseSQL(BaseSQL)
    ' All this code does is trim the ; from the end of the original SQL statement.

    Dim L As Integer, Temp As String: Dim working As String
    Let L = 1
    Do
    Let working = Mid$(BaseSQL, L, 1)
    If working <> ";" Then L = L + 1
    Loop Until working = ";"
    Let L = L - 1


    Let Temp = Left$(BaseSQL, L)
    Let BaseSQL = Temp & " "

    End Sub
    K did you Flashy thing me, K??

  3. #3
    Join Date
    May 2003
    Location
    Maryland
    Posts
    11
    Actually I made one Boo Boo here that I have found,
    On the Search results form, you must include a on open event
    for the search to update after the first search.

    Private Sub Form_Open(Cancel as Integer)
    Me.Requery
    End Sub
    K did you Flashy thing me, K??

  4. #4
    Join Date
    Jun 2003
    Posts
    19

    Access-SQL

    Blackbird,

    Thank you for your reply to my need for guidance with how to take Access check boxes and create an sql statement. I have not yet had time to check it out, but hopefully I can use some of the stuff you sent.

    Thank you
    Samandus

Posting Permissions

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