Newbie requesting VBA help

[rjonas]

I am setting up an Access database where I'm running a number of update queries.. I would like to instead set up the query actions in a module where I can then simply call the procedure I need instead of clogging up my queries section with a bunch of update queries..

I've set up the procedures, however they're not doing anything... apparently the syntax is all correct but they're not doing what they should be doing...

I am sure it's something dumb that I'm missing... would appreciate help...

Here's an example of the function I set up to call the subroutine as well as the subroutine itself... Why isn't the subroutine working? (Yes, the DB is in a trusted location)

Public Function UpdateEDU()
Call Update_EDU
End Function

Public Sub Update_EDU()
strSql = "UPDATE [Order Detail] SET [Order Detail].PRACTICE = ""EDU"" " & vbCrLf & _
"WHERE ((([Order Detail].PRACTICE)=""IC - Other"") AND (([Order Detail].[Business Area Code])=""4J00""));"
End Sub

Thank you in advance for any advice/help!!

Ron

 

[Kryst51]

How are you calling the function?

 

[MarkK]

All you have there is the query text but you still need to execute the query. One way is to use the execute method of a DAO.Database, and you can get one of those using the CurrentDB() function ...

Public Sub Update_EDU()
  CurrentDB.Execute _
    "UPDATE [Order Detail] " & _
    "SET PRACTICE = 'EDU' " & _
    "WHERE PRACTICE = 'IC - Other' " & _
      "AND [Business Area Code] ='4J00';", dbFailOnError
End Sub

Note the use of the dbFailOnError option. This forces the query to raise an error if it fails.
Note that if there is only one table in your query then fields don't need to be prefixed with a table name.

 

[rjonas]

Thanks for the input! Will give it a try when I get home and will advise how it goes and if I have any other questions.

I've always been afraid of VBA and figure it's time to get over it.