Results 1 to 5 of 5

Open Subform to Multiple Record if Exists, Open New Subform if Not

  1. #1
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    9

    Question Open Subform to Multiple Record if Exists, Open New Subform if Not

    I have a form (frm_Sch1) with a command button which is intended to open up a specific form depending on a selected compliance option.



    The IndexID and Reporting Year in tbl_Sch1 (linked to frm_Sch1) does not necessarily have a matching record in the subsequent tables but I would like this button to open up the next form if there is a match and to open a blank form if it doesn't.

    It may be important to note that the subsequent form (frm_Sch1 Adjust) contains a subform in datasheet view, this is how I would like to display the record given that one IndexID and Reporting Year in frm_Sch1 may have multiple matches in frm_Sch1 Adjust.

    I've been playing around with different functions but I'm very inexperienced with VBA and not sure if I'm using them correctly. Here is the code that I'm currently working on, it's definitely wrong but any suggestions or tips will be greatly appreciated. Thank you for taking the time to read this.

    *I'm definitely using DCount incorrectly, but the error that I'm currently receiving is Error: 2465, Access can't find the field '|1'. The read is where the error is*

    Option Compare Database
    Option Explicit

    Private Sub btnSch1Next_Click()

    Dim SQLAdj As String
    Dim SQLAnx As String

    SQLAdj = "SELECT [tbl_Sch1 Adjust] " _
    & "FROM [tbl_Sch1 Adjust] " _
    & "WHERE tbl_Sch1 Adjust.[Index&Facility] '" & Me.IndexFacility & "'" _
    & "AND tbl_Sch1 Adjust.[Reporting Year] " = Me.ReportingYear


    SQLAnx = "SELECT [tbl_Sch1 Annex] " _
    & "FROM [tbl_Sch1 Annex] " _
    & "WHERE tbl_Sch1 Annex.[Reporting Facility (Facility Name)] '" & Me.IndexFacility & "'" _
    & "AND tbl_Sch1 Annex.[Reporting Year] " = Me.ReportingYear

    If IsNull(Me.IndexFacility) Then
    MsgBox "Index and Facility not selected.", vbOKOnly, "Warning"
    End If

    If IsNull(Me.ReportingYear) Then
    MsgBox "Reporting Year not entered.", vbOKOnly, "Warning"
    End If

    If Not IsNull(Me.SulphurLimitOption) Then
    If Me.SulphurLimitOption = "Pool Average" Then
    If DCount("[Index&Facility]" And "[Reporting Year]", [tbl_Sch1 Adjust], SQLAdj) > 0 Then
    DoCmd.OpenForm "SubAdjustForm", acNormal, , SQLAdj, acFormEdit, acWindowNormal
    Else
    Forms("SubAdjustForm").Requery
    DoCmd.OpenForm "SubAdjustForm", acNormal, , , acFormAdd, acWindowNormal
    End If
    Else
    If DCount("[Reporting Facility (Facility Name)]" And "[Reporting Year]", [tbl_Sch1 Annex], SQLAnx) > 0 Then
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , SQLAnx, acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1Adjust", acNormal, , , acFormAdd, acWindowNormal
    End If
    End If
    Else
    MsgBox "Sulphur Limit Option not selected.", vbOKOnly, "Warning"
    End If

    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,742
    DCOUNT("*", "query","where clause")

    DCOUNT("*", "qsQuery","[ID]=" & txtBox)

  3. #3
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Quote Originally Posted by kennethnaga View Post
    ......Here is the code that I'm currently working on, it's definitely wrong but any suggestions or tips will be greatly appreciated.
    OK, here are some of the things I noticed:


    - There is a serious lack of comments in the code.

    - You have an extremely poor naming convention.
    -------------------
    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
    -------------------

    About the object names:
    [tbl_Sch1 Adjust] - has a space in the table name
    [Reporting Year] - has a space in the field name
    [Index&Facility] - has a special character in the field name
    [Reporting Facility (Facility Name)] - has spaces and punctuation in the field name

    - You know DCount() syntax is wrong.

    - The DoCmd.OpenForm syntax is wrong
    Code:
    DoCmd.OpenForm "SubAdjustForm", acNormal, , SQLAdj, acFormEdit, acWindowNormal
    "SQLAdj" is a SQL string. The "WHERE Condition" clause should be a where clause WITHOUT the Where, NOT the full SQL


    - And speaking of SQLAdj, there are at least 5 syntax errors. (Same for SQL Annex)
    I created a form and table for testing purposes.

    If I take "SQLAdj" and convert it to one line, this is your SQL :
    Code:
        SQLAdj = "SELECT [tbl_Sch1 Adjust] FROM [tbl_Sch1 Adjust] WHERE tbl_Sch1 Adjust.[Index&Facility] '" & Me.IndexFacility & "' AND tbl_Sch1 Adjust.[Reporting Year] " = Me.ReportingYear
    You cannot use the table name to select all fields. You must use
    Code:
    "SELECT * FROM [tbl_Sch1 Adjust]
    For the 2nd and 3rd reference to "tbl_Sch1 Adjust", they are missing the brackets (required because of the space in the table name)
    In the WHERE clause, an operator is missing between [Index&Facility] and Me.IndexFacility
    Code:
    WHERE tbl_Sch1 Adjust.[Index&Facility] '" & Me.IndexFacility &
    In this part of the SQL
    Code:
    "AND tbl_Sch1 Adjust.[Reporting Year] " = Me.ReportingYear
    the equals sign is to the right of the double quote when it should be to the left of the double quote and there is a missing ampersand BEFORE "Me.ReportingYear"

    Here is the way the SQL should be formed:
    Code:
    SQLAdj = "SELECT * FROM [tbl_Sch1 Adjust] WHERE [tbl_Sch1 Adjust].[Index&Facility] = '" & Me.IndexFacility & "' AND [tbl_Sch1 Adjust].[Reporting Year] = " & Me.ReportingYear

    Having fixed the variable "SQLAdj", let's look at the "Openform" command.
    Instead of
    Code:
    DoCmd.OpenForm "SubAdjustForm", acNormal, , SQLAdj, acFormEdit, acWindowNormal
    you should have
    Code:
    Dim AdjCriteria As String
    
    AdjCriteria = "[tbl_Sch1 Adjust].[Index&Facility] = '" & Me.IndexFacility & "' AND [tbl_Sch1 Adjust].[Reporting Year] = " & Me.ReportingYear
    DoCmd.OpenForm "SubAdjustForm", acNormal, , AdjCriteria, acFormEdit, acWindowNormal



    EDIT: You have commands like
    Code:
    DoCmd.OpenForm "SubAdjustForm", acNormal, , , acFormAdd, acWindowNormal
    In this case, "SubAdjustForm" in not a sub form.

    So I am just curious about your definition of a sub form. A form becomes a sub form when it is embedded in another form.
    If you open a form and drag & drop a different form on the first form, then you have a sub form. The 2nd form is contained in a "Sub Form" control.
    Or if you add a sub form control to a form and you set the source object to an existing form name, you have a sub form.
    Last edited by ssanfu; 11-13-2019 at 08:32 PM. Reason: Had another thought (ouch!)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    9
    Hi Steve,

    I know the naming conventions are terrible, I am working on an existing db where the designers didn't consider how it may be used. I'm super super new to VBA and Access so your help is greatly appreciated! I have managed to get it working and found myself making some pretty silly errors once you've pointed them out to me, thank you for all your help. This community is so great!!

  5. #5
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Didn't realize you are working on an inherited dB. Tough job!

    Happy to help - post back when you have more questions...... someone should be able to help...


    Good luck with your project.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Subform will not open to last record
    By MLangendorf in forum Forms
    Replies: 10
    Last Post: 09-14-2016, 03:51 PM
  2. Replies: 8
    Last Post: 08-09-2013, 09:52 AM
  3. Need to have subform open to the last record
    By PirateGenie in forum Access
    Replies: 1
    Last Post: 03-22-2013, 09:47 AM
  4. Open record from a subform
    By pratim09 in forum Forms
    Replies: 1
    Last Post: 04-28-2011, 12:01 AM
  5. Replies: 1
    Last Post: 11-30-2010, 10:05 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
  •  
Tech Forums: Microsoft Office Forums