Problem adding data to a non-AllowAdditions form

[John S. Ford, MD]

I'm working in Access 97 and have a form with an embedded subform. The
subform's default AllowAdditions property is set to FALSE. The ONLY code
anywhere in either form is placed in the OnCurrent event of the Main form:

Private Sub Form_Current()
If Me.sbfctlSubform.Form.RecordsetClone.RecordCount = 0 Then
Me.sbfctlSubform.Form.AllowAdditions = True
Me.sbfctlSubform.Form!txtName = "Zelda"
End If
End Sub

When I move to a record in the main form such that the subform contains no
records, I get the following error:

Run-time error '2448' (You can't assign a value to this object)

and line 4 is flagged. I assumed that line 3 would force a new record into
the subform and allow me to place a value in txtName (a bound TextBox) but
apparently this doesn't work. Any ideas what I'm doing wrong? Is there a
way to force the subform to make the new record the current record?

John

 

[Albert D. Kallal]

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.

 

[Van T. Dinh]

I am guessing that there is no "Current Record" since the Recordset was
empty and AloowAdditions was False.

Try:
....
Me.sbfctlSubform.Form.AllowAdditions = True
Me.sbfctSubform.SetFocus
DoCmd.GoToRecord, , acNewRec
Me.sbfctlSubform.Form!txtName = "Zelda"
....

 

[John S. Ford, MD]

Van,

Wouldn't the DoCmd.GoToRecord, , acNewRec line move the MAIN form to the new
record? I need to get the SUB form to the new record.

John

 

[Van T. Dinh]

That's why I got the SetFocus statement.

I tested in A2K2 and it worked fine.

 

[John S. Ford, MD]

Wow that's great! I didn't know I could use ANY DoCmd methods on forms
other then the one that calls it. So all I have to do is make sure the
subform control in question has the focus?

John

 

[lito paez]

(e-mail address removed)



DO YOU NEED EXTRA MONEY ..ATREVETE???



IT REALLY WORKS AND DON'T FORGET THAT IT IS LEGAL!!!!!



YOU NEED MONEY HE/SHE READS THIS ALONE IT WILL BE 5 MINUTES.. IT REALLY
WORKS AND DON'T FORGET THAT IT IS LEGAL!!!!!



This is a system easy to obtain money by mail, alone it is necessary to be
constant. As that don't catch it the ball to the first one, VERADAD?, me
same I felt THIS WAY!! ALONE it is necessary to BE CONSTANT AND to BE IN
CONTACT AS TRUTH there are DIFFERENT TYPES OF OUTLINES to OBTAIN EXTRA MONEY
they are A GARBAGE IF they ALLOW IT to ME!!!!. is WHAT HAPPENS THAT THE
PERSON BELIEVES THAT she GOES to RECEIVE MONEY WITHOUT MAKING ANYTHING AND
THIS WAY EVERYTHING IS NOT you/he/she COSTS WITH EFFORT AND DEDICATION. With
this alone system it is necessary to publish it THE KIT OF THE PACKAGE it is
THAT: To PUBLISH. AND the effect is multiplier it is WONDERFUL AS it WORKS
ALONE he/she NEEDS An IMPULSE AND I LIST!!!, it WORKS!!!! AND don't FORGET
THAT it is LEGAL!!!!.



Did this article describe the way to send A NOTE by mail DE US $1.00 or 1 ?
(EURUS) to single six people AND to WIN US $50.000.00 ((((?))) cash IN 4
WEEKS!!!. Good, the more I did think about this more curious I put on? why?,
for the way in that this worked and BECAUSE ALONE I will COST US $6.00 (AND
SIX STAMPS), AND was THAT ALL THAT HAD to PAY... AND ANYTHING MA!!!. O.K.,
the US $50.000.00 cash a high point could be of reaching, but was it
possible. I imagined that perhaps he/she could have a reach of $1.000.00 but
or less so I made it!!!.



BEFORE OF EVERYTHING IT DOES SELECT ALL EAST I ARTICULATE AND DO KEEP IT IN
THE PAD OF NOTES, DOES IT STOP THEN TO INCLUDE YOUR NAME THE EAST I
ARTICULATE, BETTER IF YOU PRINT IT TO CONSULT IN THE MOMENT THAT YOU WANT
OK?.



Now see like it works: INSTRUCTIONS



1. in one it paginates in target he/she writes the following thing: "Please
it INCLUDES MY NAME IN THEIR LIST" (PLASE ADD MY YAM TO YOUR LIST"). This
automatically generates a service and as such, he/she makes it TOTALLY
LEGAL. Starting from now not these sending A DOLLAR OR An EURUS to some
person without any reason, these paying A DOLLAR OR An EURUS for an I
legitimate service. Make sure of including your name and address. I assure
you, again that this is totally legal!. As a pretty detail you also put in
that position each name figured when you sent your dollar or Eurus, to make
it but I complete!!!. Of that it is, of to make money and to pass it very at
the same time.



2. now it bends this page written around the note of A DOLLAR OR An EURUS
(you don't send checks neither another payment type, SINGLE NOTES OF An
AMERICAN DOLLAR), put everything inside an envelope and send it to each one
of 6 striped people.



The idea of bending the paper around the note is to assure that it will
arrive to destination and THIS is IMPORTANT!!!. (otherwise people that works
in the mail could detect that you/he/she is money and to keep the thousands
of on that they go arriving!!!). for more security, you/he/she also uses a
leaf of paper coal to wrap the note, in that way you/he/she will be a lot
but difficult to see what goes inside.



3. he/she listens carefully, this it is the form of like you will receive
money by mail. He/she looks at the list of six people, it erases the first
name and he/she adds yours at the end of her, so the one that was numbers 2
it becomes I number 1, the one that was 3 becomes 2, the one that was 4
becomes 3, the one that was 5 becomes 4 etc. and you are now the I number
six. It includes your name, address, postal code and country.



4. now it publishes this I articulate in at least 200 news groups" (they
exist but of 24,000 groups). you Only need 200, but the more quantity puts,
you will receive more money!!!, if you know BBS (Bulletin Board Systems)
local with areas of messages, etc. where you are happened (he/she remembers
that it is LEGAL) .Cuando publishes the description of this I articulate,
he/she tries to give him a name that it catches, as: "DOES HE/SHE NEED QUICK
MONEY?, DO READ THIS I ARTICULATE", DOES HE/SHE NEED MONEY TO PAY THEIR
DEBTS??"?, ETC



LET US MAKE THE COMMITMENT OF TO BE HONEST AND TO FULFILL THE LEGAL THING,
PUTTING 120 PERCENT OF US SO THAT THIS SYSTEM WORKS!!!.



IT LISTS OF NAMES



1.-Jorge Padilla Abarca Misión Sto. Tomas No.7129 Edif. 1 depto. 204 Frac.
Kino sur, c.p. 22223 Tijuana Baja California Norte, México.



2.- Herrera Nestor Rubén Paul Grousac 2891 (entre las calles Ecuador y
Portugal) Ituzaingo , C.P (1714) Buenos Aires Argentina.



3.-Jose Ortega Lecaros isla tierra del fuego #8617, la granja -santiago
casilla 51146 correo central -plaza de armas santiago de chile.



4.-Juan Manuel cazalla Terrada 2333 ,C.P(8000) Bahia Blanca provincia de
Buenos Aires, Argentina.



5.-Herbert Reintsch S. M. Av. Busch, 2060 Edif.. "San Martín" Piso 2, Dpto.
"2C" La Paz, Bolivia Sud América.



6.-Amiel M. Pérez Vásquez. "Km 4 al norte" (Av. Cristo Redentor), Av. San
Silvestre, 300 Mts al naciente y 100 Mts al norte, Santa Cruz de la Sierra,
Bolivia, Sud América.





INDICACIONES.PARA TO PUBLISH



FOR THOSE THAT MANAGE NETSCAPE No.1 inside the program Netscape, go to "
Communicator " and select Group of news", then go to " File " and to select
to Subscribe. In seconds a list of all the Newsgroups of its " Server " will
appear. Make click in any newsgroup. Of this newsgroup he/she makes click
under TO NEWS", which should be up, in the left end of the page newsgroups.
This will take to the box of messages.



No.2 Fills this space. This will be the title that you/they will see all
when they travel in particular for the list of a group.



No.3 Marks the complete content of the file and copy using the same NEWS and
you are creating and filling this letter inside its program or " conceit ".



No.4 Presses " send " that is in the left superior part. AND YOU HAVE
CONCLUDED WITH THEIR FIRST ONE! ...FELICITACIONES!!!.



THOSE THAT USE INTERNET EXPLORER Not HAPPEN. 1: Go to the Newsgroups and
select Post an Article". OR in having Classified it selects to put an
Announcement". OR in the Forums of Discussion, etc.



STEP Not. 5: Copy the article of the notepad or pad of notes and hit it in
the place of the text that he/she will send or to announce. Use the same
previous technique.



STEP Not. 6 press the button Post, to Send or Poner",etc..



It is EVERYTHING!. All that has to make is to enter in different "
Newsgroups " and to fill them, when he/she already has practice, alone
he/she will take him about 30 seconds for each newsgroup! REMEMBER, HOW MANY
MA NEWSGROUPS GETS, MA ANSWERS (AND MONEY) he/she will RECEIVE!! BUT he/she
should ENTER IN at least 200 - ALREADY THIS!!!.... you will be receiving
money from all over the world, of places that neither he/she knows and in
some few days!.



Possibly he/she will want to lease a mail stall for the quantity of
envelopes that he/she will go receiving. MAKE SURE THAT ALL THE ADDRESSES
ARE CORRECT.



Now, each one of 5 people that you/they already sent me US $1.00 also make a
minimum 200 newsgroups, each one with my name in the #5 of the list and 5
people only respond to each one of the 5 originals, this makes US $25.00
more than I receive, now these 25 people put a minimum of 200 Newsgroups
with my name in the #4 and 5 answer is only received from each one. He/she
would be making other US $125.00 additional. Now this 125 people put their
minimum of 200 groups with my name in the #3 and they only receive 5 answers
each a, I receive an additional of US $625.00!. OK, here this the most
amusing part, each one of these 625 people puts their letters in other 200
groups with my name in the #2 and each one only receives 5 answers, this
makes me to receive US $3,125.00!!!. These 3,125 people will send this
message to a minimum of 200 Newsgroup with my name in the #1 and if single 5
people Respond of the 200 groups, I will be receiving US $15,625.00!!. Of an
original investment of US $6.00!! but you stamp.



FABULOUS! And like I said before that single 5 people respond very little,
the serious real average 20 or 30 people!.



This is my fourth week and I already received a total of US $23.343.00!!! it
was EXCITING!!! he/she could not believe it!!! you Have to follow it and
re-publicarlo where it is you possible, the more it is published and more
people see it, there will be more possibilities for all of making more
money, this will determine how much you will receive by mail!!!. For those
that understand the mathematics you/he/she is an arithmetic progression.



This is the form of how it is ascended: initially TO it begins and it
publishes in all the places that it is he possible the I articulate, then
five " B " appears as minimum, one of the five B is placed in the level 6,
this it goes up you a level and TO this in 5to level, then C appears and
this it is placed in 6to level and B it is placed in 5to level and TO in 4to
level. ONCE LLEGES TO THE 1ER PLACE IF YOU LIKE YOU CAN PUBLISH IT AGAIN.



BLESSINGS FOR ALL and luck, let us play clean and to take advantage of this
beautiful opportunity to make tons of money in Internet. * *Dicho is in
passing, if you defraud people putting messages with its name and he/she
doesn't send any money to the other ones in that list, you will receive
almost ANYTHING!. THE FRUITS OF THE HONESTY are PICKED UP IN VERY LITTLE
TIME AND they LAST CHINESE PROVERVIO forever.



I have conversed with people that people that made that knew and they ended
up receiving US $10.00 in some 7 weeks!!! Some decided to prove another
time, making it correctly, and in 4 to 5 weeks they received more than
$15.000.



And this it is the step that but I like. SIMPLY SIT DOWN AND he/she ENJOYS,
BECAUSE THE CASH COMES IN ROAD!!!. he/she Hopes to see a little bit of money
during the second week, but starting from the third week STORM OF ENVELOPES
IN YOUR MAIL. All that you have to make is to receive it and he/she tries
not to scream very strong when you realize that this time the lograstes!!