Updating several records at once

[toddbingham]

Need to update several humdred records at once, would like to have an easy way of doing that witha select all check box or something like that. Look at the attachmentto see wha I have.

 

[Jack Cowley]

An Update query will do what you want. Search Access help for more details on Update Queries...

hth,
Jack

 

[MadMaxx]

In the OnClick of the check box use:

Dim db As Database
Dim rs as RecordSet
Dim rCount as Integer
Dim cnt as Integer
rCount = 0

Set db = CurrentDB();
Set rs = db.OpenRecordSet("Table")

rs.MoveLast
rCount = rs.RecordCount
rs.MoveFirst

Do Until rs.EOF
   rs.Fields("Checkboxfield") =  checkbox.value
   rs.update
loop

Don't know if it works or not. Time to go home so let me know and I will work on it tomorrow.


for

 

[toddbingham]

Here is my code. Get a compile error.

Dim db As Database
Dim rs As Recordset
Dim rCount As Integer
Dim cnt As Integer
rCount = 0

Set db = "Printed Checks"();
Set rs = db.OpenRecordset("Table")

rs.MoveLast
rCount = rs.RecordCount
rs.MoveFirst

Do Until rs.EOF
rs.Fields("Checkboxfield") = CheckBox.Value
rs.Update
Loop


Get an Expected End of Statement on this Line:
Set db = Printed Checks();

What seems to be the problem?

 

[Jack Cowley]

It should read:

Set db = CurrentDb

hth,
Jack

 

[WayneRyan]

Todd,

Dim db As Database 
Dim rs As Recordset 
Dim rCount As Integer 
Dim cnt As Integer 
rCount = 0 

Set db = CurrentDb   ' What is "Printed Checks"();  Your Db?
Set rs = db.OpenRecordset("Table") 

rs.MoveLast 
rCount = rs.RecordCount 
rs.MoveFirst 

Do Until rs.EOF 
   rs.Edit
   rs.Fields("Checkboxfield") = CheckBox.Value 
   rs.Update 
   rs.MoveNext
   Loop

Wayne

 

[MadMaxx]

heh. Sorry for misleading you with the ;. I was programming in C++ when I posted it.

 

[toddbingham]

Now I get an error that says invalid outside procedure. I have tried both putting it on the click properties of the check bow like:
Private Sub Check14_Click()

Dim db As Database
Dim rs As Recordset
Dim rCount As Integer
Dim cnt As Integer
rCount = 0

Set db = CurrentDb
Set rs = db.OpenRecordset("Table")

rs.MoveLast
rCount = rs.RecordCount
rs.MoveFirst

Do Until rs.EOF
rs.Edit
rs.Fields("Check14") = CheckBox.Value
rs.Update
rs.MoveNext
Loop
End Sub

And in the declarations part of the code. What should I do?

 

[MadMaxx]

Should have brackets at the end of your set statement.

Set db = CurrentDB()

Like that

 

[toddbingham]

same error

 

[MadMaxx]

rs.Fields("Check14") = CheckBox.Value

is Check14 the field name in the database or the name of a check box.

It mus be the field name in the table.

 

[toddbingham]

Posted a stripped down version here. Take a look.

 

[toddbingham]

Didnt post. Trying again.

 

[toddbingham]

Must be too big. Have a way I can send it to you? It is 4MB.

 

[MadMaxx]

madmaxx2k3@yahoo.ca

 

[namliam]

Nice double thread