View Single Post
  #34  
Old August 12th, 2006, 09:39 AM posted to microsoft.public.access.tablesdbdesign
Bob
external usenet poster
 
Posts: 15
Default How to enforce subtypes/supertypes in Access 2000?

Thanks Graham,

I was a bit worried about Form.Undo myself (for the very reason you warn
against using it) - but it seems that access auto-saves any changes
everytime you move (eg tab) to the next field anyway. So I figured I was
safe or at worst would lose changes only to the current field. Your
solution is, of course, much better. I updated the relationships and
removed the extra undo and everything works. Thanks yet again.

As for porting to VB.Net, the learning curve for Access just seems a little
easier to climb than VB.Net; especially when you have very little spare
time. While I always enjoy pulling my hair out (to the point that I'm sure
I'm experiencing premature balding :-D ), I'm also keen to see some
progress. With Access, I hope to see a working prototype a little sooner.

I was rather hoping that the required runtime files might come with VB.Net
itself. I guess I'll have to invest in a copy of VSTO.

While I still have your attention, I'd like to ask a new question related to
form/subform design. I would like to move all of my contact phone and fax
numbers into a new table (tblTeleComs). I have created a Junction table
tblContactComs to enable a many to many relationship between tblContacts and
tblTeleComs. The table structions for the two new tables are as follows:

tblTeleComs
ComID (pk - autonumber)
ComType [to store text descriptions like "Work Tel" or "Home Tel")
ComNumber

tblContactComs
ContactID (pk - number)
ComID (pk - number)

I've created a new subform (subfrmContactNumbers) which I have set up as a
continuous form. This subform is linked to tblTeleComs.

What I would like to do is have two subforms on my existing main form. I
want my existing subform (NewIndiv/NewOrg) to go on the left, and my new
form to sit immediately to the side of that. This way all phone and fax
numbers will run down the right hand side of the main form. I want to the
user to be able to add new numbers on this form by simply pressing a command
button - and have a new blank row appear immediately below the existing fax
and phone numbers ready for a new number to be inserted. (I hope this is
clear).

Anyway, my problem is that I don't have a ContactID in tblTeleComs so how do
I link the new subform to my main form?



TIA
Bob



"Graham Mandeno" wrote in message
...
Hi Bob

Answers inline...

By the way, I think I found a slight bug in the Before_Update code you
supplied. What I have found is if I try to change the ContactType the
custom error message popups up as expected. If I then opt to delete
the existing record, everything is fine. The record is deleted and I
can still navigate the records using the record selector at the foot of
the main (Clients) form - again everything works as expected. But if I
opt to cancel the update instead, I end up getting the standard Access
message about not being able to change or delete the record (ie the one
I was getting before you came to the rescue :-) ).


That's annoying, isn't it? I reckon that this is a bug in Access. It
recognises that you have changed one of the fields in the relation but
fails to recognise that you have undone that change.

I have discovered a workaround, and that is to set the cascading updates
property on the relations between Contacts/Individuals and
Contacts/Organisations. That way, Access doesn't whinge when you change
the master side of the relationship - it just updates the foreign key
field to the same value as it had before. Of course, you can't really
change the value of Contacts.ContactTypeID because the constraint in the
related table allows only one valid value.

So... make this change and remove the extra Undo.

By the way, the correct form to use if you want to undo the entire form
is:
Me.Undo

Me is already a form object, so Me is equivalent to Me.Form.

The reason you don't want to undo the entire form is that you might have
changed a whole lot of other fields in your Contacts record before
inadvertently changing the contact type. You are then stuck between the
rock and the hard place - either delete the related record and lose all
the indiv/org data, or undo the current record and lose all your changes.

Getting back to the ADO/DAO, my long term aim would be ultimately to
port my little Access program to VB.Net - purely because I'm a sucker
for punishment :-)


You certainly are :-)

this will be a complete rewrite. there is no easy way to convert an
Access app to .Net (or VB6 for that matter).

If I code everything using DAO now, will I need to re-code everything
into ADO later, or does VB.Net not care which one you reference?


I believe you can use the DAO object model from .Net - no reason why not.

On a different tangent, if I port everything over to VB.Net, do you
know if I will need extra licences or if I have to upgrade to a
developer edition of some sort before I can distribute my VB.Net
program with an .mdb file? Will my VB.Net or existing ms office
licences cover this sort of thing (ie distribution)? And what's the
deal with runtime files? Will they be packaged with my .mdb file if I
incorporate that into a VB.Net program?


No, you don't need any licences to open an MDB using .Net or VB6. All you
need is DAO360.DLL, but I'm pretty sure this comes with Windows anyway.

If you buy Visual Studio Tools for Office (VSTO) you get with it the
Access Developer Extensions (ADE) which allows you unlimited licence to
distribute the runtime version of Access, so you can install and run your
Access app on a computer that doesn't have Access (or even Office)
installed.

Thanks for letting me pick your brain.


My pleasure :-)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand