Form/subform problems!!

K

Kim Doe

Help! I am somewhat new to Access and completed what I thought was
beautiful database design, only to encounter major problems whe
entering data on my form.

I have a Main table that is related (one to many) to several othe
small tables via a control called "PSID." My relationships screen look
like a star, with Main table in the center with several one to man
relationships with other tables via PSID (primary key in Main).

The heart of the problem:
I wanted a single form for data entry, for simplification reasons. T
do this, I created a form for Main that contained command buttons t
open one of the smaller forms. This way a user can fill out a record i
Main and click command buttons to fill out smaller forms as neede
without having to directly open a new form. This worked fine whe
entering my first record in Main. I could fill out forms that wer
opened thru command buttons. But now, I am getting the error "Canno
add or change a record because a related record is required in th
table Main."

The thing is, before I click any command buttons, I have already type
the PSID in Main (therefore creating the related record in Main, no?
and saved it. If I open the table Main, the PSID shows up. So wha
other related record is required??

The reason that I have several small tables is that not every PSID i
Main will have data in the variables within smaller tables.

PLEASE help. I've spent all day trying to figure this out with n
success.

Thanks
 
A

Allen Browne

Hi Kim

This problem can be triggered if:
- the form that gives you this error is based on a query, and
- the query includes the Main table, though the record is actually being
created in another table, and
- any fields from the Main table have a Default Value set.

What happens is that when Access begins creating the record in the related
table, it assigns the default values. Because the lookup table contains
default values, it attempts to assign the default value to those fields of
the query as well. At this point, JET wrongly thinks it is trying to create
a record in the Main table as well, and throws the error. Another symptom of
this issue is that the error can trigger early, i.e. before you expect to
see any attempt to save the record.

The workarounds for this problem are:
Option 1: Remove the Default Value property from all fields in the Main
table, and remove the Default Value from all the text boxes on the form that
are bound to fields from the Main table.
Option 2: Remove the Main table from the query that this form is based on.

There are other possible causes for this problem, but that's the most
confusing one--the behavior that (IMHO) is wrong in Access.
 
C

ChrisJ

From your words "typed in" I am assuming that your main table primary key is
not an autonumber.
Are you certain the record has been saved when you click the button to open
the slave form?
Ona bound form the record is not saved until you explicitly save it or the
form closes, or you move to another record.

If you haven't already, try explicitly saving the current record in each
button before you open the slave form
 
K

Kim Doe

Thanks for replying! The "slave" forms are not based on queries. Eac
form (main or slave) is based on its own table. Also, no field has
default value set (input masks don't count, do they?). I just wen
through each one and double checked. And neither of the slave form
have default fields.

I have a sense that the Main form is not saving properly when I clic
on the save (diskette) icon. I think this because the records that wer
giving me a pain yesterday are working fine today. However, when I star
working in a new record (new PSID), then I get the errors again.

Any other thoughts
 
K

Kim Doe

I think you are right, but I still can't figure how to solve th
problem. My Main form primary key is not an auto number. I have trie
setting up a primary key with an auto number and run into the sam
problem.

I believe that I am explicitly saving the table (I press the save ico
in the toolbar), but it is still not saving. I notice that when I ope
a new record and then go BACK to a previous record that I could no
complete because I was getting the error mentioned below, all of
sudden I can complete it. Obviously, though, this is no way to do dat
entry...

What are your thoughts?? Thanks
 
T

tina

rather than clicking the Save icon on the toolbar, try this: in the code
that the command button uses to open the "slave" form, try adding the
following code *before* the OpenForm command, as

If Me.Dirty Then Me.Dirty = False

btw, when you talk about opening a "slave" form, i am assuming that you are
actually opening a separate form window, rather than just moving to a
subform within the main form. what code are you using to set the foreign key
value in the slave form? you might try checking that code to see if the
value of PSID is being passed properly.

hth
 
R

Robin Chapple

rather than clicking the Save icon on the toolbar, try this: in the code
that the command button uses to open the "slave" form, try adding the
following code *before* the OpenForm command, as

If Me.Dirty Then Me.Dirty = False
Click to expand...

I would like to understand that because it sounds like an answer to
one of my problems.

I assume that this code is on the mainform and if the data on the main
form has been changed, ( "is dirty"), changing "dirty" to "False" is
the same as saving the record. Is that right?

Robin Chapple
 
T

tina

If Me.Dirty Then Me.Dirty = False
I assume that this code is on the mainform and if the data on the main
form has been changed, ( "is dirty"), changing "dirty" to "False" is
the same as saving the record. Is that right?
Click to expand...

correct, Robin. to gain a better understanding of the Dirty property, and
the Dirty event, recommend you look up the Dirty Property topic and the
Dirty Event topic in Access VBA Help, and read them carefully.

hth
 
K

Kim Doe

For me, I realized that I wasn't properly saving my record before tryin
to enter data in the slave form. Thank you for your help
 
T

tina

working together like this, folks here can usually come up with something
that works. glad we were able to help solve your problem! :)
 
Top