Contact Management Database Template

[Guest]

Hi there,

I've long been struggling to build a contact management portion into our
current contact and facility database and have finally resorted to trying to
use the Access2003 template provided by office. I can open and view this once
downloaded and all works fine. I then copy the tables,forms,macros,modules,
etc into my current database and the problems begin. I believe this may have
something to do with the current database being 2000 format despite being
built on 2003.

Anyway the problem that occurs first is one of the forms in the template is
called 'Reports'. Now i have no idea how they managed this as as soon as i
try to copy with the same name it tells me that name is reserved. Solved that
problem by changing the name to 'Reports_Form'....no problems there. Next
problem is the macro 'View Reports Macro'. This allows the choice of a report
to preview from a list box containing the report options. I updated this
macro to reflect the change of name of the form above and thats fine. The
problem occurs however when it runs as i get the error message "Microsoft
Access can't find the form 'View Reports' referred to in a macro expression
or visual basic code".

Now i'm positive there form is there and exists and have double checked the
name is still the same (why an office template is using such poor naming
conventions is beyond me). Below is a copy of the Macro coding and i'm hoping
someone can pick up whats wrong. Headings in {} for visual purposes:

View Reports Macros
{Macro Name:} cmdOpenReport : On Click
{Action:} Open Report
{Report Name:} =[Forms]![View Reports]![lstReports]
{View} Print Preview
{Window Mode} Normal

{Macro Name:} lstReports : On Dbl Click
{Action:} Open Report
{Report Name:} =[Forms]![View Reports]![lstReports]
{View} Print Preview
{Window Mode} Normal

{Macro Name:} EditReportList : On Click
{Action:} Open Form
{Form Name:} Reports_Form
{View} Datasheet
{Window Mode} Normal

Any ideas on this problem greatly appreciated.

Cheers,
Frusgtrated Access User

 

[Guest]

Hi Frustrated,

I believe this may have something to do with the current database
being 2000 format despite being built on 2003.

Not likely...unless there is VBA code that only works in Access 2003, such
as a newer built-in function that was not available in Access 2000. Do you
get any compile errors when you compile the VBA project?

Now i'm positive there form is there and exists and have double checked the
name is still the same

Is the View Reports form open when you run your macro?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi there,

I've long been struggling to build a contact management portion into our
current contact and facility database and have finally resorted to trying to
use the Access2003 template provided by office. I can open and view this once
downloaded and all works fine. I then copy the tables,forms,macros,modules,
etc into my current database and the problems begin. I believe this may have
something to do with the current database being 2000 format despite being
built on 2003.

Anyway the problem that occurs first is one of the forms in the template is
called 'Reports'. Now i have no idea how they managed this as as soon as i
try to copy with the same name it tells me that name is reserved. Solved that
problem by changing the name to 'Reports_Form'....no problems there. Next
problem is the macro 'View Reports Macro'. This allows the choice of a report
to preview from a list box containing the report options. I updated this
macro to reflect the change of name of the form above and thats fine. The
problem occurs however when it runs as i get the error message "Microsoft
Access can't find the form 'View Reports' referred to in a macro expression
or visual basic code".

Now i'm positive there form is there and exists and have double checked the
name is still the same (why an office template is using such poor naming
conventions is beyond me). Below is a copy of the Macro coding and i'm hoping
someone can pick up whats wrong. Headings in {} for visual purposes:

View Reports Macros
{Macro Name:} cmdOpenReport : On Click
{Action:} Open Report
{Report Name:} =[Forms]![View Reports]![lstReports]
{View} Print Preview
{Window Mode} Normal

{Macro Name:} lstReports : On Dbl Click
{Action:} Open Report
{Report Name:} =[Forms]![View Reports]![lstReports]
{View} Print Preview
{Window Mode} Normal

{Macro Name:} EditReportList : On Click
{Action:} Open Form
{Form Name:} Reports_Form
{View} Datasheet
{Window Mode} Normal

Any ideas on this problem greatly appreciated.

Cheers,
Frustrated Access User

 

[Guest]

Thanks for the quick reply. In reply

1) Ok gald that its not to do with the version.

2) No the form is not open when i run the macro. The form is opened from a
control in the contact form. I get an error now from that control and it says
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'."

The form then loads after an error report and loads just without the list of
possible reports that usually appear in the form.

Cheers,

Hi Frustrated,

I believe this may have something to do with the current database
being 2000 format despite being built on 2003.

Not likely...unless there is VBA code that only works in Access 2003, such
as a newer built-in function that was not available in Access 2000. Do you
get any compile errors when you compile the VBA project?

Now i'm positive there form is there and exists and have double checked the
name is still the same

Is the View Reports form open when you run your macro?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi there,

I've long been struggling to build a contact management portion into our
current contact and facility database and have finally resorted to trying to
use the Access2003 template provided by office. I can open and view this once
downloaded and all works fine. I then copy the tables,forms,macros,modules,
etc into my current database and the problems begin. I believe this may have
something to do with the current database being 2000 format despite being
built on 2003.

Anyway the problem that occurs first is one of the forms in the template is
called 'Reports'. Now i have no idea how they managed this as as soon as i
try to copy with the same name it tells me that name is reserved. Solved that
problem by changing the name to 'Reports_Form'....no problems there. Next
problem is the macro 'View Reports Macro'. This allows the choice of a report
to preview from a list box containing the report options. I updated this
macro to reflect the change of name of the form above and thats fine. The
problem occurs however when it runs as i get the error message "Microsoft
Access can't find the form 'View Reports' referred to in a macro expression
or visual basic code".

Now i'm positive there form is there and exists and have double checked the
name is still the same (why an office template is using such poor naming
conventions is beyond me). Below is a copy of the Macro coding and i'm hoping
someone can pick up whats wrong. Headings in {} for visual purposes:

View Reports Macros
{Macro Name:} cmdOpenReport : On Click
{Action:} Open Report
{Report Name:} =[Forms]![View Reports]![lstReports]
{View} Print Preview
{Window Mode} Normal

{Macro Name:} lstReports : On Dbl Click
{Action:} Open Report
{Report Name:} =[Forms]![View Reports]![lstReports]
{View} Print Preview
{Window Mode} Normal

{Macro Name:} EditReportList : On Click
{Action:} Open Form
{Form Name:} Reports_Form
{View} Datasheet
{Window Mode} Normal

Any ideas on this problem greatly appreciated.

Cheers,
Frustrated Access User

 

[Guest]

2) No the form is not open when i run the macro.

Well, it needs to be. So, if you are just trying to run the macro by itself,
it would not be expected to work unless you first open the form.

I get an error now from that control and it says "Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

It sounds like the Row Source for your list box is not a proper SQL
(Structured Query Language) statement. Open the form in design view. Click on
View > Properties to view the Properties dialog if it is not already
displayed. Select the list box. You should see the name of the list box in
the title bar of the Properties dialog. Select the Data tab of the Properties
dialog. Find the Row Source. Is it a saved query or is it a SELECT statement?
If it is a statement that begins with SELECT, copy it and paste it into a
reply. We can likely make suggestions based on what we see here. If it is a
saved query, then try to open the query. If the query complains, then go into
design view and click on View > SQL View. Copy the SQL statement.

By the way, did you try compiling your VBA code as I recommended in my
initial reply? If you get any compile errors, then copy the entire procedure
and paste it into a reply, indicating which line is highlighted.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks Tom,

Checked the row source and it had a text string "Add or Delete Record' that
made no sense at all. Don't know how that happened just from copying the
forms accross? Anyways, fixed it up and all works fine now.

Just an asside. What exactly did you mean be compiling VBA code? I'm a total
amatuer with VBA and i'm sure this is the cause of alot of my frustration.
Must do a course on it some time!

Thanks again for all your help!!

 

[Guest]

That sounds pretty weird. I don't know how it might have happened
either...I'm glad to read that you got the problem fixed.

What exactly did you mean be compiling VBA code?

If you have any VBA (Visual Basic for Applications) code in your database,
then you should make sure that it compiles properly. The act of compiling
code creates a second (hidden) copy of code that is typically referred to as
"P" code. Here is an article that explains more:

http://www.trigeminal.com/usenet/usenet004.asp?1033

VBA code can be present as stand-alone modules, as code behind forms, or as
code behind reports. In form design view, if you have the form itself
selected (click on the little black square in the upper left-hand corner to
select the form--you should see "Form" in the title bar of the Properties
dialog), you can inspect the Has Module property. This property is found on
the Other tab. If it is set to Yes, then the form has a code module
associated with it (although it may be an empty module, in which case it
should be deleted). The same discussion applies to reports.

If you have an existing stand-alone module, then open it in design view. For
example, the sample Northwind.mdb database includes two stand-alone modules
named "Startup" and "Utility Functions". Otherwise, you can create a
temporary stand-alone module by clicking on the New button. This should cause
the VBE (Visual Basic Editor) to open up. If you now click on View > Project
Explorer (Ctrl R), you should see a listing of all forms and reports that
include modules. If you double-click on a listed module, it will be displayed.

Compiling your code is accomplished by opening the VBE to any module (form,
report or stand-alone) and clicking on Debug > Compile ProjectName, where
ProjectName is the name of your VBA project (likely the same as the name of
your database, but not always so). If nothing appears to happen, then this is
a very good sign. It means that your code compiled without any errors. You
can verify this by attempting to compile a second time. If the option now
appears greyed out (unavailable), then the code is compiled.

If you get any compile time errors, then you should try to correct them
right away.

Oh, one final point regarding code modules. I recommend getting in the habit
of checking all modules for two very important words: Option Explicit. These
normally are the second line of code in all modules, ie:

Option Compare Database
Option Explicit

If you create a new module, and you only see the first line of code shown
above, then you should make a configuration change under Tools > Options in
the VBE editor. Here is an article that discusses this in more detail:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

I recommend also deselecting the default options for compiling on demand.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

I've long been struggling to build a contact management portion
into our current contact and facility database and have finally
resorted to trying to use the Access2003 template provided by
office.

Contact management is one of those things that people think is
simple and uncomplicated when, in fact, it's incredibly complex.
Nearly every app I've ever built has had one form or another of a
contact management module, and I've tried many different solutions
to the problems and still don't have any firm convictions on how to
build a one-size-fits-all approach to the problem space. That is,
each individual app will have its own requirements.

Also, the Access templates are notoriously poorly-designed as
database applications, so it doesn't surprise me that the one you
tried wouldn't work. I'd suggest using it instead as a source of
ideas and implementing them from the ground up in your own app,
rather than importing them directly.