Utter Confusion with Errors

[Guest]

Ok, after a great deal of time building a database with a form to input new
data, I have finally completed the task and it works wonderfully – on my
computer and one other. The idea with the database was that everyone would
be able to go to the database on their own computer to input the new data and
open the database located on a network drive. While they are able to open
the database, input and view the data, the macros I wrote don’t seem to work.
For example, there is a field for entering the date. Since the database is
updated daily, I have the expression “=Date()†entered into the Default Value
under the Data tab in the properties of the Date field. It’s under default
because we need the ability to backlog data that might have been missed. The
expression yields today’s date when I use it on my computer and one other
when I move the form to enter a new record. However, on other computers the
date field yields an error (#Name?). At first I thought the problem was due
to the version of Access. I had other computers updated to the version that
I am using and it still yields the same error. I even went as far as
creating a new Date field using the computer that wouldn’t work and I still
got the same error. In addition, the macros I created to open reports for
reference or send an email while working with the form resulted in a Date
formatting error. So, I made sure that the Date column in the table the data
was being stored had the same format and I still got the formatting error.

Any help would be greatly appreciated.

-Raymond

 

[John W. Vinson]

Ok, after a great deal of time building a database with a form to input new
data, I have finally completed the task and it works wonderfully – on my
computer and one other. The idea with the database was that everyone would
be able to go to the database on their own computer to input the new data and
open the database located on a network drive.

Before you do ANYTHING else, split the database. Access is multiuser, but in
practice having more than one user sharing a unitary database is asking for
trouble - bad performance, bloating, risk of corruption. A split application
has the Tables in one shared "backend" database, and a Frontend with the
forms, queries, reports, etc. on each user's own computer, linked to the
backend. See

http://www.granite.ab.ca/access/splitapp

for the rationale, techniques and suggestions.

While they are able to open
the database, input and view the data, the macros I wrote don’t seem to work.
For example, there is a field for entering the date. Since the database is
updated daily, I have the expression “=Date()” entered into the Default Value
under the Data tab in the properties of the Date field.

First off, change the name of the Date field. It's a reserved word; Access
will (and may be, causing this problem!) confuse the fieldname with the
builtin Date() function.

Another possibility is that this may be the very common References bug. Open
any module in design view on a computer which has the problem, or open the VBA
editor by typing Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked MISSING. Uncheck it,
recheck it, close and open Access.

If none are MISSING, check any reference; close and open Access; then uncheck
it again. This will force Access to relink the libraries.

John W. Vinson [MVP]