Is there a way of editing a record in a table with VBA?

[petna]

Hi all,

Pretty much a complete n00b to anything thats more advanced than a few tables and forms but have managed to write a whole load of VBA script which essentially returns values into strings from a whole load of stuff in excel.

Now my question is, can i take the value in my string from the VBA and use it to replace an existing value in a table where I can specify the field and record of which I would like to replace?

I'm just completely at a loss with this.

Many thanks,

petna

 

[KeithG]

Yes you sure can. Search the furom for Recordset for some examples.

 

[WayneRyan]

petna,

I don't have too many specifics from your post, but let's assume that you've got two
variables extracted from Excel --> lngPK and txtSomeField

They correspond to the numeric Primary Key of your table and some arbitrary text field.

DoCmd.RunSQL "Update YourTable " & _
             "Set SomeField = '" & txtSomeField & "' " & _
             "Where YourPK = " & lngPK

In a perfect world that would do it, BUT ...

You might have to use the DCount function to see if the record exists; doing an
INSERT if it doesn't.

You might have single-quotes in your data, in which case you could switch delimiters
from single to double quotes (or use a recordset).

hth,
Wayne

 

[petna]

Basically I have a table, "Turbine Parameters", that contains several fields, 2 of which are "Yaw Colour" and "Yaw Failure Date" and these are the ones I want to update. I am selecting the specific record set from another field called "Turbine Number".

In the VBA code I am returning values for "Yaw Colour" and "Yaw Failure Date" from an automated excel program which are currently held as a string and a date respectively.

The problem is this ..... I wish to pass updated values of "Yaw Colour" and "Yaw Failure Date" to the "Turbine Parameters" table. It can be done either through selecting the record from the "Turbine Number" field or by the record number. As I only have 5 (and will only ever have the same 5) selecting by record number is not an issue.

Hope this clarifies things a little more

Many thanks so far for the replies

 

[WayneRyan]

petna,

Something like this should work:

DoCmd.RunSQL "Update [Turbine Parameters] " & _
             "Set [Yaw Colour] = '" & YawColor & "', " & _
             "    [Yaw Failure Date] = #" & YawFailureDate & "# " & _
             "Where [Turbine Number] = " & TurbineNumber

That assumes that your VBA variables; YawColor, YawFailureDate, and TurbineNumber exist.

btw, those spaces in your table/column names will complicate life as you go on.

hth,
Wayne

 

[petna]

btw, those spaces in your table/column names will complicate life as you go on.

Why is that? What sort of things will it complicate?

Thanks for your help so far, your a life saver. Just hope i get it to work

 

[petna]

Thankyou very much WayneRyan thats worked an absolute treat!!! I can't tell you how much you've made me smile. Been tearing my hair out for hours wondering what to do

 

[WayneRyan]

Petna,

Glad to help!

For starters, the spaces require us to put the "[" and "]" around the
names. That gets cumbersome.

Additionally, there are times when the software will try and assist by
putting in the "_" character. [Some Field] and Some_Field get all
crossed up.

Also, other databases might not support the concept of a space
within a name.

It's better just to not deal with the space issue, as well as weird
characters and symbols like --> %#?

Just keep it simple, it's much easier in the long run.

See ya,
Wayne

 

[WayneRyan]

Petna,

Also, very important note !

When using "DoCmd.RunSQL ... ", you get NO feedback !!!

You will never know if you've updated 0 (or 10,000) rows.

You might want to use the DCount function to check if your TurbineNumber
exists. Otherwise, you'll be updating nothing (and not know it).

Wayne

 

[petna]

Everything should be ok for now. My whole system has got spaces in and is a pretty big thing to go back and change it all so will have to leave it like it is for now.

Its for my final year university project so hopefully when I submit it and its been graded, it will just sit there gathering dust like most things and never be used again.

 

[petna]

Petna,

Also, very important note !

When using "DoCmd.RunSQL ... ", you get NO feedback !!!

You will never know if you've updated 0 (or 10,000) rows.

You might want to use the DCount function to check if your TurbineNumber
exists. Otherwise, you'll be updating nothing (and not know it).

Wayne

It tells me that I'm about to update 1 row but would ideally like to turn this off so then the user just clicks and the program does its job. I don't ever require feedback as the records will never change apart from what I initially asked for

how do i go about turning off the update warning? its not something that i've ever needed to do before. is it just the echo command?

many thanks

 

[rainman89]

DoCmd.SetWarnings False is a way to hide those pesky warnings... dont forget to turn them back on though... if u want to that is

Its for my final year university project so hopefully when I submit it and its been graded, it will just sit there gathering dust like most things and never be used again.

Thats not good.... We dont like to hear that!!!

 

[petna]

Thats not good.... We dont like to hear that!!!

I know, it'll have taken me approx 200 hours to create!!! But dust seems to be a common problem with students work grrrrr but you never know, if it passes the seal of approval it may go on to be used for the purpose intended

 

[gemma-the-husky]

to refer to a field with a space you have to enclose it in square brackets

ie [my field]

if it doesnt have a space you can sometimes omit the brackets.

so it can sometimes be harder to find errors with field names containnig spaces, where the brackets are omitted. For readability a lot of people would use underscores or hyphens instead of spaces for this reason.

 

[voskouee]

I did a lot of replacements in my project and editing of values with VBA. its very easy.

are you ok with that or you need some examples from mine so you work on it?

let me know and i can send you some..

 

[petna]

thanks for the posts guys, is it a similar sort of proceedure if i would like to select a particular field for a record? So pretty much the same as writing as i have just done, but in reverse, so just reading the value into a string in vba?