Solved

Insert, not in list event

Posted on 2013-06-02
16
284 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
will catch up with you tomorrow.  have to hit the bed.  have a good night.
0
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)

 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

11 Experts available now in Live!

Get 1:1 Help Now