Linked Form Not Allowing New Records

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have a one to many relationship between two tables: tblCars and
tblParts (One car can have many parts). I created a form using the
wizard and chose "linked forms" instead of "form with subform" when
prompted. The problem Im having is that when entering new records in
the linked form it does not capure the "linked" field from the main
form which causes an error message which reads "Index or primary key
cannot contain a null value". I know access is supposed to "autofill"
that field but its not working. Whats more frustrating is that it
works fine for existing records. I only have a problem during data
entry. Any ideas?
 
Rob

Take another run through the wizard and use main form/sub form. The subform
construction, using the Parent/Child linked fields, will automatically use
the Parent form's primary key/index as a foreign key in the child (sub)
form. This assumes your Parts table has a field to hold the Car ID.

By the way, since most cars have wheels, I suspect you need THREE tables,
tblCars, tblParts, and trelCarParts (this is the one where you connect the
parts with the cars).

(or perhaps I just don't understand the data/domain you're working in, and
one part can only belong to one car).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Rob said:
I have a one to many relationship between two tables: tblCars and
tblParts (One car can have many parts). I created a form using the
wizard and chose "linked forms" instead of "form with subform" when
prompted. The problem Im having is that when entering new records in
the linked form it does not capure the "linked" field from the main
form which causes an error message which reads "Index or primary key
cannot contain a null value". I know access is supposed to "autofill"
that field but its not working. Whats more frustrating is that it
works fine for existing records. I only have a problem during data
entry. Any ideas?
Click to expand...

No, Access is only supposed to autofill that field if you choose to build a
form with a subform. Linked forms do NOT provide that feature. All it does
is provide a filter for EXISTING records exactly as you are seeing.

You can solve the problem by setting the DefaultValue property of the second
form to a reference of the first form...

=Forms!FirstFormName!LinkingFieldName

That will mean that you can only use the second form when the first is open
and if you open the second form, navigate to a new record, and then navigate
to a different record on the first form before you fill out the new record
on the second form the new record will still have the linking field from the
record on the first form when it was originally opened. As you can see the
linked form is not as robust as a real subform in this regard. Code can be
added to both forms to solve some of these issues.

You can also make the second form modal so that you have to close it before
you can go back to the first form and change the record on that.
 
Do you have a relationship set up between the tblCars and tblParts
tables?
Check out the LinkChild and LinkMaster fields of the subform object.
If they're not filled in, there's your problem.
 
Hey Rick. Thanks for the advice. I didnt realize that about linked
forms. Its hard for me to describe what Im working on because I cant
talk about the data but I actually started with linked forms because I
have too many subforms to fit on one main form and be usable. Are
there any other options Im not thinking of as far as subforms? I want
to make this as user friendly as possible and the linked forms
definitly work better for what I have.
 
Rob

Consider using a tab control and putting a subform on each tab.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Rob said:
Hey Rick. Thanks for the advice. I didnt realize that about linked
forms. Its hard for me to describe what Im working on because I cant
talk about the data but I actually started with linked forms because I
have too many subforms to fit on one main form and be usable. Are
there any other options Im not thinking of as far as subforms? I want
to make this as user friendly as possible and the linked forms
definitly work better for what I have.
Click to expand...
 
Rob said:
Hey Rick. Thanks for the advice. I didnt realize that about linked
forms. Its hard for me to describe what Im working on because I cant
talk about the data but I actually started with linked forms because I
have too many subforms to fit on one main form and be usable. Are
there any other options Im not thinking of as far as subforms? I want
to make this as user friendly as possible and the linked forms
definitly work better for what I have.
Click to expand...

If you set the default value property as I suggested and make the linked
form a modal form it should work. Did you try that?
 
Ill try that. I was thinking you were recommending that as a last
resort and that I should try to stick with subforms. I already have a
tabbed control with each page a different subform which works great.
The problem is that control is on the from linked from the main one.
Ill try to either combine them into one form with subforms or use the
default value propety as you suggested. Thanks a lot for your help!
 
Good advice. I actually have that but its on the form to which the
main form is linked so I still have to try and either combine the two
or use Ricks solution with setting the default value. I will try that
next. Thanks to everyone for your help. Ive never used this Google
Groups thing before. Its a great resource!


Jeff said:
Rob

Consider using a tab control and putting a subform on each tab.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Back
Top