Open forms based on criteria

[Bazmac]

Hi

I have 3 forms "Work_in_progress", "Installations" and Warranty".

Form "Work_in_progress" is a continuous form that displays uncompleted
work, each line has a control "Update" when pressed it opens form
"Installations" which until recently was fine. We have now secured a contract
to carryout warranty repairs, which requires a different form to be completed
("Warranty").

The 3 forms has "Tbl_Customers" as their control source which has a field
"Job_Type" this would contain the following data "Installations or Warranty".

What I would like to achieve is that when the control "Update" is pressed it
will open either of the forms "Installations" or "Warranty" based on the data
in "Tbl_Customers" field "Job_Type".

Example -- When field "Job_Type" = "Warranty" form "Warranty" is opened

I have searched through the posts but cannot find a solution.

Can someone provide an answer.

Thanks
Bazmac

 

[Arvin Meyer [MVP]]

You need to change the recordsource of the form to a query in order to add
the Job_Type to the form. Then you need to branch the code with an If ...
Then ... Else statement or a Select Case statement. Here's an aircode
snippet:

If Me.txtJob_Type = "Installantion" Then ' or the Job_TypeID
DoCmd.OpenForm "Form1",,, "JobID = " & Me.txtJobID
Else
DoCmd.OpenForm "Form2",,, "JobID = " & Me.txtJobID
End If

 

[Bazmac]

Arvin,

Thankyou for your quick reply I have tried your suggestion the outcome is
that "Warranty" is the only form to display regardless of job type chosen,
please find below the original on click procedure follwed by your suggestion.

Could you advise were the problem is in the code

Thanking You
Bazmac

Private Sub Update_Click()
On Error GoTo Err_Update_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Installation"

stLinkCriteria = "[JobNo]=" & "'" & Me![JobNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Update_Click:
Exit Sub

Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click


End Sub

Private Sub Update_Click()
On Error GoTo Err_Update_Click

Dim stDocName As String
Dim stLinkCriteria As String

'stDocName = "Installation"

'stLinkCriteria = "[JobNo]=" & "'" & Me![JobNo] & "'"
'DoCmd.OpenForm stDocName, , , stLinkCriteria

If Me.Job_Type = "Installantion" Then ' or the Job_TypeID
DoCmd.OpenForm "Installation", , , "[JobNo]=" & "'" & Me![JobNo] & "'"
Else
DoCmd.OpenForm "Warranty", , , "[JobNo]=" & "'" & Me![JobNo] & "'"
End If


Exit_Update_Click:
Exit Sub

Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click


End Sub

 

[Arvin Meyer [MVP]]

Could it be the typo?

If Me.Job_Type = "Installantion" Then

Installantion <> Installation

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

Arvin,

Thankyou for your quick reply I have tried your suggestion the outcome is
that "Warranty" is the only form to display regardless of job type chosen,
please find below the original on click procedure follwed by your
suggestion.

Could you advise were the problem is in the code

Thanking You
Bazmac

Private Sub Update_Click()
On Error GoTo Err_Update_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Installation"

stLinkCriteria = "[JobNo]=" & "'" & Me![JobNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Update_Click:
Exit Sub

Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click


End Sub

Private Sub Update_Click()
On Error GoTo Err_Update_Click

Dim stDocName As String
Dim stLinkCriteria As String

'stDocName = "Installation"

'stLinkCriteria = "[JobNo]=" & "'" & Me![JobNo] & "'"
'DoCmd.OpenForm stDocName, , , stLinkCriteria

If Me.Job_Type = "Installantion" Then ' or the Job_TypeID
DoCmd.OpenForm "Installation", , , "[JobNo]=" & "'" & Me![JobNo] & "'"
Else
DoCmd.OpenForm "Warranty", , , "[JobNo]=" & "'" & Me![JobNo] & "'"
End If


Exit_Update_Click:
Exit Sub

Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click


End Sub

You need to change the recordsource of the form to a query in order to
add
the Job_Type to the form. Then you need to branch the code with an If ...
Then ... Else statement or a Select Case statement. Here's an aircode
snippet:

If Me.txtJob_Type = "Installantion" Then ' or the Job_TypeID
DoCmd.OpenForm "Form1",,, "JobID = " & Me.txtJobID
Else
DoCmd.OpenForm "Form2",,, "JobID = " & Me.txtJobID
End If
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access





.

 

[Bazmac]

Alvin,

Thankyou for your help this would have the worked first time if I could
spell properly.(Installantion)

Bazmac

Arvin,

Thankyou for your quick reply I have tried your suggestion the outcome is
that "Warranty" is the only form to display regardless of job type chosen,
please find below the original on click procedure follwed by your suggestion.

Could you advise were the problem is in the code

Thanking You
Bazmac

Private Sub Update_Click()
On Error GoTo Err_Update_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Installation"

stLinkCriteria = "[JobNo]=" & "'" & Me![JobNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Update_Click:
Exit Sub

Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click


End Sub

Private Sub Update_Click()
On Error GoTo Err_Update_Click

Dim stDocName As String
Dim stLinkCriteria As String

'stDocName = "Installation"

'stLinkCriteria = "[JobNo]=" & "'" & Me![JobNo] & "'"
'DoCmd.OpenForm stDocName, , , stLinkCriteria

If Me.Job_Type = "Installantion" Then ' or the Job_TypeID
DoCmd.OpenForm "Installation", , , "[JobNo]=" & "'" & Me![JobNo] & "'"
Else
DoCmd.OpenForm "Warranty", , , "[JobNo]=" & "'" & Me![JobNo] & "'"
End If


Exit_Update_Click:
Exit Sub

Err_Update_Click:
MsgBox Err.Description
Resume Exit_Update_Click


End Sub

You need to change the recordsource of the form to a query in order to add
the Job_Type to the form. Then you need to branch the code with an If ...
Then ... Else statement or a Select Case statement. Here's an aircode
snippet:

If Me.txtJob_Type = "Installantion" Then ' or the Job_TypeID
DoCmd.OpenForm "Form1",,, "JobID = " & Me.txtJobID
Else
DoCmd.OpenForm "Form2",,, "JobID = " & Me.txtJobID
End If
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access





.