[Solved] Update a field’s value from within a VBA macro

Creating tables and queries

[Solved] Update a field’s value from within a VBA macro

Postby sameersabri » Thu Jan 21, 2016 3:06 pm

Hey Everyone!
can someone please help me on how to update a field’s value from within a VBA macro, I am running the below macro code but it doesn't updated my data, many thanks:)

Code: Select all   Expand viewCollapse view
Option Compare Database

Sub myMacro()

Dim rs As DAO.Recordset 'the IDP_20160120 needs a variable name in here
Set rs = CurrentDb.OpenRecordset("SELECT * FROM IDP_20160120")

Dim dw As DAO.Recordset 'the IDP_Translation_Table needs a variable name
Set dw = CurrentDb.OpenRecordset("SELECT * FROM IDP_Translation_Table")

'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
   
    Do Until rs.EOF = True
         strCurrentString = rs.Fields(6).Value
         
         Debug.Print "NAME field is " & strCurrentString 'prints value of 6th field in Immediate Window
       
'start second loop to compare every occurence in deleteable words table
         
        dw.MoveFirst
        boolFound = False
         Do Until dw.EOF = True Or boolFound = True
          strCurrentWordToCheckFor = dw.Fields(0).Value
         

          Debug.Print "Looking for " & strCurrentWordToCheckFor & " in " & strCurrentString
         
          Dim TestPos As Integer
          TestPos = InStr(1, strCurrentString, strCurrentWordToCheckFor)

          If TestPos > 0 Then 'a sub-string in the deletable words list table exists somewhere in the NAME field of the other table
           boolFound = True 'set TRUE flag i.e. an entry has been found
           Debug.Print strCurrentWordToCheckFor & " is in " & strCurrentString
           
           'comment the following line out later
           MsgBox "The entry " & strCurrentWordToCheckFor & " exists in " & strCurrentString & " at position " & TestPos
           
           'store original NAME
           strOldName = strCurrentString
           
           intLenOfOldName = Len(strOldName) / 2
           
           If TestPos > intLenOfOldName Then
            'deleteable entry is at the RIGHT end of the field
            strNewName = Trim(Left(strOldName, TestPos - 1))
           ElseIf TestPos < intLenOfOldName Then
            'deleteable entry is at the LEFT or MIDDLE end of the field
            strNewName = Trim(Left(strOldName, TestPos - 1)) & Mid(strOldName, TestPos + Len(strCurrentWordToCheckFor), 99)
           End If
         
            MsgBox "This could be changed to : " & strNewName & " - use REPLACE to clean up remaining characters"
          End If
       
         'Move to the next record. Don't ever forget to do this.
         dw.MoveNext
    Loop
'end second loop
       
'Move to the next record. Don't ever forget to do this.
         rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

MsgBox "Finished looping through records."

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

dw.Close 'Close the recordset
Set dw = Nothing 'Clean up
End Sub
Last edited by Hagar Delest on Sun Jan 31, 2016 11:06 pm, edited 2 times in total.
Reason: tagged [Solved].
OpenOffice 3.1 on Windows 7
sameersabri
 
Posts: 2
Joined: Thu Jan 21, 2016 2:36 pm

Re: how to update a field’s value from within a VBA macro

Postby rudolfo » Sun Jan 24, 2016 3:11 am

I spot the words DAO and recordset and you talk about a VBA macro in the subject. All this is indicating that you are working with Microsoft Office (DAO stands for Database Access Objects and is a (outdated) technology that Microsoft used with MS Access (see Wikipedia on Jet Data Access Objects and Access Book).

This is a forum for Apache OpenOffice and Libre Office. Better ask questions about MS Office in one of the Microsoft forums.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: how to update a field’s value from within a VBA macro

Postby MTP » Mon Jan 25, 2016 11:10 pm

The OpenOffice API is very different from the Microsoft Office API. In addition, there are (smaller) differences between Microsoft's VBA and StarBasic or OpenOffice Basic. Just to take the first two lines of your VBA code for Access, in StarBasic for Base you would write instead
Code: Select all   Expand viewCollapse view
Sub myMacro()

   Dim rs As Object
   Dim s As String
   Dim oStatement As Object

   oStatement = ThisDataBaseDocument.CurrentController.ActiveConnection.CreateStatement
   s = "SELECT * FROM IDP_20160120"
   rs = oStatement.executeQuery(s)
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: how to update a field’s value from within a VBA macro

Postby sameersabri » Tue Jan 26, 2016 10:42 am

Thanks:)
OpenOffice 3.1 on Windows 7
sameersabri
 
Posts: 2
Joined: Thu Jan 21, 2016 2:36 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests