Main form with Subform challenge

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,
I have three tables (I am using recipies to make my issue clearer):
tbl_Authors, primary key = AuthorID
tbl_Recipies, primary key = RecipeID
tbl_Favorites, duo primary key = RecipeID & AuthorID

To avoid a many to many relationship between tbl_Authors and tbl_Recipies,
tbl_Favorites is a bridging table.

The parent form is frm_AddAuthor.
The child form is sfm_AddFavorite.

When entering an Author, the parent form generates an AuthorID. The AuthorID
of the current record on the parent form becomes the AuthorID on the child
form. A combo box is used on the child form to select a recipe. The selected
RecipieID becomes the 2nd part of the tbl_Favorites duo primary key. The
problem is, since the parent and child forms are linked on the AuthorID
field, a record starts on the child form as soon as the main form generates
it. If there are no favorites to enter at this time, I would like to simply
close the parent form and have no records entered into the child form. But,
since the record on the child form starts as soon as an AuthorID is
generated, I get a primary key violation if I don't enter a favorite.

I would appreciate any advice.
Thank you,
Renee
 
I can't duplicate your problem.

Under normal circumstances, a new record is not created on a subform unless
you actually type something into the subform. If you're displaying the
value of the AuthorID on the subform, it may appear that a new record is
being created because the value of AuthorID will be filled in when a record
is created on the main form, but Access doesn't actually create a record in
the subform until you type something in the subform (or select from the
combo box).

If you are getting a key violation error, is it possible that it's some
other key violation (e.g., the same authorID on the main form in two
different records). Alternatively, is there some way in which you are
filling data into the subform through code or through typing such that it
goes ahead and creates the record (other than the link field)?
 
I think the problem may be in the subform. I have on the form's before update
event:
If( IsNull([MainDish])) AND (IsNull([SideDish])) Then
MsgBox "Please Select either Maindish or SideDish"
me.ck_Main.SetFocus
Cancel = true
End If

I tried to alter it using:
If (Not IsNull(me.RecipeID)) Then
If( IsNull([MainDish])) AND (IsNull([SideDish])) Then
MsgBox "Please Select either Maindish or SideDish"
me.ck_Main.SetFocus
Cancel = true
End If
End If

I was thinking if I set it to only prompt for missing data if a recipe was
selected, I might be able to not select anything on the child form (and just
click next on the parent to continue adding).

I am no longer at work, so I can't quote the error. I appreciate your time,
and will post again in the morning when I have specifics.

Thank you JP,
Renee
 
I see part of the problem.

In the code as you altered it, me.recipeID is never null because it's a
combo box and always has as its value whatever is showing in the combo box.
Thus, the second if statement will always be executed.

However, since this is in the subform's beforeupdate event, you have to have
done something inside the subform (entered some data, clicked on the combo
box, something) in order to get the beforeupdate to trigger. It won't
trigger just from adding a new record in the main form.

Renee said:
I think the problem may be in the subform. I have on the form's before update
event:
If( IsNull([MainDish])) AND (IsNull([SideDish])) Then
MsgBox "Please Select either Maindish or SideDish"
me.ck_Main.SetFocus
Cancel = true
End If

I tried to alter it using:
If (Not IsNull(me.RecipeID)) Then
If( IsNull([MainDish])) AND (IsNull([SideDish])) Then
MsgBox "Please Select either Maindish or SideDish"
me.ck_Main.SetFocus
Cancel = true
End If
End If

I was thinking if I set it to only prompt for missing data if a recipe was
selected, I might be able to not select anything on the child form (and just
click next on the parent to continue adding).

I am no longer at work, so I can't quote the error. I appreciate your time,
and will post again in the morning when I have specifics.

Thank you JP,
Renee


JP said:
I can't duplicate your problem.

Under normal circumstances, a new record is not created on a subform unless
you actually type something into the subform. If you're displaying the
value of the AuthorID on the subform, it may appear that a new record is
being created because the value of AuthorID will be filled in when a record
is created on the main form, but Access doesn't actually create a record in
the subform until you type something in the subform (or select from the
combo box).

If you are getting a key violation error, is it possible that it's some
other key violation (e.g., the same authorID on the main form in two
different records). Alternatively, is there some way in which you are
filling data into the subform through code or through typing such that it
goes ahead and creates the record (other than the link field)?


But
,
Click to expand...
Click to expand...
 
Thank you for the if statement catch! I fixed that, and decided not to embed
the form in the main form. Instead I am using a command button from the main
form to add favorites only when I select too.

Thank you again JP,
Renee

JP said:
I see part of the problem.

In the code as you altered it, me.recipeID is never null because it's a
combo box and always has as its value whatever is showing in the combo box.
Thus, the second if statement will always be executed.

However, since this is in the subform's beforeupdate event, you have to have
done something inside the subform (entered some data, clicked on the combo
box, something) in order to get the beforeupdate to trigger. It won't
trigger just from adding a new record in the main form.

Renee said:
I think the problem may be in the subform. I have on the form's before update
event:
If( IsNull([MainDish])) AND (IsNull([SideDish])) Then
MsgBox "Please Select either Maindish or SideDish"
me.ck_Main.SetFocus
Cancel = true
End If

I tried to alter it using:
If (Not IsNull(me.RecipeID)) Then
If( IsNull([MainDish])) AND (IsNull([SideDish])) Then
MsgBox "Please Select either Maindish or SideDish"
me.ck_Main.SetFocus
Cancel = true
End If
End If

I was thinking if I set it to only prompt for missing data if a recipe was
selected, I might be able to not select anything on the child form (and just
click next on the parent to continue adding).

I am no longer at work, so I can't quote the error. I appreciate your time,
and will post again in the morning when I have specifics.

Thank you JP,
Renee


JP said:
I can't duplicate your problem.

Under normal circumstances, a new record is not created on a subform unless
you actually type something into the subform. If you're displaying the
value of the AuthorID on the subform, it may appear that a new record is
being created because the value of AuthorID will be filled in when a record
is created on the main form, but Access doesn't actually create a record in
the subform until you type something in the subform (or select from the
combo box).

If you are getting a key violation error, is it possible that it's some
other key violation (e.g., the same authorID on the main form in two
different records). Alternatively, is there some way in which you are
filling data into the subform through code or through typing such that it
goes ahead and creates the record (other than the link field)?


Hello all,
I have three tables (I am using recipies to make my issue clearer):
tbl_Authors, primary key = AuthorID
tbl_Recipies, primary key = RecipeID
tbl_Favorites, duo primary key = RecipeID & AuthorID

To avoid a many to many relationship between tbl_Authors and tbl_Recipies,
tbl_Favorites is a bridging table.

The parent form is frm_AddAuthor.
The child form is sfm_AddFavorite.

When entering an Author, the parent form generates an AuthorID. The
AuthorID
of the current record on the parent form becomes the AuthorID on the child
form. A combo box is used on the child form to select a recipe. The
selected
RecipieID becomes the 2nd part of the tbl_Favorites duo primary key. The
problem is, since the parent and child forms are linked on the AuthorID
field, a record starts on the child form as soon as the main form
generates
it. If there are no favorites to enter at this time, I would like to
simply
close the parent form and have no records entered into the child form. But
,
since the record on the child form starts as soon as an AuthorID is
generated, I get a primary key violation if I don't enter a favorite.

I would appreciate any advice.
Thank you,
Renee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top