Question Debug Not in List Event (1 Viewer)

gakiss2 · Jul 16, 2019

Below is some code to allow user to enter a category into Combo53. It works for MRR DLog skinny but does not for MRR DLog Output skinny. for the 'Output version I had copied this form frmMRRLog then made mods and named it frmMRRLogALL, I only mention because that is the only thing I recall changing between the two.

Now I get an error message runtime 2113 - value isn't valid for the field after I enter a new value. It works fine if I select an item already on the list. And it is actually 'working' in that the new item that I am adding does get onto the list because you can click out of the error, delete the new item from the combo box, close the form then re-open it. Then when you click the combo, the new one you entered last time is there in the list and, of course you can select it. I've compared the code between the two dbs and it seems exactly the same to me, perhaps I've missed something. I guess I should consider myself lucky that MRR DLog works but I had made some good progress on MR DLog Output and would hate to lose it and if I don't know what went wrong I suppose I am likely to repeat the error.

I greatly appreciate any help on this issue.

Thank YOU

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 " _
& "(Forms!frmMRRLog!SupplierName,""" & NewData & """);"

'Neither fixed the issue, tried nothing and got same behavior, may need to add back???
'Try different statement
Response = dbErrorAdded
' Response = acDataErrContinue
'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
DoCmd.SetWarnings True
End If
End Sub

theDBguy · Jul 16, 2019

Hi. Just a guess but in the following section:

Code:

Response = dbErrorAdded
 ' Response = acDataErrContinue

Try commenting out the first one and uncomment the second one.

On second thought, try using this instead:

Code:

Response = acDataErrAdded

gakiss2 · Jul 16, 2019

You have helped me before and have taught me well. Yes, I had tried both of them. I got somewhat different error messages but neither worked.

I will try your alternate. I am thinking it should go right after the other two and I should comment out both previous 'repsonse' attempts.

Is this controlling how Access responds to the fact that the item is not in the list?

And, just curious, why is it necessary to adjust the column widths in the next section??, why not just say .LimitToList = False??

Thank You

gakiss2 · Jul 16, 2019

theDBguy said:
Hi. Just a guess but in the following section:

Code:

Response = dbErrorAdded ' Response = acDataErrContinue

Try commenting out the first one and uncomment the second one.

On second thought, try using this instead:

Code:

Response = acDataErrAdded

Wow! Worked even better than before. Before I had to pull the list back down and select the newly added item. This way it just pops in.

So now I want it to work in frmMRRLogAll as well. I'll try to fix it on my own. I think I need to go through the lines and replace frmMRRLog with frmMRRLogALL wherever that appears.

Here's hoping for a bit of luck this afternoon.

Thank You Very Much

theDBguy · Jul 16, 2019

gakiss2 said:
Wow! Worked even better than before. Before I had to pull the list back down and select the newly added item. This way it just pops in.

So now I want it to work in frmMRRLogAll as well. I'll try to fix it on my own. I think I need to go through the lines and replace frmMRRLog with frmMRRLogALL wherever that appears.

Here's hoping for a bit of luck this afternoon.

Thank You Very Much

Hi. You're welcome. Glad to hear you got it sorted out. Good luck with your project.

gakiss2 · Jul 16, 2019

Sorry, I celebrated too early. It does work great in the MRR DLog file but getting the same error in MRR DLogAll.

I must have tried it out in the first one when I replied that it worked great. I does work great in MRR DLog but not the MRRDLogAll. I'll dig around some more to see if I can see a difference in the code but I would appreciate another quick look to see if you are getting the difference as well and what might be causing it.

Thank You

theDBguy · Jul 16, 2019

gakiss2 said:
Sorry, I celebrated too early. It does work great in the MRR DLog file but getting the same error in MRR DLogAll.

I must have tried it out in the first one when I replied that it worked great. I does work great in MRR DLog but not the MRRDLogAll. I'll dig around some more to see if I can see a difference in the code but I would appreciate another quick look to see if you are getting the difference as well and what might be causing it.

Thank You

Sorry, I'm on my phone right now and can't download any of your files. I'll let you know if I get a chance to do it later.

gakiss2 · Jul 16, 2019

I made the text of the code for both files exactly the same (except for some commenting which shouldn't matter). It works great in the first file but doesn't in the second. I don't recall changing the underlying form but maybe I did? Or changed it unknowingly? could something like that cause this. The only other thing I can think of is if Access is assuming something about the database, forms, controls or etc. that is true on the first form but somehow not true on the newer one MRRDLog Output... the files I attached are labeled 'skinny' because I cut out some stuff to make it small enough. I was pretty sure I was cutting out unimportant stuff, maybe not?? It was mostly cutting out a chunk of the data but also some 'utility' queries I had use.

Any help is greatly appreciated.

Cronk · Jul 16, 2019

In the code that is not working, I would put a break point, and use debug.print to get the actual sql that you are trying to run, and paste that into a new query. Either the sql string will show up something or the error that results from running the query will probably shed some light on the issue.

JHB · Jul 17, 2019

You've forgotten to post the database the 3 of the tables are linked to, so it isn't possible to run the form.

arnelgp · Jul 17, 2019

I think you should also investigate this part:

Code:

DoCmd.RunSQL "INSERT INTO tblCategory " _
& "(VendorName,Category) VALUES " _
& "(Forms!frmMRRLog!SupplierName,""" & NewData & """);"

can it be like this:

Code:

DoCmd.RunSQL "INSERT INTO tblCategory " _
& "(VendorName,Category) VALUES " _
& "(" & Chr(34) & Forms!frmMRRLog!SupplierName & Chr(34) & "," & Chr(34) & NewData & Chr(34) & ");"

gakiss2 · Jul 17, 2019

Cronk said:
In the code that is not working, I would put a break point, and use debug.print to get the actual sql that you are trying to run, and paste that into a new query. Either the sql string will show up something or the error that results from running the query will probably shed some light on the issue.

But the function of adding the new item to tblCategories is working. Wouldn't that fact validate that the sql statement is working correctly? It is weird. the debug function highlights RunCommand acCmdSaveRecord but the record is saving just fine. Really, everything is 'working'. Its just that the error brings everything to a halt. It seems like I just need to find a way to have it ignore the error and behave like the other file.

Just in case I am off base, I attached an image of what Msgbox gives for the sql:

for what this is worth: If I click 'End' instead of debug, the form shows the Category ID number in the Combo instead of the Category. The Category ID is the index number for that table. Also I clicked on the combo and it showed two columns, the aforementioned Category ID number and the category I typed in. If you close the form then reopen it, it goes back to showing only the category.

gakiss2 · Jul 17, 2019

JHB said:
You've forgotten to post the database the 3 of the tables are linked to, so it isn't possible to run the form.

I attached versione with tables converted to local.

gakiss2 · Jul 17, 2019

arnelgp said:
I think you should also investigate this part:

Code:

DoCmd.RunSQL "INSERT INTO tblCategory " _ & "(VendorName,Category) VALUES " _ & "(Forms!frmMRRLog!SupplierName,""" & NewData & """);"

can it be like this:

Code:

DoCmd.RunSQL "INSERT INTO tblCategory " _ & "(VendorName,Category) VALUES " _ & "(" & Chr(34) & Forms!frmMRRLog!SupplierName & Chr(34) & "," & Chr(34) & NewData & Chr(34) & ");"

Thank You. So far I think the Sql is working. I explain in another post but basically the table is getting updated correctly, its just the error stop that is the problem. I will definitely keep your suggestion on hand.

Cronk · Jul 17, 2019

gakiss2 said:
Thank You. So far I think the Sql is working. I explain in another post but basically the table is getting updated correctly, its just the error stop that is the problem. I will definitely keep your suggestion on hand.

In your initial post, you said the INSERT was working in one instance but you got an error in the other.

I gave you a method, which not only checks the sql, but also to check what the issue was, either something wrong with the data you are trying to insert or something relating to the table you are trying to insert into.

arnelgp · Jul 17, 2019

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.

gakiss2 · Jul 17, 2019

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 · Jul 17, 2019

arnelgp said:

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 · Jul 18, 2019

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

Question Debug Not in List Event (1 Viewer)

Registered User.

Attachments

I’m here to help

Registered User.

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Registered User.

Have been here a while

..forever waiting... waiting for jellybean!

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread