If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
It sounds like you're using Access 2000, which does not have this option.
(This would also explain why your VBA references were set to ADO by default, and not DAO) You don't want SQL-92 anyway, if you are working with a Jet database. -- Cheers, Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Actually I did have another question too - more out of curiosity than anything else. You mentioned earlier that to switch to ANSI-92 mode you need to go to Tools Options Tables/Queries. When I following these directions I get a form with a Table and a Query design section. In the Table design section, I get options to change the default field sizes, the default field type, and a box for "AutoIndex on Import/Create". In the Query design section, I get checkboxes to "show table names", "output all fields" and "enable autojoin". I also get radio buttons to "Run permissions" as "Owner's" or "User's". But I don't see anything that refers to ANSI mode. Any ideas why? Regards Bob "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 |
#32
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
Yes, I run Access 2000 at home. We have access 2003 at work and I just found the right settings on that one. I'll keep away from it anyway, but its nice to know where the option is if I ever need it. 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 :-) ). I came to the conclusion that the "cboContactType.Undo" line simply wasn't working. After looking in the help files on the "dirty" event, I noticed this statement: "The BeforeUpdate and AfterUpdate events for a record occur after you have entered the new or changed data in the record and moved to another record (or clicked Save Record on the Records menu), and therefore AFTER the Dirty event for the record." (my emphasis) So I focussed on the dirty event. Anyways, for the benefit of those who might have been following this thread, I resolved the problem by simply adding "Me.Form.Undo" in the line immediately after the existing undo command. These variations also worked ("Clients" is the name of my main form): (1) Forms!Clients.Undo (2) If Forms("Clients").Dirty = True Then Forms("Clients").Undo End If Everything seems to be fine now. Thanks again for all your help on this issue. 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 :-) 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? 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? Thanks for letting me pick your brain. TIA Bob Graham Mandeno wrote: It sounds like you're using Access 2000, which does not have this option. (This would also explain why your VBA references were set to ADO by default, and not DAO) You don't want SQL-92 anyway, if you are working with a Jet database. -- Cheers, Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Actually I did have another question too - more out of curiosity than anything else. You mentioned earlier that to switch to ANSI-92 mode you need to go to Tools Options Tables/Queries. When I following these directions I get a form with a Table and a Query design section. In the Table design section, I get options to change the default field sizes, the default field type, and a box for "AutoIndex on Import/Create". In the Query design section, I get checkboxes to "show table names", "output all fields" and "enable autojoin". I also get radio buttons to "Run permissions" as "Owner's" or "User's". But I don't see anything that refers to ANSI mode. Any ideas why? Regards Bob "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 |
#33
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
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 |
#34
|
|||
|
|||
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 |
#35
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Graham,
Don't trouble yourself with my last query. I got access to create a new form using my junction and TeleComs table and then used the resulting form as my source for the subform control in my main form. This achieves what I want except that the control does not automatically resize to enable all records in the subform to be seen. Is there a way to do this? Regards Bob "Bob" wrote in message ... 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 |
#36
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Hi Bob
No doubt you worked out that you should base your subform's recordsource, not on tblTeleComs, but on a query comprising both tblTeleComs AND tblContactComs. That way you have a ContactID in your recordsource to be the link child field. The total height required by a form may be calculated as follows: (height of detail section * number of records) + height of header and footer (if present) In VBS terms: Public Function FormHeightRequired(frm as Form) as Long Dim lHeight as Long With frm .RecordsetClone.MoveLast lHeight = .Section(acDetail).Height * .RecordsetClone.RecordCount On Error Resume Next ' in case no header/footer lHeight = lHeight + .Section(acHeader).Height + ..Section(acFooter).Height FormHeightRequired = lHeight End With End Function You can call this in the Current event proc of your main form to set the height of the subform. With Me.[SubformControl] .Height = FormHeightRequired(.Form) + some constant End With The some constant is an extra bit you will have to fiddle with to accommodate the subform control's border. Start with about 30. You probably want to specify a limit, in case the required space is not available. In this case, you can turn on a vertical scrollbar for the subform: .Form.ScrollBars = 2 ' vertical only or, if there is enough space: .Form.ScrollBars = 0 ' no scrollbars -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Don't trouble yourself with my last query. I got access to create a new form using my junction and TeleComs table and then used the resulting form as my source for the subform control in my main form. This achieves what I want except that the control does not automatically resize to enable all records in the subform to be seen. Is there a way to do this? Regards Bob "Bob" wrote in message ... 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 |
#37
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
Oh, by the way... I thought you might like to know that your first email
just arrived this morning. God knows where it's been! :-) -- Cheers, Graham |
#38
|
|||
|
|||
How to enforce subtypes/supertypes in Access 2000?
I guess snail mail has gone electronic :-D
Regards Bob "Graham Mandeno" wrote in message ... Oh, by the way... I thought you might like to know that your first email just arrived this morning. God knows where it's been! :-) -- Cheers, Graham |
Thread Tools | |
Display Modes | |
|
|