Adding a New Record to Access form using Visual Basic in Excel

[super_dave_42]

I am searching for a way to create multiple records in Access 2000 from
a worksheet created in Excel. I'm pretty much self-taught with Visual
Basic so forgive me if this poorly described. Basically what happens
is, from Access, while in Form1, I click a button that opens Excel and
my worksheet. This worksheet contains a button with the following code
( I've replaced the sections where I assign cell values to the
variables with ellipses):

Dim oAcc As Object
...
Set oAcc = GetObject(, "Access.Applica tion")
...
If oAcc.Forms!Form 1.NewRecord = True Then ' check to ensure I don't
write over old data
oAcc.Forms!Form 1![Amount] = curTotal
oAcc.Forms!Form 1![Description] = strValue
oAcc.Forms!Form 1![Notes] = strNotes
End if ' oAcc.Forms!Form 1.NewRecord = True

This works just fine for one record; I close out excel with the program
and return to Access with the form filled in. However, now I'm trying
to expand to calculate multiple rows of the same formulas and create a
new record in Access for each row. From Access I would just run
DoCmd.GoToRecor d , , acNewRec but I don't know how to reference
something like that from Excel. Any suggestions?

[Tom van Stiphout]

Re: Adding a New Record to Access form using Visual Basic in Excel

On 26 Feb 2006 16:57:21 -0800, "super_dave _42"
<super_dave_42@ hotmail.com> wrote:

oAcc.DoCmd.GoTo Record,,acNewRe c

-Tom.

[color=blue]
>I am searching for a way to create multiple records in Access 2000 from
>a worksheet created in Excel. I'm pretty much self-taught with Visual
>Basic so forgive me if this poorly described. Basically what happens
>is, from Access, while in Form1, I click a button that opens Excel and
>my worksheet. This worksheet contains a button with the following code
>( I've replaced the sections where I assign cell values to the
>variables with ellipses):
>
> Dim oAcc As Object
> ...
> Set oAcc = GetObject(, "Access.Applica tion")
> ...
> If oAcc.Forms!Form 1.NewRecord = True Then ' check to ensure I don't
>write over old data
> oAcc.Forms!Form 1![Amount] = curTotal
> oAcc.Forms!Form 1![Description] = strValue
> oAcc.Forms!Form 1![Notes] = strNotes
> End if ' oAcc.Forms!Form 1.NewRecord = True
>
>This works just fine for one record; I close out excel with the program
>and return to Access with the form filled in. However, now I'm trying
>to expand to calculate multiple rows of the same formulas and create a
>new record in Access for each row. From Access I would just run
>DoCmd.GoToReco rd , , acNewRec but I don't know how to reference
>something like that from Excel. Any suggestions?[/color]

[super_dave_42]

Re: Adding a New Record to Access form using Visual Basic in Excel

Thanks -Tom,

Unfortunately, 'oAcc.DoCmd.GoT oRecord,,acNewR ec' did not work. On the
up side, it gave me no error messages. I ended up with the last row of
data transfered into the correct fields of the current record, so it
would appear each row was just overwritten by the next. I came across a
Recordset object in the help files, and the AddNew method seems like a
good alternative to DoCmd. However, Excel does not recognize the
Recordset object (fyi, I have the MS Access 9.0 Obj Lib added to my
References). The closest thing I could see is an AccessObject type. So,
I tried the following:

Dim oAcc As Object
Dim rstTable As AccessObject
...
Set oAcc = GetObject(, "Access.Applica tion")
Set rstTable = oAcc.Forms!Form 1.Recordset
...
rstTable.AddNew
...

That didn't work either, and rstTable never changed its value from
"Nothing" after the Set command was processed. So I then thought to
just let VB handle the job of determining the type.

Dim oAcc As Object
Dim rstTable As Object
...
Set oAcc = GetObject(, "Access.Applica tion")
Set rstTable = oAcc.Forms!Form 1.Recordset
...
rstTable.AddNew
...

Voila! A brand-spanking-new record for each row of my data. I can go
sleep now. :^)

p.s. One bad side effect, which doesn't bother me but might be of
interest to others, is the fact that the NewRecord property
(oAcc.Forms!For m1.NewRecord) didn't seem to change value when I used
this new code. Honestly, I don't know if it even changed with my
original code; I might have just always been using a new record. I
suggest you test more thoroughly than I have done.