Syntax problems with SQL in VBA

[shkrebs]

This is what I got:

Public VarSerie As Object
Public VarNumero As Object

Private Sub SeleccionarCategoria_AfterUpdate()
Set VarSerie = Me!SeleccionarCategoria
Me!SelectAlbaran.Enabled = True
Me!SelectAlbaran.Requery
End Sub

Private Sub SelectAlbaran_AfterUpdate()
Set VarNumero = Me!SelectAlbaran
DoCmd.ApplyFilter , "Serie = Forms!ElegirAlbaran!SelectAlbaran"
End Sub

cabeceraSQL = "INSERT INTO Cabecera SELECT [Cabecera Facturacion].* FROM [Cabecera Facturacion] WHERE [Cabecera Facturacion].Serie= " & [VarSerie] & " AND [Cabecera Facturacion].Numero= " & [VarNumero] & ""

DoCmd.RunSQL (cabeceraSQL)

The two Sub's is ListBoxes.
I cannot find the right syntax for this SQL, it just won't take the values from the two Sub's.

Can anybody find the error??

Best regards
Soren

 

[DBL]

If VarNumero is numeric take out the & "" at the very end.

 

[shkrebs]

Hi DBL.

Yes I have tried that, but I still got the problem with the other VAR which is a string. It's driving me nuts....
I've read so many places that when using strings the syntax is:

' " & VARNAME & " ' but it doesn't work with this VAR, is it because it's an Object?¿?¿

Regards
Soren

 

[shkrebs]

Thanks Pat it's working now even if I'm not using the ; at the end of the line.

Based on my new experience, and with a lot of selfconfidence I wrote a new SQL, but still I get the errormessage: Syntax error in "INSERT INTO" statement....

I tried to use the Debug.Print and it seems correct:
INSERT INTO [Cabecera Facturacion] IN \\Fujitsu\users\krebs\MS-Access\Transgesa.mdb SELECT Cabecera.* FROM Cabecera

I've tried with and without [], ' and " but with the same result. The only different between the SQL in the QBE and here is that the path and DB-name is between ' in the QBE, is that it and if it is, how do I make VBA accept them as part of the string?


Dim ExportHead2TransgesaSQL AS String
Dim Path AS String
Dim TransGesaDB AS String

TransGesaDB = "Transgesa.mdb"
Path = "\\Fujitsu\users\krebs\MS-Access"

ExportHead2TransgesaSQL = "INSERT INTO [Cabecera Facturacion] " _ & "IN " & Path & "\" & TransGesaDB & " SELECT Cabecera.* FROM Cabecera"


Best regards
Soren

 

[shkrebs]

GOT IT...!!!!!

Don't reply this mail.

It was it - I was missing the ' around the path and DB-name.
I put them into the initialization of the var's, like this:

Path = " '\\Fujitsu\users\...."
DBname = "Transgesa.mdb' "

It's not pritty but it works

Thanks everybody for your input.

Soren