Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    NM, USA

    Unanswered: SQL and VBA search form problem.

    Okay. I know I'm doing this wrong because I just can not get this to work.

    Here is the problem:

    I have written a search query that takes parameter values from the user. I have a form based on this query, which works just dandy all by its self

    Here is the query...I hope some of the details are intuitive:

    SELECT * FROM tblContractors
    WHERE strFirstName LIKE Forms![frmEditProvider]![Fname] And strLastName LIKE Forms![frmEditProvider]![Lname];
    When I open the form based on this query (which I called "FindProvider"), I get this cryptic Enter Parameter Value promt that has the "Forms!frmEditProvider..." stuff in it. As I said, it works dandy.

    Well, I have FindProvider embedded in another form, which I called "frmEditProvider"... well, when I open frmEditProvider, I get the same prompts, and if I enter the values, i get results in the embedded form. also on this form, I have text boxes for input (named Fname and Lname in their properties) and a button called "Search". As of the moment, I have:

    Private Sub cmdSearch_Click()
    End Sub

    Which gives me this error message:

    "The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating witht he OLE server or Active X control.

    * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].

    *There may have been an error evaluating the function, event, or Macro."
    What I want:
    • I don't want the prompts to pop up as soon as you open the frmEditProvider.

    • I want the user to enter their search parameters in the text boxes, click "Search" and THEN have the query run and show results in the embedded form.

    I've been looking all over the internet to find some code that will help me, but I haven't found anything yet... and deadlines are breathing down the back of my helpless neck. Please help. Thank you.

  2. #2
    Join Date
    Oct 2004
    Melbourne, Australia
    I think this is something we have seen before in some (fairly) recent threads. I believe it has something to do with the sequence of events with which Access opens the form. The query is activated before the value of data in the form is known. You need to set up a process whereby the form's Rowsource is blank when you open the form, but is set to a query string incorporating the value of a (parameter) field after you have entered data in the field (usually either AfterUpdate or LostFocus). Having set the RowSource, you then do a Requery to see all associated data. If you get the demand for parameter values on closing the form, st the Rowsource to blank again and requery in the Close event.

  3. #3
    Join Date
    Sep 2008
    NM, USA

    thank you for your reply but that doesn't really help me. I'm not entirely hip to the database world jargon yet, so you're gonna have to give that to me in English.

    I've decided I'm going to go a different direction with this anyway... instead of using an embedded query-based form, I'm going to try to do it with a flex grid, pass the values from the text boxes and run the query in the OnClick event.... gonna see if that works...

Posting Permissions

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