Opening different linked forms depending on criteria in first form

[Maboo]

I have a list of contacts containing fields [ContactID] and [ContactType].

I need to attach code to a button which will open either "frmCustomers" or "frmSuppliers" depending on the value in [ContactType] but also to display only the record relevant to [ContactID].

Hope that makes sense.

[Rabbit]

What have you tried so far?

[Maboo]

I have only been able to open one form or the other to the relative record. That bit is easy. I need to be able to open one form if it is a customer and the other form if it is a supplier as they display different tabs.

I guess it will need an If Then statement but don't know where to start.

This is what I have attempted to do (try not to laugh!)

If Me.ContactType.Value = "Customer" Then
 
 
    DoCmd.OpenForm "frmCustomers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
        Else
  If Me.ContactType.Value = "Supplier" Then
 
 
    DoCmd.OpenForm "frmSuppliers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
    End If

[Rabbit]

I have only been able to open one form or the other to the relative record. That bit is easy. I need to be able to open one form if it is a customer and the other form if it is a supplier as they display different tabs.

I guess it will need an If Then statement but don't know where to start.

This is what I have attempted to do (try not to laugh!)

If Me.ContactType.Value = "Customer" Then
 
 
    DoCmd.OpenForm "frmCustomers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
        Else
  If Me.ContactType.Value = "Supplier" Then
 
 
    DoCmd.OpenForm "frmSuppliers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
    End If

This is close. Some changes need to be made though.

1) You didn't Dim stDocName or stLinkCriteria.

2) You use two DoCmd.OpenForms to open what I assume is the same form.

3) You have stDocName as the form to open but you never populate it.

4) It's Else If on one line, not two lines.

Fix these issues and then we can tackle whatever comes up after.

[Maboo]

Thanks Rabbit

I've made the following changes which seem to work OK:

If Me.ContactType.Value = "Supplier" Then
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    DoCmd.OpenForm "frmSuppliers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
 
ElseIf Me.ContactType.Value = "Customer" Then
 
    DoCmd.OpenForm "frmCustomers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
 
    End If

Much obliged.

[Maboo]

No it doesn't! It opens the correct form but not to the correct record.

More help on this would be appreciated. Not very good with code.

[Maboo]

No it doesn't! It opens the correct form but not to the correct record.

More help on this would be appreciated. Not very good with code.

I think I've got it!

Here's what I've done:

Dim stDocName As String
Dim stLinkCriteria As String

If Me.ContactType.Value = "Customer" Then

stDocName = "frmCustomers TabControl"

ElseIf Me.ContactType.Value = "Supplier" Then

stDocName = "frmSuppliers TabControl"

End If

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

Thanks

[Rabbit]

Thanks Rabbit

I've made the following changes which seem to work OK:

If Me.ContactType.Value = "Supplier" Then
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    DoCmd.OpenForm "frmSuppliers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
 
ElseIf Me.ContactType.Value = "Customer" Then
 
    DoCmd.OpenForm "frmCustomers TabControl"
 
    stLinkCriteria = "[ContactID]=" & Me![ContactID]
 
    End If

Much obliged.

1) You want to dim the variables outside the If/Then structure.

2) You want to set the value of stDocName and stLinkCriteria before you use it.

3) You want to Open the Form after you fill in the stDocName and stLinkCriteria.

4) You'll want to use both stDocName and stLinkCriteria to open the form before like you did in your first version of the code.

5) stDocName should be set to the name of your form.