Solved

Insert, not in list event

Posted on 2013-06-02
16
291 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

856 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