Re: How to enforce subtypes/supertypes in Access 2000?

Hi Bob

I found the second one in the spam bucket - it didn't like you for some
reason :-)

Don't know what happened to the first - maybe met the same fate.

Anyway, the problem was that you were not switching the subform in
Form_Current, so it *looked* like you were on a new record but you were
actually changing the ContactType for an existing record.

Try these modifications to your code - most you can just copy and paste:

' since you need to do it in more places than one,
' add a private proc to switch the subform

Private Sub SetContactType()
With Me.Contactsubfrm
Select Case Me.cboContactType.Value
Case 1 ' Individual
.SourceObject = "NewIndiv"
.Visible = True
Case 2 ' Organisation
.SourceObject = "NewOrgs"
.Visible = True
Case Else
.Visible = False
End Select
End With
End Sub


' Call it in both cboContactType_AfterUpdate AND Form_Current

Private Sub cboContactType_AfterUpdate()
Call SetContactType
End Sub

Private Sub Form_Current()
Call SetContactType
End Sub

' now, the icing on the cake to get rid of those nasty unfriendly messages
Private Sub cboContactType_BeforeUpdate(Cancel As Integer)
Dim sTable As String
Dim sMsg As String
If Not IsNull(cboContactType.OldValue) Then
Select Case cboContactType.OldValue
Case 1
sTable = "Individuals"
Case 2
sTable = "Organisations"
End Select
If DCount("*", sTable, "ContactID=" & Me.ContactID) <> 0 Then
sMsg = "If you change the type of this contact, then you must " _
& "first delete all related information from the " & sTable _
& " table." & vbCrLf & vbCrLf & "Do you really want to do this?"
If MsgBox(sMsg, vbQuestion Or vbYesNo Or vbDefaultButton2) _
= vbYes Then
CurrentDb.Execute "Delete * from " & sTable _
& " where ContactID=" & Me.ContactID, dbFailOnError
Else
Cancel = True
cboContactType.Undo
End If
End If
End If
End Sub

You should change your VBA project references too (Tools>References).
Remove the reference to ADO (Microsoft ActiveX Data Objects 2.x) and add one
to DAO (Microsoft DAO 3.6).

Also, I suggest you make Contacts.ContactTypeID a required field.

Finally, make your subforms both Single form view (not continuous or
data***) and remove all record selectors and navigation buttons.

Let me know how you get on :-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Bob" <boblanders123@xxxxxxxxx> wrote in message
news:1155179612.187666.282010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Graham,

I must've missed your post last night. I've emailed another copy of
the file to your new email address.


Cheers
Bob


.