[RESOLVED] loop trouble

[colonel720]

Why can't the variable "word" remain the value that is assigned to it by
resp(z) when a loop occurs?

For a = 1 To 10
Data1.Recordset.MoveFirst
Do
If Data1.Recordset.EOF Then Exit Do
x = Data1.Recordset.Fields("words")
Data1.Recordset.MoveNext
If Data1.Recordset.EOF Then Exit Do
y = Data1.Recordset.Fields("words")
If x = word Then ans = ans & " " & y
Loop
resp = Split(ans, " ")
Randomize Timer
z = Int(Rnd * UBound(resp))
word = resp(z)
Label1.Caption = Label1.Caption & " " & word
Next a

When next a occurs and it returns to the beginning of the for loop, the value of the variable "word" returms to what it was previously. How do I fix this?

[dglienna]

Please explain what you are trying to do. You seem to have something wrong with your logic. That code does more work than is necessary, I think.

[si_the_geek]

It is a logic error, albeit a minor one. You always get the previous word because your array contains it and one other item, however your random number will never actually be high enough to select the other value (as the value from Rnd is always less than 1).

Try this change:

z = Int(Rnd * (UBound(resp)+1))


ps: I agree with David that the method you are using probably isn't the best, whatever it is that you are trying to acheive!

[umilmi81]

LMAO! What in the world are you doing?

The only advise I can give you is to use
Data1.Recordset.Fields("words").Value

instead of Data1.Recordset.Fields("words")

This will allow whatever crazy thing you're doing to work faster because it doesn't have to box and unbox a recordset into a string.

(Make sure X and Y are declared strings too. Otherwise it wont help)

[Comintern]

Check to see what the UBound of resp is when you do this:

z = Int(Rnd * UBound(resp))

I suspect this line is the source of the problem. You might just be indexing to the same place in the array each time through. Rnd will return the most recently generated random number if it is passed 0. I believe the correct way to get a random array index is:

z = Int((UBound(resp) - LBound(resp) + 1) * Rnd + LBound(resp))

If the LBound is always going to be 0, this reduces to:

z = Int(Rnd * (UBound(resp)[COLOR=Red] + 1[/COLOR]))

Finally, you only need to call Randomize once, not every time though the loop.

EDIT: Ooops, si_the_geek beat me to it. I must just be slow when I'm tired...

[colonel720]

z = Int(Rnd * (UBound(resp)+1))

did not work, I don't think its a logic error. here is what the program does:

It is an NLP program designed to generate raw sentences out of a list of words in a database:

Public Sub Process()
'gets input word
word = Text1.Text
Text1.Text = ""
Label1.Caption = ""
'adds word to a database if user chooses so
If Check1.Value = 1 Then
Data1.Recordset.AddNew
Data1.Recordset.Fields("words") = a
Data1.Recordset.Update
End If
' this part goes through the database, getting a record and the one immediately after it, until the the variable x (the first record) = word
For a = 1 To 10
Data1.Recordset.MoveFirst
Do
If Data1.Recordset.EOF Then Exit Do
x = Data1.Recordset.Fields("words")
Data1.Recordset.MoveNext
If Data1.Recordset.EOF Then Exit Do
y = Data1.Recordset.Fields("words")
'gathers up all occurances of a word in the database, putting the next record immediately after each occurance into a the variable ans (x is a record, y is the one immediately after it)
If x = word Then ans = ans & " " & y
Loop
'now that the variable ans has all the information it needs stored as words separated by spaces, it splits them into an array "resp"
resp = Split(ans, " ")
'randomly chooses one word out of the array to continue the sentence with and use as criteria for the next search
Randomize Timer
z = Int(Rnd * (UBound(resp) + 1))
word = resp(z)
Label1.Caption = Label1.Caption & " " & word
Next a
End Sub

even if the array was one number off die to the rnd, it is still assigning it to a variable. the odd thing is, the variable "word" seems to undergo a change between 'next a' and 'for a'.

[umilmi81]

I can't see how this line

If x = word Then ans = ans & " " & y

will ever evaluate to true assuming both "word" and "ans" are both empty at the begining of the loop.

[RobDog888]

Post #6 merged from a mis-post as a new thread.

[umilmi81]

I've looked at it again. I'd rewrite this:

For a = 1 To 10
    Data1.Recordset.MoveFirst
    Do
        If Data1.Recordset.EOF Then Exit Do
        x = Data1.Recordset.Fields("words")
        Data1.Recordset.MoveNext
        If Data1.Recordset.EOF Then Exit Do
        y = Data1.Recordset.Fields("words")
        If x = word Then ans = ans & " " & y
    Loop
    resp = Split(ans, " ")
    Randomize Timer
    z = Int(Rnd * UBound(resp))
    word = resp(z)
    Label1.Caption = Label1.Caption & " " & word
Next a

to this:

Dim a As Integer
Dim PrevRecord As String
Dim CurrRecord As String
Dim Word as String
Dim z as Integer
Dim resp() As String
 
Randomize Timer
For a = 1 to 10
    Data1.Recordset.MoveFirst
    Do While Not Data1.RecordSet.EOF
        PrevRecord = CurrRecord
        CurrRecord = Data1.RecordSet.Fields("words").Value      
        Data1.Recordset.MoveNext
        If PrevRecord = word Then
            ans = ans & " " & CurrRecord
        End If
    Loop
    resp = Split(ans," ")
    z = Int(Rnd * UBound(resp))
    word=resp(z)
    Label1.Caption = Label1.Caption & " " & Word
Next a

But I still can't see how you would ever get anywhere unless "word" or "ans" was already equal to something.

[Comintern]

OK, this is not related to your loop, but you probably shouldn't rely on position in a dataset to be static. Make the "answer" into a seperate table with a primary key of "word" and then you can just do a simple SQL query to get all of the related answers for a given word.

[colonel720]

I tried rephrasing the code, but it was essentially doing the same thing. the variables "word" and "ans" are always equal to something. when the sub is first triggered, the first thing it does is assign the contents of a text box to "word" - the word that starts off the sentence generation sequence.

Ok, I have tracked down the poit where "word" reverts to its previous value:

For a = 1 To 10
Data1.Recordset.MoveFirst
--------- word = value a
Do - this is the point where the varaible changes.
--------- word = value b
If Data1.Recordset.EOF Then Exit Do
x = Data1.Recordset.Fields("words")
Data1.Recordset.MoveNext
If Data1.Recordset.EOF Then Exit Do
y = Data1.Recordset.Fields("words")
If x = word Then ans = ans & " " & y
Loop
resp = Split(ans, " ")
Randomize Timer
z = Int(Rnd * UBound(resp))
word = resp(z)
Label1.Caption = Label1.Caption & " " & word
Next a

there is nothing about the command do that can influence the content of a variable, is there? it survives the for loop, but the do loop reverts it.

[si_the_geek]

While my alteration may not have fixed the issue, it was an essential change. For proof, try running this code (even tho it is Rnd*10, the highest value you get will be 9):

Dim a As Integer, b As Integer, c As Integer
 
a = 10
b = 0
Randomize Timer
 
Dim x As Integer
For x = 1 To 10000
  c = Int(Rnd * [b]10[/b])
  If c < a Then a = c
  If c > b Then b = c
Next x
MsgBox "lowest value: " & a & vbCr _
     & "highest value: " & b

Another change that you should do, is change the "If x = word" line to have an "Exit Do" aswell, so that you do not read more data than required (this will make it much faster). You also do not need to use If's to check the EOF state, as long as the code is modified a little (this will make it faster too). And another, there is no reason to read the y value every time (again, faster!).

Here is a version which has these modifications:

Randomize Timer  'this only needs to be done once, not each time you call Rnd
 
For a = 1 To 10
  Data1.Recordset.MoveFirst
  Do While Not Data1.Recordset.EOF
    x = Data1.Recordset.Fields("words")
    Data1.Recordset.MoveNext
    If x = word Then
      If Not Data1.Recordset.EOF Then
        y = Data1.Recordset.Fields("words")
        ans = ans & " " & y
      End if
      Exit Do
    End If
  Loop
  resp = Split(ans, " ")
  z = Int(Rnd * (UBound(resp)+1))
  word = resp(z)
  Label1.Caption = Label1.Caption & " " & word
Next a

I have also thought of a major issue with your logic - you are getting the "next" word from the database, but the picking of a random word that has already been used means that you are likely to be getting the same word again. Try instead setting z to be UBound(resp), and see what results you get then.


I cannot think why the variable is changing on the Do, I have never seen something like that before, and can think of no reason why it would happen. (but from my above comment, it may be that you are just not debugging properly). The only suggestion I can make is that you could try a different variable name, as "word" may be a name used by one of your references.

[colonel720]

I changed the code, -again- and it operated just as it did with any ither variation of the original code. the problem is - the variable is being changed by the "do" command. This is very abnormal

[si_the_geek]

Well it might not have solved the problem , but at least the code is improved for when this has been fixed.

It is indeed abnormal... Have you tried both of my suggestions in my previous post? (ie: use "z = UBound(resp)", and change "word" to something else)

If so, can I ask how you are debugging & checking the values? If you aren't doing this already, I would recommend using F8 (or the menu option) to step thru the code, and having a watch for the variable.

[colonel720]

I have tried all the suggestions - with "z = UBound(resp)" I got no reaction whatsoever - resp(z) did not calculate. i changed word to wo which changed nothing, and I put a watch on word, and debugged - this is extremely wierd - the watch did not detect the value of word change after the "Do", yet I put a "print word" before and after the do, and saw that the value definitely did change. In fact, the second time i ran the check, I noticed that the watch displayed a different value for word than the print command gave me, a non array variable cannot be more than one value at a time... what the hell is going on here?

[si_the_geek]

this is extremely odd.. could you upload your code files and database? that way we can take a look and hopefully see it happening & fix it

[colonel720]

Here are the database, project, form, and workspace files. to operate it, type a single word in the text box and hit space. if the word is not in the database, a subscript out of range error will occur, ignore it, just look in the database for words that will work. Thanks for all your help

[si_the_geek]

I've had a look, and the only major issue I can see is spaces - at the end of data in the database, and as such also in the array when you split (Trim and Mid can be used to correct this). I see the data is different to what I was expecting, so you were right to ignore the "Exit Do".

Here is an updated version which seems ok, and has a safety net to avoid the error:

Public Sub Process()
Dim x As String
Dim y As String
Dim word As String
Randomize Timer
word = Trim(Text1.Text)
Text1.Text = ""
Label1.Caption = ""
If Check1.Value = 1 Then
  Data1.Recordset.AddNew
  Data1.Recordset.Fields("words") = Trim(word)
  Data1.Recordset.Update
End If
For a = 1 To 10
  Data1.Recordset.MoveFirst
  Do While Not Data1.Recordset.EOF
    x = Trim(Data1.Recordset.Fields("words"))
    Data1.Recordset.MoveNext
    If x = word Then
      If Not Data1.Recordset.EOF Then
        y = Trim(Data1.Recordset.Fields("words"))
        ans = ans & " " & y
      End If
    End If
  Loop
  resp = Split(Mid(ans, 2), " ")
  If UBound(resp) = -1 Then  'no data
    MsgBox "No data found!"
    Exit For
  End If
  z = Int(Rnd * (UBound(resp) + 1))
  word = resp(z)
  Label1.Caption = Label1.Caption & " " & word
Next a
End Sub

[colonel720]

ha - the error was so simple! the variable ans was gathering up too much data, so things started repeating themselves - the error was not in the variable word at all! all I had to to was add a simple

ans = ""

in the beggining of each for loop