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

    Unanswered: Help with Continuous Forms and RecordSource

    Hello 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:

    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
    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.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
    Provided Answers: 15
    Here is one solution:

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

  3. #3
    Join Date
    May 2009
    Hmm...seems like this would be a good clean way to 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.


  4. #4
    Join Date
    Dec 2004
    Madison, WI
    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.

    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