Results 1 to 6 of 6
  1. #1
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    open report from main form with required field in subform

    Good morning all,

    I need your help with what I hope is a simple question: I have a form with a subform, respectively based on Table1 and Table2. Table2 contains a Required field named "Status".

    I have a button in the main form with the following code:

    Code:
    Private Sub Button_Click()
    DoCmd.OpenReport "E_Inspection", acViewPreview, , "IDInspection = " & Me.TxtID
    End Sub
    Problem: hitting the button does not prevent users from opening the report when they have not entered the appropriate information in the "Status" field (whose control is a combobox named CmbStatus) in the subform.


    Is there a way to prevent opening the report if Status has not been entered?

    Thank you all in advance!

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I would fix the issue.
    In the before update event of the subform you can prevent users from leaving the record without setting the value of cmbStatus

    Code:
    If IsNull(Me.cmbStatus) Then 
        msgbox"Status Required!"
        Cancel = True
        Exit Sub
    End IF
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Hello Minty!

    First thank you for spending some of your time on my case.

    I did exactly what you wrote but nothing happens. I'm still able to open the report.

    May I add that all fields in the subform have default values (that can be changed or left as they are), except the Status field that must be entered manually by users and the primary key field.

    So, at the moment, when I open the main form, I can directly click on the open report button, not bothering with the subform and its Status field, and that's where I need something to prevent the opening of the report, and a warning as well.

    Any idea?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can test the value in the subform:

    Forms Refer to Form and Subform properties and controls

    though that will only test the first (or selected) record. If you want to test all records, you can use DCount() with the appropriate criteria.

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    Thumbs up Solution

    I think I managed, based on your suggestion... The following code works, on the Click Event of my button, in the main form:

    Code:
    Private Sub Button_Click()
    If IsNull(Me.Fm_Directives.Form.CmbStatus) Then
      MsgBox "Status required"
      Cancel = True
    Else
       DoCmd.OpenReport "E_Inspection", acViewPreview, , "IDInspection = " & Me.TxtID
    End If
    End Sub
    Thank you!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In that situation you don't need the

    Cancel = True

    line. If you had Option Explicit at the top it would probably throw an error in fact (there's no Cancel variable in that code).

    http://www.baldyweb.com/OptionExplicit.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. SubForm will not open from Main Form
    By dvgef2 in forum Forms
    Replies: 3
    Last Post: 03-28-2013, 08:24 AM
  2. Open Report From Subform on Main Form
    By burrina in forum Forms
    Replies: 4
    Last Post: 12-25-2012, 08:11 PM
  3. Replies: 1
    Last Post: 12-04-2011, 09:11 PM
  4. Replies: 5
    Last Post: 11-11-2011, 11:06 AM
  5. Replies: 2
    Last Post: 08-11-2011, 10:02 AM

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