Solved

Insert, not in list event

Posted on 2013-06-02
16
285 Views
Last Modified: 2013-06-03
Experts, I have the below NOT IN LIST EVENT.  I think there is something wrong with the strSQL line.  Do you see what it is?  When I tab out of the field it doesnt insert.  

Private Sub cboRating_NotInList(NewData As String, Response As Integer)

       Dim strSQL As String
    On Error GoTo EH  '<-- add this for error handling
 
         strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES (" & Me!cboRatingAgency & ", " & Me!cboRating & ")"
   
    'Debug.Print strSQL
  CurrentDb.Execute strSQL, dbFailOnError   '<---- Change this line to use strSQL
   
    If IsNull(Me.cboRatingAgency) Then
        MsgBox "Enter Rating Agency first", vbInformation
   
    End If
         
 
Exit Sub


EH:  If Err.Number = 3075 Then
    Exit Sub
    'resume next  'don't know what this is for.
ElseIf Err.Number <> 0 Then
   
    MsgBox "Error " & Err.Number & ": " & Err.Description
End If

     
End Sub
0
Comment
Question by:pdvsa
  • 9
  • 4
  • 3
16 Comments
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215192
Are Agency and Rating numeric fields?  If they're not you should be enclosing them in quotes and escaping any quotes within them...

strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES ('" & replace(Me!cboRatingAgency,"'","''") & "', '" & replace(Me!cboRating,"'","''") & "')"
0
 

Author Comment

by:pdvsa
ID: 39215207
hi, thanks for the response.

<Are Agency and Rating numeric fields?
==> they are both text fields in tblRatings_Combined

It still seems to not Insert into tblRatings_Combined.  

What do you think now?
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215215
I would suggest that you uncomment the debug.print strSQL and post what your sql string looks like and also comment out your error handling for the time being and post the error that you're getting (if any).
0
 

Author Comment

by:pdvsa
ID: 39215283
I cant figure out why it is not working.   I have worked on this for awhile now.   I guess Not In List event is what I would need?  I dont know if I am using Not In List the correct way?  

here is how I have it:  

Private Sub cboRating_NotInList(NewData As String, Response As Integer)

         Dim strSQL As String
'    On Error GoTo EH  '<-- add this for error handling
 
             strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES ('" & Replace(Me!cboRatingAgency, "'", "''") & "', '" & Replace(Me!cboRating, "'", "''") & "')"
'    Debug.Print strSQL
  CurrentDb.Execute strSQL, dbFailOnError   '<---- Change this line to use strSQL
   
    If IsNull(Me.cboRatingAgency) Then
        MsgBox "Enter Rating Agency first", vbInformation
   
    End If
         
 
Exit Sub


'EH:  If Err.Number = 3075 Then
'    Exit Sub
    'resume next  'don't know what this is for.
'ElseIf Err.Number <> 0 Then
   
'    MsgBox "Error " & Err.Number & ": " & Err.Description
'End If


End Sub
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215288
The 'Debug.Print strSQL is still commented out.  Can you uncomment that out and post the SQL?  

You can tell if it's the event that you're after by putting in either a debug.print "in Not In List event" or MsgBox "in Not In List event"  statement as the first line of your event handler.
0
 

Author Comment

by:pdvsa
ID: 39215307
ok...I removed the comment mark on debug line.  
how do I post the sql?  I am thinking you are wanting me to go to the immediate window but not sure what I should type.  I obviously am far from a programmer.  

thanks...
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 39215313
The debug.print should output something to your immediate window.  If nothing is in your immediate window then the event probably isn't firing.  Add a

msgbox "in not in list"

As the very first line in your event handler.  So  your event handler looks like this:
Private Sub cboRating_NotInList(NewData As String, Response As Integer)

         msgbox "In Not In List"

         Dim strSQL As String
'    On Error GoTo EH  '<-- add this for error handling
 
             strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES ('" & Replace(Me!cboRatingAgency, "'", "''") & "', '" & Replace(Me!cboRating, "'", "''") & "')"
  Debug.Print strSQL
  CurrentDb.Execute strSQL, dbFailOnError   '<---- Change this line to use strSQL
   
    If IsNull(Me.cboRatingAgency) Then
        MsgBox "Enter Rating Agency first", vbInformation
   
    End If
         
 
Exit Sub


'EH:  If Err.Number = 3075 Then
'    Exit Sub
    'resume next  'don't know what this is for.
'ElseIf Err.Number <> 0 Then
   
'    MsgBox "Error " & Err.Number & ": " & Err.Description
'End If


End Sub

Open in new window


Run your form and enter something in the cboRating combo box that isn't in its  dropdown list.  The message box should show up.   If it doesn't then this event handler isn't tied to your cboRating control.
0
 

Author Comment

by:pdvsa
ID: 39215314
will catch up with you tomorrow.  have to hit the bed.  have a good night.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 84
ID: 39216043
strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES (" & Me!cboRatingAgency & ", " & Me!cboRating & ")"
If either Agency or Rating are Text fields, you'll have to modify your INSERT statement to handle this:

strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES ('" & Me!cboRatingAgency & "', '" & Me!cboRating & "')"

In the example above, I assumed BOTH were text fields, and include single quotes around each.
0
 

Author Comment

by:pdvsa
ID: 39216056
OK back online.  I just had a long response typed up but my connection cut out here at work.  

OK so let me summarize:
Does the field HAVE to be a combo box at the table level in order for the NOT IN LIST event to fire?   At the table level, th efield is a text box and not a combo box with any row source query.
0
 

Author Comment

by:pdvsa
ID: 39216065
I changed the field to a combo box at the form level.   I made another field at the table level, as a cbo with a row source and added it to the form and pasted the code and it seems like it is firing now.
0
 
LVL 84
ID: 39216150
Does the field HAVE to be a combo box at the table level in order for the NOT IN LIST event to fire?  
No. You should leave the setting as a Textbox. The table control setting is not tied to the Control used on the field in any way.
0
 

Author Comment

by:pdvsa
ID: 39216209
its working now with the latest Insert event you posted.  Are you the same person as clockwatcher?  Not sure how to award pts if you are.   thank you.
0
 

Author Comment

by:pdvsa
ID: 39216353
if rating could be either text or a number then would I have to modify the insert code?   I have Rating as text property.  

I thought it was working but it doesnt seem to work now.  I dont know what I am doing wrong.  

I have attached the db.  The form opens auto and I have deleted all but what is needed.  maybe you can take a quick peek.  It is 2007 format.  I have added new fields, deleted codes, deleted fields, done a lot of stuff and it doesnt work for me.  

It is most likely an easy fix.   thank you sir....
EE.accdb
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39216881
I changed your code like this:

Private Sub cboRating_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    On Error GoTo EH  '<-- add this for error handling

    strSQL = "INSERT INTO tblRatings_Combined (Agency, Rating) VALUES ('" & Me!cboRatingAgency & "', '" & NewData & "')"

    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError   '<---- Change this line to use strSQL

    If IsNull(Me.cboRatingAgency) Then
        MsgBox "Enter Rating Agency first", vbInformation

    End If
    Response = acDataErrAdded

    Exit Sub


EH:     If Err.Number = 3075 Then
        Exit Sub
        Resume Next  'don't know what this is for.
    ElseIf Err.Number <> 0 Then

        MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Sub

Open in new window

You must also set the LimitToList property of your Combo to Yes in order to cause the NotInList event to fire.

I'm not the same person as "clockwatcher". You should award points to the comments which helped you to resolve your issue. If my comments helped you, but clockwatcher's did not, then you shouldn't award points to clockwatcher. If clockwatcher's comments helped you arrive at a solution, but mine did not, then you should not accept my comments.

EE expects you to only award points for valid, verified solutions - and you should never award points merely due to the effort of an Expert.
0
 

Author Closing Comment

by:pdvsa
ID: 39217731
I see I had an issue with 2 things:  LImitToList property was NO (should have been Yes) and the "NewData" part in the Insert line (line 5).  Also, there was an extra line added:  line 15.  

It works good.  thank you for the help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access 2007 Count Unique Values in a Group 5 33
DSum for Access 6 44
sort Time by AM and PM in query 2 18
Running sum query 6 30
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now