Visual Basic and Access

[dfwelch]

I've used Visual Basic with Excel to look through my spreadsheet and make changes to "records" based on certain criteria. The syntax I used was:
ActiveSheet.Cells(3, 14) = something
where this would refer to the 3rd row, 14th column in the active spreadsheet.

Is there a similar way for me to look at for testing purposes and then make changes to a certain field for certain records in Access using VB?

 

[luceze]

As far as I know there is not a way to go directly to a field in a table. You can however cycle through records and change the value.

An approach like this could work:


Dim rs As Recordset
Dim db As database
Set db = CurrentDb()
strSQL = "SELECT YourTable.YourField FROM YourTable;"
Set rs = db.OpenRecordset(strSQL)
For x=1 to 20
if x=20 then
rs.edit
rs!YourField= 100
rs.Update
end if
rs.MoveNext
next

This code will update the 20th record in the field "YourField" to 100.

It would probably be better to use an update query or even manually update the values than use VB.

HTH,
Eric

 

[dfwelch]

I think that the code you've given me is what I need, I'm gonna try it out.

The reason I don't use an update query is that my goal is to cycle through my table of 32,000 records and select random records based on certain criteria. Once the program gets to a record I want, it will set a Yes/No field to YES. THEN I will run an append query to add all the "selected" records to a new table.