Invalid Use of New keyword

[FeS2]

I put together an Access form that links to a simple table called faxination1. I was able to get the strSQL to run but the user has to hit the cmdSearch button for each record on the table. I am trying to get a recordset connected to the table so that I can set a loop to work until it reaches the end of the recordset. However, I get a compile error that says invalid use of New keyword and I can't figure out where I went wrong. I tried changing the Set rstFax = New Recordset to ... = New ADODB.Recordset but that causes lots of other problems. We have another DB that uses the same New keyword the same way I'm using it and it works fine. Here is my code:

Private Sub cmdSearch_Click()
Dim conDB As ADODB.Connection
Dim rstFax As ADODB.Recordset
Dim strSQL As String

Set conDB = CurrentProject.Connection

strSQL = "UPDATE [AM Completions] INNER JOIN Faxination1 ON [AM Completions].PHONE = Faxination1.Phone SET Faxination1.[Prov Type] = [am completions].[prov type], Faxination1.[Order Id] = [am completions].[order id] WHERE ((([AM Completions].PHONE)= " & Phone & "));"

Set rstFax = New Recordset
rstFax.CursorLocation = adUseClient
rstFax.Open strSQL, conDB, adOpenDynamic, adLockOptimistic

If rstFax.RecordCount > 0 Then

Do While Not rstFax.EOF

DoCmd.RunSQL strSQL

Me.Requery
Loop

Else
Exit Sub
End If


End Sub

 

[Eupher]

I rarely use ADO, so I'm not an expert at all - but I poked around in the help files and experimented a bit. I think if you change your initial declaration to "Dim rstFax as New ADODB.Recordset" and eliminate the Set statement it may work for you. Just curious, don't you need to open the recordset before you use its CursorLocation property?

HTH,

Ken S.

 

[RoyVidar]

I believe the correct syntax to be:

[tt]Set rstFax = New adodb.Recordset[/tt]

Reason, you probably have both DAO and ADO references checked, and you'll need to explicitly declare and instantate the recordset with fully qualified references. The reason why it might work in another database, without fully qualifying, is probably the order of the references (VBE - Tools | References), but that's a bit dangerous to rely upon.

I'm also a fan of explicitly closing and releasing the variables at the end of the routine:

[tt]rstFax.close
set rsFax=nothing
set conDB = nothing[/tt]

Another tip is not to rely upon the ADO recordcount property, though it should probably work with the selected cursor, but rather use:

[tt]If ((not rstFax.bof) and (not rstFax.eof)) Then[/tt]

But I really don't understand what you are doing here.

You are first trying to open a recordset based on an update statement (wich probably won't return any recordset), then you're using the RunSql method of the DoCmd object to execute the same update statement?

If you're trying to update the table based on the forms recordset, why not fetch the form recordset, and loop it. Unless you're working with an ADP, or you've explicitly set the form recordset to be ADO, it will be DAO.

[tt]dim rs as dao.recordset
set rs = me.recordsetclone
rs.movefirst
do while not rs.eof
' do different things
rs.movenext
loop
set rs=nothing[/tt]

Roy-Vidar

 

[FeS2]

After I posted the message I relized that I was running the wrong SQL statement to load into the recordset. I have corrected that to just a SELECT statement and then I am using the UPDATE SQL in the part I want repeated for each record in the form. The cloning of the forms recordset works for what I'm using it for, thanks for the tip on that. Thanks Roy-vidar and Eupher