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

  • (2 Pages)
  • +
  • 1
  • 2

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

#16 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 29 May 2014 - 01:36 AM

View Postastonecipher, on 29 May 2014 - 12:57 AM, said:

Post your current working code.


okay here it is..

End If
'----------------------------------------------------------------------------------Board 2nd Term Combo1

If Combo1.Text = "Boarding Fee Term Two" And Text33.Text <> "0.00" Then
     
If lol.RecordCount = 0 Then
   lol.AddNew
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermTwo") = Text33.Text
        sumBTwo = (boardingfee) - (Text33)
        lol.Fields("BalanceTwo") = sumBTwo
        lol.Update
Else

          lol.MoveFirst
          lol.MoveLast
          
    If lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then
        
        If IsNull(lol.Fields("TermTwo")) Then
        GetBTwo = 0
        Else
        GetBTwo = lol.Fields("TermTwo")
        End If

        SumOf2 = (GetBTwo) + (Text33.Text)
        sumBTwo = (boardingfee) - (SumOf2) '<---------------(1)
        
        If IsNull(lol.Fields("balanceOne")) Then
        GetBOne = 0
        Else
        GetBOne = lol.Fields("BalanceOne")
        End If
        
        SumOfBalTwo = (GetBOne) + (sumBTwo) '<---------------(2)

        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        
        lol.Fields("TermTwo") = SumOf2
        lol.Fields("BalanceTwo") = SumOfBalTwo
        lol.Update
        lol.Requery
        
    ElseIf Not lol.Fields("EnrolmentID") = frmStudentFees.AcctNo Then
    lol.AddNew
        lol.Fields("EnrolmentID") = frmStudentFees.AcctNo
        lol.Fields("StudentID") = frmStudentFees.txtstudID.Text
        lol.Fields("TransDate") = Date
        lol.Fields("CashierCompletename") = CurrentUser.FullName
        lol.Fields("TermTwo") = Text33.Text
        sumBTwo = (boardingfee) - (Text33)
        lol.Fields("BalanceTwo") = sumBTwo
    lol.Update
End If
lol.MoveNext

End If
End If



Was This Post Helpful? 0
  • +
  • -

#17 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,771
  • Joined: 26-March 09

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

Posted 29 May 2014 - 10:33 PM

I'm not that familiar with working with Access databases and have no idea which method you're using to connect to the database (or the schema), but normally if you wanted to check for the presence of an user, you would execute a select query to see if the user exists, then based on the result, decide whether to add or update a record. Looping through the whole table whilst there are only a few records might work, but imagine what it will be like if you had hundreds or thousands of records.....not very efficient.

The example below is intended as a pointer, it's untested and incomplete.


'Create SQL query
strSQL = "Select * from Table where EnrollmentID = " & UserIDToFind

'Open recordset based on query
rst.Open sSql, Cnn, adOpenForwardOnly, , adCmdText

'Check if we got something back
if rst.eof and rst.bof then
   'Record not found
   'Add new record here

else
   'Record found
   'Store the ID field for the record - we'll use it for update
   FoundID = rst![ID]

   'Execute your update query (Build this up accordingly).
   "Update Table Set Field1 = value1, Field2=Value2 where ID = FoundID"

Endif

This post has been edited by maj3091: 29 May 2014 - 10:36 PM

Was This Post Helpful? 0
  • +
  • -

#18 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 30 May 2014 - 01:48 AM

View Postmaj3091, on 29 May 2014 - 10:33 PM, said:

I'm not that familiar with working with Access databases and have no idea which method you're using to connect to the database (or the schema), but normally if you wanted to check for the presence of an user, you would execute a select query to see if the user exists, then based on the result, decide whether to add or update a record. Looping through the whole table whilst there are only a few records might work, but imagine what it will be like if you had hundreds or thousands of records.....not very efficient.

The example below is intended as a pointer, it's untested and incomplete.


'Create SQL query
strSQL = "Select * from Table where EnrollmentID = " & UserIDToFind

'Open recordset based on query
rst.Open sSql, Cnn, adOpenForwardOnly, , adCmdText

'Check if we got something back
if rst.eof and rst.bof then
   'Record not found
   'Add new record here

else
   'Record found
   'Store the ID field for the record - we'll use it for update
   FoundID = rst![ID]

   'Execute your update query (Build this up accordingly).
   "Update Table Set Field1 = value1, Field2=Value2 where ID = FoundID"

Endif


Oh okay, i had no idea that including the loop was in affective! Let me work on it. Thanks Ill keep you guys posted!
Was This Post Helpful? 0
  • +
  • -

#19 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 30 May 2014 - 04:12 AM

View Postnitroguy, on 30 May 2014 - 01:48 AM, said:

View Postmaj3091, on 29 May 2014 - 10:33 PM, said:

I'm not that familiar with working with Access databases and have no idea which method you're using to connect to the database (or the schema), but normally if you wanted to check for the presence of an user, you would execute a select query to see if the user exists, then based on the result, decide whether to add or update a record. Looping through the whole table whilst there are only a few records might work, but imagine what it will be like if you had hundreds or thousands of records.....not very efficient.

The example below is intended as a pointer, it's untested and incomplete.


'Create SQL query
strSQL = "Select * from Table where EnrollmentID = " & UserIDToFind

'Open recordset based on query
rst.Open sSql, Cnn, adOpenForwardOnly, , adCmdText

'Check if we got something back
if rst.eof and rst.bof then
   'Record not found
   'Add new record here

else
   'Record found
   'Store the ID field for the record - we'll use it for update
   FoundID = rst![ID]

   'Execute your update query (Build this up accordingly).
   "Update Table Set Field1 = value1, Field2=Value2 where ID = FoundID"

Endif


Oh okay, i had no idea that including the loop was in affective! Let me work on it. Thanks Ill keep you guys posted!


Hay thanks SOO much you were right about the Select query, you made me see the error in my code. Thank YOU!
Was This Post Helpful? 0
  • +
  • -

#20 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,771
  • Joined: 26-March 09

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

Posted 30 May 2014 - 05:00 AM

Glad it helped, but I didn't really tell you anything more than what astonecipher was already trying to tell you. :)
Was This Post Helpful? 0
  • +
  • -

#21 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 30 May 2014 - 10:40 AM

View Postmaj3091, on 30 May 2014 - 05:00 AM, said:

Glad it helped, but I didn't really tell you anything more than what astonecipher was already trying to tell you. :)/>


I didn't really understand him at first but i'm really really thankful that the both of took you the time to help me.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2