NotInList Event Procedure Not Working

See my other post for question about the combobox's SQL statement for
RowSource property.

How do you know that the event procedure is not running? Did you try
breakpoints, as I suggested previously?

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"skyrise" u49824@uwe wrote in message news:921ca6589e67d@uwe...
Column Count = 2
Column Width = 0";2.073"

Ken Snell (MVP) wrote:
What are the ColumnWidths and ColumnCount properties?

Sorry, my mistake. The Locked Property is set to "No". I wish that was
the
[quoted text clipped - 14 lines]
Resume Combo13_NotInList_Exit
End Sub

The references to the table name match. I put in a break line on the first
line of the script. Tried to enter new text, and got the error message.

Ken Snell (MVP) wrote:
See my other post for question about the combobox's SQL statement for
RowSource property.

How do you know that the event procedure is not running? Did you try
breakpoints, as I suggested previously?

Column Count = 2
Column Width = 0";2.073"
[quoted text clipped - 6 lines]
Resume Combo13_NotInList_Exit
End Sub

This sounds to me as if the form is not connecting with the event procedure
for the combobox NotInList event. If the code is not stopping on the first
line of code in your event procedure, and instead you see the built-in error
message, then that definitely indicates that the event procedure is not
running.

Let's try a few things. Copy all your code for the NotInList procedure, and
put it in a notepad file. Then delete the code from the form's module. Save
and close the form. Reopen the form and open Visual Basic Editor. Paste the
code back into the module. This will allow the form to reconnect the
procedure to the correct event (assuming that the name of the procedure
matches the actual name of your combo box control).

Then, if that does not result in the code running the way you expect, put a
breakpoint on the following lines in your event procedure (see the lines
with ' **** at the end of the code step):

Private Sub Combo13_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo13_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The publication name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add Publication Name?") ' ****
If intAnswer = vbYes Then
strSQL = "INSERT INTO lkpPublicationName ([PublicationName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new publication name has been added to the list." _
, vbInformation, "Publication Name Added"
Response = acDataErrAdded
Else
MsgBox "Please choose a publication name from the list." _
, vbInformation, "Use Publication Name List"
Response = acDataErrContinue
End If
Combo13_NotInList_Exit:
Exit Sub ' ****
Combo13_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error" ' ****
Resume Combo13_NotInList_Exit
End Sub


Try entering a value not in the combo box's list, and see which breakpoint
is activated.
--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/



"skyrise" u49824@uwe wrote in message news:921cc0afc5940@uwe...
The table was renamed to lkpPublicationName. Missed changing that one
after
the FROM statement. No effect on the Event Procedure though.

When I go back to the form and try to input a new publication name, I get
the
Access default error message, "The text you entered is not in the list.
Select and item in the list, or enter text that matches one of the listed
items." So it seems like it is erroring rigt away and exiting the
procedure
through that Error Handler.

I put in a break point on the first line and tried to add new text.
Immediately got the error message.

Ken Snell (MVP) wrote:
Also, is your SQL statement correct for the combo box:

SELECT [lkpPublicationName].[PublicationNameID], [lkpPublicationName].
[PublicationName] FROM lkpJournalName;

You're selecting data from a table called lkpJournalName, but the fields
are
from the table lkpPublicationName ?

Sorry, my mistake. The Locked Property is set to "No". I wish that was
the
[quoted text clipped - 14 lines]
Resume Combo13_NotInList_Exit
End Sub

I did the copy and repaste with no change in outcome. I'm not familiar
enough with Breakpoints to know what to look for or expect from them when
using. I added the 3 breakpoints that you suggested and tried to enter new
text. No change in outcome. The default error message pops up to indicate
that the text is not in the established list.

I guess this forum doesn't allow attached files? I would attach a sample
copy database of what I'm trying to create.

Ken Snell (MVP) wrote:
This sounds to me as if the form is not connecting with the event procedure
for the combobox NotInList event. If the code is not stopping on the first
line of code in your event procedure, and instead you see the built-in error
message, then that definitely indicates that the event procedure is not
running.

Let's try a few things. Copy all your code for the NotInList procedure, and
put it in a notepad file. Then delete the code from the form's module. Save
and close the form. Reopen the form and open Visual Basic Editor. Paste the
code back into the module. This will allow the form to reconnect the
procedure to the correct event (assuming that the name of the procedure
matches the actual name of your combo box control).

Then, if that does not result in the code running the way you expect, put a
breakpoint on the following lines in your event procedure (see the lines
with ' **** at the end of the code step):

Private Sub Combo13_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo13_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The publication name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add Publication Name?") ' ****
If intAnswer = vbYes Then
strSQL = "INSERT INTO lkpPublicationName ([PublicationName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new publication name has been added to the list." _
, vbInformation, "Publication Name Added"
Response = acDataErrAdded
Else
MsgBox "Please choose a publication name from the list." _
, vbInformation, "Use Publication Name List"
Response = acDataErrContinue
End If
Combo13_NotInList_Exit:
Exit Sub ' ****
Combo13_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error" ' ****
Resume Combo13_NotInList_Exit
End Sub

Try entering a value not in the combo box's list, and see which breakpoint
is activated.
The table was renamed to lkpPublicationName. Missed changing that one
after
[quoted text clipped - 25 lines]
Resume Combo13_NotInList_Exit
End Sub

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200902/1

I think that I resolved the issue. (TY:scottmcd9999)

I selected the "Compact and Repair" function and enabled Macros on the
database. Both of those things has the NotInList Event Procedure working.

skyrise wrote:
I did the copy and repaste with no change in outcome. I'm not familiar
enough with Breakpoints to know what to look for or expect from them when
using. I added the 3 breakpoints that you suggested and tried to enter new
text. No change in outcome. The default error message pops up to indicate
that the text is not in the established list.

I guess this forum doesn't allow attached files? I would attach a sample
copy database of what I'm trying to create.

This sounds to me as if the form is not connecting with the event procedure
for the combobox NotInList event. If the code is not stopping on the first
[quoted text clipped - 49 lines]
Resume Combo13_NotInList_Exit
End Sub

--
Message posted via http://www.accessmonster.com