Help with 1st ever data base design please!!

[Robbie]

I have committed a new user error! Any help resolving is greatly appreciated!

In November I started building a new database. I created new forms from the first by "Saving as" a new form name and changing properties as I went. I had 8 forms and 2 forms working perfectly finally. Then somehow I deleted the main table on 12/4. (I know - I should have backed it up!) I recreated the main table and created 3 more forms and 1 subform that also functioned properly. Then the 4th form had a problem with my OnOpen event procedure - for opening on a new record. I have used this procedure in every single form up to then successfully. I finally deleted that form and was going to start over on it. Then I started opening up other forms and found that I was getting error messages all over the place.

The first two forms I created now give me a Microsoft Visual Basic error message "Error accessing file. Network connection may have been lost.

The next group of failing forms (created chronologically after those first two) result in a Microsoft Access error message "Expression OnOpen" . . . . . "Error accessing file. Network connection may have been lost. Expression may not result in the name of a macro, the name of a user-defined function or [Event Procedure]. There may have been an error evaluating the function, event, or macro.

And finally, I have 1 form and 2 subforms, created before the table deletion that work perfectly, as well as 3 other forms and 1 subform created after the deletion that are working.

I have updated links, restored relationships, analyzed when and how things happened looking for a logical cause - alas to no avail!

I need some help quickly! Thanks!

Robbie

 

[Henry]

OK! Been there done that!

Quick help is not normally available for your specific
problem because your database design and approach to
implementing a DB is not correct. Beyond the very basic
DB you can't use Access biult-in wizards to achieve a DB
aplication that is worthy of your effort. There are many
design approaches available, but most all will require
you to learn Visual Basic for Applications (VBA) to
achieve a good DB design, user interface, DB management,
and implement an application that will satisfy your
customer. The VBA learning curve is steep, but there are
many resources available to make it less painful.
I suggest you make a modest investment in a set of books
all about Access 2000. They are easy to read because the
authors present everything consisely with many examples.
The book set, two volumes, covers most all aspects of
Access 2000 with ideas and examples to help the novice
gain expertise quickly. The books include CD's with all
the examples plus many other helpfull techniques.
An investment that has paid off for me 100 fold.

The book is titled "Access 2000 Developer's Handbook"
author Ken Getz, published by SYBEX. It is available
from Amazon.com at a discount. The same author group,
there are three of them, has published a new set of books
about Access 2002. I strongly suggest you start with
Access 2000 Developer's Handbook even if you are working
with Access 2002.

Hope this helps.

Cheers,
Henry

-----Original Message-----
I have committed a new user error! Any help resolving is greatly appreciated!

In November I started building a new database. I

created new forms from the first by "Saving as" a new
form name and changing properties as I went. I had 8
forms and 2 forms working perfectly finally. Then
somehow I deleted the main table on 12/4. (I know - I
should have backed it up!) I recreated the main table
and created 3 more forms and 1 subform that also
functioned properly. Then the 4th form had a problem
with my OnOpen event procedure - for opening on a new
record. I have used this procedure in every single form
up to then successfully. I finally deleted that form and
was going to start over on it. Then I started opening up
other forms and found that I was getting error messages
all over the place.

The first two forms I created now give me a Microsoft

Visual Basic error message "Error accessing file.
Network connection may have been lost."

The next group of failing forms (created chronologically

after those first two) result in a Microsoft Access error
message "Expression OnOpen" . . . . . "Error accessing
file. Network connection may have been lost. Expression
may not result in the name of a macro, the name of a user-
defined function or [Event Procedure]. There may have
been an error evaluating the function, event, or macro."

And finally, I have 1 form and 2 subforms, created

before the table deletion that work perfectly, as well as
3 other forms and 1 subform created after the deletion
that are working.

I have updated links, restored relationships, analyzed

when and how things happened looking for a logical cause -
alas to no avail!

 

[Robbie]

Thanks Henry. I will get the books because I know I can't do anything too complicated with what I know now. I do believe though that my database is very basic and was functioning very well.

Thanks for the tip!

Robbie

 

[Scott McDaniel]

Sounds as if your database is corrupted. Try importing all objects to a new
database. Also, backup frequently <grin>.

I have committed a new user error! Any help resolving is greatly appreciated!

In November I started building a new database. I created new forms from

the first by "Saving as" a new form name and changing properties as I went.
I had 8 forms and 2 forms working perfectly finally. Then somehow I deleted
the main table on 12/4. (I know - I should have backed it up!) I recreated
the main table and created 3 more forms and 1 subform that also functioned
properly. Then the 4th form had a problem with my OnOpen event procedure -
for opening on a new record. I have used this procedure in every single
form up to then successfully. I finally deleted that form and was going to
start over on it. Then I started opening up other forms and found that I
was getting error messages all over the place.

The first two forms I created now give me a Microsoft Visual Basic error

message "Error accessing file. Network connection may have been lost."

The next group of failing forms (created chronologically after those first

two) result in a Microsoft Access error message "Expression OnOpen" . . . .
.. "Error accessing file. Network connection may have been lost. Expression
may not result in the name of a macro, the name of a user-defined function
or [Event Procedure]. There may have been an error evaluating the function,
event, or macro."

And finally, I have 1 form and 2 subforms, created before the table

deletion that work perfectly, as well as 3 other forms and 1 subform created
after the deletion that are working.

I have updated links, restored relationships, analyzed when and how things

happened looking for a logical cause - alas to no avail!

 

[Robbie]

Thanks Scott.

It turns out I had some memory issues on the computer I'm working on.

I appreciate your response!

 

[Robbie]

Henry, I got the books you suggested and they are very helpful. The db I've built is very basic and Access is probably overkill for this application. I wanted to use Access because I have the time and desire to learn how to design in access. At most, by the end of the life of this db (the application will only be valid until 1/05) there will be 200 records. So my very rudimentary db will probably be fine for this. As I do have the time to do it, I would like to improve the db to increase my skills.

I inherited Excel spreadsheet and 12 or 13 Word forms with fields that had lost their links in transfer from another project. To get away from storing individual word documents I wanted to store info in Access db. My main table unfortunately looks a lot like the Excel spreadsheet and although I understand in general how to normalize, I can't seem to get started with mine.

The deliverables for this application are hard copy of IOC (interoffice correspondence) for files, as well as ability to retrieve this information electronically, and a summation log with IOC number (unique identifier), date, to, from, subject. There are 12 or so "types" of correspondence, such as "Customer Approved Supplier", "Quarterly trend report", "delegation of authority . . ." There are fields that all types would include, but also fields that are needed for only for a particular type. So, I'm thinking I need a table for IOC# and type; maybe type and type ID#, maybe individual tables for each type with required fields for that type, lookup tables for names that could go in "to", "from", "cc", "subject" fields. Any suggestions would be greatly appreciated.

Thanks,
Robbie