Updating table from VBA (1 Viewer)

[widemonk]

Sorry if this has been covered many times, I just cant get to grips with it using others data and syntax... I never know what is a table, field name or the name of a control.

I want to update a particular record in a particular table with the results from a VBA variable.

In the table tblMySys I have...

MySysVariable (primary key)	MySysValue
FaveColour	Red
FaveFruit	Apple
FaveSoftware	MSAccess

Then I have...

Dim MyFaveFruit as String
MyFaveFruit = "Orange"

What comes next in the code to replace the word 'Apple' with 'Orange'.

Sorry its so very basic and does not have a practical application but this way I can understand what each aspect of the code is; which is a table/field name and which is an actual command/function.

 

[Isaac]

At that point you might run an Update query.
Currentdb.Execute "update tablename set [columnname] = '" & myfavefruit & "' where [columnname]=criteria

 

[Minty]

I would create an update query in the query designer then switch to SQL view.

This will give you the exact syntax Access expects.

If you get stuck converting that into a VBA query post back here.

 

[isladogs]

Not at all clear what you are doing but why not just use an update query?
Recommend you don't name your table as though it was a system table

 

[widemonk]

At that point you might run an Update query.

Currentdb.Execute "update tablename set [columnname] = '" & myfavefruit & "' where [columnname]=criteria

That might be worth playing with, thank you kindly.
Im guessing something like (and I havent tried yet so excuse the errors and give me chance to debug myself ) ...
Currentdb.Execute "update tblMySys set [MySysValue] = '" & MyFaveFruit & "' where [MySysVariable]=FaveFruit

I would create an update query in the query designer then switch to SQL view.

This will give you the exact syntax Access expects.

If you get stuck converting that into a VBA query post back here.

I use this technique a lot as a Record Source in my Forms, instead of saving a Query for each one of my forms, and the ComboBoxes within those forms.
Im sure I can make a query to return this exact record of this exact table. But extracting the field and changing it are (maybe) two different things.

Not at all clear what you are doing but why not just use an update query?
Recommend you don't name your table as though it was a system table

Thanks isladogs. Because Ive never tried them (yet!). My DBs so far have always been simple enough to never need them.
However, using a system table was very much intentional. I recently read a post elsewhere to create a tblSys for values like CompanyAddress etc.
That way when you create multiple reports, each will call that value with DLookup. If you need to change that value later on (lets say, because you relocated your business to larger premises), you only ever need to do it once and all reports are auto-updated with the new info. No need to edit every report.
Are you suggesting even in that situation I should still avoid system tables?

 

[Isaac]

I recently read a post elsewhere to create a tblSys for values like CompanyAddress etc.

Hmmmmmmmm. I would be very interested to read such a thing.

 

[theDBguy]

However, using a system table was very much intentional. I recently read a post elsewhere to create a tblSys for values like CompanyAddress etc.
That way when you create multiple reports, each will call that value with DLookup. If you need to change that value later on (lets say, because you relocated your business to larger premises), you only ever need to do it once and all reports are auto-updated with the new info. No need to edit every report.
Are you suggesting even in that situation I should still avoid system tables?

Hi. Pardon me for jumping in, but maybe what you read was about a "Settings" or "Preferences (i.e. Prefs)" table instead of a "System" table?

 

[isladogs]

System tables are used by Access 'to make things work' correctly.
Calling your table MySysVariable does not make it a system table though it may look like that due to its name.
It can be useful to create a 'settings table' to store certain data but that wouldn't be a system table.

EDIT I see DBG has written almost the same point

 

[widemonk]

Hmmmmmmmm. I would be very interested to read such a thing.

Its mentioned at the bottom of this post

Access/VBA Tutorials - Return to the same record next time form is opened

Tutorial: Return to the same record next time form is opened. A simple way to have your form 'remember' where it last was positioned in a recordset!

www.everythingaccess.com

Out of interest, the description in Step 1 of the link is incorrect. It says to have a Field Name as 'Value', which isnt allowed, its now a reserved keyword. (the post is dated 2005!) You might want to call the Field Names something like PrefsVariable, PrefsValue and PrefsDescription etc.

Hi. Pardon me for jumping in, but maybe what you read was about a "Settings" or "Preferences (i.e. Prefs)" table instead of a "System" table?

Its the link above that describes it as a system table but yes you are correct, it does really serve as a Settings function rather than a true 'system' table. For my own understanding, I'll just rename it tblSettings instead. Much better, thank you.

 

[Isaac]

Its mentioned at the bottom of this post

Access/VBA Tutorials - Return to the same record next time form is opened

Tutorial: Return to the same record next time form is opened. A simple way to have your form 'remember' where it last was positioned in a recordset!

www.everythingaccess.com

Very interesting. I read the article. I've read a lot of Allen Browne's stuff - great stuff - and this almost doesn't even sound like him. Who knows. I am unsure why he would have recommend that the table contain 'sys', nor be hidden. Anyway, storing commonly reuseable data is certainly a great idea. I don't want to nitpick.

 

[Pat Hartman]

USys is the normal prefix for user defined system tables and queries. They can easily be hidden but otherwise there is nothing special about them.

That being said, why would you use an update query or DAO/ADO to do the update when a "system" value needs to be changed? Why not just use a bound form so you don't need special code?

 

[Isaac]

USys is the normal prefix for user defined system tables

Good to know. I've never heard of that

 

[widemonk]

USys is the normal prefix for user defined system tables and queries. They can easily be hidden but otherwise there is nothing special about them.

I didnt know that either, thanks for the update.

That being said, why would you use an update query or DAO/ADO to do the update when a "system" value needs to be changed? Why not just use a bound form so you don't need special code?

I have a form to view the settings, but sometimes I want to change them using VBA as side effect from a completely different action, much like the link that describes how to return to the last viewed record in a form... hang on !!.. Duh !!

[SOLUTION] That's the very code I need. Head slap has just been self-administered!

Ive been thrown by the names of his table field names.
I havent checked it yet but hoping this will be it...

    Dim rs As DAO.Recordset

    Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset) 'Open table that contains user-settings
    With rs
        .FindFirst "[MySysVariable] = 'FaveFruit'"  'Find appropriate record
        .Edit          
          ![MySysValue] = MyFaveFruit 'Change appropriate field of that record to the new value
        .Update
    End With
    rs.Close
    Set rs = Nothing