Before I come up with a solution, I might want to ask why do you need to
"force" a child record?
All of your quires, reports etc. should work just fine when there are no
child records. Even those records that DO NOT have a child record can be
included in those reports, and as a mater of normal reporting this is
common.
For example, we might list all customers and their last invoice date. Those
customers without a invoice can also be included in this report if desired.
I only pointing out the above, as OFTEN I seen many designs want to have a
child record because some reports, or some queries don't work properly
without a child record. And, usually in 99% of these cases, the reason for
this problem is that the joins being built between the two tables are
incorrect.
In other words, you don't want to code to force a child record, but you want
to design your system so that things work without child records!
If you take a quick look at the following ER diagram, you will notice that
about 95% (or more) of the relationships between each table are what we call
a left join (that means parent records on the "left" side do NOT need child
records on the "right" side).
If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.
The tables with a left join have a arrow head on them.
http://www.attcanada.net/~kallal.msn/Articles/PickSql/Appendex2.html
tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. This makes perfect sense, as we can booking people, but they have
not yet made payments (this is allowed in our business rules here).
The same type
of relationship should exist when you have customers, and customer invoice
tables (you allow customers to be put in the system...and they DO NOT
have to have a invoice. And, if your design was such that all customers
are NOT to be entered into the system UNLESS a invoice exists, then
your relationships window should reflect this also).
When full RI (referential integrity) is enforced, and you can see the side
ways 8 "omega" sign (or is that sigma??)..
Note how MOST tables have THE ARROW HEAD. The simple lookup fields are
simply just a arrow drawn, and no "1", or sigma sign exists (tblPayments to
tblHowpaid for example is a simple lookup..and no RI is needed).
The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (i.e.: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). This just means in
plain English that when I add a booking to the system, you MUST
have at least one person in the booking.
So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join.
So, given the above issues, it does NOT make sense to force a child record,
and your designs should function correctly when no child records are present
(I seen SOOO many access databases setup the wrong way on this issue....how
do your relationships look?).
Now, having said the above, if you REALLY do in fact need the child record,
then I would programmatically add the record via code and NOT rely on a
sub-form at all. (in fact, I would not even use the sub-form).
Further, simply flipping the sub-form to allow additions and then assigning
a control a value is likely not very good approach. (you kind of need to
tell ms-access to add a record here also ...so, I am saying that "more" then
just setting allow additions is needed here. Worse yet, if you main form
does NOT have a current record, then "on current" does NOT fire!!! That
means if you use navigation keys to go to a new record, on current does NOT
fire, and thus once again your child record will NOT be created. Because of
this fact, you likely want to put your code in the forms after update event.
The after update event ONLY fires when a record is(was) edited and thus
needs to be updated. So, if you go to a form by accident, and do NOT want to
add a record, then no sub-form record will be added. You can see how using
the wrong event, or taking the wrong approach here is going to open a BIG
CAN of worms).
Anyway, lets try a code snip that does rely on the sub-form (but, if this is
data design requirement, I would suggest not using the sub-form).
I would try:
Private Sub Form_Current()
Dim fsub As Form
Set fsub = Me.sbfctlSubform.Form
If fsub.RecordsetClone.RecordCount = 0 Then
fsub.RecordsetClone.AddNew
fsub.RecordsetClone!txtName = "Zelda"
fsub.RecordsetClone!contact_id = Me!ContactID
fsub.RecordsetClone.Update
fsub.Requery
End If
Set fsub = Nothing
End Sub
Note how the above has more control, and also note that using code overrides
the forms setting (you will not see the extra line in the sub-form when you
have allowadditions = true also) . In fact, MOST of my forms have allow
additions = false. Further note that !txtName does NOT actually have to be a
control on the form, but ONLY has to be a field in the forms recordset. And,
further, note that we don't actually have to set txtName, BUT WE MUST set
the field used to build the relationship (since we are not use the form to
add..but now code. And, for all code YOU the DEVELOPER must set those fields
used in relationships).
As mentioned, likely the above code needs to be put in the forms after
update event since on current don't fire when you go to a new record.
Further, it is better coding to not have to "fire" this code for every
single record you navigate to. It makes MUCH more sense to ONLY check if a
child record exists when you do a update to the parent record. This approach
means that we ONLY check for child records when we update, not the fact we
are simply navigation around looking at thing!.
I would not want child
records being added to some screens JUST due to the fact that I am
navigating around looking that things....that is not when this should
happen! Further, we really only need to set the field in the child table
that is used for the "link" or relationship. I going to assume that zelda is
just your "example" "air code" for the sake of the question. However, we
should not be arbitrarily setting some field in the "hope" that a record
gets added. Lets add the record, and set the field use for the relationship.
Once again, this makes more sense. So, I 100% understand that "zelda" is
just air code, but we don't want to set any old field here, but lets set the
field used for the *relationship*. This is more clear, and more direct as to
what we want to do. We don't want to set any old txtContorl that may, or may
NOT be on the sub-form. (by the way, txtContorl would have to be a bound
field here to force a record add anyway if we did not set the link field
value). So, the above code really needs to remove:
fsub.RecordsetClone!txtName = "Zelda"
(since we set the field used to link the two tables, and that is ALL we
need)
I could have just simply given you some code for the "on current" event and
be done here. But, really, at the end of the day, it would have be bad
advice to JUST get your code working. Often, when a person experience a lot
of problems, or "buggy" things in ms-access, it is SEVERAL things are not
being done in the best manor. As these "bad" things pile up, then the
application begins to fell buggy. (your next post would be that on-current
don't fire when you move to a new record!, and then now you would be copying
the above code to another event!).
So, on current is likely a bad event to use to fix this problem. And,
further, as I long winded noted, you likely should NOT have to force child
records in the first place (and does your relationship window correctly show
your assumptions?). And, further if we are going to force child records,
then set the value of the link field in the child table, not just some
txtContorl.
When all the correct approaches are taken to solve a problem, then you will
find that things just flow and feel right.
