If Statements

M

Michael

I need to create an if statement that will first determine whether the
contact type is a client or a vendor. Then, it needs to open a
particular form based on criteria for each of the vendors & clients
sub types. Here is what I came up with so far but obviously it
doesn't work. Can someone show me what I'm missing?

Private Sub ContactID_DblClick(Cancel As Integer)
On Error GoTo Err_ContactID_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

If Me![ContactType] = "Vendor" Then
If Me![VendorType] = "Labor Vendor" Then
stDocName = "FRM_Vendors-Labor-Direct"
ElseIf Me![VendorType] = "Resource Vendor" Then
stDocName = "frm_vendorsResource"
Else: stDocName = "FRM_VendorsAll"
End If

stLinkCriteria = "[vendorID]=" & Me![VendorID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If Me![ContactType] = "Client" Then
If Me![ContactSubType] = "Clients-Contract" Then
stDocName = "FRM_Clients_Contract"
ElseIf Me![ContactSubType] = "Clients-Direct" Then
stDocName = "Frm_Clients_Direct"
Else: stDocName = "FRM_Clients_Indirect"
End If

stLinkCriteria = "[ContactID]=" & Me![ContactID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_ContactID_DblClick:
Exit Sub

Err_ContactID_DblClick:
MsgBox Err.Description
Resume Exit_ContactID_DblClick
End Sub
 
M

Marshall Barton

Michael said:
I need to create an if statement that will first determine whether the
contact type is a client or a vendor. Then, it needs to open a
particular form based on criteria for each of the vendors & clients
sub types. Here is what I came up with so far but obviously it
doesn't work. Can someone show me what I'm missing?

Private Sub ContactID_DblClick(Cancel As Integer)
On Error GoTo Err_ContactID_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

If Me![ContactType] = "Vendor" Then
If Me![VendorType] = "Labor Vendor" Then
stDocName = "FRM_Vendors-Labor-Direct"
ElseIf Me![VendorType] = "Resource Vendor" Then
stDocName = "frm_vendorsResource"
Else: stDocName = "FRM_VendorsAll"
End If

stLinkCriteria = "[vendorID]=" & Me![VendorID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If Me![ContactType] = "Client" Then
If Me![ContactSubType] = "Clients-Contract" Then
stDocName = "FRM_Clients_Contract"
ElseIf Me![ContactSubType] = "Clients-Direct" Then
stDocName = "Frm_Clients_Direct"
Else: stDocName = "FRM_Clients_Indirect"
End If

stLinkCriteria = "[ContactID]=" & Me![ContactID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_ContactID_DblClick:
Click to expand...


You're missing a couple of End If lines. Try using:

If Me![ContactType] = "Vendor" Then
If Me![VendorType] = "Labor Vendor" Then
stDocName = "FRM_Vendors-Labor-Direct"
ElseIf Me![VendorType] = "Resource Vendor" Then
stDocName = "frm_vendorsResource"
Else
stDocName = "FRM_VendorsAll"
End If
stLinkCriteria = "[vendorID]=" & Me![VendorID]

ElseIf Me![ContactType] = "Client" Then
If Me![ContactSubType] = "Clients-Contract" Then
stDocName = "FRM_Clients_Contract"
ElseIf Me![ContactSubType] = "Clients-Direct" Then
stDocName = "Frm_Clients_Direct"
Else
stDocName = "FRM_Clients_Indirect"
End If
stLinkCriteria = "[ContactID]=" & Me![ContactID]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
M

Michael

Perfect! I knew that's what it was but I didn't know where to put all
the EndIF's

Thanks a million! My life is now officially easier and more
productive thanks to you.
 
M

Michael

Here is my finished product if anyone wants it for reference:

Private Sub ContactID_DblClick(Cancel As Integer)
If Me![ContactType] = "Vendor" Then
If Me![ContactSubType] = "Vendors-Direct" Then
stDocName = "FRM_Vendors-Labor-Direct"
ElseIf Me![ContactSubType] = "Resource Vendor" Then
stDocName = "frm_vendorsResource"
ElseIf Me![ContactSubType] = "Employee" Then
stDocName = "frm_vendorsEmployee"
Else
stDocName = "FRM_VendorsAll"
stLinkCriteria = "[vendorID]=" & Me![ContactID]
End If

ElseIf Me![ContactType] = "Client" Then
If Me![ContactSubType] = "Clients-Contract" Then
stDocName = "FRM_Clients_Contract"
ElseIf Me![ContactSubType] = "Clients-Direct" Then
stDocName = "Frm_Clients_Direct"
Else
stDocName = "FRM_Clients_Indirect"
End If
stLinkCriteria = "[ClientID]=" & Me![ContactID]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
 
M

Marshall Barton

Michael said:
Here is my finished product if anyone wants it for reference:

Private Sub ContactID_DblClick(Cancel As Integer)
If Me![ContactType] = "Vendor" Then
If Me![ContactSubType] = "Vendors-Direct" Then
stDocName = "FRM_Vendors-Labor-Direct"
ElseIf Me![ContactSubType] = "Resource Vendor" Then
stDocName = "frm_vendorsResource"
ElseIf Me![ContactSubType] = "Employee" Then
stDocName = "frm_vendorsEmployee"
Else
stDocName = "FRM_VendorsAll"
stLinkCriteria = "[vendorID]=" & Me![ContactID]
End If

ElseIf Me![ContactType] = "Client" Then
If Me![ContactSubType] = "Clients-Contract" Then
stDocName = "FRM_Clients_Contract"
ElseIf Me![ContactSubType] = "Clients-Direct" Then
stDocName = "Frm_Clients_Direct"
Else
stDocName = "FRM_Clients_Indirect"
End If
stLinkCriteria = "[ClientID]=" & Me![ContactID]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
Click to expand...


That looks good.

Another way that may(?) be a lttle easier to read:

Private Sub ContactID_DblClick(Cancel As Integer)
If Me![ContactType] = "Vendor" Then
Select Case Me![ContactSubType]
Case "Vendors-Direct"
stDocName = "FRM_Vendors-Labor-Direct"
Case "Resource Vendor"
stDocName = "frm_vendorsResource"
Case "Employee"
stDocName = "frm_vendorsEmployee"
Case Else
stDocName = "FRM_VendorsAll"
stLinkCriteria = "[vendorID]=" & Me![ContactID]
End Select

ElseIf Me![ContactType] = "Client" Then
Select Case Me![ContactSubType]
Case "Clients-Contract"
stDocName = "FRM_Clients_Contract"
Case "Clients-Direct"
stDocName = "Frm_Clients_Direct"
Case Else
stDocName = "FRM_Clients_Indirect"
End Select
stLinkCriteria = "[ClientID]=" & Me![ContactID]
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

I can't tell what you intended, but you are not setting
stLinkCriteria for all of the vendor subtypes. If you do
want the criteria to be set in all situations, put the line:
stLinkCriteria = "[ClientID]=" & Me![ContactID]
right before the OpenForm line.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Click Event of a Button 2
I am not "getting" it.....DoCmd. OpenForm 4
DoCmd. Openform Error Message IF no records found...not getting it 0
OutputTo 3
If then statement in Visual Basic 2
How open a form with a blank record 1
Invalud Use of Null 3
Pass data to new form 2

Top