Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2012
    Posts
    24

    Unanswered: Select statement in form returns related values, not unique values

    Can someone take a look at my filter coding. When I give the form a value to search by, the subform returns all related values instead of exactly those that match. For instance, my form has a combo box to search for the appropriate name and populates the box with the name id. If I pick 3 John Smith from the pull down, and hit the search button, I get all matching records for 3 (John Smith), but also any records for name id's that begin with 3, such as 31 Leo Smith, to 39 Zach Smith and 300 (John Doe)! I think the problem is in the Select statement, but I'm not a programmer, so I can't figure out how to fix it. The query2 works perfect by itself, so I know it's not a problem with the query.


    Private Sub Show_matching_Click()
    ' Create a WHERE clause using search criteria entered by user and
    ' set RecordSource property of Query2 subform.

    Dim MySQL As String, MyCriteria As String, MyRecordSource As String
    Dim ArgCount As Integer
    Dim Tmp As Variant

    ' Initialize argument count.
    ArgCount = 0

    ' Initialize SELECT statement.
    MySQL = "SELECT * FROM query2 WHERE "
    MyCriteria = ""

    ' Use values entered in text boxes in form header to create criteria for WHERE clause.
    AddToWhere [Look For 1st Name], "[query cross names1].[name_id]", MyCriteria, ArgCount
    AddToWhere [Look For 2nd Name], "[query cross names2].[name_id]", MyCriteria, ArgCount
    AddToWhere [Look For 3rd Name], "[query cross names3].[name_id]", MyCriteria, ArgCount

    'If no criterion specified, return all records.

    If MyCriteria = "" Then
    MyCriteria = "True"
    End If

    'Create SELECT statement

    MyRecordSource = MySQL & MyCriteria

    'Set RecordSource property of Query2 subform.

    Me![Query2 subform].Form.RecordSource = MyRecordSource

    'If no records match criteria, then display message.
    'Otherwise, move focus to the Clear button.

    If Me![Query2 subform].Form.RecordsetClone.RecordCount = 0 Then
    MsgBox "No records found to match the criteria you entered.", 48, "No records Found"
    Me!Clear.SetFocus

    Else

    'Enable control in the detail section.

    Tmp = EnableControls("Detail", True)
    'Move insertion point to Query2 subform.

    Me![Query2 subform].SetFocus
    End If

    End Sub

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's AddToWhere and what does MyRecordSource contain?
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    24
    Sorry, I quess I left all the first part:

    Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

    ' Create criteria for WHERE clause.
    If FieldValue <> "" Then
    ' Add "and" if other criterion exists.
    If ArgCount > 0 Then
    MyCriteria = MyCriteria & " and "
    End If

    ' Append criterion to existing criteria.
    ' Enclose FieldValue and asterisk in quotation marks.
    MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

    ' Increase argument count.
    ArgCount = ArgCount + 1
    End If

    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by caltranscorrosion View Post
    ... If I pick 3 John Smith from the pull down, and hit the search button, I get all matching records for 3 (John Smith), but also any records for name id's that begin with 3, such as 31 Leo Smith, to 39 Zach Smith and 300 (John Doe)!
    From the moment you use "Column Like 'Value*'" as a criteria, the returned data set will include all rows for which Column begins with Value. If you want Column strictly matching Value, use = as the operator instead of Like and don't append the asterisk:
    Code:
    MyCriteria = (MyCriteria & FieldName & " = " & Chr(39) & FieldValue & Chr(39))
    Have a nice day!

  5. #5
    Join Date
    Apr 2012
    Posts
    24
    Sinndho:

    That makes sense and I should have picked up on it sooner. So I did try it, but I keep getting an error message that reads 'Data type mismatch in criteria expression'. When I click on debug, the line

    Me![Query2 subform].Form.RecordSource=MyRecordSource

    is highlighted. I've played around with it, but I can't figure out if the problem is in the coding, or the query. Any suggestions?

    Thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id guess the runtime error us because a string/text value must be delimited with either ' or "
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If [name_id] is numeric, remove the pair of chr(39) in the expression:
    Code:
    MyCriteria = MyCriteria & FieldName & " = " & FieldValue
    Have a nice day!

  8. #8
    Join Date
    Apr 2012
    Posts
    24
    Sinndho:

    Awesome! That did it!

    Thanks so much.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #10
    Join Date
    Apr 2012
    Posts
    24
    Sinndho:

    How would I take this to the next step to add a string search back in? For instance, if I wanted to filter by City along with the name id where the City is text in the table, but name id is an integer?

    AddToWhere [Look For City], "[query cross names1].[City]", MyCriteria, ArgCount

    If I just add in this line, without any other changes, when I do a search on the form, a popup window comes up requesting me to 'Enter parameter value'. Once I do that, the form filters the results correctly. Do you mind helping out again?

    Thanks

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to fully understand what precisely you're trying to achieve.

    If you want to search both for numeric and text values, you need to modify the procedure AddToWhere. Either you implement a mechanism into it that can determine the kind of column (numeric or text) in the procedure or you pass an extra parameter to it that specifies whether the concerned column is numeric or text.
    Have a nice day!

  12. #12
    Join Date
    Apr 2012
    Posts
    24
    Sinndho:

    I haven't had much luck putting the last piece of my puzzle together. Any chance that I can send you a highly reduced and simplified version of my database. I think once you see the relationships together with the forms, what I'm trying to do will be readily apparent.

    Thanks

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by caltranscorrosion View Post
    Any chance that I can send you a highly reduced and simplified version of my database.
    Sure! Please do so.
    Have a nice day!

  14. #14
    Join Date
    Apr 2012
    Posts
    24
    Sinndho:

    I've attached the database. The original has over 8000 photos that I have cataloged populating the various tables. From the onset, the table join names to photos was set up so that at some point I could search by names. The 02 find photos form filters on a number of fields. Highlight a result in the subform and click on the Photo Info, and a photo of the item pops up (not linked here) along with information about the photo.

    You provided the key for the Search by Names last week. It works great to search up to three names. It's a little funky if you want to search on just one person, as you have to fill in all three name fields with the same name id, but that's okay. What I really want is to somehow marry the two search forms, or add some of the fields such as City to the Search by names form.

    Any help is greatly appreciated.

    Thanks
    Attached Files Attached Files

  15. #15
    Join Date
    Apr 2012
    Posts
    24
    Sinndho:

    I hadn't heard anything from you, so I figured I'd better check in. Any luck?

Posting Permissions

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