VBA - Adding a new record to a table (1 Viewer)

[jessss]

[solved] - VBA - Adding a new record to a table

Morning,

I am trying to add a new record to a table using VBA, I ahve pasted the code I am using below. In the table I have columns called 7A, 7B, 7C etc. However I keep getting the error "Compule error: Expected: end of statement" on the "RST!7A = 1" line. Is the error a result of the field name being 7A or have I written the code wrong? As I don't get the error on any other line.

        Dim rst As DAO.Recordset
        Set db = CurrentDb
        Set rst = db.OpenRecordset("HealthQsMEI")
        rst.AddNew
        rst!AppRefID = appid
        rst!7A = 1
        rst.Update
        rst = Nothing

Thank you

Jessica

 

[JHB]

You need a Set.

Set rst = Nothing

 

[jessss]

Hi, Thank you for replying. I have added that in but the rst!7A = 1 is still highlighted red and I am still getting that error.

 

[jessss]

I have found a fix for the issue I was having. I found an article online saying to put [] round the field name. So I changed the rst!7A to rst![7A] and this seems to have solved the error.

 

[gemma-the-husky]

you normally need the [] syntax when you have spaces in a name

rst![field name]

so maybe the same issue is caused by starting the field name with a number.

in general having fields with names like 7A, 7B, 7C is indicative of un-normalised data, It may not be, but often such fields should be in a sub-table.