Hi Bob

Glad it's all working :-)

One thing I forgot to say was you should change your PK field
"ContactTypeID" from AutoNumber to Long integer. When you have values
hard-coded in your app, it makes no sense to have Access automatically
generating those values for you!

On the DAO/ADO thing, I could not disagree more with the authors of "The
Bible" (was that blasphemy? g)

DAO is the native object model for Jet databases and, as such, is the
"natural" way of communicating with them. ADO is a sort of Esperanto, and
while it can achieve most things, albeit in a sometimes inefficient way,
there are some things which require DAO.

There were rumours flying around about 4-5 years ago that "DAO is dead", and
that there would be no further development to the Jet engine, but this has
proved very wrong, as can be seen from what has happened with Access 2007.

At about that time, Mary Chipman, who most would consider a High Priestess
in this area, wrote the following article:
http://sqlserveradvisor.com/doc/05515

She also made this statement in a private forum, which I'm sure she would
not mind being quoted:

quote
DAO was designed, customized, and tweaked specifically for the Jet
engine. In an all-Access application, it will give you the best
performance and the most complete feature set. It will undoubtedly
continue to be supported as long as Jet ships as a database engine for
Access. If you only program in Access against Jet, you probably won't
ever need ADO.

ADO was designed as a general-purpose data access wrapper around OLE
DB and is not specific to a particular engine. If your Access
application makes use of SQL Server data, then ADO will be a better
choice when writing data access code (and is in fact used in an ADP
where the Jet engine isn't present). DAO uses the Jet engine, which
adds unnecessary overhead when coding against SQL Server, etc. If for
some reason you can't use ADO, then ODBCDirect is a better choice when
coding against SQLS because it bypasses Jet.

You don't need to worry about learning ADO.NET unless you're building
a VS.NET windows forms or an ASP.NET app, because it doesn't work in
Access or any other COM apps--it requires the .NET Framework. The
other developers you spoke to are right--ADO.NET is really a new and
different technology that bears only a superficial resemblance to
classic DAO/ADO. However, you still may need to learn ADO if you land
a project that isn't being built using .NET -- VB6 will undoubtedly be
around for years to come.

Yes, the alphabet soup of data access technologies is confusing -- but
there's sound technical reasons why you'd choose one data access
method over another in a given situation.
\quote

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Bob" wrote in message
...
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