Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    refresh? (XP,2000)

    I have a subform named "prospect_lookup" that uses the contents of an unbound textbox control (searchbox) on the parent form for the select criteria for the data source. Problem is that when the form appears, the subform automatically displays its data results even though the textbox control (searchbox) is null. In other words, it displays all records. Is there any way i can prevent it from automatically displaying those records until the textbox is updated? I do have the following event for textbox.after_update:

    Private Sub Searchbox_AfterUpdate()
    Refresh
    End Sub

    Here is the data source for the subform "prospect_lookup":

    SELECT PROSPECTS_Table.[Prospect/Area Name], PROSPECTS_Table.[Original/Alias Prospect Name], Count(LINE_Table.ProspectID) AS CountOfProspectID FROM PROSPECTS_Table LEFT JOIN LINE_Table ON PROSPECTS_Table.ProspectID = LINE_Table.ProspectID GROUP BY PROSPECTS_Table.[Prospect/Area Name], PROSPECTS_Table.[Original/Alias Prospect Name], PROSPECTS_Table.ProspectID HAVING (((PROSPECTS_Table.[Prospect/Area Name]) Like "*" & [forms]![query_input]![searchbox] & "*")) OR (((PROSPECTS_Table.[Original/Alias Prospect Name]) Like "*" & [forms]![query_input]![searchbox] & "*")) OR (((Count(LINE_Table.ProspectID)) Like "*" & [forms]![query_input]![searchbox] & "*")) ORDER BY PROSPECTS_Table.[Prospect/Area Name];

    What do I have wrong? Every time I open the parent form and enter something into the textbox control, I get a message the form referred to in my VB code cannot be found. I know the form is here so I must have bad syntax.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: refresh? (XP,2000)

    When you use a Like operator and phrase it <pre>Like "*" & <some value> & "*"</pre>

    then you will get all records returned if the value is null. To prevent that, you have to include a condition like <pre>AND <some value> Is Not Null</pre>

    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refresh? (XP,2000)

    Super - that did it. Thank you.

Posting Permissions

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