Results 1 to 8 of 8
  1. #1
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23

    VBA Code - if MsgBox Displays do not open report

    Hi, I've tried for hours to figure out this code with no luck.



    I’ve set up code “On Load” of a report that looks to see if the Manufacture Date field is filled in, if it is the report opens, no worries.
    However if there is no date I have a message come up saying so. Again no worries.
    Here's the problem. Once the OK button on the warning message is clicked the report opens, but the whole point of this code is that if there isnt a Manufacture Date it will not let you view the report.

    Can anyone help me? I am a newbie when it comes to all this code stuff. Cheers
    Click image for larger version. 

Name:	HELP.JPG 
Views:	12 
Size:	38.6 KB 
ID:	27076

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your strFilter will do nothing as shown.
    so change:

    Code:
    if IsNull(me.ManufDate) then
       msgbox "No date entered"
    else
       strFilter = "[manufDate]='" & me.manufDate & "'"
       docmd.openreport "report", acViewReport, strFilter
    endif

  3. #3
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23
    I used your code but the report still displays when there is no Manufacture Date.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Can you confirm your code now squirrel?


    Sent from my iPhone using Tapatalk

  5. #5
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23
    Sure.
    Click image for larger version. 

Name:	HELP2.JPG 
Views:	9 
Size:	34.0 KB 
ID:	27092

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are in the load event of a report, are you opening a different report or the same one? If it is the same one then this code should be in the place where the report is being run from.

  7. #7
    Squirrel1804 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    23
    I am opening the report from the switchboard. However I dont want the report to open at all if there is no manufacture date.
    This is how Access set up the switchboard and it cant be converted to VBA.
    I did try added the code in below the bit with the star which is the opening of the report but it just come up with a popup about no manufacture date
    Click image for larger version. 

Name:	HELP3.JPG 
Views:	8 
Size:	49.2 KB 
ID:	27093

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If it is the same one then this code should be in the place where the report is being run from.
    I think I know what is meant by this, but I'll put it another way and add something.
    Whatever event you're using to open the report, test for the field value. If not as desired, don't open the report. Something like
    Code:
    IF IsNull(Me.ManufDate) Then
      Exit Sub
    Else
      Docmd.OpenReport....
    End If
    Not sure if you're saying the report has no data if the field in question is Null or "". If that's the case, you can also use the report OnNoData event.

    EDIT: OK I think we all assumed this wasn't done via a Macro.
    Last edited by Micron; 01-18-2017 at 05:28 PM. Reason: additional info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2014, 01:05 PM
  2. Replies: 3
    Last Post: 10-20-2014, 03:25 PM
  3. VBA code for MsgBox
    By mladen273 in forum Programming
    Replies: 8
    Last Post: 01-17-2013, 10:05 PM
  4. Cannot locate VBA code for MsgBox!
    By sjl in forum Forms
    Replies: 5
    Last Post: 12-20-2011, 05:26 PM
  5. Use MsgBox to open different forms
    By blueraincoat in forum Forms
    Replies: 10
    Last Post: 03-13-2011, 10:40 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