Results 1 to 9 of 9

If no data in query field print error message

  1. #1
    benJAMin14 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    12

    If no data in query field print error message


    I have a query that feeds into a report. If one specific field of the query does not have data in it I do not want the report to be able to be displayed. Instead I would like a message box or error message that says "Please Enter Information". I have been trying to work in the design tab and use criteria like IIF(IsNull([fieldname]),MsgBox("Please Enter Information",[vbOk]),). I am not sure if this will work or even where to place the code in the design tab.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,034
    Options:

    1. code behind form that opens report only if condition is met

    2. code in report Open event that conditionally cancels opening

    Either is tricky because the condition relies on data within the report RecordSource. For option 2 the data is not yet available for reference in Open event.

    Is this a multi-record report or is it filtered to a single record? So which record should be tested to determine if there is data?
    How are you opening report?
    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
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,665
    check the query in the report for data ,then open

    Code:
    if Dcount("*","qsRptQry") = 0 then
       msgbox "No data to report"
    else
      docmd.openreport "rMyReport"
    endif

  4. #4
    benJAMin14 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    12
    Is there a way to just check a single field in the query instead of the whole thing. The Report is fed three fields from the query, but I only need the one field on the report to always have information.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,194
    perhaps review dcount function here https://www.techonthenet.com/access/...ain/dcount.php
    AFAIK, without criteria the function will return 0 if no records have a value in the field being checked, but I only know that to work on numeric field. Possibly text field as well, but probably not if there are any "" (zls) values. However, putting this function in a query would be sort of pointless if it returned a bunch of 0's in that field. You'd have to examine the return value from code anyway, so may as well do it there. I say that because the original statement implies to me that this is about query design ("query design tab").

    But I'm confused about the idea of a message box to provide info. This should be taken care of before the query is run unless one wants to add a parameter to it. That is not foolproof because user can just click OK without providing a valid value. I think more info on the whole process would allow for more focused answers.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  6. #6
    benJAMin14 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    12
    So I have a report that takes information from a query and displays it. The query has three fields so the report only displays three fields. Two of those fields are always filled in no matter what. O
    ne of the automatically input fields is a run number, so when they select the report I have it so that they have to input the run number parameter to display that record in the report.
    The third field is often forgotten to be input by my coworkers. I do not want them to be able to open the report to print it out without the third field that they always forget to fill in having information on it. I would like for some sort of an error message to occur if the field is not filled in for that particular record. I am not sure if this is possible.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,194
    If there is a form involved, very doable, but you haven't indicated as much. Controlling user input is one of the basic reasons for using forms.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,034
    Could you answer questions in post 2?

    Use domain aggregate function to determine if field is null for any record (I never allow empty strings in text fields). If there is a filter applied to report, might want function to use same criteria. So code behind button on form:

    If DCount("*", "tablename", "fieldname Is Null") = 0 Then
    DoCmd.OpenReport "reportname", acViewPreview
    Else
    MsgBox "Must enter data into fieldname"
    End If
    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.

  9. #9
    benJAMin14 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    12
    Quote Originally Posted by June7 View Post
    If DCount("*", "tablename", "fieldname Is Null") = 0 Then
    DoCmd.OpenReport "reportname", acViewPreview
    Else
    MsgBox "Must enter data into fieldname"
    End If
    This worked as I had a button on my form opening the report and was able to code this into the button. Thank you!

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

Similar Threads

  1. Replies: 4
    Last Post: 05-01-2017, 11:00 PM
  2. Replies: 4
    Last Post: 02-24-2017, 10:16 AM
  3. Replies: 3
    Last Post: 01-20-2015, 01:35 PM
  4. Replies: 4
    Last Post: 10-02-2014, 11:01 AM
  5. Replies: 2
    Last Post: 04-04-2013, 03:13 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
  •  
Tech Forums: Microsoft Office Forums