Not able to update a table in VBA Access code

[Arasi]

Hi all,
I am a beginner in Access VBA.
I have an issue in updating a Table in the VBA code.

I am reading a text file in this VBA code and extracting the message IDs from the file and write some comment for the message ID.
I am trying to update the message ID and the comment to the Table "Result" using the below code. It is giving me Run time error - 3001. Invalid Argument.
it stops at the line Rslt.Update.
Dim db As Database
Dim Rslt As Recordset
Set db = CurrentDb()
Set Rslt = db.OpenRecordset("Result")
Rslt.AddNew
Rslt("Field1") = msg_id
Rslt("Field2") = comment
Rslt.Update

Please some one help me to overcome this. It was working fine before. Now its giving error. Donno where I ve gone wrong.

Thanks,
Arasi

[CreganTur]

Welcome to the forum- it's always good to see new members.

Your code looks correct for a DAO update procedure, except for one thing- you're not fully dimensioning your db and Rslt variables, and I think that might be the problem. Try this:

Dim db As DAO.Database
Dim Rslt As DAO.Recordset
Set db = CurrentDb()
Set Rslt = db.OpenRecordset("Result")
Rslt.AddNew
Rslt("Field1") = msg_id
Rslt("Field2") = comment
Rslt.Update


See if that fixes your issue:thumb

[Arasi]

Thanks a lot for your quick response Randy.
I tried with the full dimension now. ie with DAO.
There is no run time error now. But the code stop running once it reaches the Rslt.Update line.
Actually this is in a loop until the end of line of the text file. Its not even gettin executed once.

[CreganTur]

tried with the full dimension now. ie with DAO.
There is no run time error now. But the code stop running once it reaches the Rslt.Update line.
Actually this is in a loop until the end of line of the text file. Its not even gettin executed once.
I figured explicit dimensioning would clear that error.

Please post your code so we can see what the problem is.

[Arasi]

Hi Randy,

This is the entire code.


Option Explicit
Private Sub cmdexecute_Click()
Option Explicit
Dim db As DAO.Database
Dim Prnt As DAO.Recordset
Dim Elmt As DAO.Recordset
Dim Rslt As DAO.Recordset

Dim msg_id, comment As String
Dim flag_found As Boolean
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from Result"
DoCmd.SetWarnings True

Dim found_first, not_found_all As Boolean

Set db = CurrentDb()
Set Prnt = db.OpenRecordset("Print")
Set Elmt = db.OpenRecordset("Element")
Set Rslt = db.OpenRecordset("Result")

Prnt.MoveFirst
Do Until (Prnt.EOF)
msg1= Prnt("Field1")

Elmt.MoveFirst

Do Until (Elmt.EOF)
found_first = False


If (CStr(Trim(Elmt("Des1"))) = CStr(Trim(Prnt("Message")))) Then
found_first = True
not_found_all = False
no1 = Elmt("Length")

For j = 2 To no1
Prnt.MoveNext
If Not (CStr(Trim(Elmt(j))) = CStr(Trim(Prnt("Message")))) Then
not_found_all = True
End If
Next j
End If

If (found_first = True And not_found_all = True) Then
For j = 1 To no1
Prnt.MovePrevious
Next j
End If

If (found_first = True And not_found_all = False) Then
GoTo end_loop
End If
Elmt.MoveNext

Loop

end_loop:
If (found_first = True And not_found_all = False) Then

msg1= Prnt("Field1")
comment = "Success - Message Found"
ElseIf (found_first = True And not_found_all = True) Then

msg1= Prnt("Field1")
comment = "Failure - Only part of message found"
ElseIf (found_first = False) Then


msg1= Prnt("Field1")
comment = "Failure - Message not found "

End If

Rslt.AddNew
Rslt("Field1") = msg_id
Rslt("Field2") = comment
Rslt.Update


Prnt.MoveNext

Loop

Prnt.Close
Elmt.Close
Rslt.Close

Set Prnt = Nothing
Set Elmt = Nothing
Set Rslt = Nothing

MsgBox ("Validation Completed")
End Sub

[Arasi]

:( I tried explicit dimensioning too.. didnt work out

[CreganTur]

Another issue I see is that you have a number of undefined variables used within your code. You're using Option Explicit, which is great, so it should be throwing errors whenever you try to run this code.

Click Debug -> Compile; this should thow an error on your undimensioned variables. Fix those, and then see if your code works.

If an error doesn't appear because of undimensioned variables, it's probably because an error happened in the past that has forced your warnings to be turned off. Type this in the Immediate Window and press Enter to turn them back on:

DoCmd.SetWarnings True

Also, when you post code please wrap it in VBA tags (click the green VBA button). This will format your code according to VBIDE and make it much easier to read:thumb