Form to open based on selection of another form

[Jean-Francois Gauthier]

Hello,

I would like some help on how to do the following:

I have a form called "frmpreselect". In addition to this I have three other
forms called "FINENTRY", "TBSENTRY", "PSHRMACENTRY".

On form "frmpreselect" I have two fields, one being "DeptID" and "Document
Type". These are both combo boxes. The form also has an open form command
button.

What I would like to have the form and button to work as is the following:

If in field "DeptID", the user selects "FIN", then when he presses the open
form button on "frmpreselect", that the form "FINENTRY" is opened. If the
user chooses "TBS", then the form "TBSENTRY" opens. If the user chooses
"PSHRMAC", then the form "PSHRMACENTRY" opens.

I have tried to play with the code for the command button to add ifs
statements, however this has not worked.

Here is what I had as code.


Private Sub begin_Click()
On Error GoTo Err_begin_Click

Dim stDocName1 As String
Dim stLinkCriteria As String
Dim stfield As String
Dim DeptID As Field
Dim stDocName2 As String
Dim stDocName3 As String

DeptID = "FIN" Or "TBS" Or "PSHRMAC"
stDocName1 = "FINENTRY"
stDocName2 = "TBSENTRY"
stDocName3 = "PSHRMACENTRY"

If DeptID = "FIN" Then stDocName1
DoCmd.OpenForm stDocName1, , , stLinkCriteria
Else
If DeptID = "TBS" Then stDocName2
DoCmd.OpenForm stDocName2, , , stLinkCriteria
Else
If DeptID = "PSHRMAC" Then stDocName3
DoCmd.OpenForm stDocName3, , , stLinkCriteria
End If

Exit_begin_Click:
Exit Function

Err_begin_Click:
MsgBox Err.Description
Resume Exit_begin_Click

End Function
End Sub


If you could tell me what I have wrong with my code or how I should code
this button to work the way I described above, it would be greatly
appreciated.

Sincerely,

 

[Damian S]

Hi again Jean-Francois,

Your code is close, but not quite. Try this:

Private Sub begin_Click()
On Error GoTo Err_begin_Click

Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String

stDocName1 = "FINENTRY"
stDocName2 = "TBSENTRY"
stDocName3 = "PSHRMACENTRY"

If me.DeptID = "FIN" Then
DoCmd.OpenForm stDocName1
ElseIf me.DeptID = "TBS" Then
DoCmd.OpenForm stDocName2
ElseIf me.DeptID = "PSHRMAC" Then
DoCmd.OpenForm stDocName3
End If

Exit_begin_Click:
Exit Function

Err_begin_Click:
MsgBox Err.Description
Resume Exit_begin_Click

end sub

Hope that helps.

Damian.

 

[Jean-Francois Gauthier]

Hi Damian,

Thanks for your help.

The only issue with your code I see is that I once I hit the open button on
the form, which should take me to the next form, I get the error message

"Exit function not aloud in Sub or Property".....then when I click ok it
takes me to the line "Exit Function" under Exit_Begin_click

Any idea as to why?

Thanks,

 

[Damian S]

Sorry, that's a copy/paste error from your code earlier. It should say Exit
Sub.

Damian.

 

[Jean-Francois Gauthier]

Works like a charm, thank you very much for your help!!!

Jean-Francois