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

Using a VB function to update records

P: n/a
Sid
Hi,
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
X_From_BrgDist([clnBearing],[clnDistance],[clnViewPointLongitude],[clnViewPointLatitude)
FROM tblSurvey1MinedArea INNER JOIN tblSurvey1 ON
[tblSurvey1MinedArea].[clnSurveyGUID]=[tblSurvey1].[clnGUID]);

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.

Regs
Sid
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
code:

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
method
before moving to the next record or changes are lost.

to access fields in your table just use:

rs!<name_of_your_field>

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.