• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Insert, not in list event

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
pdvsa
Asked:
pdvsa
  • 9
  • 4
  • 3
1 Solution
 
clockwatcherCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
clockwatcherCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
pdvsaProject financeAuthor Commented:
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
 
clockwatcherCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
clockwatcherCommented:
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
 
pdvsaProject financeAuthor Commented:
will catch up with you tomorrow.  have to hit the bed.  have a good night.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pdvsaProject financeAuthor Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
pdvsaProject financeAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now