vba update data in column

[martin461]

Hi,

I have code witch Sets up everything for my Database Access but I am having trouble being able to retieve data. My codes looks like this:

'declare database, recordset, columns, fields as objects
        Dim dbRequests As Object
        Dim recordsetItems As Object
        Dim fieldColumns As Object
        Dim fldEnumerator As Object
 
        'set up Database, recordset, columns, fields
        Set dbRequests = CurrentDb
        Set recordsetItems = dbRequests.OpenRecordset("student Data")
        Set fieldColumns = recordsetItems.Fields
 
        ' Scan the records from beginning to each
        While Not recordsetItems.EOF
            ' Check the current column
            For Each fldEnumerator In recordsetItems.Fields
                ' If the column is named Student_ID
                'If fldEnumerator.Name = "Student_ID" and Then
                    'if  recordsetItems.MoveNext
                    'move to last record
                    recordsetItems.MoveLast
                    'set variable to store auto Number
                    Dim lastRecord As String
                    'store our value
                    lastRecord = recordsetItems("DOB").Value
                    'increment auto Number
                    lastRecord = lastRecord + 1
                    'display Auto number in textbox1
                    TextBox1.Text = lastRecord
                End If
            Next
            ' Move to the next record and continue the same approach
            recordsetItems.MoveNext
        Wend
    'close recordset
    recordsetItems.Close
    'clear recordset
    Set recordsetItems = Nothing
End Sub

I need the code to move through the table until a particular student id is found, then it should look it should enter a new DOB that I have stored in a text box near by. Hope this makes sense.

I think the problem is I cannot move through the items until I have located the appropriate student ID. Once I have this I have to tell vba to go to the DOB field and update it.

Cheers.

 

[martin461]

Quick update on the code I have so far:

        'declare database, recordset, columns, fields as objects
        Dim dbRequests As Object
        Dim recordsetItems As Object
        Dim fieldColumns As Object
        Dim fldEnumerator As Object
 
        'set up Database, recordset, columns, fields
        Set dbRequests = CurrentDb
        Set recordsetItems = dbRequests.OpenRecordset("Support Services Data")
        Set fieldColumns = recordsetItems.Fields
 
        ' Scan the records from beginning to each
        While Not recordsetItems.EOF
            ' Check the current column
            For Each fldEnumerator In recordsetItems.Fields
                ' If the column is named Student_ID
                If fldEnumerator.Name = "Student_ID" Then
                    If recordsetItems("student_ID").Value = student Then
                    'change our value
                    recordsetItems.Edit
                    recordsetItems("new_reduction").Value = reduction
                    'change our value
                    recordsetItems("new_entitlement").Value = entitlement
                    recordsetItems.Update
                    End If
                End If
            Next
            ' Move to the next record and continue the same approach
            recordsetItems.MoveNext
        Wend
    'close recordset
    recordsetItems.Close
    'clear recordset
    Set recordsetItems = Nothing

Now the code doesn't seem to do anything, it should write the two new values into the Database. Any idea, anyone???

Regards.

 

[martin461]

the above code works, sorry, but it was taking a while to update and the form displaying th results needed refreshing. Ty.

 

[DCrake]

What you are doing seems to me to be over complicated. Where did you pick up this apporach from? This is not the normal vba syntax for what you are doing. This is my approach

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("Select * From [Support Services Data] WHERE [StudentId] = " & Student)

If Not Rs.EOF And Not Rs.BOF Then

   Rs.Edit
   Rs("New_Reduction") = Reduction
   Rs("New_Entitlement") = Entitlement
   Rs.Update
   Rs.Close
End If
Set Rs = Nothing

David