else without if

**kallm** · 05-12-2012, 01:00 PM

ok, i can't think anymore. could somebody please take a look at the code below and find out why im getting the else without if error
************code**********
Dim db As Database
Dim rec As DAO.Recordset
Dim x As Integer
Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT *.RETRN FROM RETRN", dbOpenDynaset)

Select Case (Me.cbOptions)

Case 1
If Not IsNull(Me.txtOutInv) Or Me.txtOutInv <> "" Then
With rec
.FindFirst "OUTINV = '" & Me.txtOutInv & "'"
If Not .NoMatch Then
If MsgBox("message" _
vbYesNo + vbQuestion) = vbYes Then
.Edit
!TTLRTAMNT = !TTLRTAMNT + Me.txtStotal
!RDATE = Me.txtDtDeal
!OUTINV = Me.txtOutInv
!ID_RT = x
.Update
Me.txtGen = x
ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT= forms.Temp.txtGen WHERE ITEMS.SEL <> 0")
Else
GoTo ext
End If
Else
.AddNew
!TTLRTAMNT = Me.txtStotal
!RDATE = Me.txtDtDeal
!OUTINV = Me.txtOutInv
!FLAG = 1
.Update
GoTo Updt
End If

Else
If MsgBox("message again" _
, vbOKCancel + vbInformation ) = vbOK Then

.AddNew
!TTLRTAMNT = Me.txtStotal
!RDATE = Me.txtDtDeal
GoTo Updt
End If

GoTo ext
End If
End With

Case 2
ExecSQL ("UPDATE qrySource SET qrySource.ID_OR =" & Me.ID_OR & " WHERE ITEMS.SEL <> 0")
GoTo ext

Case 3
ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 0, qrySource.SPRICE=0, qrySource.SOLD=0, qrySource.SDATE=Null" & _
" WHERE ITEMS.SEL <> 0")
GoTo ext
Case 4

If IsNull(Me.cbVndrs) Then
MsgBox "another message" _
, vbOKOnly + vbExclamation
End If

End Select
seePrice: If nnz(DLookup("ID_IT", "qrySource", "qrySource.SEL=-1 AND qrySource.SPRICE=0")) <> 0 Then
MsgBox "some message" _
, vbOKOnly + vbExclamation
End If

: Updt
ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT=DMAX(""ID_RT"",""RETRN"") WHERE ITEMS.SEL <> 0")

: ext

rec.Close
Set db = Nothing
End Sub

**Bob Fitz** · 05-12-2012, 01:44 PM

I would advise you to use indentation in code. It makes it much easier to follow.
I have made two changes to your code as noted in red.
I am unable to test the code, but this may work for you now

.

Code:

Dim db As Database
Dim rec As DAO.Recordset
Dim x As Integer
Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT *.RETRN FROM RETRN", dbOpenDynaset)
Select Case (Me.cbOptions)
Case 1
  With rec 'MOVED THIS OUTSIDE IF/THEN BECAUSE END WITH IS OUTSIDE
  If Not IsNull(Me.txtOutInv) Or Me.txtOutInv <> "" Then
      .FindFirst "OUTINV = '" & Me.txtOutInv & "'"
      If Not .NoMatch Then
        If MsgBox("message", vbYesNo + vbQuestion) = vbYes Then 'ADDED COMMER
          .Edit
          !TTLRTAMNT = !TTLRTAMNT + Me.txtStotal
          !RDATE = Me.txtDtDeal
          !OUTINV = Me.txtOutInv
          !ID_RT = x
          .Update
          Me.txtGen = x
          ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT= forms.Temp.txtGen WHERE ITEMS.SEL <> 0")
        Else
          GoTo ext
        End If
      Else
        .AddNew
        !TTLRTAMNT = Me.txtStotal
        !RDATE = Me.txtDtDeal
        !OUTINV = Me.txtOutInv
        !FLAG = 1
        .Update
        GoTo Updt
      End If
    
    Else
      If MsgBox("message again", vbOKCancel + vbInformation) = vbOK Then
        .AddNew
        !TTLRTAMNT = Me.txtStotal
        !RDATE = Me.txtDtDeal
        GoTo Updt
      End If
    
      GoTo ext
    End If
  End With
Case 2
  ExecSQL ("UPDATE qrySource SET qrySource.ID_OR =" & Me.ID_OR & " WHERE ITEMS.SEL <> 0")
  GoTo ext
Case 3
  ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 0, qrySource.SPRICE=0, qrySource.SOLD=0, qrySource.SDATE=Null" & _
  " WHERE ITEMS.SEL <> 0")
  GoTo ext
Case 4
  If IsNull(Me.cbVndrs) Then
    MsgBox "another message" _
    , vbOKOnly + vbExclamation
  End If
End Select
seePrice: If nnz(DLookup("ID_IT", "qrySource", "qrySource.SEL=-1 AND qrySource.SPRICE=0")) <> 0 Then
            MsgBox "some message", vbOKOnly + vbExclamation
          End If
: Updt
  ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT=DMAX(""ID_RT"",""RETRN"") WHERE ITEMS.SEL <> 0")
: ext
rec.Close
Set db = Nothing
End Sub

**ssanfu** · 05-13-2012, 01:17 AM

Code:

   Dim db As DAO.Database
   Dim rec As DAO.Recordset
   Dim x As Integer

   Set db = CurrentDb
   Set rec = db.OpenRecordset("SELECT RETRN.* FROM RETRN", dbOpenDynaset)

   Select Case (Me.cbOptions)
      Case 1
         With rec
            If Not IsNull(Me.txtOutInv) Or Me.txtOutInv <> "" Then
               .FindFirst "OUTINV = '" & Me.txtOutInv & "'"
               If Not .NoMatch Then
                  If MsgBox("message", vbYesNo + vbQuestion) = vbYes Then
                     .Edit
                     !TTLRTAMNT = !TTLRTAMNT + Me.txtStotal
                     !RDATE = Me.txtDtDeal
                     !OUTINV = Me.txtOutInv
                     !ID_RT = x
                     .Update
                     Me.txtGen = x
                     ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT= forms.Temp.txtGen WHERE ITEMS.SEL <> 0")
                  Else
                     GoTo Ext
                  End If
               Else
                  .AddNew
                  !TTLRTAMNT = Me.txtStotal
                  !RDATE = Me.txtDtDeal
                  !OUTINV = Me.txtOutInv
                  !flag = 1
                  .Update
                  GoTo Updt
               End If
            Else
               If MsgBox("message again", vbOKCancel + vbInformation) = vbOK Then
                  .AddNew
                  !TTLRTAMNT = Me.txtStotal
                  !RDATE = Me.txtDtDeal
                  .Update
                  GoTo Updt
               End If

               GoTo Ext
            End If
         End With
      Case 2
         ExecSQL ("UPDATE qrySource SET qrySource.ID_OR =" & Me.ID_OR & " WHERE ITEMS.SEL <> 0")
         GoTo Ext
      Case 3
         ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 0, qrySource.SPRICE=0, qrySource.SOLD=0, qrySource.SDATE=Null WHERE ITEMS.SEL <> 0")
         GoTo Ext
      Case 4
         If IsNull(Me.cbVndrs) Then
            MsgBox "another message", vbOKOnly + vbExclamation
         End If
   End Select

seePrice:
   If NZ(DLookup("ID_IT", "qrySource", "qrySource.SEL=-1 AND qrySource.SPRICE=0")) <> 0 Then
      MsgBox "some message", vbOKOnly + vbExclamation
   End If

Updt:  
   ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT= " & DMax("ID_RT", "RETRN") & " WHERE ITEMS.SEL <> 0")

Ext:  

   rec.Close
   set rec = Nothing
   Set db = Nothing

End Sub

---------------------------
Things I found wrong:

This

Code:

  Set rec = db.OpenRecordset("SELECT *.RETRN FROM RETRN", dbOpenDynaset)

should be

Code:

Set rec = db.OpenRecordset("SELECT RETRN.* FROM RETRN", dbOpenDynaset)

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

This line on wrong place: With rec (as Bob said)
Missing comma in Message box line (as Bob said)
Put all message box arguments on one line
---------------------------

Declared variable "x" but did not initialize it before use.
---------------------------

Don't know what "ExecSQL" is. Custom function/sub? Or were trying to use
db.Execute ("SQL statement"), dbfailonerror

Need to concantate reference to form in this line:

Code:

ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT = " & Forms!Temp.txtGen & " WHERE ITEMS.SEL <> 0")

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

Missing update command:

Code:

                  .AddNew
                  !TTLRTAMNT = Me.txtStotal
                  !RDATE = Me.txtDtDeal
                  .Update

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

Poor programming practice to use GOTOs (in BLUE)
---------------------------

Code:

nnz(DLookup("ID_IT", "qrySource", "qrySource.SEL=-1 AND qrySource.SPRICE=0"))

"nnz" should be

Code:

NZ(DLookup("ID_IT", "qrySource", "qrySource.SEL=-1 AND qrySource.SPRICE=0"))

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

: Updt should be Updt:
: ext should be Ext:
---------------------------

Need to concantate DMax value in this line:
ExecSQL ("UPDATE qrySource SET qrySource.FLAG = 1, qrySource.ID_RT= " & DMax("ID_RT", "RETRN") & " WHERE ITEMS.SEL <> 0")

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

Wasn't trying to pick the code apart, but these are the errors I saw.

Sorry...

else without if

Thread Tools

else without if

Posting Permissions