Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006

    Question Unanswered: Getting Dialog Box Input into WHERE clause?

    We have a form that has a query as a row source. That query calls other queries to eventually compose the resultset on the form. Nearly at the bottom of the query chain is a [Enter Job ID] variable which, when the form is executed pops up a dialog box asking for the ID. Once supplied, the form displays.

    The problem we're having is storing this dialog box entered value for later use within a WHERE clause for a combo-box shown on the same form. If we add [Enter Job ID] to the combo-box's WHERE clause hoping this value would be the same dialog box input, it simply pops up another dialog box asking for the Job ID. This is not the effect we're after.

    Any clues on how we can access the dialog box entered value, which clearly is feeding a 8-levels deep query clause, within the form's combo-box?

    We've had luck adding a hidden combo-box (named: Job_ID) to the same form and having its RowSource set for [Job ID] (we modified the form's row source to return back this value so that we can determine what was entered) and then using Forms![MyForm]![Job_ID] within other combo-box WHERE clause. Unfortunately when we try to access combo-box of a new record expecting to see a filtered list of items to select from, we see blank (the Job ID returned null).

    So we changed things up and tried using Me.Recordset.Fields("Job ID").Value within the Form_Open event. We're trying to store the value in a global variable for use. Some forms work but others report a 3021 - No Current Record error.

    Any ideas on how to approach this?
    Last edited by quantass; 09-06-06 at 16:50.

  2. #2
    Join Date
    Oct 2004
    Oxfordshire, UK
    After [Job_ID] has changed, you'd need to requery the ComboBox which has RowSource referencing Forms![MyForm]![Job_ID] (or any object) to get a valid recordset. The problem with using parameter queries is that you haven't got just one event (which would be triggered by changing the query results) where you could write some code saying:


    You could use the Form_Current Event, but this would:
    1. Run each time you navigate a record (as well as when the parameter is entered)
    2. Not run if no records were returned.

    Open the form and enter data in an unbound control. Change the value of [Job_ID] here and you can use the Control_AfterUpdate event to change the whole form recordset (Me.Requery) and any related ComboBoxes (ComboBox.Requery).

  3. #3
    Join Date
    May 2006

    Could you please help?

    Dear All,

    I have a database and on one of the command buttons which creates invoice, I am having a bit of problem with it.
    Basically the command button is called "InvoiceClient" and when I click on the button it creates an invoice (Report) which is called "rptInvoiceClient".
    Once i raise an invoice it automatically produces an autodate (Todays date) Sometimes, we need to reprint a copy of the invoice BUT when we do so, then the date again changes to a new date and I WANT THE DATE TO REMAIN AS THE VERY FIRST TIME WHEN THE FIRST INVOICE WAS RAISED.

    I would be most grateful if anyone could help.

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    Last edited by izyrider; 09-09-06 at 12:19. Reason: was a reply to Emal - since then i found his thread
    currently using SS 2008R2

Posting Permissions

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