Trying to create code to loop to run thru a table

[rew2009]

Trying to create code to loop to run thru a table but I get an error "Argument not optional" and it highlights my EOF function. The following is the code I am using:

DoCmd.GoToRecord , , acFirst
Dim n As Integer
n = 1
Do While Not EOF
DoCmd.GoToRecord , , acNext
n = n + 1
Loop

Thanks

 

[dhookom]

I'm not sure what you are attempting to do. The EOF is genearlly used with a recordset. It looks like your code simple counts the number of records. This can be done with:

  N = DCount("*","tablename")

Maybe you could provide some context to what you are attempting to do.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

No, I am not trying to count records. I needed an engine to step through record by record the tables I am using to enable me to update the main table. I did not want to use an update query because the update is fairly complex and if I made a mistake I could seriously damage the main table. I feel I could control it better if I wrote the code and could step through the process for "n" number of records and test the results. Back in my dBase days this would have been a simple process with dBase3 code. Now I am trying to do it with Access vb code. I am in the initial stages of writing that code and need to flesh out the basic machine to start the process. I can test with say n=5 but when I want to run thru the entire table I want to have it stop at the EOF but I can't seem to get the syntax of the EOF function right. Thanks for the help Dhookom.

 

[PHV]

You have to learn how to use a Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You need to be a less shy about what you really want to accomplish. Your initial question doesn't provide near enough information to give you any kind of decent answer.

I generally use DAO in Access however you can also use ADO.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String
Dim intN as Integer
Set db = Currentdb
strSQL = "SELECT * FROM tblName"
Set rs = db.OpenRecordset(strSQL)
With rs
  Do Until .EOF Or intN = 5
    intN = intN + 1
    'do something here like
    .edit
      !FieldA = intN
      !FieldB = intN * 100
    .Update
    .MoveNext
  Loop
  .Close
End With
Set rs = Nothing
Set db = Nothing
MsgBox "Finished processing " & intN & " records."

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

dhookom, I ran your code from within a test form using a local smaller version the Client table that I will eventually update and it worked fine (a good sign!). However, the actual table that I need to update is on a SQL server and I think I need to use ADO. I made the change to "ADO" in the code and used another backup table that resides on the server which is an ADO table (I think is - it is represented by the little "world globe" icon), but I got the following error:

"User defined type not defined" and it highlighted the ADO lines of the code.

When I changed back to "DAO" I got the following error:

"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that as an IDENTITY column."

The SQL table had a ID column but the local version that worked with your code did not. The following is your modified code that I used for the ADO version:

'Dim db As DAO.Database
'Dim rs As DAO.Recordset

Dim db As ADO.Database
Dim rs As ADO.Recordset

Dim strSQL As String
Dim intN As Integer
Set db = CurrentDb
strSQL = "SELECT * FROM Client"
Set rs = db.OpenRecordset(strSQL)
With rs
Do Until .EOF Or intN = 5
intN = intN + 1
.Edit
!Field9 = intN
!Field10 = intN * 100
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
MsgBox "Finished processing " & intN & " records."

End Sub

I hope I wasn't too shy this time. I am always hoping for very simple code like in my dBase3 days. But there is no better way of learning the correct coding than seeing it done for your particular application.

Also, I need to open a second table to update the first from, but I wanted to get the original code working first for one table.

Thanks

 

[kjv1611]

I'm thinking you need to look specifically at ADODB, since you're dealing with a SQL Server table. That's what I've ended up having to use in the past.

However, being that you're talking VBA for your question, it probably needs to be over in the forum:

http://www.tek-tips.com/threadminder.cfm?pid=705

--

"If to err is human, then I must be some kind of human!" -Me

 

[rew2009]

Sorry, I don't follow you. I am working within an access form and am using that code in a command button. Where is the "VB editor window"? I can't find it in the tool bars.

Thanks

 

[dhookom]

You are working in the Microsoft Visual Basic editor to enter all of your code.

You can use DAO but your code with a linked SQL Server table should be

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim strSQL as String
  Dim intN as Integer
  Set db = Currentdb
  strSQL = "SELECT * FROM Client"
  db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
  [green]'...etc...[/green]

Duane
Hook'D on Access
MS Access MVP