If,Then, Else

[Alansa59]

Could someone please help me with the correct syntax for the "IF, Then: Else:
" command

 

[Stuart McCall]

Could someone please help me with the correct syntax for the "IF, Then:
Else:
" command

Sure. Let's say you want to do something if a file doesn't exist. It could
be coded like this

If Dir(MyFileName) = "" Then
'This part executes when the above condition is True
MsgBox "Doesn't exist"
Else
'This part executes when the above condition is False
MsgBox "Exists"
End If

This could also be coded on one line:

If Dir(MyFileName) = "" Then MsgBox "Doesn't exist" Else MsgBox "Exists"

Note that when you use the one-line syntax you don't provide an End If.

 

[Douglas J. Steele]

While it refers to Visual Studio,
http://msdn.microsoft.com/en-us/library/752y8abs(VS.80).aspx applies to VBA
as well

 

[Rose B]

Can you be a bit more specific? In general you could either have....

If <Condition> then <Action>

or

If <Condition> then
<Action>
else
<Action>
end if

You can nest your IF statements as well.

Try using the Access Help function - this describes it very well.

 

[Alansa59]

I have a form that all the text boxes need to be filled in before it is saved
using a button "SUBMIT/CLOSE".
Below is the code I attempted to use and it comes up with "Error, Else
without If

If IsNull(Opened_Date) And IsNull(Start_Date) Then cmdSUBMIT_CLOSE.Enabled =
True

Else: cmdSUBMIT_CLOSE = False
End If

 

[Alansa59]

I have a form that all the text boxes need to be filled in before it is saved
using a button "SUBMIT/CLOSE".
Below is the code I attempted to use and it comes up with "Error, Else
without If

If IsNull(Opened_Date) And IsNull(Start_Date) Then cmdSUBMIT_CLOSE.Enabled =
True

Else: cmdSUBMIT_CLOSE = False
End If

 

[Stuart McCall]

I have a form that all the text boxes need to be filled in before it is
saved
using a button "SUBMIT/CLOSE".
Below is the code I attempted to use and it comes up with "Error, Else
without If

If IsNull(Opened_Date) And IsNull(Start_Date) Then cmdSUBMIT_CLOSE.Enabled
=
True

Else: cmdSUBMIT_CLOSE = False
End If

Ok, that should read:

If IsNull(Opened_Date) And IsNull(Start_Date) Then
cmdSUBMIT_CLOSE.Enabled = True
Else
cmdSUBMIT_CLOSE.Enabled = False
End If

The reason you got an Else without If error is because your first code line
is the one-line syntax I mentioned previously. So the command ended, then
the next thing to come along is an Else statement, but it has no
corresponding If because the one-liner ended.

Incidentally, the whole thing could be reduced to:

cmdSUBMIT_CLOSE.Enabled = (IsNull(Opened_Date) And IsNull(Start_Date))

because the expression (the part on the right of the equals sign) will
return True or False, which is exactly what you require to set the enabled
property.

 

[John W. Vinson]

I have a form that all the text boxes need to be filled in before it is saved
using a button "SUBMIT/CLOSE".
Below is the code I attempted to use and it comes up with "Error, Else
without If

If IsNull(Opened_Date) And IsNull(Start_Date) Then cmdSUBMIT_CLOSE.Enabled =
True

Else: cmdSUBMIT_CLOSE = False
End If

Reread the Help message for If in the VBA online help. It's pretty clear and
it doesn't resemble what you've done much at all.

The correct syntax is

IF <true or false expression> Then
<statements to be executed if it is true>
Else
<statements to be executed if it is false>
End If

In your case:

If IsNull(Opened_Date) And IsNull(Start_Date) Then
cmdSUBMIT_CLOSE.Enabled = True
Else
cmdSUBMIT_CLOSE.Enabled = False
End If

I'm guessing that you want to set the Enabled property in the false branch,
rather than the value of the control.

In this case, it's actually quite possible to do without the IF entirely: you
just want to set the Enabled property to TRUE or FALSE, and you have an
expression which is TRUE or FALSE:

Me!cmdSUBMIT_CLOSE.Enabled = (IsNull(Opened_Date) AND IsNull(START_DATE))

This will enable the control cmdSUBMIT_CLOSE if both OPENED_DATE and
START_DATE are NULL, and disable cmdSUBMIT_CLOSE if either or both controls
contain data. (I suspect that isn't actually what you want but my crystal ball
is cloudy tonight...)

 

[Alansa59]

Stuart thanks a ton . I have another problem. I would like to use that same
control to generate an Outlook email and attach the form that the control is
part of, to the email. Any ideas?

 

[Stuart McCall]

Stuart thanks a ton . I have another problem. I would like to use that
same
control to generate an Outlook email and attach the form that the control
is
part of, to the email. Any ideas?

Unfortunately I've not worked much with outlook. However there are others on
here that will no doubt help. If you get no reply to this, try re-posting as
a new thread.

 

[trevorC via AccessMonster.com]

Hi,
Hope this helps you...

What i can't do is set the print option for a text box created on an excel
sheet via vba code.

This makes an excel file from a query, does stuff to it (create list, format
page) then sends it to the selected recipients without stoping. (selectable -
comment out .display)

(personal information removed)
Dim strPath As String
Dim rst As DAO.Recordset
Dim AppOutLook
Dim MailOutLook
Dim olmailItem
Set AppOutLook = CreateObject("Outlook.Application")
Set MailOutLook = AppOutLook.CreateItem(olmailItem)
Dim EContent As String
Dim stDocName As String
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Data_Range
Dim Worksheet_Name
Dim db As Database
Dim rs As Recordset

RC = Me.MyCount
If IsEmpty(RC) Then
Exit Sub
End If
Set db = CurrentDb
Set rs = db.OpenRecordset("E-Mail Recipients")
rs.MoveFirst
Do While Not rs.EOF
If rs![Active] = True Then
ttt = rs![E-Mail]
Mail_to_list = Mail_to_list + ttt & ";"
End If
rs.MoveNext
Loop
rs.Close
dt = Format(Forms![main menu]![repairs in report date], " dd-mm-yy")
dd = Format(Now, " hh-mm")
gg = "C:\Dispatch Details\Dispatch Details for - " & ds & dt & dd & ".
xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "daily
repairs in report", gg, True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Workbook.Worksheets(1).Name = "Transfer Details"
Set Current_Worksheet = Excel_Workbook.Worksheets("Transfer Details")
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True

Current_Worksheet.Columns("L:L").Delete Shift:=xlToLeft
Current_Worksheet.Range("L1").FormulaR1C1 = "Date Received"
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Rows("1:1").Insert Shift:=xlDown
Current_Worksheet.Range("A1").FormulaR1C1 = "Reciept Details for " & dt
Current_Worksheet.Rows("1:1").Font.Bold = True
Current_Worksheet.Rows("1:1").Font.Size = 18
Current_Worksheet.Rows("1:1").Font.Name = "Times New Roman"

With Excel_Application.ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3"
.Orientation = xlLandscape
.PaperSize = xlPaperA4
End With

rng22 = "B" & 4 & "" & gb1 + 4 ' "$A$5:$D" & Mid
(Current_Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address, 4, 3) - 1
Current_Worksheet.Range(rng22).Select
With selection
Current_Worksheet.ListObjects.Add(xlSrcRange, , xlYes, xlYes).Name =
"List2"
End With

Excel_Workbook.Save
Excel_Application.Quit
T = MsgBox("Select Yes to send the E-Mail now or No to exit without
sending the E-Mail.", vbYesNo, "Send E-Mail Confirmation")
If T = 6 Then
Else
Exit Sub
End If
With MailOutLook
.To = Mail_to_list
.Subject = "Receipt of Units for Repair"
.Attachments.Add gg
.Body = "This is an automatically generated E-Mail " & vbCrLf &
vbCrLf & _
"Attention To - ABC Asset Tracking Department" & vbCr & _
"Attention To - Logistics Department" & vbCr & vbCr & _
"Please find attached Details for the Units returned for
Repair" & vbCrLf & vbCrLf & _
"Comments: -" & Me.Comments & vbCrLf & vbCrLf & vbCrLf & _
"Regards," & vbCrLf & mail_from & _
"Disclaimer" & vbCrLf & _
"This email may contain confidential information."
.Display
SendKeys "%{s}", True '''' only to send automaticaly
End With
End Sub