By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 1,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Using a VB function to update records

P: n/a
I have a VB function that calculates a spatial point from a bearing
and distance. I need to be able to update point co-ordinates (Lat and
Longs) in the Db with the function. I can call the function to do an
insert but not an update. I coded the sql as follows.

UPDATE x_CoordFix SET x_CoordFix.oldlat = (Select
FROM tblSurvey1MinedArea INNER JOIN tblSurvey1 ON

X_From_BrgDist() is the function that returns a Latitude point.

I receive an error "Operation must be an updatable query"

Has anyone any ideas what I've done wrong.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
Hi Sid,

You may be able to use an update query to do what you try to do, but I
don't know that much about those. What I would do is work with a
recordset, from code.

In order to open a recordset you must first include a reference to
Microsoft DAO Object Library (in the VB editor click Tools, then
References, browse down to Microsoft DAO 3.6 Object Library, and check
it). Then you declare the Recordset by writing the following line of

Dim rs As DAO.Recordset

"rs" is the name of the recordset, you may, and should, change it.
Next you build the rs by writing the following:

Set rs = CurrentDB.OpenRecordset("<Your Select Query goes here>")

Then you have several commands you can use on the recordset:

rs.MoveFirst - Moves to the first record in the recordset.
rs.MoveNext - Obviously, moves to the next...
rs.MoveLast - too obvious...
rs.Edit - Opens the current record for editing.
rs.Update - Saves changes you made to the table. You must use this
before moving to the next record or changes are lost.

to access fields in your table just use:


They'll behave almost like a variable, which is most conveniant.

Hope this helps...

Noam Nelke, Israel.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.