Results 1 to 12 of 12
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    One query and two different conditions

    Hi to all,

    I have a query with several fields, I want to use the same query when clicking two different buttons;


    When cmdButton1 is clicked open the query with condition fkStatus =4
    When cmdButton2 is clicked open the same query with condition fkDeleted=0
    Is this possible ?

    Sincerely yours
    Khalil

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Khalil Handal View Post
    Hi to all,

    I have a query with several fields, I want to use the same query when clicking two different buttons;
    When cmdButton1 is clicked open the query with condition fkStatus =4
    When cmdButton2 is clicked open the same query with condition fkDeleted=0
    Is this possible ?

    Sincerely yours
    Khalil
    IMHO it would be better not to actually open a query. Best to keep users away from tables and queries.

    I would use an SQL SELECT statement as the Record Source of a form or a report depending on how the user needs to interact with that data. A report if they just need to view it or a form if they need to make some additions or edits to it. With a form you have control of just what the user can do to the data.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is this possible ?
    yes - for forms, but not for queries. So create a form for the query, then in your button click event you would have

    docmd.openform "myform",,"fkStatus=4"

  4. #4
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    Thanks for the reply. I will give it a try and keep you updated.
    What if I want to export it to an Excel file ?

    Khalil

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use the transferspreadsheet command

  6. #6
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Thank you all.
    Khalil

  7. #7
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi again,
    I used a report for print preview the data and it has On No Data property that I can use if no records exists.
    How would I display a message box if no data is found for the form (based on the query), the query shows all the records without filter ?
    the filtering is done when opening the form .

    Khalil

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Put the following in the reports On No Data event:
    Code:
    MsgBox "Your message goes here"
    Cancel = True ' Use this line to stop report from appearing
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Thanks, This works fine for the report.

    What about the Form?
    How can this be done with the Form because there isn't (On NO Data) ???

    Khalil

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest use something like this in your button click event

    Code:
    if dcount("*","myQuery","fkStatus=4")=0 then
        msgbox "No Records to display"
    else
        docmd.openform "myForm",,"fkStatus=4"
    end if

  11. #11
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Thank you very much. It worked fine

  12. #12
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    I used the OutputTo method to have the records exported to Excel. I used the following code and it shows ALL the records in the query.
    Code:
    DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, strPath & "\" & strFileD
    How should I only select those records with fkStatusId=4 ?

    Khalil

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

Similar Threads

  1. Replies: 8
    Last Post: 09-08-2017, 01:23 PM
  2. Doing a Date query with conditions
    By bozin in forum Access
    Replies: 2
    Last Post: 08-08-2015, 07:55 PM
  3. Query insert with too many conditions...?
    By gmaster in forum Queries
    Replies: 1
    Last Post: 05-19-2014, 11:49 AM
  4. Replies: 6
    Last Post: 02-13-2011, 06:02 PM
  5. Query on multipl conditions.
    By Goldenbird in forum Queries
    Replies: 0
    Last Post: 06-28-2009, 11:45 PM

Tags for this Thread

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