Whoo Hoo!

Thanks alot Graham. It's finally working. :-)

Just one more question though, why do you prefer DAO over ADO? I've now
acquired a copy of the "Microsoft Office Access 2003 Bible" and there's a
small section in it that asserts that ADO is to be preferred over DAO
because Microsoft does not plan to provide any future enhancements for the
latter. I'm still starting out at this, so I'm not entirely clear on the
other pros and cons of each.


Regards
Bob


"Graham Mandeno" wrote in message
...
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 (ToolsReferences).
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
datasheet) 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" wrote in message
ups.com...
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