By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,050 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,050 IT Pros & Developers. It's quick & easy.

Problem updating record from subform if it contains only prefilled values

P: n/a
Hi - (This is my very first post)
I have gotten to this point of registering to post because I have been
spinning my wheels so long, I believe I am all caught up in the weeds.
(and mud)

I have a form, that has several tabs. On one tab, I have a subform
(wr_completion_subform). This subform is bound to a table
(wr_completion_table).
On the subform, I have a button from which I have added code to email
a report of the completion data to our wr completion mailbox.
(wr=work request).
I also prefill many of the controls with information from the other
tabs.

If the user clicks on any of the controls in the subform, the table is
updated.
However, if the user accepts all the prefilled info and clicks the
'email' button, a blank report generates (no record on table to
report).

I saw a very similar post from 10/01 where the developer was advised
to use the me.dirty = false and the me.refresh. I have tried this and
other methods I have stumbled upon in the usenet group. Nothing seems
to work.

The table is bound to the subform, but I still added sql to check for
the record existance and if not there, SQL to insert else SQL to
update. I thought a manual insert or update would get around it - it
does not.

One clue: I read somewhere where the record selector will turn to a
pencil if it needs to be updated and will be an arrow if nothing to
update. I turned the record selector on in my subform and noticed it
was an arrow before submitting the report and a pencil after
submitting the report. (was not dirty until sometime AFTER the button
was clicked).

Please - If anyone has any wisdom, I would greatly appreciate it. I
need to be rolling out the product next week. (3/03/04)
Thank you in advance.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Maybe it's a cheat, but what if your command button clicks one of the
checkboxes (at random, doesn't matter) *twice*? That does not change the
entry, but does set the record dirty.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #2

P: n/a
"Mary Litten" <ma*********@alldata.net> wrote in message
news:83**************************@posting.google.c om...
Hi - (This is my very first post)
I have gotten to this point of registering to post because I have been
spinning my wheels so long, I believe I am all caught up in the weeds.
(and mud)

I have a form, that has several tabs. On one tab, I have a subform
(wr_completion_subform). This subform is bound to a table
(wr_completion_table).
On the subform, I have a button from which I have added code to email
a report of the completion data to our wr completion mailbox.
(wr=work request).
I also prefill many of the controls with information from the other
tabs.

If the user clicks on any of the controls in the subform, the table is
updated.
However, if the user accepts all the prefilled info and clicks the
'email' button, a blank report generates (no record on table to
report).

I saw a very similar post from 10/01 where the developer was advised
to use the me.dirty = false and the me.refresh. I have tried this and
other methods I have stumbled upon in the usenet group. Nothing seems
to work.

The table is bound to the subform, but I still added sql to check for
the record existance and if not there, SQL to insert else SQL to
update. I thought a manual insert or update would get around it - it
does not.

One clue: I read somewhere where the record selector will turn to a
pencil if it needs to be updated and will be an arrow if nothing to
update. I turned the record selector on in my subform and noticed it
was an arrow before submitting the report and a pencil after
submitting the report. (was not dirty until sometime AFTER the button
was clicked).

Please - If anyone has any wisdom, I would greatly appreciate it. I
need to be rolling out the product next week. (3/03/04)
Thank you in advance.


At first glance, it look sto me as if you do not actually create a record in
the subform and that is why it the report is blank. Imagine you did
everything as you described (simply pressing the button and 'accepting the
defaults') but with the AllowAdditions property of the subform turned off.
Now what happens?
Was there some event, some code which creates the related record? Or do you
find that no record is created? If I am on the right track here, then
perhaps you need to ask yourself:
Should one (or more) related records be automatically created without user
intervention?
What event should trigger the creation of the related record(s)?

Fletcher
Nov 12 '05 #3

P: n/a
thanx for the quick response.
I don't have any check boxes, but I appended a space to one of the
fields - and it worked.

stProjName = Forms![* Project
Communication]![WR_Completion_Subform].Form![Project Name] & " "
Forms![* Project
Communication]![WR_Completion_Subform].Form![Project Name] = stProjName

I'd like to figure out how to do it without cheating, though. The
screen kinda winks/blinks at me because of the 'cheat'. Like it knows
what I am up to :)!

However - I this will at least let me hit my deadline. Hopefully I can
get a better understanding later and remove the 'cheat'.

thanx again!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
Thanx for the feedback, Fletcher. I have included your questions and my
responses below. If you have a suggestion for a solution, let me know.

with the AllowAdditions property of the subform turned off.
Now what happens?
-> The subform does not display. I'm not sure what this shows me??

Was there some event, some code which creates the related record? Or do
you
find that no record is created?
-> The form is bound to the table. This should cause the data to be
prepared for insert into the table, but the insert is not committed to
the table until you leave the form.
Should one (or more) related records be automatically created without
user intervention?
-> In this case, Yes. They review the information on the screen before
sending the report and have the opportunity to change it. If the report
has not been sent, no harm has been done.

What event should trigger the creation of the related record(s)?
-> The button that submits the report should create the record. However,
once again, it does not save the data until the user leaves the form.
(the button alone does not fire any of the events). I have tried code
in a variety of places to no avail.

I would like to find out HOW to commit the updates before the report is
created - and/or WHERE to put the update (dirty) logic.
FYI - if the record has been previously created and the user returns to
the form and updates the data, followed by submitting the report, the
updates ARE reflected. So, I finally got that logic to work. The
difference here is that the user has to click on a control on the form
to fire the update.
Knowledge (like life) is a journey, not a destination.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
"Mary" <an*******@devdex.com> wrote in message
news:40*********************@news.frii.net...
Thanx for the feedback, Fletcher. I have included your questions and my
responses below. If you have a suggestion for a solution, let me know.

with the AllowAdditions property of the subform turned off.
Now what happens?
-> The subform does not display. I'm not sure what this shows me??

Was there some event, some code which creates the related record? Or do
you
find that no record is created?
-> The form is bound to the table. This should cause the data to be
prepared for insert into the table, but the insert is not committed to
the table until you leave the form.
Should one (or more) related records be automatically created without
user intervention?
-> In this case, Yes. They review the information on the screen before
sending the report and have the opportunity to change it. If the report
has not been sent, no harm has been done.

What event should trigger the creation of the related record(s)?
-> The button that submits the report should create the record. However,
once again, it does not save the data until the user leaves the form.
(the button alone does not fire any of the events). I have tried code
in a variety of places to no avail.

I would like to find out HOW to commit the updates before the report is
created - and/or WHERE to put the update (dirty) logic.
FYI - if the record has been previously created and the user returns to
the form and updates the data, followed by submitting the report, the
updates ARE reflected. So, I finally got that logic to work. The
difference here is that the user has to click on a control on the form
to fire the update.

Hi Mary,
Now it seems very clear what is happening - if the user does not use the
subform, then no record ever gets created.

Here is one possible solution:

On your main form set AllowAdditions to false.
Create a new button on the form which says New... and performs the following
actions:

1. Creates and saves a new main record
2. Creates and saves a related record
3. Requeries the main form
4. Goes to the newly created main record

Would you be able to write the code for this?

One question I still have is this:
If one (and only one?) related record needs to be created automatically in
the subform, then are you sure the 2 records don't belong in the same table,
or can you have more related records but a minimum of one?

I don't know what the table structure is like, but in a typical database you
might have tblOrders and tblOrderItems and you might have a button saying
"New Order". When you press it, it creates asks you what you want to order,
then creates a new order with today's date, etc and a single new order item.
It then takes you to the order record where you could add more items. The
point is that it automatically creates the first one since an order with no
items might make no sense. Is your a similar scenario?
Fletcher
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.