Ms Acess 2013 update record in table using VBA loop

I am trying to update the tblNewBillsToProcess document# field with what is currently in the document# field + DOCN.

I have created the code the only problem is I keep getting the error 13 type mis match any help on this would be appreciated.

the document# fileld is short text field.

Sub UpdateDocumentNumber()

Dim db As DAO.Database
Dim tblNewBillsToProcess As DAO.Recordset
Dim strSQL As String
'Dim rs AS Recordset
Dim DOCN As String
'Integer

On Error GoTo ErrorHandler

   Set tblNewBillsToProcess = CurrentDb

   'Open a recordset on all records from the tblNewBillsToProcess table that have
   'a Null value in the Document# field.
   strSQL = "SELECT * FROM tblNewBillsToProcess"
   strSQL = strSQL & "WHERE [Document#] IS NULL"
  Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

   'If the recordset is empty, exit.
   If rst.EOF Then Exit Sub

   DOCN = 1
   With rst
      Do Until .EOF
         .Edit
      'update Document# with Document# and DOCN
        ![Document#] = tblNewBillsToProcess![Document#] & DOCN
         .Update
         .MoveNext
         intI = intI + 1
      Loop
   End With

   tblNewBillsToProcess.Close

   Set tblNewBillsToProcess = Nothing
   
   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
LVL 2
h11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

h11Author Commented:
corrected code

Sub UpdateDocumentNumber()

Dim db As DAO.Database
Dim tblNewBillsToProcess As DAO.Recordset
Dim strSQL As String
'Dim rs AS Recordset
Dim DOCN As String
'Integer

On Error GoTo ErrorHandler

   Set tblNewBillsToProcess = CurrentDb

   'Open a recordset on all records from the tblNewBillsToProcess table that have
   'a Null value in the Document# field.
   strSQL = "SELECT * FROM tblNewBillsToProcess"
  strSQL = strSQL & "WHERE [Document#] IS NULL"
  Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

   'If the recordset is empty, exit.
   If rst.EOF Then Exit Sub

   DOCN = 1
   With rst
      Do Until .EOF
         .Edit
      'update Document# with Document# and DOCN
        ![Document#] = tblNewBillsToProcess![Document#] & DOCN
         .Update
         .MoveNext
         'adds one to the DOCN to increase number by one
         DOCN = DOCN + 1
      Loop
   End With

   tblNewBillsToProcess.Close

   Set tblNewBillsToProcess = Nothing
   
   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
0
Rgonzo1971Commented:
Hi,

pls try

Clng(tblNewBillsToProcess![Document#] & DOCN)

Open in new window


Regards
0
Dale FyeCommented:
Since your [Document#] field is NULL, then you simply need to insert the DOCN value in that field.  Try changing this:

        ![Document#] = tblNewBillsToProcess![Document#] & DOCN

to:

        ![Document#] = DOCN

Also, in the first statement above, you tried referring directly to the table, not the recordset, you should use:

        ![Document#] = rst![Document#] + DOCN
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

h11Author Commented:
the sql query was wrong i do not want to find is null I want to find  "is not null" and update them. I made the change it still gives me the error. I believe it has somehting to do wiht this statement
Dim tblNewBillsToProcess As DAO.Recordset
0
Dale FyeCommented:
OK, as I understand your comment, you want to append a string DOCN to the end of the [document#] field, which apparently is already a text value, is that correct?  If so, how large to you expect the value of DOCN to get?  When I use strings this way, I like to format the number with a specific number of characters, using the Format( ) function.  I've changed a number of things below, and tagged some of them, but not all

Sub UpdateDocumentNumber()

Dim db As DAO.Database   '<= changed this
Dim rst As DAO.Recordset  '<= changed this
Dim strSQL As String
Dim DOCN As long   '<= changed this

On Error GoTo ErrorHandler

   Set db = CurrentDb  '<= changed this

   'Open a recordset on all records from the tblNewBillsToProcess table that have
   'a Null value in the Document# field.
   strSQL = "SELECT * FROM tblNewBillsToProcess"
   strSQL = strSQL & "WHERE [Document#] IS NOT NULL"  '<= changed this
   Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

   'If the recordset is empty, exit.
    If rst.EOF Then Exit Sub

   DOCN = 1
   With rst
      Do Until .EOF
         .Edit
      'update Document# with Document# and DOCN
        ![Document#] = ![Document#] & Format(DOCN, "000")   '<== changed this
         .Update
         .MoveNext
         'adds one to the DOCN to increase number by one
         DOCN = DOCN + 1
      Loop
   End With

   rst.Close
   set rst = Nothing
   set db = nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub 

Open in new window

0
h11Author Commented:
now I have this error
error 3131 syntax error in from clause
0
Dale FyeCommented:
insert a space at the end of the SELECT line (before the quote), or at the beginning of the WHERE line (after the quote).

The way I generally go about debugging stuff like that is to insert a line to print out my SQL string to the immediate window

   strSQL = "SELECT * FROM tblNewBillsToProcess "  '<= changed this by adding a space
   strSQL = strSQL & "WHERE [Document#] IS NOT NULL"  
debug.print strsql
   Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbfailonerror)  '<= added the dbfailonerror

Open in new window

I also added the dbFailOnError option to the OpenRecordset method to raise an error if the SQL string generates an error.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
h11Author Commented:
That worked perfect.  Now it is doing what I want it to do. thanks
0
Dale FyeCommented:
glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.