I am an intermediate user of Access. I do not have any programming knowledge (VB, etc.).
I have a data entry form that consists of the main form (master table) plus 4 tab pages with subforms (child tables).
My main form is built off of a query that is based on 1 table (master table). My subforms are built off of queries also based off of just 1 table per page (child tables).
My problems began when I added a control button that is linked to a macro (which opens a query). I also adjusted my subforms so that they were based on a query as opposed to the table (so that I could add sort functions). After I added this control button/adjusted my queries I started receiving this error message when trying to enter data into my subforms: “The Link Master Fields property setting has produced this error: Invalid Data Format.” When I finally got rid of that message, another one popped up: “Cannot add record(s); join key of table not in recordset.”
That is when I started searching on the internet. Previous to my searching on the internet, I had my subforms based on a queries that had fields from the Master and Child table. I subsequently went back and redid my queries so that the main form is based only off of the master table, and the subforms are built only off of 1 child table (1 per tab page). I still get the Link Master fields property error message.
I then tried to build my subforms off of the actual table as opposed to the query (which is based directly on the table). Same error message.
I then decided I would create the entire form again from scratch. Still no dice. When trying to build the main form I get the error message: “Undefined function “isnumeric’ in expression.” After clicking ok 2x to get rid of that message, another one pops up, “Invalid use of null.” I have no idea what it is talking about. I have no expressions anywhere (in the queries/tables). I have checked that my data types for my primary key/foreign key are the same. I have also doublechecked that I have my master/child tables relationships set correctly in the relationships table.
I am stumped and have no idea what is wrong. I also don’t understand why everything worked fine up until the point I created the control button and adjusted my subforms. It was only then that my problems started with the error messages.
Any help anyone can offer would be much appreciated.
Sounds like something you would need to see and play with to debug (at least I would). How big is this puppy? Can you post a copy of it in a zip file?
This file is about 11 mb zipped.
The interesting thing is that I finally gave up fixing this and decided I was going to drag/drop tables into a new db and see if I could create the forms there without problem. I was able to do just that. So, I moved all tables/queries/macros/forms etc over and re-created everything. Not an error message anywhere...which makes me wonder if my original file was corrupted? But if it were...wouldn't I have run into problems in my new file?
I opened my original file, which since I moved it to another folder...had become a replica instead of the Design Master (I work in a replicated db). Interestingly enough, I could now enter data into one of my subforms without an error message (earlier I could not enter data into any of my subforms). I made this a Design Master again...and tried to add in a subform...immediate error messages and once again I cannot enter data into any of my subforms. Now, an error message pops up when this file is even opened. The more I tinker with it...the more error messages develop.
The form I enter data into is called "frmPeopleUpdates." I am not sure what other info to give you, especially since I am thinking this file is corrupted? As I do not know any programming, I have no idea how to "debug" anything.
I have tried to attach the file 2x, but it doesn't want to attach (I keep getting an error message). I would be happy to try to attach it again later today if you still feel it worthwhile to look at.
If corruption is the issue, compacting the DB may resolve the problem.
Actually, I compact and repair this daily.
I am torn between assuming this is corrupted data and letting it go, and continuing to search so that I can be sure (so I don't have this problem again).
Since moving all of my data into another database, I have not had any problems. Nor did I have any error message when creating my forms/subforms. I took care that my main form was based on my parent table, and my subforms were based on my child tables (I didn't mix fields from the parent/child tables in the queries I based my form/subforms off of). My database is not a complex one (it is not automated, doesn't have advanced programming of any sort...as I don't know how to do all of that yet). Everytime I open up the old database...I get different error messages. Last time I had error messages just upon opening it up. Since the error messages change and pop up at different times, that sure sounds like something is corrupted in it. Guess I'll have to let it go. :-)