Edit a Field in a table using SQL

Trevor G

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,847
Office Version
  1. 365
Platform
  1. Windows
I am seeking to use a module sheet to create a sub/function to update a field if it meets certain criteria

I have a table called = "GDV_Rates_Conventional_Sum"

and I have a fields called "No_Of_Risks_Sum"

What I need to do is fill a field if the following occurs

number of No_Of_Risks_Sum is above 100 Then place in 1
number of No_Of_Risks_Sum is Between 50 and 100 Then place in 2
number of No_Of_Risks_Sum is Below 50 Then place in 3

The field to update is called Credibility

Due to the database I am not allowed to create a query, I have to resort to VBA. Any help is greatly appreciated.
 
Sort by date Sort by votes
Trevor

You aren't allowed to create a query but you can use VBA, bizarre.:rolleyes:

The only way I can think that you'd be able to do what you want would require you to create recordsets/SQL etc

Are you allowed to do that?
 
Upvote 0
Norie said:
Trevor

You aren't allowed to create a query but you can use VBA, bizarre.:rolleyes:

The only way I can think that you'd be able to do what you want would require you to create recordsets/SQL etc

Are you allowed to do that?
Click to expand...

Yes I can use recordset.

I kind of understand that the code needs something like this, but I like most have limitations on my knowledge

sub fillfield
dim db as dao.db
dim tdf as dao.tabledef
dim rst as dao.recordset
dim sql as string

Besides this I think I could manage the Set commands
Will this help at all?
 
Upvote 0
Trevor

I don't think you need the tabledef, in fact when I think about it you might not even need the recordset.

You could just create the SQL in your code and then use DoCmd.RunSQL to execute it.

Kind of like running a query without actually creating one.:)
 
Upvote 0
Norie said:
Trevor

I don't think you need the tabledef, in fact when I think about it you might not even need the recordset.

You could just create the SQL in your code and then use DoCmd.RunSQL to execute it.

Kind of like running a query without actually creating one.:)
Click to expand...

Thanks for this, sadly its the RunSQL code I I dont know how to add. I understand that DoCmd is basically the MACRO side of the database, The reason why I can't use the new query build is that the database has been secured down, but I can with a permission access the VBA area
 
Upvote 0
Trevor

You don't need to create a query to use RunSQL.

Here's a small example.
Code: 
    strSQL = "CREATE TABLE Top500Imported (" & Join(arrFlds, ",") & ");"
    
    DoCmd.RunSQL strSQL
Ignore the actual SQL, it's just the first example I could find.:)

The important thing is building the appropriate SQL statement.

In the above example I'm creating a table using SQL, you'll probably want an UPDATE/INSERT query.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Getting Ghost lines in table probably due to using GetFocus to populate a value on new entry
2
Replies
10
Views
383
Edgar_
Edgar_
J
  • Solved
Having a problem in the expression builder, very new to Access. It thinks I'm trying to use Automation objects?
2
Replies
15
Views
1K
Micron
M
K
  • Solved
Error performing UPDATE query
Replies
5
Views
564
Joe4
Joe4
K
  • Solved
Building email recipient list for multiple addresses
Replies
8
Views
688
Micron
M
K
  • Solved
Automaticaly Sum multiple fields in a form
Replies
9
Views
1K
welshgasman
welshgasman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top