Want to open a specific form based on a field value in an open form 2

[yogi71]

I have a form called frmProjects. This form has a field on it called Department, which returns a 3 letter abbreviation for the department in field DeptID.
I want a button on this form, that when pushed will open the appropriate form based on the DeptID.

If DeptID = PNC, open frmPNC
If DeptID = STA, open frmSTA
If DeptID = TEL, open frmTEL
And there are 4 others.
It should leave frmProjects open, so all it needs to do is open the additional form.

My VB skills are very limited, but this sounds like it should be doable, I just have no idea how.
Any assistance would be greatly appreciated. My DB was ready for use until user-feedback dictated this button be created.
Please help.

 

[PHV]

A starting point:
DoCmd.OpenForm "frm" & Me!DeptID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[yogi71]

PHV, at first, I was mad that you only gave me a vague starting point, especially when I said my VB skills are very limited. I do understand your reasoning, as I believe we've crossed paths before. I would have preferred a little more help getting started, but your did give me enough to figure this one out (with a little help from google), so thank you. Just wish I could have completed this 3 hours ago.


Here is what I ended up doing.

Private Sub estimate_Click()
Dim DeptID As String
Dim sWHERE As String

sWHERE = "[uniqueID] = '" & Me.[ProjectID] & "'"


If Me!DeptID = "PNC" Then
DoCmd.OpenForm "frmPNC", , , sWHERE
ElseIf Me!DeptID = "PLA" Then
DoCmd.OpenForm "frmPLA", , , sWHERE
ElseIf Me!DeptID = "PRJ" Then
DoCmd.OpenForm "frmPRJ", , , sWHERE
ElseIf Me!DeptID = "STA" Then
DoCmd.OpenForm "frmSTA", , , sWHERE
ElseIf Me!DeptID = "TEL" Then
DoCmd.OpenForm "frmTEL", , , sWHERE
ElseIf Me!DeptID = "TST" Then
DoCmd.OpenForm "frmTST", , , sWHERE

End If

End Sub

 

[Andrzejek]

What you could get out of PHV's post was:

Private Sub estimate_Click()
DoCmd.OpenForm "frm" & Me!DeptID, , , "[uniqueID] = '" & Me.[ProjectID] & "'"
End Sub

BTW, is your uniqueID field a text?
If not, and it is a number, try:

Private Sub estimate_Click()
DoCmd.OpenForm "frm" & Me!DeptID, , , "[uniqueID] = " & Me.[ProjectID] 
End Sub

Have fun.

---- Andy

 

[PHV]

What about this ?

Private Sub estimate_Click()
  DoCmd.OpenForm "frm" & Me!DeptID , , , "uniqueID='" & Me!ProjectID & "'"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[yogi71]

I originally had the string assigned to sWHERE in the DoCmd.OpenForm, but decided for consistency and reusability, that a variable seemed the right way to go, that way if I ever change it, I only have one place to change it.

I also hardcoded the form names in case of inconsistency in the naming of the forms (I deleted the 7th one because it is different).

I have a tendency to use [] around field names because the first DB I ever worked on used multiple words to name fields all the time, and I quickly learned that you had to enclose them or they didn't work.

Lastly, uniqueID is a string as it contains letters and numbers, so the first example works better.

Thanks guys.

 

[Andrzejek]

All very good points.

May I suggest this instead of: If Me!DeptID = "PNC" Then ...

Select Case Me!DeptID 
  Case"PNC" 
    DoCmd.OpenForm "frmPNC", , , sWHERE
  Case "PLA" 
    DoCmd.OpenForm "frmPLA", , , sWHERE
  Case "PRJ"
    DoCmd.OpenForm "frmPRJ", , , sWHERE
  Case "STA"
    DoCmd.OpenForm "frmSTA", , , sWHERE
  Case "TEL"
    DoCmd.OpenForm "frmTEL", , , sWHERE
  Case "TST"
    DoCmd.OpenForm "frmTST", , , sWHERE
End  Select

Same logic, it just reads better, IMO

Have fun.

---- Andy