VBA Not running SQL statement

[helenmayhew88]

Hi everyone,

I am currently writing a module that checks a table's primary key (a mixture of letters and numbers) and increments the last relevant code by 1, before creating a record for this key and adding it to the table.

The code I've written manages to get the right code, increase the number, and then brings up an input box for the user to enter a description of the item (each code refers to a piece of musical equipment...just to give you a bit of background). However, the SQL statement I then use to add the record to the table doesn't work.

Access doesn't give any error messages or problems with the statement, and it seems to work perfectly, but when I go to the table, the record hasn't been inserted.

I've tried the SQL statement in queries and it works perfectly, and it is definitely getting all the parameters as I have used debug.print and checked the statement. It just isn't adding to the table!!

Please help coz I'm going crazy

'If they do enter an item, add the record to the table
If stItemDescript <> "NONE" Then
stSQL2 = "INSERT INTO tblInstrumentsandEquipment (Item, [soundLINCS Code], Location) VALUES ('" & stItemDescript & "', '" & stFinalCode & "', 'In office')"
DoCmd.SetWarnings WarningsOn
DoCmd.RunSQL stSQL2
'Let them know the item has been added successfully
Response = MsgBox("Item added!", vbOKOnly, "Success!")
'Close the form
If Response = vbOK Then
DoCmd.Close acForm, stDocName, acSaveNo
End If

Apologies for my amateurish code, I've been teaching myself as I go along :S

Thanks in advance for your help
Helen
xxx

 

[Vonnie]

Is [soundLINCS Code] a field on a form? or an argument sent to a routine?

 

[helenmayhew88]

It's a field in a table.
Helen x

 

[helenmayhew88]

Never mind I've fixed it. Don't ask how coz it's far too embarrassing to share!!!