Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    Unanswered: Me! in Query or SQL statement

    Question 1

    Have 2 comboboxes in all my purchase and sale forms. One for "Company" and another on for "Contact". I have a query that limits the Contact-box to the Contacts working in the above selected Company - box. SQL Statement like this:


    SELECT TblCONTACTS.contactID, TblCONTACTS.NAME
    FROM TblCONTACTS
    WHERE (((TblCONTACTS.[COMPANY ID])=[Forms]![BROSWEALLpurchases]![COMPANY ID]))
    GROUP BY TblCONTACTS.contactID, TblCONTACTS.NAME;


    But I dont like having to do new queries for every form. Have many.

    So Id like to change: =[Forms]![BROSWEALLpurchases]![COMPANY ID]))

    into something like this =Me.[COMPANY ID])) or in other words limit the contacts-box in the Current form. No matter what the form where both controls are in no matter what the form is called.

    But cant get it to fly. Dont know the syntax.


    Question 2

    Can I do all this, universally, in a module or something so that all controls named say ContactAtCompany_Combo should have this rowsetting?
    And furthermore can I make the current Private Sub Company_Combo_AfterUpdate()
    Me.ContactAtCompany.requery
    Somewhere universally also? So I dont have to apply that to all the company controls?

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    "Me" won't work in queries.
    You can create a public function which returns the value you want and use it in your queries.

    Public Function Qwerty() As Long
    Qwerty = Screen.ActiveForm.txtCompanyID
    End Function

    SELECT TblCONTACTS.contactID, TblCONTACTS.NAME
    FROM TblCONTACTS
    WHERE (((TblCONTACTS.[COMPANY ID])=Qwerty()))
    GROUP BY TblCONTACTS.contactID, TblCONTACTS.NAME;

    Igor

Posting Permissions

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