Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert, not in list event

Posted on 2013-06-02
16
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

636 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