Loop Error?

[SAMS]

Hi, guys. As per below is some code which i had encounter problem with. Adodc2 did not select only those claim that have the same code(from text70) but selected all the records from the table (claim). Is it something wrong with the loop?? Pls suggest. Thks.

Adodc9.Recordset.MoveLast
Text71 = Adodc9.Recordset.RecordCount
Adodc9.Recordset.MoveFirst

Static qwAttemp As Integer

Do While (qwAttemp < Text71)

Dim dbs As Database
Set dbs = OpenDatabase("c:\p1\data.mdb")
dbs.Execute "INSERT INTO " & Text70.Text & " SELECT er.* From er"
MsgBox "Update " & Text70.Text & " Done", vbOKOnly, "Update Condition"
dbs.Close

Dim str as string

str = " Select * from '" & Text70.Text & "'"

Data3.RecordSource = str
Data3.Refresh
(Error "incomplete query")

(Ask recordset to find the specific data from text70)
Adodc2.RecordSource = "select * from claim where claim.hpcode = '" & Text70.Text & "'"
Adodc2.Refresh

Do While (Adodc2.Recordset.EOF = False)
Data3.Recordset.AddNew
Data3.Recordset.Fields(0) = 1
Data3.Recordset.Fields(1) = text4
Data3.Recordset.Fields(2) = 1
Data3.Recordset.Fields(3) = text6
Data3.Recordset.Update

Adodc2.Recordset.MoveNext
Loop

MsgBox " '" & Text70.Text & "' checked", vbOKOnly, "BONS checked"

Adodc9.Recordset.MoveNext
qwAttemp = qwAttemp + 1
Loop

Any advice will be appeaciated


[This message has been edited by SAMS (edited 10-19-1999).]

[JorgeLedo]

Check if Text70.Text has some space on the left if Yes then Ltrim it.

[Clunietp]

store your recordcount in a numeric variable instead of a textbox. the value in the textbox sometimes does not return the actual number you see... it's kinda weird...otherwise, use the clng(text71.text) to convert the string to a number.

I would suggest using a variable instead of a textbox to control your loop. reading a variable is much faster than accessing a control, plus the control probably will not return the correct value unless you convert it.

HTH

Tom

[SAMS]

If you notice, i believe the loop is quite alright. As notice in the early stage where i used text70 to create a table -it can get the name.

First of all when i used LTrim for my text70 Adodc2.Recordset did not select those that are specific under text70 but selected everything.

I think that maybe the problem might be cause by the sentence Data3 as Data3.Refresh can not be refresh and i got the error - incomplete query. As after trying th CLng I still get the right figure.

Anyway thks guys. But do you have any ideas what's wrong with my Data3 and Adodc2-select statement?? Why can't my data3 be refresh??

I really suspect did the adodc2 read my statement as '" & Text70.Text & "' or read as ''. Else it should not select the whole thing??

Pls help.