Hi Nicole,
First, does your code compile without any errors? With the VBE (Visual Basic
Editor) open, click on Debug > Compile ProjectName, where ProjectName is the
name of your VBA project (likely the same as the name of your database). You
should not get any compile errors when you do this. If you do, then you need
to fix those first. If the option to compile becomes greyed out, then that is
a very good sign--it means that your code compiled without any errors.
Note: Errors can include compile-time errors and run-time errors. The two
are different. A clean compile does not mean that all is well, but it's
certainly a good start.
I am a VBA dummy and don't know how to set a breakpoint and step through the
code.
To set a break point, click your mouse into the grey margin area on the
left-hand side, when you have the VBE open. Note: You cannot set a break
point on any Dim statements; you'll need to pick a different line of code.
When you've successfully set a break point, you should see a maroon colored
dot in the grey area. In addition, the line of code will be highlighted with
the maroon color. (The maroon color assumes that you have not set a different
color, under your VBE Options dialog). You can set break points of different
lines of code, so that you have multiple break points added.
Using a break point
Start the procedure {a procedure is a subroutine or function} using the
normal method. In this case, it would involve entering a name into your combo
box that is not in the list. This should fire the Not-In-List event
procedure. The code should stop at whatever line you set the break point on.
You can now single-step through the code, one line at a time, using the F8
key. Alternatively, you can advance to the next break point (or to the end of
the procedure) by pressing the F5 button.
Note: In order for break points to work, you must have the option in Access,
under Tools > Startup: "Use Access Special Keys" checked. If this option is
unchecked, the code will not stop at a break point.
Single step through your code until you can determine exactly which line of
code is causing the error. Your code indicates that you have a field name
that is exactly the same as your table name:
rs.AddNew
rs!Employees = NewData <-------------
rs.Update
Does your Employees table include a field named Employees? I suspect that
you might have a field named something like EmployeeName instead.
It will be helpful to add error handling to your code. Try pasting in this
revised version of the code, replacing your existing version. Save the
project after pasting in the code. Then make sure to compile your code, as
discussed earlier:
Private Sub Proj_Tech_Lead_NotInList _
(NewData As String, Response As Integer)
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Employee Name." _
& vbCrLf & vbCrLf & "Do you want to associate the new " _
& "Name to the current Employee List?" & vbCrLf & vbCrLf _
& "Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenDynaset)
'On Error Resume Next '<-----Comment out for now **************
rs.AddNew
rs!EmployeeName = NewData '<---Use the correct field name in your
case.
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error in Proj_Tech_Lead_NotInList event procedure..."
Resume ExitProc
Resume
End Sub
I have commented out a line of code shown above, which reads On Error
Resume Next. Also, note the extra Resume statement added at the very end:
Resume ExitProc
Resume <--------------
End Sub
Normally, this line of code will never be executed. However, in break mode,
you can drag the yellow arrow to this line of code, bypassing the normal
Resume ExitProc. If you now hit the F8 key one more time, generally, the
offending line of code that had the problem will be highlighted. If you find
the offending line of code, please post back indicating which line is
involved.
This procedure uses DAO (Data Access Objects) code. Therefore, you must have
a reference set to the "Microsoft DAO 3.6 Object Library" (use version 3.51
for Access 97). Here is more information on references in Access databases:
Solving Problems with Library References (Allen Browne)
http://allenbrowne.com/ser-38.html
Access Reference Problems (Doug Steele)
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
Finally, there is an outside chance that your DAO library is not correctly
registered on your PC. To re-register this library, click on Start > Run.
Enter the following command, and then press the Enter key:
Regsvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\Dao360.dll"
You should see a message indicating success. It does not hurt anything to
re-register this file even if it is already registered properly.
**Remember to get in the habit of clicking on Debug > Compile, whenever you
are editing VBA code. You'll want to correct any compile-time errors as they
occur, instead of letting them build up.
Good Luck, and please let us know how it goes!
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
