Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    29

    Unanswered: Help with Continuous Forms and RecordSource

    Hello all...got a question. Been hashing this out and cant get it to do what I want it to do. I have a Continuous Form that looks at a table and returns results based off a search box on a form. What I want to do is have a MsgBox pop up if no results are returned. Here is what I currently have:

    Code:
    Private Sub search_button_Click()
    
    If Me.filter_ID = "" Then
    MsgBox "Please Enter Merchant (DBA or Legal) or an ID to Search for."
    Exit Sub
    
    Else
    Me.text_search.Visible = True
    
    Me.filter_ID = SuperReplace(Me.filter_ID, " ")
    Me.filter_ID = "*" & Me.filter_ID & "*"
    
    Me.RecordSource = "SELECT * FROM qryMERCHSearch WHERE DBASearch LIKE '" & Me.filter_ID & "'"
    Me.Requery
    
    Me.filter_ID = Replace(Me.filter_ID, "*", "")
    
    Me.ID.Visible = True
    Me.DBA.Visible = True
    Me.Merch.Visible = True
    Me.Email_Date.Visible = True
    Me.Line65.Visible = True
    Me.text_search.Visible = False
    
    End If
    End Sub
    I know i need to do something like a If IsNull or IF ____ = True with the Recordsource and Requery function, but i cannot get it to work correctly...

    any ideas???

    Thanks ahead of time!!!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is one solution:

    dim rst as dao.recordset
    set rst=me.recordsetclone
    rst.recordsource = "SELECT ...
    rst.requery
    if rst.eof then ' no matching records
    msgbox ...
    else
    me.recordsource = "SELECT ...
    me.requery
    end if
    rst.close
    set rst.nothing
    Have a nice day!

  3. #3
    Join Date
    May 2009
    Posts
    29
    Hmm...seems like this would be a good clean way to go...my problem is its thrwoing me an error...
    "Method or Data Member Not Found"

    It seems to not like my "Me.filter_ID" code in my "SELECT...." statement.

    Thoughts?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Otherwise, you can also simply change the recordsource at any time using this command:

    me.recordsource = "MyQueryName"

    where "MyQuerName" is a query you design (using the wonderful designer tool) and save. You can use the OnCurrent event of the form to test the count() to see if any records are returned.

    I wouldn't recommend using the filter command in any way (or code behind it). There are issues with it. Especially when you try to filter by records with odd characters. If you absolutely must (again, not recommended) use the filter command, I'd recommend storing the value of the filter into a variable first and then use the variable for your criteria.

    Instead, you should modify the recordsource for the form (or a listbox) or a subform (however you have it setup.)

    See Sinndho's code:

    If you're having problems creating the "SELECT..." statement,
    simply design a query which has the criteria that meets your needs and save it. Then substitue the query name in the "Select...." (keeping the ")

    Note: In the query, you can reference a value from the form in the criteria for a field by using: =Forms!MyMainFormName!MyFieldName. This might be based upon an unbound combobox or listbox on a form.

    Often times, I will base my recordsource off a query with criteria and then use the AfterUpdate event of the combobox or listbox (search type box) to then simply just issue a requery command to the form.
    ie.

    me.requery
    Last edited by pkstormy; 10-16-09 at 02:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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