Need Help- Access 03 SP3 forms are blank screens

Topic

New Reply

Dear Loungers,

Here is a problem that has been a stumper for me for the past 1 1/2 weeks.

The problem is in an MS Access 2003 database application. The problem only occurs on my new Windows 7 64-bit machines. It doesn’t occur on my Windows Vista or Xp 32-bit machines. I have tried uninstalling and reinstalling MS Office 2003 Prof. SP3, and even installing MS Office 2007. Doing this hasn’t solved the problem.

The Access database is set up with the queries loaded on the front end (.mde on the user’s PC), and they are linked to the database on a shared drive on a server (an .mdb file containing just the tables of data). The backend database is on Windows Server 2003. The database is password protected using the “Workgroup Administrator” password feature in Access.

The applications has Access forms with tabbed subforms. The problem is that when users are browsing through different tabbed forms/subwindows, some of the sub-forms are blank. In addition some of the subform menu bars dissapear. If I refresh the form, I get an error: “Function is not available in expressions in query expression ‘DateDiff …”

I was able to identify part of the problem based on the “Datediff” function error that is shown in attached word doc with screenshots. A library file for the MS Access was not linked correctly. I had to browse into the database and to the References, and point the references to the correct place. Please see the attachment entitled “Fixing a missing library file” for details.

However, after I did this fix, the problem came back again the next day.

Here are some solution requirements:

1) As an FYI – I inherited the MS Access database application with no documentation from a former IT admin. I did not code the application myself.

2) I don’t have VBA coding experience with Access or SQL databases. So please don’t just say “Run this VBA code” in your solution, because I won’t know where to add it to my Access application, or how to compile it. Please supply a website tutorila on VBA for Access databases if you can. I did study C++ and I have done Unix and some Windows shell scripting. (see my profile for more info). I do not use any SQL databases in the access application.

3) Please write out the instuctions with lots of spacing, and clear instuctions on where to find the options to adjust.

4) If you don’t write up a solution yourself, but link to a solution, please give a short intro to the solution.

Please see the attached MS Word files for clarity. One file displays the error. The second file shows the fix I implemented. I added a lot of screen shots for clarity.

This is a huge problem, because my users on the new computers cannot access all the data in the datebase from certain forms.
Thank you for your help.

-Peter

Reply | Quote

Replies

More often than not, an error that says “Function is not available…” such as you are getting indicates that a reference is missing. Unfortunately this appears to be a fairly complex application, and to do any more than make some general suggestions will require being able to look at the processes behind the forms involved. Can you post a stripped down copy that has been sanitized to remove any personal information? See Post 401925 for instructions on how to do that.

Reply | Quote

Hi Wendall,

Thanks for your reply. I just looked through the VBA Editor in my Access app, and found about 100 forms. My backend .mdb containing tables of data is 99MB. My front-end .mde file is about 19MB (after compact and repair). I also have the orginal .mdb file that is linked to the backend tables.

I have some questions on how to shrink the database, based on your reference to the other post. The steps are quoted below, my questions follow.

To post a stripped down copy of the database:
• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
• Attach the zip file to a reply.

Is this how I should proceed to start shrinking the db?

-Makes copies of the orginal front end .mdb, and the backend .mdb containing the tables.
-Link the front end copy to the new back end copy.
-Start deleting forms and queries and table data not relevant to the problem.
I don’t know how choose what the delete in the copy file without breaking it.

Or should I combine the front and end and back end into one database file for testing, to shrink it down and make it mroe easilier portable to share in this forum?

Below are steps, including questions on how I would combine the front end, and back end.

-Combine the front-end and back-end componets of the access application, to make a single testing .mdb file. Open the orginal front end .mdb, and delete the links to the backend .mdb.
However, I can’t find were to access and view the original design screen for new database. The screen with the Objects (Tables, Queries, Forms, Reports, Pages, Macros, Modules) listed on the left, and a white box on the right with “Create Query in Design View”.Once at the screen I will go to the table view, and delete all the links to the backend .mdb.
Then I will right click Tables and select import, and then import the data in directly to the front-end .mdb.

-At this point I should have a single .mdb database will all the queries, forms, and tables together.
Now do you recommend I start shrinking this copy of the database? Should I delete data strait out of the tables to make it smaller?
How do you recommend I shrink down the forms? I don’t really know how to pick and choose with ones to delete.
-If I needed to, how would I save the database in Access 97 format?

Like I said, I didn’t develop this access database, nor the front end interface/switchboard.

What do you think is the best way for me to proceed? Do you think the best way if for me to read a whole book on programming access 2003 (I own Allison Balter’s Mastering Microsoft Office Access 2003) before attempting the above steps? Or do you think you will be able to guide me throught the process of shrinking the database

Thank you,
Peter

More often than not, an error that says “Function is not available…” such as you are getting indicates that a reference is missing. Unfortunately this appears to be a fairly complex application, and to do any more than make some general suggestions will require being able to look at the processes behind the forms involved. Can you post a stripped down copy that has been sanitized to remove any personal information? See Post 401925 for instructions on how to do that.

Reply | Quote

Hi Wendall,

Thanks for your reply. I just looked through the VBA Editor in my Access app, and found about 100 forms. My backend .mdb containing tables of data is 99MB. My front-end .mde file is about 19MB (after compact and repair). I also have the orginal .mdb file that is linked to the backend tables.

I have some questions on how to shrink the database, based on your reference to the other post. The steps are quoted below, my questions follow.
….
Like I said, I didn’t develop this access database, nor the front end interface/switchboard.

What do you think is the best way for me to proceed? Do you think the best way if for me to read a whole book on programming access 2003 (I own Allison Balter’s Mastering Microsoft Office Access 2003) before attempting the above steps? Or do you think you will be able to guide me throught the process of shrinking the database

Thank you,
Peter

Did you try the step of checking references in the Visual Basic for Applications? If you have one that says “Missing” then that is probably the source of your problems. If not then you are going to need to either enlist the assistance of an experienced Access developer, or try to shrink it to the point where it can be uploaded. I doubt reading Allison’s book – one of the best in my view – will help you get this done in a timely manner. Access is something that takes most mortals several months to get up to speed – at least where you are dealing with complex applications.

If you do try to shrink it, I would first make a copy of the front-end DB, and make sure you are using the copy. Then look to see if you have any local tables and determine if they are necessary to demo the application. If not, and they are simply temporary tables, they can be deleted. The next step would be to delete any forms and reports not necessary to demo the problem. At that point do a compact and repair on the front-end. Next, take the back-end with the tables, make a copy of it and make sure you are using the copy, and remove all but a few sample records. Unfortunately that may be difficult, as there are probably relationships which prevent you from deleting records in one table until all the records in another table that link to that record have been deleted – for example a person record cannot be deleted until all the address, phone number, and contribution records have been deleted. Hopefully that should eventulaly get you under the 2MB size for the backend. I should add that neither task is a trivial one – it will take several hours on each in all probability.

Reply | Quote

I am having similar issues.
My forms capture a date from a calendar and then passes that date to another form that shows only the information for that date.
It works in 97 and 2000.
When I covert to 2003, I have a blank screen.
I am not sure it it access, I think the issue is something in my version of windows.
I have a query that separates the dates…I use Left, Mid and Right to extract the dates.
That query works in windows 2000 but does not on my XP or VIsta machines.

How can a function mmonth: (IIf(Left([dateoffered],1)=0,Mid([dateoffered],2,1),Left([dateoffered],2))) work in one and not in the other???

Reply | Quote

How did you establish that the function is causing the blank screen? Is that function being used in a query, or in a VBA procedure? In either case, open a VBA procedure and look for any missing references by choosing Tools / References in the VBA editor. By chance are you using the Microsoft Calendar ActiveX control, or another vendors calendar control. If that is missing on the XP system, that might explain it.

Reply | Quote

How did you establish that the function is causing the blank screen? Is that function being used in a query, or in a VBA procedure? In either case, open a VBA procedure and look for any missing references by choosing Tools / References in the VBA editor. By chance are you using the Microsoft Calendar ActiveX control, or another vendors calendar control. If that is missing on the XP system, that might explain it.

Wendell, there is a query that extracts based off the date that was selected.
The calendar is VBA code…not the active x. Once the user clicks on a date, a sub form appears with the classes offered for that day. The use can click on the class and then register for it (if it worked). Once the users clicks, it stores the year in a field, month and day in a field.
They are used as linking fields for the subform.

The query uses the [forms]![calendarmain]![calendersub]![year] to extract only that year and so forth for month and day.

Again, it works in 2003 on a machine running XP at work. It does not work on my computer at home…2003 running XP as well or at the school that I designed it for…they are using 2003 and XP.

I will need to check the references…later when I get home.

Thanks for the information, it will at least get me pointed in a direction to try and correct this.

Reply | Quote

….
I will need to check the references…later when I get home.

Thanks for the information, it will at least get me pointed in a direction to try and correct this.

Did you get a chance to check the references on your home system? That seems to be the most likely problem for that expression not working in a query….

Reply | Quote

Hi Wendall,
Thanks for your help so far.
To answer your questions of missing references, please review my first post, as it explains how I re-linked a missing reference:

I was able to identify part of the problem based on the “Datediff” function error that is shown in attached word doc with screenshots. A library file for the MS Access was not linked correctly. I had to browse into the database and to the References, and point the references to the correct place. Please see the attachment entitled “Fixing a missing library file” for details.
However, after I did this fix, the problem came back again the next day.
…
Please see the attached MS Word files for clarity. One file displays the error. The second file shows the fix I implemented. I added a lot of screen shots for clarity.

Can you help me with the below question?

However, I can’t find were to access and view the original design screen for new database. The screen with the Objects (Tables, Queries, Forms, Reports, Pages, Macros, Modules) listed on the left, and a white box on the right with “Create Query in Design View”.Once at the screen I will go to the table view, and delete all the links to the backend .mdb.
Then I will right click Tables and select import, and then import the data in directly to the front-end .mdb.

Thanks for your advice, I’m glad I have a good reference book.
-Peter

Did you try the step of checking references in the Visual Basic for Applications? If you have one that says “Missing” then that is probably the source of your problems. If not then you are going to need to either enlist the assistance of an experienced Access developer, or try to shrink it to the point where it can be uploaded. I doubt reading Allison’s book – one of the best in my view – will help you get this done in a timely manner. Access is something that takes most mortals several months to get up to speed – at least where you are dealing with complex applications.

If you do try to shrink it, I would first make a copy of the front-end DB, and make sure you are using the copy. Then look to see if you have any local tables and determine if they are necessary to demo the application. If not, and they are simply temporary tables, they can be deleted. The next step would be to delete any forms and reports not necessary to demo the problem. At that point do a compact and repair on the front-end. Next, take the back-end with the tables, make a copy of it and make sure you are using the copy, and remove all but a few sample records. Unfortunately that may be difficult, as there are probably relationships which prevent you from deleting records in one table until all the records in another table that link to that record have been deleted – for example a person record cannot be deleted until all the address, phone number, and contribution records have been deleted. Hopefully that should eventulaly get you under the 2MB size for the backend. I should add that neither task is a trivial one – it will take several hours on each in all probability.

Reply | Quote

Hi Wendall,
Thanks for your help so far.
To answer your questions of missing references, please review my first post, as it explains how I re-linked a missing reference:

Then I should assume that there are no missing references when you look at Tools/References in the VBA editor? If so, how did it show up again the next day? Once an issue like that shows up and you fix it, it should stay fixed. Did you recompile the VBA after that? Normally it isn’t necessary, but I have seen cases where it does not stick unless you do a recompile and then save the VBA project before closing the VBA editor.

Can you help me with the below question?

Peter Z, on 2010-04-20 11:30, said:

However, I can’t find were to access and view the original design screen for new database. The screen with the Objects (Tables, Queries, Forms, Reports, Pages, Macros, Modules) listed on the left, and a white box on the right with “Create Query in Design View”.Once at the screen I will go to the table view, and delete all the links to the backend .mdb.
Then I will right click Tables and select import, and then import the data in directly to the front-end .mdb.

I think you do need to look at Allison’s book to understand how the database container window works – that’s what you first see when you create a new database in Access 2003. Skim through the first few chapters where she deals with how to get started creating a database. You obviously have selected the Queries category on the right side, so it gives you a wizard to create a new query. But you don’t want to create a new one, you want to make a copy{/b] (using Windows explorer) of the front-end and the back-end mdb and relink the linked tables to the copy of the back-end as you suggested. Then you want to start deleting things – the delete key will delete the object that is currently selected. You may have to do that process several times before you are comfortable, but using a copy of the file (both front-end and back-end) is much less work than trying to import only the right objects.

I should also add that there are have been a few bizzare problems reported with forms that link to graphic images using code when running Access on 64-bit Win7 systems. Does this application display graphic images on the forms or reports?

Reply | Quote

Hi Wendall,

It looks like you were right, the re-linking of the one missing link in References did fix it. The steps I posted in the first attachments do show how to fix the link error.

What had happened was that my user was confused and told me “it wasn’t working” after I gave them the new re-linked (and recompiled) .mde. But they were reporting a different problem. A toolbar was dissapearing for them. And it order to make it come back I just had to tell them to change the view from “Form view” to “Datasheet View” and back. They said it has been fine since then.

Thanks for your pointers along the way. I am now going to try to figure out how to relink the copy of my front end .mdb file with the copy of my backend database file. Having a test copy of the db will come in handy so I can start testing modifications to the forms and developing new forms and such.

Peter

Then I should assume that there are no missing references when you look at Tools/References in the VBA editor? If so, how did it show up again the next day? Once an issue like that shows up and you fix it, it should stay fixed. Did you recompile the VBA after that? Normally it isn’t necessary, but I have seen cases where it does not stick unless you do a recompile and then save the VBA project before closing the VBA editor.

I think you do need to look at Allison’s book to understand how the database container window works – that’s what you first see when you create a new database in Access 2003. Skim through the first few chapters where she deals with how to get started creating a database. You obviously have selected the Queries category on the right side, so it gives you a wizard to create a new query. But you don’t want to create a new one, you want to make a copy{/b] (using Windows explorer) of the front-end and the back-end mdb and relink the linked tables to the copy of the back-end as you suggested. Then you want to start deleting things – the delete key will delete the object that is currently selected. You may have to do that process several times before you are comfortable, but using a copy of the file (both front-end and back-end) is much less work than trying to import only the right objects.

I should also add that there are have been a few bizzare problems reported with forms that link to graphic images using code when running Access on 64-bit Win7 systems. Does this application display graphic images on the forms or reports?

Reply | Quote