3 Replies - 773 Views - Last Post: 03 May 2013 - 05:21 AM Rate Topic: ***** 1 Votes

#1 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

working with while loop

Posted 30 April 2013 - 05:06 AM

I am working with the while loop and its been working great on all my major searches and has been returning the values as expected, but now in just one section of my app I need to implement some action if the while loop is not successful in its search. Now my question is how can I implement code if the code fails to locate a row which matches the criteria

     If txtSun.Text = "SUN" Then
                        Set rst = New ADODB.Recordset
                        rst.Open "SundryProduct", Cnn, adOpenForwardOnly, , adCmdTable
                        rst.MoveFirst

                        While Not rst.EOF

                            If (txt_con_code.Text = rst!ProdCont And MyTempCustomerCode = rst!CustomerCode) Then'My important condition
                                          'Some action here
                                          'MsgBox ("Found")
                                          Exit Sub
                            Else
                                          'MsgBox (" Not Found") I thought putting the code here would not work as the loop is still on
                                          rst.MoveNext
                            End If

                           Wend
                           'Do I Place the code here
           End If
                           'Or maybe after the End if




i tried to implement an alternative code to trap the event where the criteria is not matched but this code segment is producing results


While rst.EOF
 rst.MoveFirst
        If (txt_con_code.Text <> rst!ProdCont And MyTempCustomerCode <> rst!CustomerCode) Then
                If rst.RecordCount = 0 Then
                        SundryLoadNumber = "1"
                        SundryAccLoadTon = Format(txtNettWeight.Text, "######.#0")
                End If
                Exit Sub
                End If
Wend


Any help?

Is This A Good Question/Topic? 0
  • +

Replies To: working with while loop

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3491
  • View blog
  • Posts: 11,910
  • Joined: 12-December 12

Re: working with while loop

Posted 30 April 2013 - 05:39 AM

If you want to know if something exists in the SundryProduct table then use a WHERE clause in a SQL statement.

Also, when looping a recordset, you need to have .MoveNext at the bottom of the loop, not nested in an if-statement. Without this, it is possible for the loop never to end.
Was This Post Helpful? 1
  • +
  • -

#3 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: working with while loop

Posted 02 May 2013 - 04:00 AM

Thanks so much i am now using the select statements
Was This Post Helpful? 0
  • +
  • -

#4 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: working with while loop

Posted 03 May 2013 - 05:21 AM

i got stuck with the select statements so I finally went back to the loop and now the code works .

If txtSun.Text = "SUN" Then
                   Set rst = New ADODB.Recordset
                   rst.Open "SELECT * FROM SundryProduct WHERE ProdCont='" & txt_con_code.Text & "' ", Cnn, adOpenForwardOnly, , adCmdText
                   rst.MoveFirst

                   While Not rst.EOF

                       If (txt_con_code.Text = rst!ProdCont And LTrim$(MyTempCustomerCode.Text) = rst!CustomerCode) Then
                                     SundryLoadNumber = rst!LoadNumber
                                     SundryAccLoadTon = rst!AccumTons
                                     Exit Sub
                       Else
                                     SundryLoadNumber = 1
                                     SundryAccLoadTon = Format(0, "######.#0")
                               
                       End If
                       
                                    rst.MoveNext
                        
                      Wend
                     
      End If




Was This Post Helpful? 0
  • +
  • -

Page 1 of 1