Results 1 to 8 of 8
  1. #1
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24

    Question Error 13: Type Mismatch. Open record in form if exists, if not create new

    Hi there, I am currently trying to code a button to read entries (IndexFacility, ReportingYear, SulphurLimitOption) in Form1 (frm_Sch1). The idea is to send the user to the appropriate form depending on the SulphurLimitOption and to open the record for editing if it exists or creating a new one if it doesn't.

    I'm receiving a type mismatch error and am struggling with debugging it, the highlighted lines are shown below in red.

    I also have a question on how to reference textboxes. For the field contained within square brackets do I put in the name of the textbox or the control source name.

    Here is the code below...

    Option Compare Database
    Option Explicit

    Private Sub btnSch1Next_Click()

    Dim IndFac As String
    Dim RepYr As Integer
    Dim SLO As String

    If IsNull(IndFac = Forms![frm_Sch1]!IndexFacility) Then
    MsgBox "Index and Facility not selected.", vbOKOnly, "Warning"
    End If

    If IsNull(RepYr = Forms![frm_Sch1]!ReportingYear) Then
    MsgBox "Reporting Year not entered.", vbOKOnly, "Warning"
    End If

    If Not IsNull(SLO = Forms![frm_Sch1]!SulphurLimitOption) Then


    If SLO = "Pool Average" Then
    If "[IndexFacilityAdj] = '" & IndFac & "' And [ReportingYearAdj] = " & RepYr Then
    DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormAdd, acWindowNormal
    End If
    Else
    If "[ReportingFacility] = '" & IndFac & "' And [ReportingYearAnx] = " & RepYr Then
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormAdd, acWindowNormal
    End If
    End If
    Else
    MsgBox "Sulphur Limit Option not selected.", vbOKOnly, "Warning"
    End If

    End Sub

    If anything is unclear, please let me know and I will do my best to clarify. I'm new to VBA and am working on an existing db, sorry if things are labelled weird. Thank you!

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,239
    Is this code in frm_Sch1 module?

  3. #3
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Yes it is in that module

  4. #4
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    My bad it's under Class Objects

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,239
    My bad it's under Class Objects
    No problem. A form is a Class Object.

    Try this. It's unclear where the IndexFacilityAdj and ReportingYearAdj are located. It they are textboxes on the form, code is OK.
    The Me. prefix refers to controls on the forms, actually the CONTENTS or VALUES of those controls.
    Brackets must be used to refer to the values of fields in the form's recordsource that are not on the form.
    Brackets are also REQUIRED for any object on the form that contains spaces in its name.

    An image of the form in design mode posted here would be helpful to clarify.

    Code:
    Option Compare Database
    Option Explicit
    Private Sub btnSch1Next_Click()
    Dim IndFac As String
    Dim RepYr As Integer
    Dim SLO As String
    If IsNull(Me.IndexFacility) Then
        MsgBox "Index and Facility not selected.", vbOKOnly, "Warning"
        Exit Sub
    End If
    If IsNull(Me.ReportingYear) Then
        MsgBox "Reporting Year not entered.", vbOKOnly, "Warning"
        Exit Sub
    End If
    
    'above entries OK, so continue
    
    If Not IsNull(Me.SulphurLimitOption) Then
        If Me.SulphurLimitOption = "Pool Average" Then
            If Me.IndexFacilityAdj = Me.IndexFacility And Me.ReportingYearAdj = Me.ReportingYear Then
              DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormEdit, acWindowNormal
           Else
              DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormAdd, acWindowNormal
          End If
     Else
         If Me.ReportingFacility = Me.IndexFacility And Me.ReportingYearAnx = Me.ReportingYear Then
            DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormEdit, acWindowNormal
         Else
           DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormAdd, acWindowNormal
        End If
    End If
    Else
        MsgBox "Sulphur Limit Option not selected.", vbOKOnly, "Warning"
    End If
    End Sub
    Note: added Red code so that execution does not fall through when data is missing.
    Last edited by davegri; 11-05-2019 at 10:44 AM. Reason: clarif

  6. #6
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Thanks for your response. The red objects are names of textboxes in the frm_Sch1 Adjust and frm_Sch1 Annex forms, respectively. Access is giving me an error that says "Method of data member not found" probably due to this. How do I reference those particular textboxes.

    The idea is to open the specific Adjust from for a matching Index&Facility and Reporting Year Record and if not to open a new form (same with the Annex form)

    Option Compare Database
    Option Explicit

    Private Sub btnSch1Next_Click()
    Dim IndFac As String
    Dim RepYr As Integer
    Dim SLO As String

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

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


    'above entries OK, so continue

    If Not IsNull(Me.SulphurLimitOption) Then
    If Me.SulphurLimitOption = "Pool Average" Then
    If Me.IndexFacilityAdj = Me.IndexFacility And Me.ReportingYearAdj = Me.ReportingYear Then
    DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormAdd, acWindowNormal
    End If
    Else
    If Me.ReportingFacility = Me.IndexFacility And Me.ReportingYearAnx = Me.ReportingYear Then
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormAdd, acWindowNormal
    End If
    End If
    Else
    MsgBox "Sulphur Limit Option not selected.", vbOKOnly, "Warning"
    End If
    End Sub


    NEW CODE. I tried this and got the same error as last time. I'm very new to this and programming in general, any suggestions are appreciated!

    Option Compare Database
    Option Explicit

    Private Sub btnSch1Next_Click()

    Dim IndFac As String
    Dim RepYr As Integer
    Dim SLO As String

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

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

    If Not IsNull(Me.SulphurLimitOption) Then
    If Me.SulphurLimitOption = "Pool Average" Then
    If "[IndexFacilityAdj] = '" & Me.IndexFacility & "' And [ReportingYearAdj] = " & Me.ReportingYear Then
    DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1 Adjust", acNormal, , , acFormAdd, acWindowNormal
    End If

    Else
    If "[ReportingFacility] = '" & Me.IndexFacility & "' And [ReportingYearAnx] = " & Me.ReportingYear Then
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormEdit, acWindowNormal
    Else
    DoCmd.OpenForm "frm_Sch1 Annex", acNormal, , , acFormAdd, acWindowNormal
    End If
    End If
    Else
    MsgBox "Sulphur Limit Option not selected.", vbOKOnly, "Warning"
    End If

    End Sub




  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,239
    Problem is, you cannot refer to a control on a form if that form is not open.
    You need to rethink how the user interface is going to work.

  8. #8
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    That makes sense, thank you!



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

Similar Threads

  1. Replies: 3
    Last Post: 10-30-2019, 11:32 AM
  2. Type mismatch error in a form
    By B66 in forum Forms
    Replies: 29
    Last Post: 08-29-2016, 02:41 PM
  3. Type 13 (Type Mismatch) error
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 01-22-2016, 10:01 AM
  4. Replies: 2
    Last Post: 08-06-2012, 08:27 AM
  5. Replies: 1
    Last Post: 05-11-2012, 10:59 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