When trying to run the following code, I’m getting an error when it hits Dim qdf As QueryDef saying
User-defined type not defined
I think it’s really weird because I have a backup of this same project in which not many changes were made after it which is working completely fine for this. I’ve tried to retrace my steps and am struggling with that.
Any clue on what I might be missing? I’ve already tried the common suggestion of Tools > References and adding from there, and I had it working without that before anyway.
Private Sub Complete_Click()
Set objAD = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objAD.UserName)
strDisplayName = objUser.DisplayName
Dim dtTrained As String
Dim sprTrained As String
Dim compCheck As String
dtTrained = InputBox("Enter date trained as 'mm/dd/yyyy':", "", Format(Date, "mm/dd/yyyy"))
Debug.Print dtTrained
sprTrained = InputBox("Trained By:", "", strDisplayName)
Debug.Print sprTrained
compCheck = InputBox("How was competency verified?", "", "Enter here")
Debug.Print compCheck
Dim ConfirmMsg, ConfirmStyle, ConfirmTitle, ConfirmResponse
ConfirmMsg = "Continue?"
ConfirmStyle = vbYesNo
ConfirmTitle = " "
ConfirmResponse = MsgBox(ConfirmMsg, ConfirmStyle, ConfirmTitle)
If ConfirmResponse = vbYes Then
Const dbConnStr = "CONNECTION STRING IS HERE"
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("")
Dim sqlString As String
sqlString = "INSERT INTO dbo.TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS, COMPETENCY) " & _
"SELECT '" & EMPLOYEE_ID.Value & "', '" & DOCUMENT_ID.Value & "', '" & LATEST_REV.Value & "', '" & dtTrained & "', '" & sprTrained & "', 'C', '" & compCheck & "'"
qdf.sql = sqlString
qdf.ReturnsRecords = False
qdf.Connect = dbConnStr
Debug.Print sqlString
qdf.Execute
'CurrentDb.Execute "INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, EMPLOYEE_NAME, DOCUMENT_ID, FILENAME, REVISION, DATE_TRAINED, TRAINED_BY, STATUS) " & _
'"SELECT EMPLOYEE_ID, EMPLOYEE_NAME, DOCUMENT_ID, FILENAME, REVISION, DATE_TRAINED, TRAINED_BY, STATUS FROM uSysTRAINING_RECORDS " & _
'"WHERE DOCUMENT_ID = '" & DOCUMENT_ID.Value & "'"
CurrentDb.Execute "INSERT INTO TRAINING_RECORDS (EMPLOYEE_ID, DOCUMENT_ID, REVISION, DATE_TRAINED, TRAINED_BY, STATUS) " & _
"SELECT * FROM uSysTRAINING_RECORDS " & _
"WHERE EMPLOYEE_ID = '" & EMPLOYEE_ID.Value & "'"
CurrentDb.Execute "DELETE FROM TRAINING_NEEDED " & _
"WHERE EMPLOYEE_ID LIKE '" & EMPLOYEE_ID.Value & "' AND DOCUMENT_ID LIKE '" & DOCUMENT_ID.Value & "'"
Forms!TRAINING_MATRIX.TRAINING_NEEDED_SUBFORM.Form.Requery
Forms!TRAINING_MATRIX.TRAINING_RECORDS_SUBFORM.Requery
Forms!TRAINING_MATRIX.ALL_TRAINING_RECORDS.Requery
Else
End If
End Sub
Advertisement
Answer
Because QueryDefs like TableDefs, Recordsets, and even Database are objects under Access’ Database Object (DAO) library, you need to have your MS Access project reference the library. Otherwise, VBA cannot interact with such objects, properties, and methods. DAO is the default database API of MS Access and very important to have selected if developing VBA modules among other defaults as shown below:
- Visual Basic for Applications
- Microsoft Access #.0 Object Library
- OLE Automation
- Microsoft Office #.# Access database engine Object Library
By contrast, ADO is a different database API requiring a different, non-default library: Microsoft ActiveX Data Objects #.# Library. In fact, as shown above, VBA itself is a reference as first checked item!
Actually, the full named reference of DAO objects would be prefixed with DAO to underscore its source. It is shorthand as you do to leave it out.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set rst = db.OpenRcordset("SELECT * FROM myTable")
Set tdf = db.TableDefs("myTable")
Set qdf = db.QueryDefs("myQuery")