Base Form Button Macro Doesn't Run

[MadMacks]

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 
  kill(sFile)
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
    ThisDatabaseDocument.CurrentController.connect
endif

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));"
oStatement.execute(strSQL)

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

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"";"
oStatement.execute(strSQL)

REM Delete the temporary text export table
strSQL ="drop table ""Mail"";"
oStatement.execute(strSQL)

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

End Sub

[Ratslinger]

Hello,

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.

[mauricio]

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

[Ratslinger]

Hello,

See a few items. Changed this line:

sUrl = ThisComponent.getURL()

to:

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.

Edit:

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

[Ratslinger]

Note - macro executed from button Execute action event.

No arguments needed.