Unable to Edit and Update Recordsource

[tdrBates]

I'm trying to change the LINEABBR field to 18. Can anyone figure out what's wrong with this function? Thanks

BLOCKNAME LINEABBR NODEABBRA NODEABBRB
004-02 4 AIRP MLK
004-02 4 AIRP MLK
004-02 4 AIRP MLK
004-02 4 AIRP MLK
004-02 4 SPRI MLK
004-02 4 SPRI MLK
004-02 4 SPRI MLK
004-02 4 AIRP MLK
004-02 4 SPRI MLK
004-02 4 VARG H183
004-02 4 IH35 E7TH
004-02 4 IH35 E7TH
004-02 4 IH35 E7TH
004-02 4 IH35 E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 PVAL E7TH
004-02 4 TECH CENTR
004-02 4 TECH CENTR
004-02 4 TECH CENTR
004-02 4 TECH CENTR

Public Function basPairedRoutes()

Dim dbs As DAO.Database
Dim rstSrc As DAO.Recordset

Set dbs = CurrentDb
Set rstSrc = dbs.OpenRecordset("WithoutPairedRoutes", dbOpenDynaset)

With rstSrc
While Not .EOF
If ((!BLOCKNAME = "004-02" And _
!NODEABBRA = "SPRI" And !NODEABBRB = "MLK")) Then
'Edit Record
.Edit
!LINEABBR = "18"
.Update
End If
.MoveNext
Wend
End With
rstSrc.Close
Set rstSrc = Nothing
End Function

 

[PHV]

If LINEABBR is defined as numeric, replace this:
!LINEABBR = "18"
By this:
!LINEABBR = 18

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PaulF]

Try This

Dim dbs As DAO.Database
Dim rstSrc As DAO.Recordset

Set dbs = CurrentDb
Set rstSrc = dbs.OpenRecordset("WithoutPairedRoutes", dbOpenDynaset)

With rstSrc
If .RecordCount>0 then
.MoveFirst
Do While Not .EOF
If ((!BLOCKNAME = "004-02" And _
!NODEABBRA = "SPRI" And !NODEABBRB = "MLK")) Then
'Edit Record
.Edit
!LINEABBR = "18"
.Update
End If
.MoveNext
Loop
End If
End With
rstSrc.Close
Set rstSrc = Nothing
End Function


PaulF

 

[PHV]

Another way:
DoCmd.RunSQL "UPDATE WithoutPairedRoutes SET LINEABBR='18'" _
& " WHERE BLOCKNAME='004-02' And NODEABBRA='SPRI' And NODEABBRB='MLK'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tdrBates]

The DoCmd.RunSQL function works, but editing and updating the recordsource using the edit and update methods still do not work. Am I missing something?

Thanks for DoCmd.RunSQL function!

 

[tdrBates]

How might I use ADO instead of DAO in the function above? Thanks in advance for any assistance.

 

[RoyVidar]

He he - use an execute on the connection...

[tt]strSQL= "UPDATE WithoutPairedRoutes SET LINEABBR='18'" _
& " WHERE BLOCKNAME='004-02' And NODEABBRA='SPRI' And NODEABBRB='MLK'"
currentproject.connection.execute strSQL[/tt]

Point is - when it's possible to use a query, use a query! They will (almost) always outperform recordset approaches.

Else the ADO approach, as you'll find thru a search here, doesn't differ much from the DAO, though there seems to be a preference towards "splitting up" declaratins and assigning recordset properties (default DAO recordset is updateable, whilst default ADO recordset is readonly/forwardonly, so on need to set cursor and lock type).

Using a recordset on something like this (I'd probably used a query, though), I'd at least entered the criteria in the query string.

[tt]dim rs as adodb.recordset
dim strsql as string
set rs=new adodb.recordset
strsql="select LINEABBR from WithoutPairedRoutes " & _
"WHERE BLOCKNAME='004-02' And NODEABBRA='SPRI' And NODEABBRB='MLK'"
with rs
.activeconnection=currentproject.connection
.locktype=adlockoptimistic
.cursortype=adopenkeyset
.open strsql,,,,adcmdtext
do while not rs.eof
.fields("LINEABBR").value = "18"
.movenext
loop
end with
rs.close
set rs=nothing[/tt]

ADO does not have the .edit method, and contrary to DAO, it updates when a .move<record> is issued, but it's no harm throwing in an .update either (or do some batch stuff). Addressing the field can be done exactly as with DAO, or for instance as above.

- typed not tested

Roy-Vidar

 

[tdrBates]

Thanks. This is helpful.