create new record (addnew?)

[AccessMan]

I have a form that contains only unbound controls. These are used for
selecting values for new records that I want to create in multiple tables.
I've made an attempt with AddNew but have encounted an 'invalid use of
property' error on the second statement below.

Dim rst As DAO.Recordset

rst = CurrentDb.OpenRecordset("table name")

Yes, there is an embedded space in the table name, but enclosing in square
brackets makes no difference (regarding this error). Can someone please help
me understand what is wrong here?

Also, I read an Allen Browne post from 3/27/2008 that suggests an approach
using an sql string and ...

CurrentDb.Execute strSql, dbFailOnError

Is this a cleaner approach that AddNew?

How would I pass control back to the form if there is an insertion failure?

Thanks!!!

 

[roger]

have you thought about using ADO instead of DAO
The syntax is a little shorter and I think ADO is newer.

in ADO it would just be:

dim objTable as object
Set objTable= CurrentDb.OpenRecordset("tblMytable")

objTable.addnew
objTable.myfield= whatever
objTable.myotherfield= whatever else
objTable.update

'(always close your vars)
set objTable =nothing

In DAO it takes two lines to declare a table in ADO just one.

I have no idea if that fixes your space problem

 

[Marshall Barton]

I have a form that contains only unbound controls. These are used for
selecting values for new records that I want to create in multiple tables.
I've made an attempt with AddNew but have encounted an 'invalid use of
property' error on the second statement below.

Dim rst As DAO.Recordset

rst = CurrentDb.OpenRecordset("table name")

Since a recordset is an object, you must use a Set
statement:
Set rst = CurrentDb.OpenRecordset("table name")

Also, I read an Allen Browne post from 3/27/2008 that suggests an approach
using an sql string and ...

CurrentDb.Execute strSql, dbFailOnError

Is this a cleaner approach that AddNew?

Cleaner?? For one record, I think they're fairly close to
the same speed. The code to concatenate all the field
values into the SQL statement might(?) be less
straightforward than assigning the field values. I guess I
would say that it's your call.

How would I pass control back to the form if there is an insertion failure?

Using dbFailOnError allows you to use error trapping to
catch any errors.

 

[AccessMan]

It turns out that the embedded space in the table name was not an issue.

I had neglected to include "Set " in the third line below.

Private Sub AddRecord_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Table Name")
rst.AddNew
rst!Field1 = "field value"
rst.Update

End Sub


This seems to work fine, and it looks essentially identical to your ADO
suggestion. What is the advantage to ADO?