Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: help with VB code for a search box.

    Ok, yes I am a noob. I have been self-teaching myself access and VB.

    So the background on the database. It's pretty simple, it is a question and answer database for a library designed in access 2003.

    I found some code for a search box online and I have been trying to make it work. In code I've posted below there are numerous problems but only two I need to focus on.

    1. for searching topic, question, and answer the function looks only a the first word of the field. For example, if I searched for "help" and the record was "I need help" it would return no results.

    2. for area of law, the combo box searching doesn't work at all. When I select from the pull-down and search a pop-up appears stating "enter parameter"

    Can y'all help if possible? I am trying to self-teach myself VB and it is not going very well. I need someone to walk me through it.

    Private Sub btnSearch_Click()
    ' Update the record source
    Me.ref_records_Subform.Form.RecordSource = "SELECT * FROM qryRefRecords " & BuildFilter

    ' Requery the subform
    Me.ref_records_Subform.Requery
    End Sub

    Private Sub Form_Load()

    ' Clear the search form
    btnClear_Click

    End Sub

    Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null ' Main filter

    ' Check for LIKE Topic
    If Me.txtTopic > "" Then
    varWhere = varWhere & "[Topic] LIKE """ & Me.txtTopic & "*"" AND "
    End If

    ' Check for LIKE Question
    If Me.TxtQuestion > "" Then
    varWhere = varWhere & "[Question] LIKE """ & Me.TxtQuestion & "*"" AND "
    End If

    ' Check for LIKE Answer
    If Me.TxtAnswer > "" Then
    varWhere = varWhere & "[Answer] LIKE """ & Me.TxtAnswer & "*"" AND "
    End If

    ' Check for lawarea
    If Me.cmblawarea > 0 Then
    varWhere = varWhere & "[lawarea] = " & Me.cmblawarea & " AND "
    End If

    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
    varWhere = ""
    Else
    varWhere = "WHERE " & varWhere

    ' strip off last "AND" in the filter
    If Right(varWhere, 5) = " AND " Then
    varWhere = Left(varWhere, Len(varWhere) - 5)
    End If
    End If

    BuildFilter = varWhere

    End Function
    TYIA and please be gentle.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    for searching topic, replace (for every occurences):
    Code:
    varWhere = varWhere & "[Question] LIKE """ & Me.TxtQuestion & "*"" AND "
    with:
    Code:
    varWhere = varWhere & "[Question] LIKE ""*" & Me.TxtQuestion & "*"" AND "
    for area of law, is there a control (combo I suppose) named cmblawarea and is there a column named lawarea in the query qryRefRecords?
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    Thx!!!

    Quote Originally Posted by Sinndho View Post
    for searching topic, replace (for every occurences):
    Code:
    varWhere = varWhere & "[Question] LIKE """ & Me.TxtQuestion & "*"" AND "
    with:
    Code:
    varWhere = varWhere & "[Question] LIKE ""*" & Me.TxtQuestion & "*"" AND "
    for area of law, is there a control (combo I suppose) named cmblawarea and is there a column named lawarea in the query qryRefRecords?
    Thanks for the changes to my like strings. The searches work better, but they are only searching one area of law which is no bueno, I need them to search all areas unless area of law has a filter applied.

    I put the rest of the VB code here
    Option Compare Database
    Private Sub btnClear_Click()
    Dim intIndex As Integer

    ' Clear all search items
    Me.cmblawarea = 0
    Me.TxtQuestion = ""
    Me.TxtAnswer = ""
    Me.txtTopic = ""

    End Sub
    thats the only cmblawarea is mentioned. I assume this is something I have to add somewhere.

    I do have a query qryRefRecords and there is a column lawarea in it. However, the code seems to generate its own query. It is
    TmprryQryFrLnkFldsCrtn0123456789
    I don't really understand what is going on with that.

    THX for your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to search for several criteria, the operator should be Or instead of And:
    Code:
    varWhere = varWhere & "[Question] LIKE ""*" & Me.TxtQuestion & "*"" OR "
    Have a nice day!

Posting Permissions

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