Opening Form with ElseIf Statement

[PBCN]

So what I want to do is have multiple forms sourcing the same table and the opening of which form is depending on which type of work is being carried out.



What I have done so far is create a column that has a small statement referring to what what type of work is being done for each record, then under a click event (the Job No) a set of else if statements.

 
Private Sub JobNo_Click()
Dim Strfun As String

If Me.TypeOfApproval = "CDC Only" Then
    Strfun = "JobsFCDCOnly"
ElseIf Me.TypeOfApproval = "Stage Inspections" Then
    Strfun = "JobsF"
ElseIf Me.TypeOfApproval = "CDC, PCA and OC" Then
    Strfun = "JobsFCDCOC"
ElseIf Me.TypeOfApproval = "CC, PCA and OC" Then
    Strfun = "JobsFCCPCAOC"
ElseIf Me.TypeOfApproval = "CC Only" Then
    Strfun = "JobsFCCOnly"
ElseIf Me.TypeOfApproval = "PC and OC Only" Then
    Strfun = "JobsFPCOC"
ElseIf Me.TypeOfApproval = "BCA Report" Then
    Strfun = "JobsFReport"
End If

DoCmd.OpenForm Strfun, acNormal, , "ID=" & Me.ID

Ive done some google research and what I have above should work but I am getting a run-time error "The action or method requires a Form Name argument"

Anyone able to shed some light on where I've gone wrong?

[sgthuth]

I did not encounter any issues with the code. I removed the ID portion of the open form command as I do not have the ID set with in my form. I just did this as a test and it worked fine. I added the else statement with the msgbox because if the information is left blank within your choices, it will cause an error.

Option Compare Database
Option Explicit


Private Sub JobNo_Click()
Dim Strfun As String


If Me.TypeOfApproval = "CDC Only" Then
Strfun = "JobsFCDCOnly"
ElseIf Me.TypeOfApproval = "Stage Inspections" Then
Strfun = "JobsF"
ElseIf Me.TypeOfApproval = "CDC, PCA and OC" Then
Strfun = "JobsFCDCOC"
ElseIf Me.TypeOfApproval = "CC, PCA and OC" Then
Strfun = "JobsFCCPCAOC"
ElseIf Me.TypeOfApproval = "CC Only" Then
Strfun = "JobsFCCOnly"
ElseIf Me.TypeOfApproval = "PC and OC Only" Then
Strfun = "JobsFPCOC"
ElseIf Me.TypeOfApproval = "BCA Report" Then
Strfun = "JobsFReport"
Else
MsgBox "Please make a selection for the task at hand."
End If


DoCmd.OpenForm Strfun, acNormal
End Sub

[ranman256]

you should use a table,
or a select statement:

Select Case Me.TypeOfApproval
    Case "CDC Only"
        Strfun = "JobsFCDCOnly"
    Case "Stage Inspections"
        Strfun = "JobsF"
    Case "CDC, PCA and OC"
        Strfun = "JobsFCDCOC"
    Case "CC, PCA and OC"
        Strfun = "JobsFCCPCAOC"
    Case "CC Only"
        Strfun = "JobsFCCOnly"
    Case "PC and OC Only"
        Strfun = "JobsFPCOC"
    Case "BCA Report"
        Strfun = "JobsFReport"
End Select

is : "CDC, PCA and OC"
one word or 3 VALUES:
"CDC", "PCA", "OC"

[PBCN]

I did not encounter any issues with the code. I removed the ID portion of the open form command as I do not have the ID set with in my form. I just did this as a test and it worked fine. I added the else statement with the msgbox because if the information is left blank within your choices, it will cause an error.

Option Compare Database
Option Explicit


Private Sub JobNo_Click()
Dim Strfun As String


If Me.TypeOfApproval = "CDC Only" Then
Strfun = "JobsFCDCOnly"
ElseIf Me.TypeOfApproval = "Stage Inspections" Then
Strfun = "JobsF"
ElseIf Me.TypeOfApproval = "CDC, PCA and OC" Then
Strfun = "JobsFCDCOC"
ElseIf Me.TypeOfApproval = "CC, PCA and OC" Then
Strfun = "JobsFCCPCAOC"
ElseIf Me.TypeOfApproval = "CC Only" Then
Strfun = "JobsFCCOnly"
ElseIf Me.TypeOfApproval = "PC and OC Only" Then
Strfun = "JobsFPCOC"
ElseIf Me.TypeOfApproval = "BCA Report" Then
Strfun = "JobsFReport"
Else
MsgBox "Please make a selection for the task at hand."
End If


DoCmd.OpenForm Strfun, acNormal
End Sub

Actually I just did a new test DB and it worked for me too, maybe my VB code is too damaged, I have been decompiling but Im still getting some actions that dont make sense.

[PBCN]

CDC, PCA and OC is one word but I dont think its the wording, it seems the Docmd line is not accepting the variable Strfun in the string.

[sgthuth]

PBCN,
What are the errors? Before was the run-time error and I believe you were just passing a "" value to the string. I learned a long time ago that if I'm having issues troubleshooting to take things step by step. Have you tried stepping through your vb?

[sgthuth]

You can try moving the code to a module and making it a public function and just call it with in the form code. Then you can test the code with the form open without having to switch back and forth. And the choices for the Else-If Statement, are they in a dropdown? For the test I did earlier, that's what I used to limit the information passed. Also, I added a simple error check.

Option Compare Database
Option Explicit


Private Sub JobNo_Click()
Dim Strfun As String
Dim OpenYN As Integer


OpenYN = 1


If Me.TypeOfApproval = "CDC Only" Then
Strfun = "JobsFCDCOnly"
ElseIf Me.TypeOfApproval = "Stage Inspections" Then
Strfun = "JobsF"
ElseIf Me.TypeOfApproval = "CDC, PCA and OC" Then
Strfun = "JobsFCDCOC"
ElseIf Me.TypeOfApproval = "CC, PCA and OC" Then
Strfun = "JobsFCCPCAOC"
ElseIf Me.TypeOfApproval = "CC Only" Then
Strfun = "JobsFCCOnly"
ElseIf Me.TypeOfApproval = "PC and OC Only" Then
Strfun = "JobsFPCOC"
ElseIf Me.TypeOfApproval = "BCA Report" Then
Strfun = "JobsFReport"
Else
MsgBox "Please make a selection for the task at hand."
OpenYN = 0
End If


If OpenYN = 1 Then
DoCmd.OpenForm Strfun, acNormal
End If


End Sub

[davegri]

Problem is that if no selection matches, you try to open the form anyway, and Strfun is blank. Add the red line to get out after displaying the messagebox.

Else
MsgBox "Please make a selection for the task at hand."
Exit Sub
End If


DoCmd.OpenForm Strfun, acNormal
End Sub

[PBCN]

So I found out what was wrong, I had all statements slightly misspelt.

I guess I learnt a lesson about writing code at 1:30am