VBA Append Query Using Access

Question

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.

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. — exceptional exception
– exceptional exception, Commented Jan 16, 2015 at 16:03
Out of curiosity, is there a reason you aren't using bound fields to do accomplish what you are trying to do? — Newd
– Newd, Commented Jan 16, 2015 at 16:44
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
– SarahSedaii, Commented Jan 16, 2015 at 16:48
I think avoiding bound fields in MS Access is going to make your coding experience less than pleasant. — Newd
– Newd, Commented Jan 16, 2015 at 17:14

HansUp · Accepted Answer · 2015-01-16 16:13:13Z

2

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.

edited Jan 16, 2015 at 16:13

answered Jan 16, 2015 at 16:01

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Newd Over a year ago

I don't even know why I try, I spot 2 things and you spot 8 Haha. Apparently I need to hone my powers of observation. Didn't even catch the non-numerical values in that IDKey, I just saw ID and assumed it was numerical.

HansUp Over a year ago

Well I spotted the missing quotes right off, but that didn't account for the error message Sarah reported. It took me longer to realize CurrentProject.Execute was the cause of the first error. And the quote issue was waiting to trip her up after the first error was fixed. :-)

SarahSedaii Over a year ago

Thanks! It seems to be working now! Just adding one field at a time since I stink at "' or ". When I am inserting a date... do I use " or '"?

HansUp Over a year ago

Include # characters to delimit Date/Time values ... #2015-01-16#. If prefer to use yyyy-mm-dd for Date/Time to avoid any possible confusion over whether the date is dd/mm/yyyy or mm/dd/yyyy.

HansUp Over a year ago

The # should be inside quotes ... instead of "','" & #Me.DateRec# & "','" ... use "',#" & Me.DateRec & "#,'"

|

Newd · Accepted Answer · 2015-01-16 16:05:37Z

1

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

answered Jan 16, 2015 at 16:05

Newd

2,1832 gold badges18 silver badges33 bronze badges

2 Comments

SarahSedaii Over a year ago

Well that did something! Now I am getting a Rune Time error '3061': Too few parameters. Expected 1.

Newd Over a year ago

Can you post your debug line again? Edit: Just ignore my answer and go with HansUp :)

Collectives™ on Stack Overflow

VBA Append Query Using Access

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related