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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
Rgonzo1971Commented:
Hi,

pls try

Clng(tblNewBillsToProcess![Document#] & DOCN)

Open in new window


Regards
Dale FyeOwner, Developing Solutions LLCCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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

h11Author Commented:
now I have this error
error 3131 syntax error in from clause
Dale FyeOwner, Developing Solutions LLCCommented:
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.

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
Dale FyeOwner, Developing Solutions LLCCommented:
glad I could help.
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.