Compile Error: Method or Data Memeber Not Found - LOSING MY MIND

Question

I keep getting a compile error on code that I'm running successfully on other tables.  It's a SQL INSERT statement that is just plugging in some data...nothing complex at all.  I've checked spelling about a dozen times on the table, controls, and code. Intellisense recognizes my control even though it claims it doesn't recognize it. I've tried dotting it and banging it. I've been staring at this for an hour now and I am out of patience. Like I said: this code works on other tables.  Can someone please help me? 



strSQL_2 = " insert into tblOutreaches (PARIS, DateOfOutreach, OutreachType, EntityName, Location, Finding, Notes)" _
& "select '" & Me.txtParis & "','" & Me.txtDate & "','" & Me.cmbType & "','" & Me.cmbName _
& "','" & Me.cmbLocation & "','" & Me.cmbFinding & "';"
dbs.Execute strSQL_2

Edit:  I forgot to point out it hangs up on cmbFinding.  I've deleted the control and remade it, changed the spelling, etc...  Strangely, Intellisense always recognizes it even though it throws an error.

Answer 1

Hi. One thing I would suggest is to make sure you don't have a "run-on" sentence by missing spaces between each SQL keyword. For example, I can see that you may have a run-on here: ...,Finding, Notes)select '...

That might be fine or not, but I would recommend adding a space just to be sure. Just my 2 cents...

Answer 2

I believe that is just from copying and pasting. It jumbled up the spacing when I pasted it.  There are spaces between all the fields/controls in the actual code.

Answer 3

Hi. I would just double-check. Your code looks like this to me:

strSQL_2 = "insert..., Finding, Notes)" _
& "select '" &...

If that is true, then the result of strSQL_2 would have a run-on at "...Notes)select..."

Answer 4

Nope.  I got paranoid and even put it all on one line to make 100% sure.  It's still throwing an error.

Answer 5

I keep getting a compile error on code that I'm running successfully on other tables.  It's a SQL INSERT statement that is just plugging in some data...nothing complex at all.  I've checked spelling about a dozen times on the table, controls, and code. Intellisense recognizes my control even though it claims it doesn't recognize it. I've tried dotting it and banging it. I've been staring at this for an hour now and I am out of patience. Like I said: this code works on other tables.  Can someone please help me? 



strSQL_2 = " insert into tblOutreaches (PARIS, DateOfOutreach, OutreachType, EntityName, Location, Finding, Notes)" _
& "select '" & Me.txtParis & "','" & Me.txtDate & "','" & Me.cmbType & "','" & Me.cmbName _
& "','" & Me.cmbLocation & "','" & Me.cmbFinding & "';"
dbs.Execute strSQL_2

Edit:  I forgot to point out it hangs up on cmbFinding.  I've deleted the control and remade it, changed the spelling, etc...  Strangely, Intellisense always recognizes it even though it throws an error.

Hi WTF,

You could try:

strSQL_2 = " insert into tblOutreaches (PARIS, DateOfOutreach, OutreachType, EntityName, Location, Finding, Notes)" _
& " VALUES ('" & Me.txtParis & "','" & Me.txtDate & "','" & Me.cmbType & "','" & Me.cmbName _
& "','" & Me.cmbLocation & "','" & Me.cmbFinding & "')"

Imb.

Answer 6

I re-wrote my original code and the error did not occur.   However, the data is not being inserted into the table - so now I have a whole new problem. 

Imb-hb"  I tried that, I get the same results as when I re-wrote the statement - no error, but also no data is inserted into the table.

Answer 7

I have data validation using a dlookup and that works when I try and enter a duplicate, so it IS looking at the table....I've put stops in the code and checked the variables; they are storing the data, just not passing it.

Answer 8

I'm calling it quits for today.  If anyone has any other ideas, it is most appreciated.  I'll take a look at it again when I get back to work. 

Thanks to everyone who commented.

Answer 9

Try the following:

Private Sub RunSQLInsertTest()
    RunSQLInsert Me.txtParis, Me.txtDate, Me.cmbType, Me.cmbName, Me.cmbLocation, Me.cmbFinding, Me.txtNotes
    
End Sub
Private Sub RunSQLInsert(Paris As String, OutreachDate As Date, OutreachType As String, EntityName As String, Location As String, Finding As String, Notes As String)
    
    On Error GoTo Err_Process
    
    Dim strSQL As String
    Dim dbs1 As Database
    
    Set dbs1 = CurrentDb
    
    strSQL = "INSERT INTO tblOutreaches ( " & _
    "PARIS, " & _
    "DateOfOutreach, " & _
    "OutreachType, " & _
    "EntityName, " & _
    "Location, " & _
    "Finding, " & _
    "Notes ) " & _
    "VALUES ( " & _
    "'" & Paris & "', " & _
    "#" & OutreachDate & "#, " & _
    "'" & OutreachType & "', " & _
    "'" & EntityName & "', " & _
    "'" & Location & "', " & _
    "'" & Finding & "', " & _
    "'" & Notes & "' );"
    
    dbs1.Execute strSQL, dbFailOnError
    MsgBox "Record was inserted successfully.", vbInformation, "Insert Complete"
    
Exit_Process:
    Set dbs1 = Nothing
    Exit Sub

Err_Process:
    MsgBox Err.Number & " " & Err.Description, vbExclamation, "Error"
    Resume Exit_Process
    
End Sub

The first problem you had is too few input values.  You were missing one for "Notes."

The other problem you may have is that your data types may be off.  You have a date value being inserted, but you were attempting to insert as a string.  Now, if your field is defined as Text, that would be ok on this end, but it would be poor in table design.  For a date value, you must enclose the value in hash marks (#).

You also have what could be a numeric value, "OutreachType."  If it is numeric, you need to remove the enclosing quotes.

Hopefully this will work for you.

Best of luck!

Answer 10

“Too few values” implies that THIS table is different from THE OTHER tables, on which this code works.

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 11

The other problem you may have is that your data types may be off.  You have a date value being inserted, but you were attempting to insert as a string.  Now, if your field is defined as Text, that would be ok on this end, but it would be poor in table design.  For a date value, you must enclose the value in hash marks (#).

You also have what could be a numeric value, "OutreachType."  If it is numeric, you need to remove the enclosing quotes.

Hopefully this will work for you.

Best of luck!

The Notes field and the hash marks were an oversight on my part; I had been staring at a screen for eight hours and my focus wasn't there.  The date field is defined as a date/time field, so the table was set up correctly.   All other fields (except the PK ID, an autonumber) are text with the exception of Notes, which is a Memo type. 

I have corrected my previous errors, but unfortunately it is still not working.  I have tried your code also; nothing.  I'm about to the point where I'm just going to wipe this table and form and start from scratch.  Any other ideas before I do that?

Answer 12

So, I built a new table and a new form and used the same code and it works.  I even copied and pasted the controls from the previous form, so nothing changed with them.  The table structure/naming convention is the same.  I'm not sure why it wouldn't work on the previous form, but it works now so I guess...problem solved?  IDK.  Anyway, thanks for those  who responded.