L Irving
|
| Posted: 10/21/2004, 2:50 AM |
|
I have a data base with a plans table and progress table
Every time I add a progress record I want to update the next_contact date field in the plans table
i tried the after insert event with this code
dim add_case_sql
add_case_sql = "update plans set " &_
"[Next_Contact]=" & DBConnection1.ToSQL(Reviews.review_date.value, "Date")&_
"where plan_id =" & DBConnection1.ToSQL(reviews.plan_id.value, "Number")
DBConnection1.execute add_case_sql
But the field does not update
Help
Wayne
|
|
|
 |
L Irving
|
| Posted: 10/21/2004, 3:12 AM |
|
i tried the after insert event with this code as well
dim add_case_sql
add_case_sql = "update plans set " &_
"[Next_Contact]=" & Reviews.review_date.value &_
" where plan_id =" & reviews.plan_id.value
DBConnection1.execute add_case_sql
But the field puts in a bogus date december 31, 1899 no matter what the time
Help
Wayne
|
|
|
|
peterr
Posts: 5971
|
| Posted: 10/21/2004, 8:28 AM |
|
Wayne,
I recommend that you always debug your code, so that you can see exactly why something doesn't work, without guessing. I mean that you should print the values of Reviews.review_date.value and reviews.plan_id.value , to see which one is empty, wrong or if there is a problem with both.
I am guessing that reviews.plan_id.value is empty because it doesn't exist. It is not used because it is unknown. The database creates it automatically when a new record is created, therefore only the database "knows" what it is. You may need to use some database function, or retrieve that value from the database otherwise. Some of our examples in CCS Example Pack show this, one of them being "Multi-Step User Registration".
Hope this helps.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
peterr
Posts: 5971
|
| Posted: 10/21/2004, 8:33 AM |
|
As for the date, again, you can print its value to see what it is on the screen. This could give you some clues.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
LW Irving
|
| Posted: 12/17/2004, 6:02 AM |
|
That is it exactly
The record does not exist in the second table
I want a list of people who have a record in the first table and no record
in the second
The date is secondary for the moment unecessary
I need to basically search the second table and use
"if Recordset.EOF" then add that referral to the list
just not sure how to hide the row if a record does exist
I do not need to find the last referral
Regards
Wayne
"peterr" <peterr@forum.codecharge> wrote in message
news:64177d5a586db9@news.codecharge.com...
> Wayne,
>
> I recommend that you always debug your code, so that you can see exactly
> why
> something doesn't work, without guessing. I mean that you should print the
> values of Reviews.review_date.value and reviews.plan_id.value , to see
> which
> one is empty, wrong or if there is a problem with both.
>
> I am guessing that reviews.plan_id.value is empty because it doesn't
> exist. It
> is not used because it is unknown. The database creates it automatically
> when a
> new record is created, therefore only the database "knows" what it is. You
> may
> need to use some database function, or retrieve that value from the
> database
> otherwise. Some of our examples in CCS Example Pack show this, one of them
> being "Multi-Step User Registration".
>
> Hope this helps.
> _________________
> Peter R.
> YesSoftware Support Representative
> http://support.codecharge.com
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/17/2004, 12:15 PM |
|
Not sure if I understand. Your initial question was "I want to update the next_contact date field in the plans table". Now you wrote "I want a list of people...".
If these are 2 different questions I recommend creating new forum topic to avoid confusion.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
LW Irving
|
| Posted: 12/17/2004, 5:22 PM |
|
As peterr pointed out this is a different question, I have changed the
database since it was first posted.
The question concerns updateing a second table afer submitting a form, I was
using this code
dim add_case_sql
add_case_sql = "update plans set " &_
"[Next_Contact]=" & DBConnection1.ToSQL(Reviews.review_date.value, "Date")&_
"where plan_id =" & DBConnection1.ToSQL(reviews.plan_id.value, "Number")
DBConnection1.execute add_case_sql
I haven't continued with that Idea but think the problem was not having ##
characters around the dat in the SQL Query
Regards
Wayne
Not sure if I understand. Your initial question was "I want to update the
next_contact date field in the plans table". Now you wrote "I want a list of
people...".
If these are 2 different questions I recommend creating new forum topic to
avoid confusion.
"peterr" <peterr@forum.codecharge> wrote in message
news:641c33e7266ae8@news.codecharge.com...
> Not sure if I understand. Your initial question was "I want to update the
> next_contact date field in the plans table". Now you wrote "I want a list
> of
> people...".
> If these are 2 different questions I recommend creating new forum topic to
> avoid confusion.
> _________________
> Peter R.
> YesSoftware Support Representative
> http://support.yessoftware.com
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
DonB
|
| Posted: 12/17/2004, 5:29 PM |
|
Whether you use the "#" is dependent upon the database and whether you use
classic ODBC or the OLEDB data providers. Why Access instigated the "#"
delimiter I'll never understand. Really boneheaded.
I stick with the OLEDB providers whenever possible and find they are much
improved over ODBC.
--
DonB
http://www.gotodon.com/ccbth
"LW Irving" <lirving@coffs.com.au> wrote in message
news:cq00nv$cr8$1@news.codecharge.com...
> As peterr pointed out this is a different question, I have changed the
> database since it was first posted.
>
>
>
>
> The question concerns updateing a second table afer submitting a form, I
was
> using this code
>
> dim add_case_sql
>
>
> add_case_sql = "update plans set " &_
> "[Next_Contact]=" & DBConnection1.ToSQL(Reviews.review_date.value,
"Date")&_
> "where plan_id =" & DBConnection1.ToSQL(reviews.plan_id.value, "Number")
>
> DBConnection1.execute add_case_sql
>
> I haven't continued with that Idea but think the problem was not having ##
> characters around the dat in the SQL Query
>
> Regards
> Wayne
>
>
>
> Not sure if I understand. Your initial question was "I want to update the
> next_contact date field in the plans table". Now you wrote "I want a list
of
> people...".
> If these are 2 different questions I recommend creating new forum topic to
> avoid confusion.
>
> "peterr" <peterr@forum.codecharge> wrote in message
>news:641c33e7266ae8@news.codecharge.com...
> > Not sure if I understand. Your initial question was "I want to update
the
> > next_contact date field in the plans table". Now you wrote "I want a
list
> > of
> > people...".
> > If these are 2 different questions I recommend creating new forum topic
to
> > avoid confusion.
> > _________________
> > Peter R.
> > YesSoftware Support Representative
> > http://support.yessoftware.com
> > ---------------------------------------
> > Sent from YesSoftware forum
> > http://forums.codecharge.com/
> >
>
>
|
|
|
|
|
