Updating fields on a table based on value entered in a form

[Santiago Gomez]

I would like to automatically update all records on a table based on what
the user enters in a form.

Table1
Name
Sex
Courses

on the form, when a user enters a number on txtCourse, I want to open the
table, go through each record, and insert the same number on all of them.

Furthermore, I want the code to be reusable, as I have 5 of these instances,
all going to different tables. If I name the control the same as the table,
would that help? then I can just pass the control name and insert it into
the sql command.

Please help.

this is what I have so far but I dont know how to update the record...

Private Sub UpdateTable(sTableName As String, dblValue As Double)
Dim rstnew As Recordset

Set rstnew = CurrentDb.OpenRecordset("select * from " & sTableName)
rstnew.MoveFirst
Do Until rstnew.EOF
rstnew.Update
? For Each Row In rstnew
? .Value = dblValue
? Next Row
rstnew.MoveNext
Loop

End Sub

 

[Santiago Gomez]

I guess this works a little better, but I still dont know how to change the
value to the control's value.
thanks

Public Sub UpdateTable()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.Open "tblSecondaryBus", Options:=adCmdTableDirect
.MoveFirst
Do Until .EOF
Debug.Print .Fields("Courses")
.MoveNext
Loop
.Close
End With
Set rst = Nothing
End Sub

 

[Santiago Gomez]

I finally got it, sometimes it is just good to put the problem in writing.
If anyone finds an error please let me know,
thanks


Public Sub UpdateTable()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

With rst
.MoveFirst
Do Until .EOF
.Fields("Courses") = Me.txtCourses
.MoveNext
Loop
.Close
End With
Set rst = Nothing
End Sub