Results 1 to 10 of 10
  1. #1
    the_reg is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    8

    Code for selecting records !! Please can someone help

    hi Guys - i am doing something simple and getting it wrong and frustratingly dont know where !! can someone help. The code below falls over on line

    Me.frmsubClients.Form.RecordSource = strSQL ..... saying i have a syntax error . I am pulling records from a sub form called 'frmsubClients' thanks in advance

    Private Sub btnSearch_Click()
    ' Update the record source
    Dim strBuiltFilter As String
    Dim strSQL As String
    strBuiltFilter = BuildFilter


    Debug.Print BuildFilter
    If strBuiltFilter = "" Then
    strSQL = "Select * FROM qryClientData" & strBuiltFilter
    Else
    strSQL = "SELECT * FROM qryClientData WHERE" & strBuiltFilter
    End If
    Debug.Print strSQL
    Me.frmsubClients.Form.RecordSource = strSQL
    ' Requery the subform
    Me.frmsubClients.Requery
    End Sub

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    Can you tell us in plain English WHAT you are trying to do?
    You have shown us How you have implemented something that isn't working. But we really need to know WHAT you're trying to do, before offering assistance.
    Also, can you tell us about the form Me represents? and its Recordsource?

    Form/subform syntax at http://access.mvps.org/access/forms/frm0031.htm

  3. #3
    the_reg is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    8

    Creating search functions within Forms

    Thanks Orange – I am trying to create a search function withina Form called ‘frmSearch’. I have created a sub form called ‘frmsubClients’ thatdisplays within the Form ‘frmSearch’. I want to entry a string called ‘txtFirstName’and for that to search all the records I’ve created in a table called ‘Table1’and display only those records that filter and search out the records that match‘txtFirstName’.
    The code that sits behind the ‘Search’ button is as follows: and seems to fall over on the line
    Me.frmsubClients.Form.RecordSource = strSQL
    Access says incorrect use of Form ... dont understand

    thanks again




    Quote Originally Posted by the_reg View Post
    hi Guys - i am doing something simple and getting it wrong and frustratingly dont know where !! can someone help. The code below falls over on line

    Me.frmsubClients.Form.RecordSource = strSQL ..... saying i have a syntax error . I am pulling records from a sub form called 'frmsubClients' thanks in advance

    Private Sub btnSearch_Click()
    ' Update the record source
    Dim strBuiltFilter As String
    Dim strSQL As String
    strBuiltFilter = BuildFilter
    Debug.Print BuildFilter
    If strBuiltFilter = "" Then
    strSQL = "Select * FROM qryClientData" & strBuiltFilter
    Else
    strSQL = "SELECT * FROM qryClientData WHERE" & strBuiltFilter
    End If
    Debug.Print strSQL
    Me.frmsubClients.Form.RecordSource = strSQL
    ' Requery the subform
    Me.frmsubClients.Requery
    End Sub

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    What do you get in the debug.print statement?
    I'd start by putting a space/blank after the .....WHERE "

  5. #5
    the_reg is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    8

    thank you

    Orange you are a star thank you so much - its been some time since i last used Access and thought that it would automatically correct syntax errors - thanks again for taking the time to reply

    Quote Originally Posted by the_reg View Post
    Thanks Orange – I am trying to create a search function withina Form called ‘frmSearch’. I have created a sub form called ‘frmsubClients’ thatdisplays within the Form ‘frmSearch’. I want to entry a string called ‘txtFirstName’and for that to search all the records I’ve created in a table called ‘Table1’and display only those records that filter and search out the records that match‘txtFirstName’.
    The code that sits behind the ‘Search’ button is as follows: and seems to fall over on the line
    Me.frmsubClients.Form.RecordSource = strSQL
    Access says incorrect use of Form ... dont understand

    thanks again

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    Quote Originally Posted by the_reg View Post

    ...thought that it would automatically correct syntax errors...
    The Access Gnomes do correct syntax errors...to some extent! If, for instance, you type in

    Var=1+2+3

    and move off of the line, Access will correct the spacing, giving you

    Var = 1 + 2 + 3

    But when you are typing something within Double Quotes, such as

    "SELECT * FROM qryClientData WHERE" & strBuiltFilter

    the Double Quotes are telling Access that everything which lies within them are to be taken literally, and the Gnomes won't mess with this!

    Being in forced retirement, I spend about 6-8 hours a day here, and on four or five other Access sites, and this particular error, involving WHERE and the missing space, pops up half a dozen times every single week!

    Linq ;0)>

  7. #7
    the_reg is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    8
    hi Guys - got to say i am completely stumped by this problem. I want this code to search on two different fields together, say country and geographic region and to display the results within a sub-form called frmsubClients. I keep on getting the message that ive got the syntax wrong in the statement ..... strSQL = "SELECT * FROM qryClientData WHERE" & BuildFilter

    it is utterly frustrating given that i can program but i am truly lost - can some expert like Orange or MissingLinq please help - thanks a lot in advance

    Private Sub btnSearch_Click()

    On Error GoTo errr
    Dim strSQL As String
    Dim x As String
    Dim strBuiltFilter As String

    strBuiltFilter = BuildFilter
    strSQL = "SELECT * FROM qryClientData WHERE" & BuildFilter
    Me.frmsubClients.Form.RecordSource = strSQL
    errr:
    MsgBox Err.Description
    End Sub

    Private Sub Form_Load()
    ' Clear the search form
    btnClear_Click

    End Sub
    Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim tmp As String
    tmp = """"
    varWhere = Null ' Main filter
    'varColor = Null ' Subfilter used for colors

    ' Check for Country
    If Me.txtCountry > "" Then
    varWhere = varWhere & "[Country] like " & tmp & Me.txtCountry & tmp & " AND "
    End If
    ' Check for Company
    If Me.txtCompany > "" Then
    varWhere = varWhere & "[Company] like " & tmp & Me.txtCompany & tmp & " AND "
    End If
    ' Check for Geographic Region
    If Me.TxtGeographicRegion > "" Then
    varWhere = varWhere & "[Geographic_Region] LIKE " & tmp & Me.TxtGeographicRegion & tmp & " AND "
    End If
    '& " * " And ""

    BuildFilter = varWhere

    End Function

  8. #8
    the_reg is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    8
    ive also tried putting space after the WHERE and before the " .. but still not working

    strSQL = "SELECT * FROM qryClientData WHERE " & BuildFilter

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked at your code an made a few changes.

    The sub "btnSearch_Click()" was falling through to the error statement.
    The function "BuildFilter" was being called twice. Once at the line "strBuiltFilter = BuildFilter" and again at "strSQL = "SELECT......"
    I changed the check to see if there were entries in the text box controls.. (see code)
    The last " AND " was not removed from the variable "varWHERE".


    One thing that doesn't make sense to me is if you enter "USA" for Me.txtCountry, the criteria will be
    Code:
    [Country] like "USA"
    There is no wild card, so this is the same as
    Code:
    [Country] = "USA"
    Are you wanting to find any country that begins with the data in Me.txtCountry?
    For example, if you entered "U" in Me.txtCountry and clicked the search button, would you want USA, United Kingdom, Uganda, UAE,...?
    If so, then you would have to add the wild card:
    (example)
    Code:
    varWhere = varWhere & "[Country] LIKE " & tmp & Me.txtCountry & "*" & tmp & " AND "

    (I created test forms.. )
    If the name of the subform is the same as the name of the subform control, I got an error.
    I changed the name of the subform control to "sfrmsubClients" and ... no errror .
    I would have different names for the subform and the subform control.

    Modified code:
    Code:
    Private Sub btnSearch_Click()
       On Error GoTo Err_btnSearch_Click
    
       Dim strSQL As String
       '   Dim x As String
       Dim strBuiltFilter As String
    
       strBuiltFilter = BuildFilter
    
       If Len(strBuiltFilter) > 0 Then
          strSQL = "SELECT * FROM qryClientData WHERE " & strBuiltFilter
       Else
          'no criteria entered
          strSQL = "SELECT * FROM qryClientData"
       End If
    
       '   Debug.Print strSQL
       Me.frmsubClients.Form.RecordSource = strSQL
    
    Exit_btnSearch_Click:
       Exit Sub
    
    Err_btnSearch_Click:
       MsgBox Err.Number & "  " & Err.Description
       Resume Exit_btnSearch_Click
    
    End Sub
    
    
    
    Private Function BuildFilter() As Variant
       Dim varWhere As Variant
       Dim tmp As String
       tmp = """"
       varWhere = Null   ' Main filter
       'varColor = Null ' Subfilter used for colors
    
       ' Check for Country
       If Len(Me.txtCountry) > 0 Then
          varWhere = varWhere & "[Country] LIKE " & tmp & Me.txtCountry & tmp & " AND "
       End If
       
       ' Check for Company
       If Len(Me.txtCompany) > 0 Then
          varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
       End If
       
       ' Check for Geographic Region
       If Len(Me.TxtGeographicRegion) > 0 Then
          varWhere = varWhere & "[Geographic_Region] LIKE " & tmp & Me.TxtGeographicRegion & tmp & " AND "
       End If
    
       If Len(Trim(varWhere)) > 0 Then
          varWhere = Left(varWhere, Len(varWhere) - 5)
       End If
    
       BuildFilter = varWhere
    
    End Function
    (The blue text is what I modified/added.....)

  10. #10
    the_reg is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    8

    Correct use of the SELECT FROM syntax in Access VBA

    ssanfu (Steve) - thank you so much for this, I really appreciate your expert help - I would never have been able to figure it out.. sending some of London's heat wave Anchorage's Way !!! thanks again

    Quote Originally Posted by ssanfu View Post
    I looked at your code an made a few changes.

    The sub "btnSearch_Click()" was falling through to the error statement.
    The function "BuildFilter" was being called twice. Once at the line "strBuiltFilter = BuildFilter" and again at "strSQL = "SELECT......"
    I changed the check to see if there were entries in the text box controls.. (see code)
    The last " AND " was not removed from the variable "varWHERE".


    One thing that doesn't make sense to me is if you enter "USA" for Me.txtCountry, the criteria will be
    Code:
    [Country] like "USA"
    There is no wild card, so this is the same as
    Code:
    [Country] = "USA"
    Are you wanting to find any country that begins with the data in Me.txtCountry?
    For example, if you entered "U" in Me.txtCountry and clicked the search button, would you want USA, United Kingdom, Uganda, UAE,...?
    If so, then you would have to add the wild card:
    (example)
    Code:
    varWhere = varWhere & "[Country] LIKE " & tmp & Me.txtCountry & "*" & tmp & " AND "

    (I created test forms.. )
    If the name of the subform is the same as the name of the subform control, I got an error.
    I changed the name of the subform control to "sfrmsubClients" and ... no errror .
    I would have different names for the subform and the subform control.

    Modified code:
    Code:
    Private Sub btnSearch_Click()
       On Error GoTo Err_btnSearch_Click
    
       Dim strSQL As String
       '   Dim x As String
       Dim strBuiltFilter As String
    
       strBuiltFilter = BuildFilter
    
       If Len(strBuiltFilter) > 0 Then
          strSQL = "SELECT * FROM qryClientData WHERE " & strBuiltFilter
       Else
          'no criteria entered
          strSQL = "SELECT * FROM qryClientData"
       End If
    
       '   Debug.Print strSQL
       Me.frmsubClients.Form.RecordSource = strSQL
    
    Exit_btnSearch_Click:
       Exit Sub
    
    Err_btnSearch_Click:
       MsgBox Err.Number & "  " & Err.Description
       Resume Exit_btnSearch_Click
    
    End Sub
    
    
    
    Private Function BuildFilter() As Variant
       Dim varWhere As Variant
       Dim tmp As String
       tmp = """"
       varWhere = Null   ' Main filter
       'varColor = Null ' Subfilter used for colors
    
       ' Check for Country
       If Len(Me.txtCountry) > 0 Then
          varWhere = varWhere & "[Country] LIKE " & tmp & Me.txtCountry & tmp & " AND "
       End If
       
       ' Check for Company
       If Len(Me.txtCompany) > 0 Then
          varWhere = varWhere & "[Company] LIKE " & tmp & Me.txtCompany & tmp & " AND "
       End If
       
       ' Check for Geographic Region
       If Len(Me.TxtGeographicRegion) > 0 Then
          varWhere = varWhere & "[Geographic_Region] LIKE " & tmp & Me.TxtGeographicRegion & tmp & " AND "
       End If
    
       If Len(Trim(varWhere)) > 0 Then
          varWhere = Left(varWhere, Len(varWhere) - 5)
       End If
    
       BuildFilter = varWhere
    
    End Function
    (The blue text is what I modified/added.....)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-05-2012, 08:33 AM
  2. Selecting records based on particular value
    By usman400 in forum Queries
    Replies: 4
    Last Post: 09-30-2011, 07:58 AM
  3. Selecting records within a form
    By crowegreg in forum Programming
    Replies: 29
    Last Post: 08-19-2011, 08:18 AM
  4. Selecting the State Code
    By TheBigMaple in forum Queries
    Replies: 1
    Last Post: 03-30-2011, 05:34 PM
  5. Replies: 1
    Last Post: 03-17-2006, 12:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums