Updating A Table

[PC User]

I'm trying to update a table using DAO recordsets. The recordset update works partially, but when the process has completed the data has not changed. There must be something wrong with the recordset code. Also by looking at the Debug.Print results, it looks as though it didn't process all the symbols in the table. There's no error message on these problems; so I'm not sure what the cause of this could be. Can someone see anything that can help?

 

[llkhoutx]

I can';t open a zip, Dufus here, but off the foillowing

dim db as database
dim rs as dao.recordset

set db=currentdb
'open dynaset
set rs=db.openrecordset("TableName",dbopendynaset)


'adding a record
rs.addnew
rs.fields(1) = value1
rs.fields(2) = value2
'save the new record
rs.update

rs.close
db.close
set rs=nothing
set db=nothing

 

[PC User]

Thanks for your reply. The records are already existing and I needed to edit them so I used rs.edit instead of rs.addnew. Everything else is the same as what you show. I've attached the sample database in Access 2000 format without compressing it, so it should be easy to look at.

Thanks,
PC

 

[jdraw]

I opened your file and looked at it yesterday. I found a few things that \i\ll describe here.
(I'm at a different location at the moment and don't have Access available)

I found that you were using Mid function and extracting part of a "string" for some of the returned values. The issue is that these values don't get returned from Yahoo with enclosing quotes.

I also adjusted your records to select a specific record as compared to the use of the tblStockTicker.

I suggest
"SELECT * FROM tblStockTicker " _
& " WHERE TickerId ='" & strSymbol &"'"

You could make tickerId the primary key for tblStockTicker.

I don't use the Yahoo Financials, nor anything else using an xml/http GET request. However, I think there must be some sort of return code sent with the message that could be checked after the request is returned.

I found that when executing the Update, sometimes there was an error in the request or there was a response error. I couldn't trap it, but I'm guessing there is a return code that should be checked.

I have an updated mdb on my machine at home that I'll send later (tonight).

I don't understand your subform. The way the data is processed you'll never see the subform (individual results).

 

[llkhoutx]

You're doing this wrong.

Your table being updated is the recordsource for the subform.

Displaying tblStockTicker records in a subform and updating all records are 2 different things. You display one record in the subform, but attempt to update all.

All you need is the form button. Displaying one record while updating all is doing useless work. Without knowing or digging into your record locking settings, I suspect displaying the one record locked your entire table, preventing updates.

 

[PC User]

I've implemented your suggestions and its seems to have made some progress with the output; however, by looking at the Debug.Print in the Immediate Window the process doesn't look like it cycles through all the symbols. There still isn't any error message to give me some clue to solve the problem. See attached changes.

 

[Noewon]

i'm having a similar issue. I'm trying to update the values of a field in a table, but the changes just aren't sticking.

The code posted looks pretty much the same as the example above, so i'm still stuck as to where i'm going wrong.

t_invoice is a table with one field and one record.
If anyone can suggest another way of storing a unique incrementing number (to be used for numbering invoices) that'd be great....

-------------
Function Get_Invoice_Number() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fd As DAO.Field
Dim n As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("t_invoice")
rs.MoveFirst
' increments the invoice number
n = rs!Number
Inc n ' this is just a simple n = n + 1 function
rs.Edit
rs!Number.value = n
' converts invoice number to string and trims leading spaces, returns the new invoice number
Get_Invoice_Number = LTrim(Str(rs!Number))

rs.Close
db.Close
End Function


edit: it appears I have it... "rs.update" saves changes...
gotta love learnin this on the fly

 

[PC User]

Try something like this:

Function Get_Invoice_Number() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fd As DAO.Field
Dim n As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("t_invoice", dbOpenDynaset)
rs.MoveFirst
n = 1
Do Until rs.EOF
with rs
.Edit
rs!Number = CStr(n)
.Update
End With
n = n + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Db.Close
Set Db = Nothing
End Function

Regards,
PC