Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    How to loop (Access 2000)

    I want to update all the controls in a form called Students and containing above 200 different students.
    The problem is that i want to update these fields through a function. The function is rather conplicated but it works in other instances of my programme, so i will call if fot the sake of clarity as MyFunction. I want to update the field Room, which format is Currnecy with my function. My form is based on the table Students and the table students has a field called Room. The form also has a field called Room.
    The problem is that when i click the code shown below, all the fields in the table Students and also in the form, are updated with the
    value ony for the first Student.
    Here is my code:

    Private Sub Command594_Click()

    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Students")
    Do While Not rst.EOF
    db.Execute "UPDATE products SET Room = '" _
    & MyFunction _
    & "' WHERE StudentID = " & rst!StudentID
    End Sub

    I am afraid i am not very clear so i want to repeat my problem. For example my function calculates that the amount for the student Number 1 is Dollar 2,30. It is true, but it calculates the same amount for the Student number 2, and so on.And at last i receive that all students have the same amount of Dollars 2,30.In short the function is is true only for the first Student but then all the other students receive the same amount,which is not true. I see that the problem is somehwere in the looping, i have to make my code loop though all the recrods otherwise i receive funny results.
    Can somebody help me with seeting my function to work ?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Sint Niklaas, Belgium
    Thanked 0 Times in 0 Posts

    Re: How to loop (Access 2000)

    I don't see any argument passed to the function. So if your function read some data from the form, it get the data from the record that is displayed when you run the function. And with & "' WHERE StudentID = " & rst!StudentID it will change every record of the recordset
    Two possibilities to correct this:
    change the sql to:
    db.Execute "UPDATE products SET Room = '" _
    & MyFunction _
    & "' WHERE StudentID = " & Me!StudentID
    where Me!StudentID the name is of the control containing StudentID on the form

    or you can put the following in the on current event of the form :
    Me!Room = MyFunction().
    and put the same line in the afterupdate event of every textbox that is involved to calculate Room

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: How to loop (Access 2000)

    You might be better off calling your function in an update query and passing it the StudentID as an argument. That would also eliminate the need to move through the recordset.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts