Help Adding Records To Table

[LongFeiFengWu]

What I'm attempting to do is cycle through one table and use the values there to write to another table. The problem is that It's not looping. I only get the first record to write and none of the records that follow. Any help is greatly appreciated.

<!--#Include file="../../../Includes/asp/OpenDBConnections2.asp" -->

<%

[b] Setting Threshold [/b]

ThisMonth = request.form("ThisMonth")
TheMonth = ThisMonth
TheYear = Year(Now())

SQL = "SELECT * FROM Threshold"
objrec.open SQL,objcon,3,3

Tier1 = objrec("Tier1")
Tier2 = objrec("Tier2")

IF TheMonth = 1 THEN EoM = 31
IF TheMonth = 2 THEN EoM = 28
IF TheMonth = 3 THEN EoM = 31
IF TheMonth = 4 THEN EoM = 30
IF TheMonth = 5 THEN EoM = 31
IF TheMonth = 6 THEN EoM = 30
IF TheMonth = 7 THEN EoM = 31
IF TheMonth = 8 THEN EoM = 31
IF TheMonth = 9 THEN EoM = 30
IF TheMonth = 10 THEN EoM = 31
IF TheMonth = 11 THEN EoM = 30
IF TheMonth = 12 THEN EoM = 30

FOR i = 1 to EoM
TheDate = TheMonth & "/" & i & "/" & TheYear
DayOfWeek = WeekDay(TheDate)
	IF DayOfWeek <> 1 AND DayOfWeek <> 7 THEN 
	DayCount = DayCount + 1
	END IF
NEXT

Threshold1 = DayCount * ( Tier1 * 7.5 )
Threshold2 = DayCount * ( Tier2 * 7.5 )

[b] Done Setting Threshold [/b]

%>

<!--#Include file="../../../Includes/asp/closeDBConnections.asp" -->
<!--#Include file="../../../Includes/asp/OpenDBConnections1.asp" -->
<!--#Include file="../../../Includes/asp/format.asp" -->

<%

[b] Selecting Records That Meet Thresholds [/b]

SQL = "SELECT * FROM ContactsQuery WHERE ScoreMonth = "& ThisMonth &" "
SQL = SQL & "AND SumOfContacts >= "& Threshold2 &" "
objrec.open SQL,objcon,3,3

IF objrec.recordcount > 0 THEN

do while not objrec.eof

	IF objrec("SumOfContacts") >= Threshold1 THEN AddCredit = 50
	IF objrec("SumOfContacts") >= Threshold2 AND Contacts < Threshold1 THEN AddCredit = 25

	VZID = objrec("VZID")
	Name = objrec("Name")
	SupID = objrec("SupID")

[b] Attempt At Using Selected Records To Write To 2nd Table [/b]

	objrec.close

	SQL = "SELECT * FROM Bank WHERE VZID = '"& VZID &"'"
	objrec.open SQL,objcon,3,3
	
	IF objrec.recordcount > 0 THEN
	
	objrec("Credit") = FormatADD(objrec("Credit"),AddCredit)
	
	objrec.update
	
	ELSE
	
	objrec.addnew
	
	objrec("VZID") = VZID
	objrec("Name") = Name
	objrec("Credit") = AddCredit
	objrec("SupID") = SupID
	
	objrec.update
	
	END IF
	
objrec.movenext

loop

END IF

%>

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[Sheco]

First question, before looking at the code:

Is there any reason you can't do this with a CREATE TABLE statmetn in SQL and just specifying the criteria in the WHERE clause?

OK. Now I'll look at the code.

 

[Sheco]

The problem in your code is that you are using the recordset variable named objrec for both loop control and to insert the new records.

Take a look at this piece of code:

[red]do while not objrec.eof[/red]

    IF objrec("SumOfContacts") >= Threshold1 THEN AddCredit = 50
    IF objrec("SumOfContacts") >= Threshold2 AND Contacts < Threshold1 THEN AddCredit = 25

    VZID = objrec("VZID")
    Name = objrec("Name")
    SupID = objrec("SupID")

Attempt At Using Selected Records To Write To 2nd Table 

    [red]objrec.close

    SQL = "SELECT * FROM Bank WHERE VZID = '"& VZID &"'"
    objrec.open SQL,objcon,3,3

    ...

For writing that 2nd table you should use a different recordset object. By calling [red]objrec.close[/red] it causes the problem with the loop because the loop is controlled by do while not objrec.eof

 

[steven290]

also

iF clng(objrec("SumOfContacts")) >= Threshold1 THEN AddCredit = 50

 

[Sheco]

Good mornin' steven290!

 

[steven290]

morning ;-)

 

[LongFeiFengWu]

I'm using an Access 2000 DB...does a Create Table statement work in an Access 2000 DB?

"If nothing within you stays rigid, outward things will disclose themselves. Moving, be like water. Still, be like a mirror. Respond like an echo." ~ Bruce Lee

 

[bslintx]

you guys ever sleep? it's 5:10 AM for gawd sakes?! ;-)

 

[bslintx]

ooops....you did sleep in i read the date ..lmao...1150.....oh well...maybe i need to go back to bed...or get my caffeine;-)



sorry long....see these guys all the time...yes you can use the create table in access

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp