VBA/SQL Append error

[danmack]

Hi, I'm trying to append a record to an Access database from an Excel userform.

I have already written update and select queries from the same form for the same database which work.

This is the offending code;

StrSQL = "INSERT INTO [DFIDatabase] ([Part No], [Supply], [Supplier],[DFI No])" & _
    " VALUES (" & pn & "," & domex & "," & vend & "," & DFI & ")"
 
    Set accApp = CreateObject("Access.Application")
     With accApp
     .OpenCurrentDataBase "C:\DFI Register and Metrics2.mdb"
     .DoCmd.RunSQL StrSQL
     .Quit
 End With

First I get asked to enter the parameter value for the Supply and Supplier Fields (Displays the string to be entered and i have to type in the field names) - the code works if i do this but will not be acceptable to end users.

When i cancel and debug, the error that crops up is 3021 - Reserved Error.

I've checked field names for spelling errors, compiled, compacted and repaired, checked for missing addins/references but still no joy....any help or suggestions would be appreciated. I'm using Access 2010, .mdb format & excel 2010
Thanks
Dan

 

[namliam]

You are not quoting things properly... String values needs to be "" and dates ##, numbers dont need anything

" VALUES (" & pn & ",""" & domex & """ ,#" & vend & "#," & ...
This is assuming
- pn is a number
- domex is a text
- vend is a date, remember that when using dates in VBA they MUST be in US Format or expect problems.

 

[danmack]

Namliam - Brilliant, thank you. Also :banghead: