Code for selecting records !! Please can someone help

[the_reg]

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

[orange]

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

[the_reg]

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

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

[orange]

What do you get in the debug.print statement?
I'd start by putting a space/blank after the .....WHERE "

[the_reg]

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

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

[Missinglinq]

...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)>

[the_reg]

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

[the_reg]

ive also tried putting space after the WHERE and before the " .. but still not working

strSQL = "SELECT * FROM qryClientData WHERE " & BuildFilter

[ssanfu]

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

[Country] like "USA"

There is no wild card, so this is the same as

[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)

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:

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.....)

[the_reg]

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

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

[Country] like "USA"

There is no wild card, so this is the same as

[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)

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:

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.....)