Solved: .Update causing app error

[SilverSN95]

Hi, Im not sure if this is should be in the Access forum, as the coding is done in excel but the error is happening in access.
Anyways, Ive modified a macro that I would like to create a new record in the db from information I have in a column in excel (column B). The field names to be updated are in column A. I had simular macro working just fine where I would just say:

.AddNew
.Fields("Insured Name")= Range("B" & r).Value

but I want it to be able to run in a loop as I'm attempting below.


Private Sub CommandButton231_Click()
Dim curDatabase As DAO.Database
Dim table As DAO.Recordset
Dim r As Long
Dim str As String
Set curDatabase = OpenDatabase(Range("J1").Value)
Set table = curDatabase.OpenRecordset(Range("J2").Value)

r = 1 'start row in xls
'MsgBox Range("A" & r)

Do While Len(Range("A" & r).Formula) > 0 ' Scale rows untill bottom of column A
With table
.AddNew
.Fields(Range("A" & r)) = Range("B" & r).Value
End With
r = r + 1 'next row
Loop
table.Update
table.Close
Set table = Nothing
curDatabase.Close
Set curDatabase = Nothing
MsgBox "Information Submitted"
CommandButton1.Visible = False
End Sub

The application error occurs at table.Update. Any ideas?
Just a heads up I know very little VBA although I'm not new to programming in general.
Thanks.

[JimmyTheHand]

I think the loop structure is wrong as the .AddNew method executes too often. Right now for each field a new record is added, but not updated. Only when the loop reaches the last field is the table updated, but at that point only one field of the record is filled so probably some a critical data (that should be "NOT NULL") is missing. So try to modify the code like this:

Private Sub CommandButton231_Click()
Dim curDatabase As DAO.Database
Dim table As DAO.Recordset
Dim r As Long
Dim str As String
Set curDatabase = OpenDatabase(Range("J1").Value)
Set table = curDatabase.OpenRecordset(Range("J2").Value)

r = 1 'start row in xls
'MsgBox Range("A" & r)
With table
.AddNew
Do While Len(Range("A" & r).Formula) > 0 ' Scale rows untill bottom of column A
.Fields(Range("A" & r)) = Range("B" & r).Value
r = r + 1 'next row
Loop
.Update
.Close
End With

Set table = Nothing
curDatabase.Close
Set curDatabase = Nothing
MsgBox "Information Submitted"
CommandButton1.Visible = False
End Sub


Jimmy

[OBP]

I am not sure that it is a good idea to call the variable "table", as that could be a VBA reserved word. The table.bookmark is also missing, but not essential.

[SilverSN95]

Yeah, taking the .AddNew out of the loop fixed the error I was getting. Thanks for the help, it's harder to debug something when its a generic error and I'm new to writing in VBA.
Are there ways to interpret the application errors, or are we just stuck with the generic message?

[JimmyTheHand]

Sometimes the error messages are quite informative, sometimes not. I don't know what it depends on. But they always hint on the type of error. In most cases that's just enough. Personally, I use step-by-step execution and the Locals window as debugging tools.

Jimmy