Help to adapt address book database – access 2003

[Kenny9002]

Hi all,

I belong to a fast-growing association and we decided to introduce a membership database. This will enable us to enter members basic details and be able to mail-merge letters/documents to members. Among other things, the database will be able to report members’ birthdays so we’ll be able to send birthday cards to members.

I searched and found a good Microsoft Database template – an Address Book Database (Access 2003). However, I need to tweak and adapt the database to meet our needs, and I need help please on this.

I have attached a copy of the database. I would need the database tweaked and adapted for me in the following areas:

• The BirthDate field: The entry for the birthdate is found In the ‘Other Information’ tab. However, the birthdate is designed to show the dd/mm/yyyy format. Most members are not keen to provide their ‘year’ of birth. They will only provide the day and month. That’s fine. I’d like the birthdate field to be changed to show only the day and month (i.e. the dd/mm format).
• Resetting the ID Numbering to zero: When I was trying the db out to see how it works, I created some dummy entries. And each time, an entry is created, an automatic ID number is generated. However, when I go to the Addresses Table and delete all the entries so far made, so I can start using the db afresh – with the ID numbering starting afresh from No.1, I find that I can’t achieve that. Even though all the existing records are deleted from the table, when I try to enter new entries, the ID numbering doesn’t start from No.1, it starts from the ‘next’ number after the number of the last entry made before all the records were deleted in the Table. I’d like to be able to reset the ID numbering when any previous record is deleted. If resetting is not possible, then I’d like the ID numbering field removed.
• Additional Buttons: I would like to have (at the bottom of the db, the following additional buttons built in, to enable ease of use: ‘Delete Entry’, ‘Find Entry’, ‘Filter & Sort’. ‘Cancel’. The ‘Delete Entry’ button should enable me to delete any entry/record, and when that is done, the ‘Numbering’ should be able to syncronize & appear sequentially.
• Please kindly delete the following: ‘Provide Feedback’ which shows in the ‘Under Reports’ as it is not necessary for us.
• If it’s possible, I’d like a ‘View Entries’ section just below the additional buttons requested for in 3 above. The View Entries section will have the following:

‘View Entry No.__’, ‘Previous __’, ‘Next’, ‘Last’

I am sorry that I am asking for so much tweaking to be done for. Even if the items in No. 5 above can’t be done, I’ll be happy with adapting the db to meet our nos 1 – 4 needs.

I need to clarify that I am adapting the Access 2003 Database cos in the Microsoft Office site where it is available for download (sorry it's not possible to include the link for the original db here), it is only the Access 2003 template that is available for download. No Access 2007 version.

Thanks for your kind help.

Kenny

 

[Pat Hartman]

Welcome aboard.
I think you've misunderstood the function of this forum. We are not here to do custom programming for you for free. We are here to provide guidence and help you over the hurdles of doing it yourself.

1. Leaving the birth date field as a Date datatype will help with using it later. If you enter just month and day, it will default to the current year which shouldn't cause a problem. You can adjust the format property of the form/report controls to show only month and day.
2. The autonumber is used to provide a unique identifier for a row. It should be used as the primary key for a table if you don't have a candidate key in the set of attributes for the table. You should attribute no meaning to this field and you can hide it in most cases. It will only be used to join tables together. Do not worry about gaps either. This number is used ONLY to uniquely identify a row and to join to other tables.
3. If you have the wizards turned on (they are on by default), you will be able to assign functionality to a button as you add it to a form. The wizard will build the code.
4. & 5. You'll be able to do these after you poke around a little bit.

 

[Kenny9002]

Thanks Pat for your kind response. I am grateful. I am also sorry if my initial post appeared to be a request for custom programming for me for free. That was not my intention.

I am very new and a complete novice with respect to Access. However, I am willing to learn and should be grateful for any help to enable me to learn.

Concerning the Birthdate, I have tried 'poking' around to see how I can format it to dd/mm, but have been unsuccessful. If you could please give me the steps to achieve that, I will be grateful.

Regarding the autonumber - if you have a look at the sample database I attached to my initial post, you'll observe that I went to the Table and deleted No. 3 record, and that gap still remains. I know you have indicated that it does not really matter. However, when using the db and I try to scroll from one record to another, it can be seen that No. 3 record is blank. It doesn't really look nice. If it is not possible to do anything about syncronizing the numbering when a record is deleted, I was thinking of deleting the ID Number column itself completely. I do not believe that I will have any future need for the ID numbers. I'll appreciate your advice, please.

Re Nos. 3,4 & 5, I shall do some further search & 'poking around' and reading to find out how best I can achieve those. Given my non-existent knowledge/skills in Access, I know it will be daunting, but I'll give it a try.

Again, thanks for your kind help. I remain grateful for any further help.

Kenny

 

[Alansidman]

Regarding the autonumber - if you have a look at the sample database I attached to my initial post, you'll observe that I went to the Table and deleted No. 3 record, and that gap still remains. I know you have indicated that it does not really matter. However, when using the db and I try to scroll from one record to another, it can be seen that No. 3 record is blank. It doesn't really look nice.

Tables are used for data storage in Access. Think of your table as big garbage barrel that you throw data into. You would not go diggging through the garbage to get your data back. Likewise you should not be looking at your table as your report. In Access, you get your data back for review, analyzation and presentation through queries and reports. Therefore it does not matter how it is sorted in the table. The gap will not appear if you use reports and queries to search and present your data.

Alan

 

[Kenny9002]

Hi Alan,

Thanks for your response. May be I'm getting something wrong - given my lack of knowledge/skills in Access. The reason I keep bothering about the Table and how it looks is this: If you read my initial post, I indicated that we hope to use the information stored to do, among other things, mail merge.

I know that in order to do a mail merge, one needs an Excel table (like the 'Addresses' under Table in the database). Is it possible that the db could be further designed to generate an Excel-type Report that is idential to the 'Addresses' in the Table, that could be used for such tasks as mail merge - with all the same headings as are available in the 'Addresses' under Table? If that is the case, then that's very good. But my problem then shifts on how to design such a 'report'. Any suggestions, please?

Thanks.

Kenny

 

[Pat Hartman]

I do not believe that I will have any future need for the ID numbers. I'll appreciate your advice, please.

As I pointed out in my earlier post - YOU DO HAVE NEED for the ID. It is used to join to the other tables so you can select related data.

Access can do a mail merge directly, you don't need to export to Excel.

You can export tables or queries directly to Excel by using the right-click menu.

You can create your own forms and reports by using the Wizards. Make a copy of the database that you can try things in. You'll be amazed at how easy it is to create reports.

 

[Kenny9002]

Hi Pat,

Thanks again for your further advice. I'll do my best to explore more with a view to getting the database do just what I desire.

You have advised using the wizard. I am not trying to start a brand new database from the scratch. As I indicated, I wish to tweak the existing database (a copy of which I attached in my initial post). Is it possible to use the wizard even for this particular database to do further work on it? Could you kindly point me to how I can invoke the wizard for that purpose. Still willing and ready to learn.

Thanks for all your kind help.

Kenny

 

[sparks80]

Hi Kenny,

On the left hand side of the main database window you will see several objects. Click on "Forms" and you will be shown all the forms in the database.

At the top of the database window click New. The Form Wizard is the second option on the list.

The same is true for reports - select "Reports" and click on New. The second option is the Report Wizard

 

[Kenny9002]

Hi Sparks80,

Thanks for your kind suggestions. I will try them out.

Kenny

 

[Kenny9002]

Hi,

Thanks very much to you and to all the other members who responded very kindly to my Post. With all your help and advice, I was encouraged to do some learning/work on my own and was able to tweak the database (attached to my first Post) to suit my needs. I have got everything that I wanted included in the database.

However, I have a nagging problem - when I click to open the database either in Office 2007 computer or in Office 2003 computer, and should be grateful for your kind help.

Office 2007:
a) When I click to open the database, it opens up BUT with Microsoft Office Access 2007 alongside at the same time - and with the database navigation pane showing on the lefthand side.

b) In addition, there is always a Security Warning showing - with an 'Option' button beside it. When I click the option button, I get a screen with the following message: "Access has disabled potentially harmful content in theis database.If you trust the contents of this db & would like to enable it for this session only, click Enable this content. I realise that the database would only work properly if I click the 'Enable this content' option.

Request:
1) I don't want the database opening with Microsoft Office Access 2007 application. I'd like the database to open just itself as an application for entry of data etc. If at any time I wish to tweak any of the Tables, Reports, Forms, etc., it will be OK for me to first open Access 2007 and then open the database to access the various objects.

2) Is it possible to disable the Security Warning from showing and have the macros permanently enabled. If any other user clicks to open the database, I'd like it to open up with the macros enabled without requiring each user to click 'option' and then choose 'Enable this content'.

3) Finally, I'd like to protect the database and not allow any other user to access the objects, i.e. the Tables, Forms, Reports, etc. through Microsoft Access 2007 Application. Users should only be able to open the database as an application, key in information and run the reports available from the database screen.

Office 2003:
The above issues - especially (b), (2) (3) apply when I use a pc with Office 2003. The exception being that with Office 2003, when I click to open the database on its own - the first time, it opens up without Microsoft Access 2003. But if I wish to do some work on the Tables, Forms, Reports I first open Access 2003 and then open the database from within Access 2003. However, when I finish working on any of the objects, close and try to open the database on its own, it does only after the following message "Please wait while Windows configures Microsoft Office Access Runtime (English) 2007". But at least even after this, I am usually able to open the database on its own in Office 2003 without Access 2003 opening at the same time. But it is not the case when it is used on a pc with Access 2007.

Thanks for your anticipated help with the above. I remain very grateful.

Kenny

 

[Pat Hartman]

a) You can use the Access Options to hide the nav pane on startup and specify which form you want the database to open to. In A2007, press the office button at the upper left of thw Access window. Options is in tiny print at the bottom of the dialog. While your there turn off all the Name AutoCorrect options.
b) From this same Options menu, choose Security options and then Trusted Locations. Press the button and add the directory where you keep most of your Access applications. If they're all over the place, you should consider consolidating them or you'll be adding new directories all the time. Also, while you're here, set the macro security to the lowest setting.
1) As I suggested in a above, you have some control over what shows but you can't turn off Access entirely.
2) see above
3) You need to create a menu if you haven't already, that gives the users access to everything you want them to see. This plus the Options setting I suggested earlier will give you a start. Then create an .accde. Make sure you keep the .accdb in a safe place because there is no way to go back. Whenever you need to change an object, you'll go back to the .accdb, make the change and then make a new .accde. The final step is to rename the .accde to .accdr. This tells Access to always open the application as if it were the runtime engine. This effectively hides everything that is design related. It doesn't provide any real security because techniques like this are widely discussed but it will prevent accidental probing and avoid accidents. Anyone who knows anything about Access can still get in to the tables, queries, and macros although the forms, reports, and modules cannot be changed.

 

[Kenny9002]

Thanks Pat for your kind suggestions. I managed to 'poke' around and find solutions to some of the issues raised in my most recent post - especially the Security notification.

However, I'm still not able to disable all the Access 2007 menus when I open the database. The Home tab still shows! I went into Options, and cleared the 'Allow Full Menus' check box, but I still have the Home tab showing. My desire is to open the database and not have any of the Access 2007 menus (including the Home tab) showing.

When I open the database using a pc with Office 2003, this works fine: None of the Access 2003 menus shows up at all. However, I want the same when I open the database with a pc with Office 2007, but I'm not getting that. Is it that it can work OK with Office 2003 but not with Office 2007? Or am I doing anything wrong?

If I can get this using a pc with Office 2007, then there would not be any need for creating the sort of menu you suggested in (3) above. As indicated earlier, when the database is opened under Office 2003 platform, no Access 2003 menus come up. The db opens as a standalone .exe file with none of the Access 2003 menus, and therefore no need for creating a menu.

I'll appreciate any further help, please.

Thanks.

Kenny

 

[Pat Hartman]

You can look into building your own ribbon. There are free tools available. At a minimum, you need the ability to print so you can't usually get rid of the ribbon entirely.

 

[Kenny9002]

Hi All,

Could someone out there please help with clear suggestions on how I can solve resolve the issue that I raised in my most recent post - Post #12 above - i.e. disabling the entire Access 2007 menu when I open my database. As indicated earlier, my knowledge of Access is very basic - almost non-existent.

Although I have been doing my best to learn, can I not achieve the same result - opening the database on a pc using Office 2007 as I do when I open the database on a pc with Office 2003? And if I must build my own ribbon, is there any suggestion on where I can find the tools and 'how to' instructions?

I'll appreciate any help.

Thanks.

Kenny