IIf Statements with DoCmd (1 Viewer)

jtundra05

New member
Local time
Today, 17:04
Joined
Jun 19, 2008
Messages
9
I have a MS sample database with a form named Task List that tracks daily task summary in table view. The first field reads either open if there is data for that record or new if data is null. When u click open, it opens the Task Detail form. Ive added a form to track Projects to this database and modified the Task List form to have a field "Task Type" to distinguish between type of work. I want modify the click open command to use an iif statement that opens either Project Details or Task details (Depending on the input) but it uses macros that i don't understand. I keep trying to code it in vba but i cant get it to work right. It seems simple but i cant get it.
I want the click on open to work like:
iif([task type]= "Project", DoCmd.openform ("Project Details"), DoCmd.openform ("Task Details")).
This generates a Complie Error: Expected: =
Can anybody help? Much Appreciated.
 

marianne

Registered User.
Local time
Yesterday, 23:04
Joined
Mar 26, 2009
Messages
327
where did you made that code? inside microsoft visual basic ide or inside a textbox?
 

jtundra05

New member
Local time
Today, 17:04
Joined
Jun 19, 2008
Messages
9
Sorry I fixed it. Code was off. I did type it in vb just had to change the syntax a bit. I appreciate your help. Just for viewing here is the code that does work:

If Forms![Task List]![Task Type] = "Project" Then
DoCmd.OpenForm ("Project List")
End If

I thought it should be that easy.
 

jtundra05

New member
Local time
Today, 17:04
Joined
Jun 19, 2008
Messages
9
Marianne, thank you for your help. I figured out how to code the click open command for 1 work type option by the code:

If Forms![Task List]![Task Type] = "Project" Then
DoCmd.OpenForm ("Project List")
End If.

Works like a charm. I have two more different work type, "Trouble Response" and "Task", that I would like to have added to this code to open their work details form accordingly. All I did was edit the previous code to reflect the new options and the forms they should open as so:

If Forms![Task List]![Task Type] = "Project" Then
DoCmd.OpenForm ("Project List")
Else
If Forms![Task List]![Task Type] = "Trouble Response" Then
DoCmd.OpenForm ("Response List")
Else
If Forms![Task List]![Task Type] = "Task" Then
DoCmd.OpenForm ("Task List")
End If

The same simple evalutation, just 2 more added. I keep getting: Complie Error: Block If without End If. I tried messin with it but all with the same compile error result. Any suggestions?

Josh
 

jtundra05

New member
Local time
Today, 17:04
Joined
Jun 19, 2008
Messages
9
Marianne, thank you for your help. I figured out how to code the click open command for 1 work type option by the code:

If Forms![Task List]![Task Type] = "Project" Then
DoCmd.OpenForm ("Project List")
End If.

Works like a charm. I have two more different work type, "Trouble Response" and "Task", that I would like to have added to this code to open their work details form accordingly. All I did was edit the previous code to reflect the new options and the forms they should open as so:

If Forms![Task List]![Task Type] = "Project" Then
DoCmd.OpenForm ("Project List")
Else
If Forms![Task List]![Task Type] = "Trouble Response" Then
DoCmd.OpenForm ("Response List")
Else
If Forms![Task List]![Task Type] = "Task" Then
DoCmd.OpenForm ("Task List")
End If

The same simple evalutation, just 2 more added. I keep getting: Complie Error: Block If without End If. I tried messin with it but all with the same compile error result. Any suggestions?
 

SOS

Registered Lunatic
Local time
Yesterday, 23:04
Joined
Aug 27, 2008
Messages
3,517
Change the red part:

Code:
If Forms![Task List]![Task Type] = "Project" Then
DoCmd.OpenForm "Project List"
[COLOR=red][B]Else[/B][/COLOR]
If Forms![Task List]![Task Type] = "Trouble Response" Then
DoCmd.OpenForm "Response List"
[COLOR=red][B]Else[/B][/COLOR]
If Forms![Task List]![Task Type] = "Task" Then
DoCmd.OpenForm "Task List"
End If

To this

Code:
If Forms![Task List]![Task Type] = "Project" Then
   DoCmd.OpenForm "Project List"
[COLOR=red][B]ElseIf[/B][/COLOR] Forms![Task List]![Task Type] = "Trouble Response" Then
   DoCmd.OpenForm "Response List"
[B][COLOR=red]Else[/COLOR][COLOR=red]If[/COLOR][/B] Forms![Task List]![Task Type] = "Task" Then
   DoCmd.OpenForm "Task List"
End If
 

SOS

Registered Lunatic
Local time
Yesterday, 23:04
Joined
Aug 27, 2008
Messages
3,517
But I would really go for using a Select Case Statement:
Code:
Select Case Forms![Task List]![Task Type] 
    Case "Project" 
       DoCmd.OpenForm "Project List"
    Case "Trouble Response"
       DoCmd.OpenForm "Response List"
    Case "Task"
       DoCmd.OpenForm "Task List"
End Select

Much cleaner and clearer this way.
 

Users who are viewing this thread

Top Bottom