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

    Open report based on multiple criteria using vba

    Hi to all,


    I have a form with a subform. A command button is on the main form to open a report. I select a person form the main form and the subform shows related records for that specific person. I want to print a report for records in the subform where a date field is Null. I used this line of command but it is not working.
    Code:
    DoCmd.OpenReport "rptMemberBorrowedBooks", view:=acViewPreview, _
                  WhereCondition:="[fkMemberId] = " & Me!pkMemberId & " and Forms![frmBooksBorroewdSubForm]![DueDate] is Null "
    I do not know where the error is?
    Any help is appreciated.

    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
    Perhaps:

    DoCmd.OpenReport "rptMemberBorrowedBooks", view:=acViewPreview, _
    WhereCondition:="[fkMemberId] = " & Me!pkMemberId & " and " & Forms![frmBooksBorroewdSubForm]![DueDate] & " is Null"
    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
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi Bob

    I tried it and had the following error:

    Error 2450: cannot find the referenced form 'frmBooksBorrowedSubForm'

    The form exists.
    Khalil

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The form exists.
    But is it Open. It would need to be open.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    yes the form is open.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I’ve just noticed that the name of the form suggests to me that it is actually a sub form on another form. If that is correct then the expression to reference it would be something like:

    DoCmd.OpenReport "rptMemberBorrowedBooks", view:=acViewPreview, _
    WhereCondition:="[fkMemberId] = " & Me!pkMemberId & " and " & Forms!NameOfMainForm.[frmBooksBorroewdSubForm].Form.[DueDate] & " is Null"

    You would need to substitute NameOfMainForm with the appropriate form name.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Here is how it looks now. It gives an error of not finding the field:
    Code:
    DoCmd.OpenReport "rptMemberBorrowedBooks", view:=acViewPreview, _
            WhereCondition:="[fkMemberId] = " & Me!pkMemberId & " and " & _
                Forms![frmBorrowingBooks].[frmBorrowedBooksSubForm].Form.[DueDate] & " is Null"

    The error is:
    Error 2465: can't find the field '|1' refered to in your expression
    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
    Can post a copy of the db. compact and zip it.
    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
    it is 32 MB file size

  10. #10
    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
    it is 32 MB file size
    Even after doing a Compact and Repair and then zipping it?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    After zipping it, the size is 13 MB.

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Did you compact and repair first?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    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
    After zipping it, the size is 13 MB.
    I suggest that you create a new db. Import into it just the tables, queries, forms and report that are needed to illustrate the problem.
    do a Compact and Repair and then zip it before posting.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  14. #14
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi Bob,

    I have created the new database and I am attaching it for you.

    KhalilLibrary_FE.zip

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    PMFJI, but make sure frmBorrowedBooksSubForm is the actual subform control name?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-31-2020, 04:20 AM
  2. Replies: 10
    Last Post: 04-19-2017, 09:42 AM
  3. Replies: 2
    Last Post: 10-08-2016, 10:06 PM
  4. Replies: 8
    Last Post: 05-29-2015, 11:52 AM
  5. Replies: 1
    Last Post: 07-02-2010, 03:55 AM

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