Do... Loop Issues

[FRIDL125]

I am having difficulties extracting information from another application to load in my database. I have one table (tblAccounts) that stores account numbers and another table set up to receive the account number and the additional information I'm screen scraping from our system. I am using a Do... Loop to a) scroll through the list of accounts and b) to scroll through multiple lines on each account.

My first issue is that it doesn't seem to pull account numbers correctly and the second issue is that it's not saving any data.

Can anyone see what my problem might be?

TIA,
Lisa
***************Code****************
Set rstAccountNumber = dbsCurrent.OpenRecordset("tblAccounts", dbOpenDynaset)
Set rstStrings = dbsCurrent.OpenRecordset("tblStrings", dbOpenDynaset)

rstAccountNumber.MoveLast
LastAcct = rstAccountNumber![AccountNumber]
rstAccountNumber.MoveFirst
FirstAcct = BeginAcct

Do
For i = BeginAcct To LastAcct
CurAcct = rstAccountNumber![AccountNumber]
FDRSess.Screen.SendKeys &quot;BS &quot; & CurAcct & &quot;<Enter>&quot;
Phone = FDRSess.Screen.GetString(4, 67, 4, 78)
FDRSess.Screen.SendKeys &quot;<Home>CIS<Enter>&quot;
NameString = FDRSess.Screen.GetString(2, 1, 2, 80)

Do
For j = 2 To 24
InitialCheck = FDRSess.Screen.GetString(j, 23, 80)
If InitialCheck = &quot;BC *&quot; Then
MemoString = InitialCheck

rstStrings.AddNew
rstStrings![AccountNumber] = CurAcct
rstStrings![NameString] = NameString
rstStrings![MemoLine] = MemoString
rstStrings![Phone] = Phone
rstStrings.Update

End If
Next j
Loop Until j = 2


If CurAcct = LastAcct Then
MsgBox &quot;Complete&quot;
End If
Exit Do

Next i

Loop Until i = LastAcct

End Function

 

[krsherm]

Lisa,

I think you're reaching here. Give me the structure of tblAccounts and tblStrings (including their relationships) as well as an exact explanation of what you are trying to do, and I'll help you write the code. It looks like you are making things more difficult than they need to be... I started to re-write your code, but you are missing some important items (mainly: variable types, the relationships between the two recorsets (what is the primary key / foreign key).

Explain it to me like I would explain it to my six-year-old and I'll make it work.

Standing by...

Kevin

(Rock ON!)

 

[FoxProProgrammer]

I agree with Kevin, but two things do stand out as possible problems.

1. The outer For loop starts at BeginAcct and ends at LastAcct. This will only work if the table is indexed on AccountNumber in ascending order. I agree with Kevin that this is probably more complicated than it needs to be.

2. You don't move the record pointer inside the loop, so you are always operating on the first record. You would need to add a rstAccountNumber.MoveNext at the bottom of the loop.

A few general comments...I don't understand why you have so many loops, but this could just be a style issue. Instead of looping on the account number, you might loop until rstAccountNumber.EOF() is true. Then you could eliminate the following:

rstAccountNumber.MoveLast
LastAcct = rstAccountNumber![AccountNumber]
rstAccountNumber.MoveFirst
FirstAcct = BeginAcct

And do something like this:

Do While Not rstAccountNumber.EOF
' Store your data here
Loop

This would also eliminate the issue with whether the table is indexed.

dz

Sorry Kevin...I couldn't resist! )