Errore run-time '3067'

omaggi

Registered User.
Local time
Today, 12:35
Joined
Oct 19, 2009
Messages
43
Hi everybody.

I'm trying to write a query for an insert, but when I click the button, I have an error 3067...

This is my code:
Private Sub bInserisci_Click()
Dim query As String
query = "insert into prova (data_ora, id_puntoMisura, id_obis, valore) "
query = query & "values ('" & Me.record_dataOra & "', "
query = query & "(select id_puntoMisura from puntomisura where puntoMisura='" & Me.record_puntoMisura & "'), "
query = query & "(select id_obis from obis where obis='" & Me.record_obis & "'), " & Me.record_punta & ");"
DoCmd.RunSQL query
End If
End Sub

Anybody can help me?

Thx for any suggestions.

Greetings.
 
3067 = ?
Try doing a debug

Comment out the
doCmd
and do a
debug.print query


Take the actual query from the immediate pane and have a look at that
 
I tried but I don't have any answer... :(
 
The debug doesn't go. I have as answer only a "beep sound"... very strange!

Any other suggestions?
 
post the new code ...
You are looking at the immediate pane? Ctrl + G
 
Could it be that you are using query as a variable?

Query is a reserved word, try changing query to strQuery.
 
@dcb
The output of the debug ist:
insert into prova (data_ora, id_puntoMisura, id_obis, valore) values ('10.07.2008 10:45:00', (select id_puntoMisura from puntomisura where puntoMisura='CH10019012345-K-LUCT-LUCT-00AECA1'), (select id_obis from obis where obis='1-1:1.29.0*255'), 611);

Seems to be right... What do you think?

@Poppa Smurf
Query is not the problem. I used in other code and works greatly. Thanks for the help.
 
Makes sense - reserved word after 2000
Change it to qry
 
@dcb
The output of the debug ist:
insert into prova (data_ora, id_puntoMisura, id_obis, valore) values ('10.07.2008 10:45:00', (select id_puntoMisura from puntomisura where puntoMisura='CH10019012345-K-LUCT-LUCT-00AECA1'), (select id_obis from obis where obis='1-1:1.29.0*255'), 611);

Seems to be right... What do you think?

@Poppa Smurf
Query is not the problem. I used in other code and works greatly. Thanks for the help.

Have you pasted this into a sql pane and run it?
change the word "Query" the reserved words can cause intermittent issues
 
I try the query directly in mysql and it works perfectly.
I also try to change the name of the string but it's not this the problem...I still have the same problem.:mad::mad::mad:

Other suggestions?
 
I wonder if it is not the Driver to MYSQL thats having the issue - if you are using a linked table setup do you have the same names? or does it say something like public_puntomisura ?

lets write it differently :

Code:
Private Sub bInserisci_Click()
Dim sqlInsert As String, sqlPunt as string, sqlObis as string
Dim db as database
Dim rstPunt as dao.recordset, rstObis as dao.recordset
'' Dim strPunt as string, strObis as string
Dim intPunt as integer, intObis as integer        ''Assume your id's are numbers
 
Set db = currentdb
 
sqlObis = "select id_obis from obis where obis='" & Me.record_obis & "';"
sqlPunt = "select id_puntoMisura from puntomisura where puntoMisura='" & Me.record_puntoMisura & "';"
 
set rstPunt = db.openrecordset(sqlPunt)
intPunt = rstPunt("id_puntoMisura")
set rstObis = db.openrecordset("sqlObis")
intObis = rstObis("id_Obis")
 
Debug.print sqlObis
Debug.print intObis
Debug.print ""
Debug.print sqlPunt
Debug.print intPunt

sqlInsert = "insert into prova (data_ora, id_puntoMisura, id_obis, valore) "
sqlInsert = query & "values ('" & Me.record_dataOra & "', "
sqlInsert = query & intPunt & ", "
sqlInsert = query & intObis & ", " & Me.record_punta & ");"

Debug.print ""
Debug.Print sqlInsert
 
DoCmd.SetWarnings False
DoCmd.RunSQL query
DoCmd.Setwarnings True
End If
End Sub
 
Thanks to all of you. I tried all the possibilities and now it works. At the end I did it with the automatic configuration of query...

Strange... ehehhe!

Greetings.
 

Users who are viewing this thread

Back
Top Bottom