Please Help! Infinite loops when updating a record on Access via V

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1555 Views - Last Post: 30 May 2014 - 10:40 AM Rate Topic: -----

#1 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 04:43 PM

Okay my program is going into infinite loops when i try to update a previous record on Access via VB6. I simply coded it to check if the table is empty, it should create a new Student record. And if the table contains a record it should check whether that record matched the student ID if not it should create a new record as well else it should update the current record. The thing is it works just fine when you add three to four new Students, but the moment you try and update a previous student it crashes and goes through an infinite loop.

I'm still a beginner in coding, but i think my mistake is caused by the .movefirst .movelast .movenext and the while wend loop. but im not sure. Iv tried removing the While NOT recordset.EOF but all that does is prevent the
the records from updating. Iv been stuck for four days strait, tried searching similer threads on google but no luck so far. i just do know what to do. I could really use the advise, help or a point at the right direction. Thanks here is my code.

 If Combo1.Text = "Boarding Fee Term One" And Text33.Text <> "0.00" Then

 If lol.RecordCount = 0 Then                                        '<----Checks if field has a Null Value 
    lol.AddNew                                                      
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo           
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = Text33.Text
        sumBOne = (boardingfee) - (Text33.Text)
        lol.Fields("BalanceOne") = sumBOne
    lol.Update
 Else
                                                                   '<----This While code scans the entire table to
  While Not lol.EOF                                                
        lol.MoveFirst                                              
        lol.MoveLast                                               
                                                                   
    If lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then
        
        If IsNull(lol.Fields("TermOne")) Then
        GetBOne = 0
        Else
        GetBOne = lol.Fields("TermOne")
        End If
        
        SumOf1 = (GetBOne) + (Text33.Text)
        sumBOne = (boardingfee) - (SumOf1)
        
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = SumOf1
       
        lol.Fields("BalanceOne") = sumBOne
        lol.Update
        
    
    ElseIf Not lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then '<--This code here Checks if there are any records
     lol.AddNew                                                        
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo             
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = Text33.Text
        sumBOne = boardingfee - Text33
        lol.Fields("BalanceOne") = sumBOne
    lol.Update

    End If
    
    lol.MoveNext
    Wend
    
 End If
End If




Is This A Good Question/Topic? 0
  • +

Replies To: Please Help! Infinite loops when updating a record on Access via V

#2 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 04:51 PM

Why do you need a loop at all? You are updating a single record if none exist, correct?
Was This Post Helpful? 0
  • +
  • -

#3 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 04:54 PM

View Postastonecipher, on 28 May 2014 - 04:51 PM, said:

Why do you need a loop at all? You are updating a single record if none exist, correct?


Cause it was the only way i could get it to update a current student record without it creating a new record that already exists.
Was This Post Helpful? 0
  • +
  • -

#4 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:00 PM

[quote name='nitroguy' date='28 May 2014 - 04:54 PM' timestamp='1401321285' post='2015546']

View Postastonecipher, on 28 May 2014 - 04:51 PM, said:

Why do you need a loop at all? You are updating a single record if none exist, correct?


Cause it was the only way i could get it to update a current student record without it creating a new record that already exists.

I knew its wrong. But iv tried shifting the While loop to just the update and that didnt help. Each time i tried to update a current student it creates another record. But when i add the while loop over it all it works but when you create a new record then the programs crashes.

I really need the advice. Like really really.
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:01 PM

Understand I am not a VB6 programmer.

Select the primary key to see if a record exists, if not create it? Databases don't have end of file, so it would never end your loop.
Was This Post Helpful? 0
  • +
  • -

#6 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:10 PM

View Postastonecipher, on 28 May 2014 - 05:01 PM, said:

Understand I am not a VB6 programmer.

Select the primary key to see if a record exists, if not create it? Databases don't have end of file, so it would never end your loop.


interesting....

but the primary key is auto increment, thats why I use

 if lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then  


condition for it to check if there is a new record or one that matches that Specific EnrolmentID id. To update.

but..i could try setting the EnrolmentId as a primary key...but what do i do about the While loop?
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:15 PM

I don't see why a loop is necessary at all. You can select the record using any number of queries,

Select feilds from table where search_criteria = string

Then you can get the unique id, provided it cannot be provided. If it is known a simple update is all that is required.
Was This Post Helpful? 0
  • +
  • -

#8 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:31 PM

View Postastonecipher, on 28 May 2014 - 05:15 PM, said:

I don't see why a loop is necessary at all. You can select the record using any number of queries,

Select fields from table where search_criteria = string

Then you can get the unique id, provided it cannot be provided. If it is known a simple update is all that is required.


Well, geus what. It doesn't work. I tried getting rid of the while loop and cleared the database. Here is what happened

>The first record was created
>I was also able to update the first Record
>And so I added a new record, and it was also created BUT when id tried updating it, gave me a

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to premit duplicate entries or try again"

>But the PART i dont understand is, if i update the FIRST record again it works just fine. But any other record below it they tend to create duplicate records.

sorry correction on "cleared the database." i meant cleared the Table
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:32 PM

You need the auto increment if no other unique identifier is available.


The only time you would need the unique identifier after that if for selecting, updating, and deleting the record.
Was This Post Helpful? 0
  • +
  • -

#10 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 05:39 PM

Think about it this way,


INSERT name INTO table (name) VALUES ('Bob')
INSERT name INTO table (name) VALUES ('John')
INSERT name INTO table (name) VALUES ('Jane')

SELECT * FROM table

With auto increment and an id field should return,

1 Bob
2 John
3 Jane

UPDATE table SET name = 'John' WHERE id = 2

SELECT name FROM table WHERE id = 2

Should return,

John


Understanding?
Was This Post Helpful? 0
  • +
  • -

#11 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 06:06 PM

View Postastonecipher, on 28 May 2014 - 05:39 PM, said:

Think about it this way,


INSERT name INTO table (name) VALUES ('Bob')
INSERT name INTO table (name) VALUES ('John')
INSERT name INTO table (name) VALUES ('Jane')

SELECT * FROM table

With auto increment and an id field should return,

1 Bob
2 John
3 Jane

UPDATE table SET name = 'John' WHERE id = 2

SELECT name FROM table WHERE id = 2

Should return,

John


Understanding?


i understand BUT let me put it this way. Observe the code carefully


If Combo1.Text = "Boarding Fee Term One" And Text33.Text <> "0.00" Then

 If lol.RecordCount = 0 Then                                        '<----Checks if field has a Null Value 
    lol.AddNew                                                      
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo           
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = Text33.Text
        sumBOne = (boardingfee) - (Text33.Text)
        lol.Fields("BalanceOne") = sumBOne
    lol.Update
 Else

'<----here I have removed the While loop along with the .movefirst .movelast                                             
                                                                   
    If lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then
        
        If IsNull(lol.Fields("TermOne")) Then
        GetBOne = 0
        Else
        GetBOne = lol.Fields("TermOne")
        End If
        
        SumOf1 = (GetBOne) + (Text33.Text)
        sumBOne = (boardingfee) - (SumOf1)
        
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = SumOf1
       
        lol.Fields("BalanceOne") = sumBOne
        lol.Update
        
    
    ElseIf Not lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then '<--This code here Checks if there are any records
     lol.AddNew                                                       '   that match the Enrolment ID if not, It create a
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo             '   new field in the table.
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = Text33.Text
        sumBOne = boardingfee - Text33
        lol.Fields("BalanceOne") = sumBOne
    lol.Update

    End If
    
   '<------Here iv also removed the .movenext
    
 End If
End If



With that code i can only update the FIRST record, BUT i cant do So with the 2nd Or 3rd and with the rest that follow below it. BUT if do this (Note the comments id added)


If Combo1.Text = "Boarding Fee Term One" And Text33.Text <> "0.00" Then

 If lol.RecordCount = 0 Then                                        '<----Checks if field has a Null Value 
    lol.AddNew                                                      
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo           
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = Text33.Text
        sumBOne = (boardingfee) - (Text33.Text)
        lol.Fields("BalanceOne") = sumBOne
    lol.Update
 Else
                                                                   
                                            
        lol.MoveFirst        '<-----Iv also added .Movefirst and .Movelast                                      
        lol.MoveLast                                               
                                                                   
    If lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then
        
        If IsNull(lol.Fields("TermOne")) Then
        GetBOne = 0
        Else
        GetBOne = lol.Fields("TermOne")
        End If
        
        SumOf1 = (GetBOne) + (Text33.Text)
        sumBOne = (boardingfee) - (SumOf1)
        
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = SumOf1
       
        lol.Fields("BalanceOne") = sumBOne
        lol.Update
        
    
    ElseIf Not lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then '<--This code here Checks if there are any records
     lol.AddNew                                                       '   that match the Enrolment ID if not, It create a
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo             '   new field in the table.
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermOne") = Text33.Text
        sumBOne = boardingfee - Text33
        lol.Fields("BalanceOne") = sumBOne
    lol.Update

    End If
    
    lol.MoveNext '<----Iv added .movenext
    Wend
    
 End If
End If




with those three codes that i have added.

Am able to add Student 1 and also update his record, student 2 and also update his record and so on BUT (here comes the confusing part). If i try to UPDATE student 1 record when i have ALREADY added 4 or more students int he table It tries to Duplicate the record.
Was This Post Helpful? 0
  • +
  • -

#12 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 06:11 PM

Trust me The EnrolmentID's Unique.
Was This Post Helpful? 0
  • +
  • -

#13 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 06:31 PM

The code you posted just duplicates itself.

if a = a then
 1 + 1
elseif a <> a then
 1 + 1 


Is a simplistic version of what you have.

Quote

19	    If lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then
20	         
21	        If IsNull(lol.Fields("TermOne")) Then
22	        GetBOne = 0
23	        Else
24	        GetBOne = lol.Fields("TermOne")
25	        End If
26	         
27	        SumOf1 = (GetBOne) + (Text33.Text)
28	        sumBOne = (boardingfee) - (SumOf1)
29	         
30	        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
31	        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
32	        lol.Fields("TransDate") = Date
33	        lol.Fields("CashierCompletename") = CurrentUser.FullName
34	        lol.Fields("TermOne") = SumOf1
35	        
36	        lol.Fields("BalanceOne") = sumBOne
37	        lol.Update
38	         
39	     
40	    ElseIf Not lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then '<--This code here Checks if there are any records
41	     lol.AddNew                                                       '   that match the Enrolment ID if not, It create a
42	        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo             '   new field in the table.
43	        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
44	        lol.Fields("TransDate") = Date
45	        lol.Fields("CashierCompletename") = CurrentUser.FullName
46	        lol.Fields("TermOne") = Text33.Text
47	        sumBOne = boardingfee - Text33
48	        lol.Fields("BalanceOne") = sumBOne
49	    lol.Update
50	 
51	    End If




When you loop you would be entering duplicate values for everything that was entered into the form.

Example with SQL statements.
Was This Post Helpful? 0
  • +
  • -

#14 nitroguy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 28-May 14

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 28 May 2014 - 11:40 PM

okay so i already removed the While loop. Its no longer there. So its not looping anymore. The problem is it only updates the first record and duplicates the rest even if:

The EnrolmentID in the Access table Matches the EnrolmentID of the current student i want to update.

instead of it comparing the enrolmentID with Current studentID

If lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then


And updating the current student. IT jumps to

If NOT lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then


and tries to create a new line.
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 564
  • View blog
  • Posts: 2,536
  • Joined: 03-December 12

Re: Please Help! Infinite loops when updating a record on Access via V

Posted 29 May 2014 - 12:57 AM

Post your current working code.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2