CODEWITHSUNDEEP
sqlms-accessvba
SarahSedaii
SarahSedaii

Reputation: 25

VBA Append Query Using Access

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.

Upvotes: 2

Views: 6583

Answers (2)

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 2

Newd
Newd

Reputation: 2185

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")

Upvotes: 1

Related Questions