What's Wrong with this Branching Statement?

[Bile]

-->>Hello World of VB 6 Programers...
-->>I've been using a block of Branching statement that worked fine in all the places I've used it...
-->>Now I came to develop this form thats when things started to look bad...
-->>I just check for the availability of a record in a database if exists then 'Error Masage'...
-->>Else i want the values to be store here is the code:

Private Sub btnAdd_Note_Click()
'**************************************************************************'
On Error GoTo errtrap
                  Call dbConnect

                  SQL = "SELECT * FROM GPATB WHERE ((GPATB.User_name) = '" & USER_NAME & "');"
                  RS.Open SQL, Conn, adOpenDynamic

                  If Not RS.EOF Then
                         RS.MoveFirst
                         Do While Not RS.EOF
'*********************************************************************'
'* THIS PART EXECUTE JUST FINE WHEN THERE IS A MATCHING RECORD FOUND *'
'*********************************************************************'          
                   If RS.Fields("User_name") = USER_NAME And RS.Fields("Study_year") = YEAR And RS.Fields("Semister") = SEMISTER Then
                    MsgBox "G.P.A For Year : " & YEAR & " Semister : " & SEMISTER & " already Exists.", vbOKOnly + vbCritical, "G.P.A Alredy Exists in SPGPAC - File."

                   End If

                         RS.MoveNext
                         Loop
'*********************************************************************'
'*    THIS PART DOESNT EXECUTE WHEN THERE IS NO MATCHING RECORD FOUND     *'
'*********************************************************************'
                  Else
                  Conn.Execute "INSERT INTO GPATB(User_name,Study_year,Semister,GPA,Comments) VALUES ('" & USER_NAME & "','" & YEAR & "','" & SEMISTER & "','" & GPA & "','" & GPA_CLASS & "');" 
                  MsgBox "G.P.A Information of Year " & YEAR & ",Semister " & SEMISTER & " Saved Succesful in SPGPAC - File.", vbOKOnly + vbInformation, "G.P.A Accepted in SPGPAC - File"  
              End If
                  Set RS = Nothing
                  Set Conn = Nothing
                  Exit Sub
errtrap:
      MsgBox Err.Description, vbCritical, "SPGPAC - File System Encountered an Error,You will be Loged out"
'**************************************************************************'
End Sub

-->>But when I change the code Like this:

Private Sub btnAdd_Note_Click()
'**************************************************************************'
On Error GoTo errtrap
                  Call dbConnect

                  SQL = "SELECT * FROM GPATB WHERE ((GPATB.User_name) = '" & USER_NAME & "');"
                  RS.Open SQL, Conn, adOpenDynamic

                  If Not RS.EOF Then
                         RS.MoveFirst
                         Do While Not RS.EOF
'*********************************************************************'
'*    THIS PART EXECUTE JUST FINE WHEN THERE IS A MATCHING RECORD FOUND     *'
'*********************************************************************'          
                   If RS.Fields("User_name") = USER_NAME And RS.Fields("Study_year") = YEAR And RS.Fields("Semister") = SEMISTER Then
                    MsgBox "G.P.A For Year : " & YEAR & " Semister : " & SEMISTER & " already Exists.", vbOKOnly + vbCritical, "G.P.A Alredy Exists in SPGPAC - File."

                     Set RS = Nothing 
                     Exit Sub                
'*******************************************************************************'
'*    THIS PART EXECUTE JUST FINE AS WELL WHEN THERE IS NO A MATCHING RECORD FOUND     *'
'*******************************************************************************'                            
                   Else
                  Conn.Execute "INSERT INTO GPATB(User_name,Study_year,Semister,GPA,Comments) VALUES ('" & USER_NAME & "','" & YEAR & "','" & SEMISTER & "','" & GPA & "','" & GPA_CLASS & "');" 
                  MsgBox "G.P.A Information of Year " & YEAR & ",Semister " & SEMISTER & " Saved Succesful in SPGPAC - File.", vbOKOnly + vbInformation, "G.P.A Accepted in SPGPAC - File"  
                   End If

                         RS.MoveNext
                         Loop
       End If
                  Set RS = Nothing
                  Set Conn = Nothing
                  Exit Sub
errtrap:
      MsgBox Err.Description, vbCritical, "SPGPAC - File System Encountered an Error,You will be Loged out"
'**************************************************************************'
End Sub

-->>1.So I was wondering what is wrong for the two codes that it works fine in other forms but only here?
-->>2.Is there any demages that can apper form either of the two code blocks?
-->>3.Which one seems to be the right way of Branching?
-->>Thanks...

[BitBlt]

Where to start?

In your first statement, I assume you might be retrieving more than one row. So that means that you might have a non-EOF condition based on user-name. However, as you cycle through the rows, you might not get a match based on the additional criteria of YEAR and "SEMISTER" (maybe "semester"?). Therefore you might get a "false positive" of having rows but no matches and no actual "damage". You can repair this by changing your SQL statement to include YEAR and SEMISTER in the selection criteria and not worrying about iterating through the recordset.

In your second statement, you cycle through your rows, and for each row that doesn't match your additional criteria, you will be adding in essence a duplicate row. So, for example, if I have 3 rows for user "JOE", but none for YEAR 2012, you will add 3 rows for JOE, all for 2012. Probably not what you're looking for. Therefore, your second statement will potentially do some serious damage to your data. Repair for this is similar to the repair for the first statement...include additional selection criteria in your SQL statement.

Hope this helps! Happy coding!

[tinstaafl]

In the first example you only call the code for non matching after you've reached EOF. In the second example you've put that code inside the while loop so that it executes before EOF.

The differences might have to do with different interpreters/compilers that you've used in the past. Each one has their own little quirks that need to be ironed out when you prot your code over.

[tinstaafl]

Took a little closer look. I think your first example can work if you remove line 25, and add an Exit Sub statement at line 17.

[Bile]

-->>Thaks tinstaafl and BitBit...
-->>I hope the additional Criteria FIXED the PROBLEM,I just change the SQL to look like:

SQL = "SELECT * FROM GPATB WHERE ((GPATB.User_name) = '" & USER_NAME & "') and ((GPATB.Study_year) = '" & YEAR & "') and ((GPATB.Semister) = '" & SEMISTER & "');"

-->>And everything was fine and Fixed by using the First block of code/Statemnts...
-->>But hey BitBit does this still ring a bell to You?
Click Here
-->>Thanks once again...

[BitBlt]

If this thread is solved, please mark it solved.

And yes, it does ring a bell. On that other thread, I gave you what I thought you needed to help you solve your dilemma. You didn't indicate that you had used it. I asked for additional info and never got it. Can't help if you don't provide the info.