VBA Append Query Using Access

2
votes

I am attempting to add a button to a form to add the contents of the form as a new line in my table. I have written this line of code before, but this time it isn't working.

My form contains text and integers. 3 boxes on the form are auto-populated when the form loads. (Will the auto populated boxes change anything?)

Instead of trying to make the whole string work, I have reduced my code to just add one box (one of the auto-populated boxes). I get a Rune-time error '438': Object doesn't support this property or method.

I've tried googling the error, but I cannot find anything that applies.

The code is as follows:

Dim strInsert As String

strInsert = "INSERT INTO NLog(IDKEY) " & _
    " Values(" & Me.TxtIdKey & ")"

Debug.Print strInsert
CurrentProject.Execute strInsert, dbFailOnError


MsgBox ("Entry Added")

When I look at the debug screen it shows the following: INSERT INTO NLog(IDKEY) Values(OH08801405)

I am so frustrated! I am still very new to this, and I feel totally out of my element.

2
sqlms-accessvba
I'm guessing the error is on the line CurrentProject.Execute? The error means that the object "CurrentProject" doesn't support the property or method "Execute". I don't see anywhere you've made a database connection. You need a connection object and a command object to execute a query. – user3479671
Out of curiosity, is there a reason you aren't using bound fields to do accomplish what you are trying to do? – Newd
That is the way I initially had my DB set up since it is a simple database. Because I am autopopulating two of the fields in order to Append to the DB, I didn't think that bound would work. My brother, who writes code for Wolfram, said that bound fields are bad, so I was also trying to get away from bound forms. – SarahSedaii
I think avoiding bound fields in MS Access is going to make your coding experience less than pleasant. – Newd

2 Answers

2
votes

You could execute your INSERT statement with ADO like this ...

CurrentProject.Connection.Execute strInsert

Note CurrentProject.Connection.Execute, not CurrentProject.Execute, and don't include the DAO constant dbFailOnError when executing from ADO.

Or with DAO like this ...

CurrentDb.Execute strInsert, dbFailOnError

Also, you were attempting to insert a text value into the IDKEY field. Assuming text is the correct datatype for that field, add quotes around the value you're inserting ...

strInsert = "INSERT INTO NLog(IDKEY) " & _
    " Values('" & Me.TxtIdKey & "')"

Then you should see Debug.Print output similar to this ...

INSERT INTO NLog(IDKEY) Values('OH08801405')

Without quotes surrounding OH08801405, the db engine will not understand it is supposed to be a string value it should insert, and instead will assume it is a parameter for which you haven't supplied a value.

1
votes

I have to assume that the error message is happening at CurrentProject.Execute strInsert, dbFailOnError since your debug actually shows something.

You will need to set up a connection to your database and use db.Execute strInsert, dbFailOnError.

You declare a connection like this:

Dim db As DAO.Database
Set db = CurrentDb

So you should end up with something like: 

Dim strInsert As String
Dim db As DAO.Database

strInsert = "INSERT INTO NLog(IDKEY) " & _
            " Values(" & Me.TxtIdKey & ")"

Debug.Print strInsert

Set db = CurrentDb
db.Execute strInsert, dbFailOnError


MsgBox ("Entry Added")