Results 1 to 4 of 4
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Need help correcting Code


    I'm trying to modify the 2nd if statement to read that if ... is <=2 And Me.SKID= Not "SKID5-F" Then...... But I keep getting a type mismatch. Am I doing this wrong?
    Code:
    Private Sub Command522_Click()    If DLookup("[Total]", "qry_BP40_Gummy_We04_Pt2") <= 30 Then
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt8"
                    DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt4"
                    DoCmd.RefreshRecord
            Else
            If DLookup("[#ofRows2]", "qry_BP40_Gummy_We04_Pt3") <= 2 Then
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt5"
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt9"
                    DoCmd.SetWarnings (warningsOn)
                    DoCmd.RefreshRecord
        Else
            Select Case Me.SKID
                Case "SKID5"
                    Call We04_Pt1
                    Call We04_Pt2
                Case "SKID5-F"
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt8"
                    DoCmd.SetWarnings (warningsOn)
                    DoCmd.RefreshRecord
                Case "SKID6"
                    Dim I As Integer
                    For I = 1 To DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
                        DoCmd.SetWarnings (WarningsOff)
                        DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt7"
                        DoCmd.SetWarnings (warningsOn)
                        DoCmd.RefreshRecord
                    Next I
                End Select
            End If
        End If
    End Sub

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Quote Originally Posted by lccrews View Post
    I'm trying to modify the 2nd if statement to read that if ... is <=2 And Me.SKID= Not "SKID5-F" Then...... But I keep getting a type mismatch. Am I doing this wrong?
    Code:
    Private Sub Command522_Click()    If DLookup("[Total]", "qry_BP40_Gummy_We04_Pt2") <= 30 Then
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt8"
                    DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt4"
                    DoCmd.RefreshRecord
            Else
            If DLookup("[#ofRows2]", "qry_BP40_Gummy_We04_Pt3") <= 2 Then
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt5"
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt9"
                    DoCmd.SetWarnings (warningsOn)
                    DoCmd.RefreshRecord
        Else
            Select Case Me.SKID
                Case "SKID5"
                    Call We04_Pt1
                    Call We04_Pt2
                Case "SKID5-F"
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt8"
                    DoCmd.SetWarnings (warningsOn)
                    DoCmd.RefreshRecord
                Case "SKID6"
                    Dim I As Integer
                    For I = 1 To DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
                        DoCmd.SetWarnings (WarningsOff)
                        DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt7"
                        DoCmd.SetWarnings (warningsOn)
                        DoCmd.RefreshRecord
                    Next I
                End Select
            End If
        End If
    End Sub
    Can you try <=2 And Me.SKID <> "SKID5-F"?

    Cheers,
    Vlad

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks Vlad! I'm still a beginner so I'm learning these things.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are other things that look wrong.....

    Should NOT use special characters in object names. It appears you have field names "[#ofRows2]" and "[#ofRows]".
    Better would be "NumOfRows" and "NumOfRows2"

    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 use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.

    ------------------

    In your code, you have
    Code:
    DoCmd.SetWarnings (WarningsOff)
    and
    DoCmd.SetWarnings (warningsOn)
    The correct syntax, per Access, is
    Code:
    DoCmd.SetWarnings False
    and
    DoCmd.SetWarnings True
    ------------------


    I never use DoCmd.SetWarnings. Instead, I use the Execute method.
    Example
    Code:
    currentdb.execute (strSQL), dbfailonerror
    I modified your posted code.
    Code:
    Private Sub Command522_Click()
        Dim d As DAO.Database
    
        Set d = CurrentDb
    
        If DLookup("[Total]", "qry_BP40_Gummy_We04_Pt2") <= 30 Then
            d.Execute ("qry_BP40_Gummy_We04_Pt8"), dbFailOnError
            d.Execute ("qry_BP40_Gummy_UPK_Add_Pt4"), dbFailOnError
            DoCmd.RefreshRecord
        Else
            If DLookup("[#ofRows2]", "qry_BP40_Gummy_We04_Pt3") <= 2 Then
                d.Execute ("qry_BP40_Gummy_We04_Pt5"), dbFailOnError
                d.Execute ("qry_BP40_Gummy_We04_Pt9"), dbFailOnError
                DoCmd.RefreshRecord
            Else
                Select Case Me.SKID
                    Case "SKID5"
                        Call We04_Pt1
                        Call We04_Pt2
                    Case "SKID5-F"
                        d.Execute ("qry_BP40_Gummy_We04_Pt8"), dbFailOnError
                        DoCmd.RefreshRecord
                    Case "SKID6"
                        Dim I As Integer
                        For I = 1 To DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
                            d.Execute ("qry_BP40_Gummy_We04_Pt7"), dbFailOnError
                            DoCmd.RefreshRecord
                        Next I
                End Select
            End If
        End If
    
        Set d = Nothing
    End Sub

    ------------------


    In code like this,
    Code:
    Case "SKID6"
        Dim I As Integer
        For I = 1 To DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
             d.Execute ("qry_BP40_Gummy_We04_Pt7"), dbFailOnError
             DoCmd.RefreshRecord
        Next I
    End Select
    you are assuming that the DLookup will ALWAYS return a number of rows greater than 0.

    Consider
    Code:
    Case "SKID6"
        Dim I As Integer
        Dim NumRows As Integer
    
        NumRows = DLookup("[#ofRows]", "qry_BP40_Gummy_We04_Pt3")
        If NumRows > 0 Then
            For I = 1 To NumRows
                d.Execute ("qry_BP40_Gummy_We04_Pt7"), dbFailOnError
                DoCmd.RefreshRecord
            Next I
        End If
    End Select

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

Similar Threads

  1. Asistance correcting an error in my Code
    By VWSpeedyB in forum Modules
    Replies: 2
    Last Post: 06-30-2015, 08:04 AM
  2. Stop VB Editor from auto-correcting
    By DubCap01 in forum Programming
    Replies: 5
    Last Post: 03-03-2015, 05:30 AM
  3. Need help correcting record count
    By Fivehole91 in forum Access
    Replies: 2
    Last Post: 05-15-2012, 05:15 PM
  4. Help for correcting Before Update
    By Pragmatic in forum Forms
    Replies: 11
    Last Post: 12-28-2011, 11:59 AM
  5. correcting typographical errors
    By aflashman in forum Access
    Replies: 1
    Last Post: 07-31-2011, 03:22 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
  •  
Other Forums: Microsoft Office Forums