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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

super_dave_42 - 27 Feb 2006 00:57 GMT

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.Application")
...
If oAcc.Forms!Form1.NewRecord = True Then ' check to ensure I don't
write over old data
oAcc.Forms!Form1![Amount] = curTotal
oAcc.Forms!Form1![Description] = strValue
oAcc.Forms!Form1![Notes] = strNotes
End if ' oAcc.Forms!Form1.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.GoToRecord , , acNewRec but I don't know how to reference
something like that from Excel. Any suggestions?

Reply to this Message

Tom van Stiphout - 27 Feb 2006 01:36 GMT

oAcc.DoCmd.GoToRecord,,acNewRec

-Tom.

>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
[quoted text clipped - 21 lines]
>DoCmd.GoToRecord , , acNewRec but I don't know how to reference
>something like that from Excel. Any suggestions?

Reply to this Message

super_dave_42 - 27 Feb 2006 07:23 GMT

Thanks -Tom,

Unfortunately, 'oAcc.DoCmd.GoToRecord,,acNewRec' 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.Application")
Set rstTable = oAcc.Forms!Form1.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.Application")
Set rstTable = oAcc.Forms!Form1.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!Form1.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.

Reply to this Message