Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2019, 09:33 AM   #16
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,623
Thanks: 59
Thanked 2,440 Times in 2,340 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Debug Not in List Event

Quote:
It is weird. the debug function highlights RunCommand acCmdSaveRecord but the record is saving just fine.
you got the error because Not In a List event is a Validation event. You don't put any code there to save the record, yet.

this is working without error:
Code:
' File MRR DLog skinny
Private Sub Combo53_NotInList(NewData As String, Response As Integer)
StrNew = NewData
strTmp = "Add '" & NewData & "' as a new category?"
DoCmd.SetWarnings False
 If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
       
       
DoCmd.RunSQL "INSERT INTO tblCategory " _
            & "(VendorName,Category) VALUES " _
            & "(" & Chr(34) & Forms!frmMRRLog!SupplierName & Chr(34) & "," & Chr(34) & NewData & Chr(34) & ");"
    
    'Neither fixed the issue, tried nothing and got same behavior, may need to add back???
    'Try different statement
     'Response = dbErrorAdded
     'Response = acDataErrContinue
    
    Response = acDataErrAdded
    
    'loops
    'With Forms("frmMRRLog").Controls("Combo53")
    '.ColumnWidths = "0.25 in.;0 in.;1 in."
    '.LimitToList = False
    'End With
    ' RunCommand acCmdSaveRecord
    'With Forms("frmMRRLog").Controls("Combo53")
    '.ColumnWidths = "0 in.;0 in.;1 in."
    '.LimitToList = True
    'End With
     
     'Me.Combo53.Requery


End If
DoCmd.SetWarnings True
End Sub
those code commented out are not needed.
when you set Response=acDataErrAdded, the combo will retain the New Category. Again, you are in Validation event, so you don't requery your combo.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
gakiss2 (07-18-2019)
Old 07-17-2019, 11:55 AM   #17
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Debug Not in List Event

Sorry for the confusion. The new category is definitely getting inserted into the table. Not only does it show up in the Combo box, I can also see it added to tblCategory when I open it.

And your clarification lets me know that it is a good idea to go ahead and try your method to see if it tells us anything additional.

Thank You for your help.
gakiss2 is offline   Reply With Quote
Old 07-17-2019, 12:00 PM   #18
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Debug Not in List Event

Quote:
Originally Posted by arnelgp View Post
you got the error because Not In a List event is a Validation event. You don't put any code there to save the record, yet.

this is working without error:
Code:
' File MRR DLog skinny
Private Sub Combo53_NotInList(NewData As String, Response As Integer)
StrNew = NewData
strTmp = "Add '" & NewData & "' as a new category?"
DoCmd.SetWarnings False
 If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
       
       
DoCmd.RunSQL "INSERT INTO tblCategory " _
            & "(VendorName,Category) VALUES " _
            & "(" & Chr(34) & Forms!frmMRRLog!SupplierName & Chr(34) & "," & Chr(34) & NewData & Chr(34) & ");"
    
    'Neither fixed the issue, tried nothing and got same behavior, may need to add back???
    'Try different statement
     'Response = dbErrorAdded
     'Response = acDataErrContinue
    
    Response = acDataErrAdded
    
    'loops
    'With Forms("frmMRRLog").Controls("Combo53")
    '.ColumnWidths = "0.25 in.;0 in.;1 in."
    '.LimitToList = False
    'End With
    ' RunCommand acCmdSaveRecord
    'With Forms("frmMRRLog").Controls("Combo53")
    '.ColumnWidths = "0 in.;0 in.;1 in."
    '.LimitToList = True
    'End With
     
     'Me.Combo53.Requery


End If
DoCmd.SetWarnings True
End Sub
those code commented out are not needed.
when you set Response=acDataErrAdded, the combo will retain the New Category. Again, you are in Validation event, so you don't requery your combo.
OK, I will try that in the morning. busy day tomorrow so I may not get back to you all until Friday. But be sure I am very appreciative of your assistance.

Gary

gakiss2 is offline   Reply With Quote
Old 07-18-2019, 03:52 AM   #19
gakiss2
Newly Registered User
 
Join Date: Nov 2018
Posts: 78
Thanks: 15
Thanked 0 Times in 0 Posts
gakiss2 is on a distinguished road
Re: Debug Not in List Event

Yes, It worked on the MRR DLog output db. Thank You Sir.

gakiss2 is offline   Reply With Quote
Reply

Tags
not in list , runtime 2113 , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not In List Event to improve UX of Edit List Items MS ACCESS PROBZZZ Forms 20 09-04-2018 11:53 AM
VBA List Only Select Query Names to Debug Window Trevor G Modules & VBA 3 04-16-2012 05:47 PM
Limit to List and using the On Not In List Event and error question. r3df1sh Forms 2 10-20-2004 05:26 AM
Not in List Event Joshann Modules & VBA 0 07-01-2003 06:34 PM
Not In List Event Taxcop Forms 2 09-07-2001 08:53 AM




All times are GMT -8. The time now is 07:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World