Block IF End IF Confusion

[Phred]

Access 2007, Windows 7, not compiled, not split, skill level medium

I'm having trouble placing the Block if and End if statements located correctly and keep getting Block IF without End IF errors.

The user selects a date to schedule a meal and clicks a button. The code below runs. The DFirst statement checks to see if the date is already scheduled. If the date has NOT been scheduled the code should jumb to the stSql Insert statement and write the record.

If the DFirst finds that a date HAS already been scheduled the user should get a message box allowing them to make a choice to stop or proceed. If they stop they exit the code. IF they proceed they should jump to the stSql Insert statement.

(One date can have several meals scheduled for it. The user just needs a choice.)



I am having trouble getting the block if end if statements correct.

Dim Msg, Style, Title, Response, MyString

If IsNull(DFirst("DayAssigned", "tbl_createscheduledmeal1", "DayAssigned = #" & AssignedDate & "#")) = False Then
' If isnull=true then the date DOES NOT EXIST in the table and the code should jump to the SQL Update statement
' If isnull=false then the date DOES EXIST in the table the code should give the user a choice to proceed or not.
   Msg = "A meal has already been scheduled for this date." & vbCr & vbCr & "Do you want to continue?"
   Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
   Title = "Scheduled Meal Exists"
   Response = MsgBox(Msg, Style, Title)
   If Response = vbNo Then
   End If
   Exit Sub
   Else
      If Response = vbYes Then
           'if yes then Go to stSql Insert Into statement
      Else
            '_____________________________________________ This code works fine.
            stSql = "INSERT INTO Tbl_CreateScheduledMeal1" _
            & "( CategoryID, CategoryName, MealID, MealName,DayAssigned )" _
            & "VALUES (" & IntCatID & ", '" & stCatName & "', " & intMealid & ",'" & stMealName & "', #" & dtScheduledDate & "#);"
            DoCmd.SetWarnings False
            DoCmd.RunSQL stSql
            Me.Requery
            Me.Refresh
            DoCmd.SetWarnings True
            '____________________________________________ This code works fine. 
      End If
End Sub

Thanks Phred

[orange]

Instead of
If IsNull(DFirst("DayAssigned", "tbl_createscheduledmeal1", "DayAssigned = #" & AssignedDate & "#")) = False Then

I would try

If Not IsNull(DFirst("DayAssigned", "tbl_createscheduledmeal1", "DayAssigned = #" & AssignedDate & "#")) Then

Also, I'm not sure what you mean/meant by this line

Dim Msg, Style, Title, Response, MyString

All of these variable will be Variant type

You must explicitly dim variables to get specific data types

Dim Msg as String, Style as String or
Dim Title as String
Dim Response as Integer
Dim MyString as String.....

[Phred]

Thanks Orange but the "Not IsNull" statement doesn't seem to work. On testing, it gives me the same result if the Meal Name exists or not. Also I am still getting the BlockIF without EndIF error.

The "dim msg, style, title, response, my string" I got from another post and it seems to work so I used it exactly as presented.

Aside from the block if end if problem, I just don't think I have the right test going here to see if the Meal Name already exists. I seem to have problems testing its existance no matter what I do.

Is there another approach to testing this?

Thanks Fred

[orange]

Post the whole Sub so we can see the entire If End if...

Your Dim is inconsistent with what you think you are doing.
Explicitly Dim your variables --- In fact use the Option Explicit for all your modules -- a very good practice.

[ssanfu]

PMFJI,

Thanks Orange but the "Not IsNull" statement doesn't seem to work. On testing, it gives me the same result if the Meal Name exists or not. Also I am still getting the BlockIF without EndIF error.

"BlockIF without EndIF error" --- You are missing an "End If" before "End Sub"

The "dim msg, style, title, response, my string" I got from another post and it seems to work so I used it exactly as presented.

The message box example in HELP declares the variables the same way. (not the way I would do it, but it works)

Aside from the block if end if problem, I just don't think I have the right test going here to see if the Meal Name already exists. I seem to have problems testing its existance no matter what I do.

Is there another approach to testing this?

Thanks Fred

Put a breakpoint in the code and single step through it. IMHO the logic was wrong... the response = vbTrue was in the false part of the first IF() statement - response will always be false. Should have been in the True (top part) as part of "If Response = vbNo".
So I tried rewriting the code - hope you don't mind.



Here is my attempt:

'Public Sub AddMeal()
'The user selects a date to schedule a meal and clicks a button. The code below runs.
'The DFirst statement checks to see if the date is already scheduled.
'If the date has NOT been scheduled the code should jumb to the stSql Insert statement and write the record.

'If the DFirst finds that a date HAS already been scheduled the user should get a message box allowing them
' to make a choice to stop or proceed. If they stop they exit the code.
'IF they proceed they should jump to the stSql Insert statement.

   Dim MealScheduled As Boolean
   Dim Msg, Style, Title, Response, MyString

   MealScheduled = Not IsNull(DFirst("DayAssigned", "tbl_createscheduledmeal1", "DayAssigned = #" & AssignedDate & "#"))
   ' If MealScheduled = TRUE   then the date DOES EXIST in the table the code should give the user a choice to proceed or not.
   ' If MealScheduled = FALSE then the date DOES NOT EXIST in the table and the code should jump to the SQL Update statement

   If MealScheduled Then
      'MealScheduled True, then ASK
      Msg = "A meal has already been scheduled for this date." & vbNewLine & vbNewLine & "Do you want to continue?"
      Style = vbYesNo + vbCritical + vbDefaultButton2   ' Define buttons.
      Title = "Scheduled Meal Exists"
      Response = MsgBox(Msg, Style, Title)

      If Response = vbYes Then   'chose Yes
         'add a meal
         MealScheduled = False
      Else                      'chose No
         'MealScheduled = true so do nothing
      End If
   End If

   If MealScheduled Then  ' True
      'do nothing
   Else                            'False
      'Schedule a meal
      '_____________________________________________ This code works fine.
      stSql = "INSERT INTO Tbl_CreateScheduledMeal1" _
              & "( CategoryID, CategoryName, MealID, MealName, DayAssigned )" _
              & "VALUES (" & IntCatID & ", '" & stCatName & "', " & intMealid & ",'" & stMealName & "', #" & dtScheduledDate & "#);"
      CurrentDb.Execute stSql, dbFailOnError
      Me.Requery
      Me.Refresh
      '____________________________________________ This code works fine.
   End If
'End Sub

[RayMilhon]

Personally I would do the following In the code where you have the Exit Sub I would change it to Goto Exit_Handler.Then at the bottom after you've done all your processingExit_Handler: Exit SubEnd SubI always use that and put any clean up code in the Exit Handler. For example if I'm going through a recordset I will include the rs.close and set rs = nothing. Every Sub Procedure or Function I put into code has the following framework.Public sub sub1() On error goto Err_HandlerExit_Handler: Exit SubErr_Handler: Msgbox err.description, vbokonly,"Sub Procedure sub1 Error" Resume Exit_Handler:End Sub

[RayMilhon]

Anybody know how I can get these stupid messages to look the way I typed them after I've posted them?????????????????????????????????????????????

[orange]

Have you tried [ code ] and [/ code ] tags? (remove spaces)

[RayMilhon]

no let's see:

Public sub sub1() On error goto Err_HandlerExit_Handler:     Exit SubErr_Handler:     Msgbox err.description, vbokonly,"Sub Procedure sub1 Error"     Resume Exit_HandlerEnd Sub

[RayMilhon]

Ok a little better. Still a ways to go. Are those are html tags?

[orange]

no, not specifically but they are similar and used on forums.

There's also [ quote ] and [ /quote ] which doesn't preserve leading spaces. Do you have CrLf at the end of Lines?

I think this is what you were going for

Public sub sub1() 
On error goto Err_Handler

Exit_Handler:    
Exit Sub

Err_Handler:     
Msgbox err.description, vbokonly,"Sub Procedure sub1 Error"     
Resume Exit_Handler
End Sub

On this forum the code tags are done by means of the #. JUst highlight the code and hit that # in the formatting area.

Are you aware of MZTools? It is a free utility at http://www.MZTools.com
It does many things, one of which is to add an Error handler... but there are many other features.

[Phred]

I need to study your code. I don't fully understand it but I do understand that it works. Thanks I was sweating bullets.

I have marked this thread as solved.

Thanks

Phred