Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Location
    Islamabad
    Posts
    13

    Exclamation Query Criteria Based On Form's Text Boxes and Combo Boxes

    Hey to All
    I am wondering and searching through threads since many hours about the solution of my problem. I success to find only 1 thread on this topic but in which 1 part of my problem is mentioned.

    What is My Problem?
    I HAVE...


    ...I have a query(qrySearch) which contains data from 2 tables(Accounts & Transaction).

    ... I have a form(SearchForm) where i put some text boxes such as(txtStartDate, txtEndDate, txtPaymentDescription, txtPaymentType(Cash or Instrument), txtSubGroup, txtAmount, e.t.c) and some combo boxes such as (cboAccountType, cboAccountName, cboAccountNumber, cboTransactionID, cboPaymentMode(Credit or Debit)).

    and

    Command Box(Open Query).

    WHAT I WANT?
    I want user can open query sub form according to his/her criteria typed in text boxes or if he/she leave the text boxes or combo boxes blank then all related records show in query sub form.

    WHAT I DO?
    Here is The Code Behind Search Query.

    Code:
    SELECT Transcation.Category, Transcation.PayDetails, Transcation.Description, Transcation.DepositAmount, Transcation.[W/DAmount], Transcation.Date, Transcation.TranID, Account.AccountName, Account.AccountNumber, Account.AccountID
    FROM Account INNER JOIN Transcation ON Account.AccountID = Transcation.AccountID
    WHERE ((([Description] Like "*" & [Forms]![SearchForm]![txtamountdetail] & "*" Or [Forms]![SearchForm]![txtamountdetail] Is Null)=True) AND (([Category]=[Forms]![SearchForm]![txtsubcategory] Or [Forms]![SearchForm]![txtsubcategory] Is Null)=True) 
    AND (([AccountName]=[Forms]![SearchForm]![cboacname] Or [Forms]![SearchForm]![cboacname] Is Null)=True) 
    AND (([AccountNumber]=[Forms]![SearchForm]![cboacno] Or [Forms]![SearchForm]![cboacno] Is Null)=True) AND (([TranID]=[Forms]![SearchForm]![cbotransno] Or [Forms]![SearchForm]![cbotransno] Is Null)=True));
    But it is not working.....



    I will appreciate for your help. Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    May 2010
    Location
    Islamabad
    Posts
    13
    thanks for your reply. i check this tutorial help full and also find another tutorial which is as i like but in its searchform query retrieve data from one table only. while in my form the query retrieve data from two jointed tables. as i put code above which is using behind this query.

    now problem is that when i open form then at searchform subform of query pupolated with data but as i press command button for requery of query subform then all the data get hide.





    I am wondering where is problem. in my code behind the searchquery or because of my query is retireving date from 2 tables...??

  4. #4
    Join Date
    May 2010
    Location
    Islamabad
    Posts
    13
    i am uploading a sample database file which i found somewhere in thread. i want search form like this but its not working.


    please help me to sort out it?

    thanks

  5. #5
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Hi Hasan,

    Can you upload your db with sample data, so it would be easy to work on and give you the clear solution of your requirement.

  6. #6
    Join Date
    May 2010
    Location
    Islamabad
    Posts
    13
    thank for your reply. my database is here attached.

  7. #7
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Hello Raza, I will go through and will download the db in my office Inshallah. The solution would be not very difficult

    Hope for the best.

  8. #8
    Join Date
    May 2010
    Location
    Islamabad
    Posts
    13
    thanks for your cooperation. bit part of issue solved but still have to be solved.

  9. #9
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Hi Hasan,
    I worked on your db and hence attaching it back with some changes in the Search Query of the subform. (I worked on some fields)

    Please note that I am using Microsoft Access 2011 and with 64 Bit, so I have converted your db to this verion. Anyhow I am pasting here the SQL of search query:

    Code:
    SELECT Account.AcTypeID, Transcation.DepositAmount, Transcation.[W/DAmount], Transcation.Date, Transcation.PayDetails, Transcation.Category, Transcation.Description, Transcation.TranID, Account.AccountName, Account.AccountNumber, Account.AccountID
    
    FROM Account INNER JOIN Transcation ON Account.AccountID = Transcation.AccountID
    
    WHERE ((([AcTypeID]=[Forms]![SearchForm]![cboactype] Or [Forms]![SearchForm]![cboactype] Is Null)=True) AND (([AccountName]=[Forms]![SearchForm]![cboacname] Or [Forms]![SearchForm]![cboacname] Is Null)=True) AND (([AccountNumber]=[Forms]![SearchForm]![cboacno] Or [Forms]![SearchForm]![cboacno] Is Null)=True));
    you can copy and paste the query to your search query. Also there are some changes I made in the Click Event of the Search Button and Clear Button of the Form:

    Hope this Help! and you can go through for other Fields/Control on your Form:

  10. #10
    BlueIshDan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    8
    This may be of help
    It will generate a form for you to go by
    https://www.accessforums.net/code-re...orm-44282.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 04-01-2011, 11:41 AM
  2. Running a query based on 2 combo boxes.
    By blessoni in forum Queries
    Replies: 4
    Last Post: 12-12-2010, 02:09 PM
  3. Replies: 0
    Last Post: 12-02-2010, 11:59 AM
  4. Replies: 15
    Last Post: 09-18-2010, 01:19 PM
  5. populate boxes based on combo box choice
    By Mattm1958 in forum Forms
    Replies: 13
    Last Post: 08-30-2010, 02:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums