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

Sub or Function not defined

P: 5
Hi all! Maintaining a database that was created by someone else, and I tried running a compile to be sure the code is all good. However, I got a "Sub or Function not defined" error on the following, where "Update" is highlighted:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_GotFocus()
  2. Update AutoTbl
  3. Set AutoTbl.AutoStatus = "Expired"
  4. WHERE AutoTbl.AutoStatus = "Not Compliant" Or AutoTbl.AutoStatus = "Approved" And AutoTbl.AutoExpDt <= Now()
  5.  
  6. End Sub
When I place cursor over AutoTbl, the definition comes back as "Identifier under cursor is not recognized".

There is a table in the database "AutoTbl," but to be honest, I am not that knowledgeable about VBA and as to why it is not recognizing the table.

Appreciate any help you can give me. I tried searching for the answer, but didn't see another question that seemed the same. Basically, Bytes.com is my "teacher" for using VBA, since I don't have any formal training.

Again, thank you.
Jun 28 '17 #1

✓ answered by NeoPa

Hi Evelyn.

I can't see how that ever worked as you have it. The code inside the VBA procedure definition is actually SQL code. To get SQL code to work (At least a common way) is to put it in a string and pass that string to a call to the Execute() method of a Database or TableDef object.

You're probably looking for something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_GotFocus()
  2.     Dim strSQL As String
  3.     Dim dbVar As DAO.Database
  4.  
  5.     strSQL = "UPDATE [AutoTbl] " _
  6.            & "SET    [AutoStatus]='Expired' " _
  7.            & "WHERE  (([AutoStatus]='Not Compliant') " _
  8.            & "   OR  ([AutoStatus]='Approved')) " _
  9.            & "  AND  ([AutoExpDt]<=Now())"
  10.     Set dbVar = CurrentDb()
  11.     Call dbVar.Execute(strSQL)
  12. End Sub

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,489
Hi Evelyn.

I can't see how that ever worked as you have it. The code inside the VBA procedure definition is actually SQL code. To get SQL code to work (At least a common way) is to put it in a string and pass that string to a call to the Execute() method of a Database or TableDef object.

You're probably looking for something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_GotFocus()
  2.     Dim strSQL As String
  3.     Dim dbVar As DAO.Database
  4.  
  5.     strSQL = "UPDATE [AutoTbl] " _
  6.            & "SET    [AutoStatus]='Expired' " _
  7.            & "WHERE  (([AutoStatus]='Not Compliant') " _
  8.            & "   OR  ([AutoStatus]='Approved')) " _
  9.            & "  AND  ([AutoExpDt]<=Now())"
  10.     Set dbVar = CurrentDb()
  11.     Call dbVar.Execute(strSQL)
  12. End Sub
Jun 28 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.