Base Form Button Macro Doesn't Run

Created and tested a macro in Base. Running the macro prompts the user to input a value and then based on that value runs a set of SQL statements and writes a file to the system folder structure. This all works when I run the macro directly from the macro editor.

I then added a button to the main form I’m using, and assigned the macro to the Execute method of the button. I then exit design mode, open the form, and can roll thru records and do whatever I what with the basic form. But, when I push the button that has the macro assigned to it nothing happens. I don’t get an impute prompt and the SQL statements don’t run and the external export file is not created.

Are there restrictions on using input prompts from a button macro? Do I need to assign the macro to a different method on the button? I’m using a MacBook so does the touch pad click on the button work differently than if I was using a real mouse?

Here’s the Macro…

REM ***** Extracts members who still get the Mailing *****

Sub CreateMailList

REM If the target file exists delete it before continuing
REM or you will concatinate the data making a larger file
sUrl = ThisComponent.getURL()
sParts = Split(sUrl, "/")
ReDim Preserve sParts(0 to UBound(sParts) - 1)
CurrentFolder = Join(sParts, "/")
sFile = CurrentFolder & "/Mail.csv"
If FileExists(sFile) Then 
End If 

REM Get the cut off date
InputVal = InputBox("Enter Cut Off Date (YYYY/MM):", "Mailing Cutoff", "2020/10")

Dim oStatement As Object

REM Connect to Local DB 
if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then

oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()

REM Define the temporary text export table
strSQL="create text table ""Mail""" &_
       "(""LastName"" varchar(20), ""FirstName"" varchar(20), ""Address"" varchar(40), " &_
       """City"" varchar(20), ""State"" varchar(20), ""Zip5"" varchar(5), ""Zip4"" varchar(4));"

REM Map it to the external CSV file
strSQL ="set table ""Mail"" source ""Mail.csv;encoding=UTF-8"";"

REM Extract the target data and put it in the external file
strSQL ="insert into ""Mail"" SELECT ""FirstName"", ""LastName"", " &_
        """Address"", ""City"", ""State"", ""Zip5"", ""Zip4"" " &_
        "FROM ""Members"" WHERE (""MailThru"" >= '" &_
        InputVal &_
        "' ) " &_
        "ORDER BY ""Zip5"", ""Zip4"";"

REM Delete the temporary text export table
strSQL ="drop table ""Mail"";"

MsgBox("Mailing File Was Created...")

End Sub


There are items which are different when running from different places. ThisComponent is an example. Without knowing what your code is, can’t tell the problem. Input prompts can be a problem depending upon what is issuing the prompt and possibly the database being used - neither is stated.

Is better if you show your code, or the error.

If you assign your macro to button, this macro should receive one argument.

Sub myMacro(args)
    REM Your code
End Sub


See a few items. Changed this line:

sUrl = ThisComponent.getURL()


sUrl = ThisDatabaseDocument.getURL()

Also, you can greatly cut down on code by replacing the create/set/insert statements with a Select statement → How to export from OO Base as a .CSV?

Did test with HSQLDB embedded on Ubuntu 18.04 with LO v6.4.1.2 and using my own table and the above select alternative. Code worked from both the IDE and a form.


Also guessing your input data is a text field in the table record as that is not a proper date format for databases.

In case of further problems, here is the sample I used. Exited sub before executing your SQL.

Sample ----- TextFileMacro.odb

Note - macro executed from button Execute action event.

No arguments needed.