Jack
|
| Posted: 03/06/2002, 2:45 AM |
|
Dear Sir,
How do I create a auto_update procedure ?
I use "before show" event to modify the initial value of one field, ex :
fix_sum.
It comes from another detail page and summarizes some sub_totals.
I want to automatically update this value to DB, how to do that ?
I use the code :
pPKfix_id = GetParam("PK_fix_id")
sWherem = "fix_id=" & ToSQL(pPKfix_id, "Text")
sSQLm = "update fixmain set " & "[fix_sum]=" & ToSQL(fldfix_sum,
"Number")
sSQLm = sSQLm & " where " & sWherem
cn.execute sSQLm
in open or close event. But nothing happened.
Would someone please give me a hand ?
thanks
Jackson
|
|
|
 |
Alexey Alexapolsky
|
| Posted: 03/06/2002, 6:04 AM |
|
try to replace update sSQL with this one
sSQLm = "update fixmain set " & "fix_sum=" & ToSQL(fldfix_sum,
"Number")
--
Alex
CodeCharge Developer
"Jack" <jack38@ms10.url.com.tw> wrote in message
news:a64s0n$n90$1@news.codecharge.com...
> Dear Sir,
>
> How do I create a auto_update procedure ?
>
> I use "before show" event to modify the initial value of one field, ex :
> fix_sum.
> It comes from another detail page and summarizes some sub_totals.
> I want to automatically update this value to DB, how to do that ?
>
> I use the code :
>
> pPKfix_id = GetParam("PK_fix_id")
> sWherem = "fix_id=" & ToSQL(pPKfix_id, "Text")
> sSQLm = "update fixmain set " & "[fix_sum]=" & ToSQL(fldfix_sum,
> "Number")
> sSQLm = sSQLm & " where " & sWherem
> cn.execute sSQLm
>
> in open or close event. But nothing happened.
>
> Would someone please give me a hand ?
>
> thanks
>
> Jackson
>
>
|
|
|
|
dsafar
|
| Posted: 03/06/2002, 6:26 AM |
|
That would update every record. fix this line (added space before fix_id so
you where fix_id not wherefix_id:
sWherem = " fix_id=" & ToSQL(pPKfix_id, "Text")
If this doesn't work, do response.write sSQLm and review sql generated code.
"Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
news:a657ln$bl7$2@news.codecharge.com...
> try to replace update sSQL with this one
>
> sSQLm = "update fixmain set " & "fix_sum=" & ToSQL(fldfix_sum,
> "Number")
>
>
> --
> Alex
> CodeCharge Developer
>
>
> "Jack" <jack38@ms10.url.com.tw> wrote in message
>news:a64s0n$n90$1@news.codecharge.com...
> > Dear Sir,
> >
> > How do I create a auto_update procedure ?
> >
> > I use "before show" event to modify the initial value of one field, ex :
> > fix_sum.
> > It comes from another detail page and summarizes some sub_totals.
> > I want to automatically update this value to DB, how to do that ?
> >
> > I use the code :
> >
> > pPKfix_id = GetParam("PK_fix_id")
> > sWherem = "fix_id=" & ToSQL(pPKfix_id, "Text")
> > sSQLm = "update fixmain set " & "[fix_sum]=" & ToSQL(fldfix_sum,
> > "Number")
> > sSQLm = sSQLm & " where " & sWherem
> > cn.execute sSQLm
> >
> > in open or close event. But nothing happened.
> >
> > Would someone please give me a hand ?
> >
> > thanks
> >
> > Jackson
> >
> >
>
>
|
|
|
|
Jack
|
| Posted: 03/06/2002, 7:16 AM |
|
Dear dsafar,
Thanks for your advice.
As my problem is to auto-update the record with a summarize field,
I prefer that user did not need to hit "update" button.
It means that I could use this update procedure in OPEN/or/CLOSE/or
other form event.
my case is :
master/detail form
I have a field in detail table for item price.
I have a customerized field in detail table for each item's sub_total.
I have a field in master table named total amount to summarize all
sub_totals for each fix job.
user input the item, it auto produce sub_total.
I use "before show" event to show the summary of all sub_total for one
fix job
on master (record) form.
I'd like to set a auto_update procedure in master (record) form to save
the total
amount to master table.
I'm not sure if it's a good solution.
Would you please give me some instructions ?
thanks
Jackson
"dsafar" <dsafar@hotmail.com> wrote in message
news:a658tr$eab$1@news.codecharge.com...
> That would update every record. fix this line (added space before fix_id
so
> you where fix_id not wherefix_id:
> sWherem = " fix_id=" & ToSQL(pPKfix_id, "Text")
>
> If this doesn't work, do response.write sSQLm and review sql generated
code.
>
>
> "Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
>news:a657ln$bl7$2@news.codecharge.com...
> > try to replace update sSQL with this one
> >
> > sSQLm = "update fixmain set " & "fix_sum=" & ToSQL(fldfix_sum,
> > "Number")
> >
> >
> > --
> > Alex
> > CodeCharge Developer
> >
> >
> > "Jack" <jack38@ms10.url.com.tw> wrote in message
> >news:a64s0n$n90$1@news.codecharge.com...
> > > Dear Sir,
> > >
> > > How do I create a auto_update procedure ?
> > >
> > > I use "before show" event to modify the initial value of one field, ex
:
> > > fix_sum.
> > > It comes from another detail page and summarizes some sub_totals.
> > > I want to automatically update this value to DB, how to do that ?
> > >
> > > I use the code :
> > >
> > > pPKfix_id = GetParam("PK_fix_id")
> > > sWherem = "fix_id=" & ToSQL(pPKfix_id, "Text")
> > > sSQLm = "update fixmain set " & "[fix_sum]=" & ToSQL(fldfix_sum,
> > > "Number")
> > > sSQLm = sSQLm & " where " & sWherem
> > > cn.execute sSQLm
> > >
> > > in open or close event. But nothing happened.
> > >
> > > Would someone please give me a hand ?
> > >
> > > thanks
> > >
> > > Jackson
> > >
> > >
> >
> >
>
>
|
|
|
|
dsafar
|
| Posted: 03/06/2002, 9:29 AM |
|
This would be violating a basic rule of database design that you should not
store calculated fields in the database. This is the safest way to make
sure you are always displaying correct total information (by calculating
each time you display master record - put a function in globals so you can
call it). But, if you want to update it, you could put the same code (set
a field = to the retrieve totals code) in your "before update" event on the
master record form. make sure the field is included in the master record
form (don't use type label, use hidden type as label type will not provide
update code).
"Jack" <jack38@ms10.url.com.tw> wrote in message
news:a65bri$jsd$1@news.codecharge.com...
> Dear dsafar,
>
> Thanks for your advice.
> As my problem is to auto-update the record with a summarize field,
> I prefer that user did not need to hit "update" button.
>
> It means that I could use this update procedure in OPEN/or/CLOSE/or
> other form event.
>
> my case is :
>
> master/detail form
>
> I have a field in detail table for item price.
> I have a customerized field in detail table for each item's sub_total.
> I have a field in master table named total amount to summarize all
> sub_totals for each fix job.
>
> user input the item, it auto produce sub_total.
>
> I use "before show" event to show the summary of all sub_total for one
> fix job
> on master (record) form.
>
> I'd like to set a auto_update procedure in master (record) form to save
> the total
> amount to master table.
>
> I'm not sure if it's a good solution.
>
> Would you please give me some instructions ?
>
> thanks
>
> Jackson
>
>
>
>
>
>
>
> "dsafar" <dsafar@hotmail.com> wrote in message
>news:a658tr$eab$1@news.codecharge.com...
> > That would update every record. fix this line (added space before fix_id
> so
> > you where fix_id not wherefix_id:
> > sWherem = " fix_id=" & ToSQL(pPKfix_id, "Text")
> >
> > If this doesn't work, do response.write sSQLm and review sql generated
> code.
> >
> >
> > "Alexey Alexapolsky" <alexa@codecharge.com> wrote in message
> >news:a657ln$bl7$2@news.codecharge.com...
> > > try to replace update sSQL with this one
> > >
> > > sSQLm = "update fixmain set " & "fix_sum=" & ToSQL(fldfix_sum,
> > > "Number")
> > >
> > >
> > > --
> > > Alex
> > > CodeCharge Developer
> > >
> > >
> > > "Jack" <jack38@ms10.url.com.tw> wrote in message
> > >news:a64s0n$n90$1@news.codecharge.com...
> > > > Dear Sir,
> > > >
> > > > How do I create a auto_update procedure ?
> > > >
> > > > I use "before show" event to modify the initial value of one field,
ex
> :
> > > > fix_sum.
> > > > It comes from another detail page and summarizes some sub_totals.
> > > > I want to automatically update this value to DB, how to do that ?
> > > >
> > > > I use the code :
> > > >
> > > > pPKfix_id = GetParam("PK_fix_id")
> > > > sWherem = "fix_id=" & ToSQL(pPKfix_id, "Text")
> > > > sSQLm = "update fixmain set " & "[fix_sum]=" & ToSQL(fldfix_sum,
> > > > "Number")
> > > > sSQLm = sSQLm & " where " & sWherem
> > > > cn.execute sSQLm
> > > >
> > > > in open or close event. But nothing happened.
> > > >
> > > > Would someone please give me a hand ?
> > > >
> > > > thanks
> > > >
> > > > Jackson
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|
