Incorrect Property Value 2nd time through loop

English
, , , , , ,
#1

Hi folks! I’m working on a database that will track the weekly giving of a church and produce a deposit slip for our treasurer. It uses macros to total the various types of money coming in (coins, bills, checks), but I’m having a problem with the macro that totals up the checks.

The database table has 30 columns for checks (labeled Checks_1, Checks_2, Checks_3, etc) and each of these columns has a control on the main form so that the treasurers can enter the checks one by one. The table also has a column called Checks_Total that is to be dynamically updated by a macro that adds up all the checks as they’re being entered in.

I’ve been able to successfully write a macro to accomplish this for the other types of giving (coins/bills), but because of the number of columns, I am attempting to do the same thing through a for…next loop. However, I’m running into an “Incorrect Property Value” error in this. As I’ve worked on debugging this, I’ve discovered that the first time through the loop, it works fine. It’s on the second time through the loop that I get the error. I’m not much of a programmer, but I’m learning. Here is a snippet of the code for that macro. Hopefully you can find what it is I’m doing wrong:

Dim TotChecks, addToTotal
Dim checksGBN as String
     	
root_form = ThisComponent.Drawpage.Forms
main_frm = root_form.getByName("MainForm")

For Counter = 0 to 29
	checksGBN = "fmtChecks_" + (Counter + 1)
	addToTotal = main_frm.getByName(checksGBN)
	TotChecks = TotChecks + addToTotal ' incorrect property value 2nd time
Next Counter

I’ve tried designating the TotChecks and addToTotal variables as various types (such as Double, Single, and Currency) but it fails the first time through the loop. I’ve also tried running conversion functions on them (such as CSng, CDbl) but to no avail. Also, running “TypeName” on the variables shows that they become Objects after they are given a value. Not sure if this is the problem and/or how I can overcome this.

Thanks in advance for your help!

Edit: Just in case it would help to see how I managed to do this with the other types of giving, here’s the rest of my macros (including all the debugging garbage I had inserted into the above code):

Sub CalculateCoins (oEvent As Object)

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim strSQL as String
Dim Conn
Dim Stmt
Dim DB
Dim TableName as String
Dim DatabaseName as String
	 
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DatabaseName = "LBCN_Giving_Database"
DataSource = DatabaseContext.getByName(DatabaseName)
TableName = "Main_Database_Table"

Connection = DataSource.GetConnection("","")

Dim NumDollars, NumQuarters, NumDimes, NumNickels, NumPennies
Dim TotCoins as Double

root_form = ThisComponent.Drawpage.Forms
main_frm = root_form.getByName("MainForm")
NumDollars = main_frm.getByName("fmtCoins_100")
NumQuarters = main_frm.getByName("fmtCoins_25")
NumDimes = main_frm.getByName("fmtCoins_10")
NumNickels = main_frm.getByName("fmtCoins_5")
NumPennies = main_frm.getByName("fmtCoins_1")
TotCoins = (CInt(NumDollars.text) + (CInt(NumQuarters.text) * .25) + (CInt(NumDimes.text) * .1) + (CInt(NumNickels.text) * .05) + (CInt(NumPennies.text) * .01))

DB=DatabaseContext.getByName(DatabaseName)
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL = "UPDATE ""Main_Database_Table"" SET ""Coins_Total"" = '" + CStr(TotCoins) + "'"
Stmt.executeUpdate(strSQL)
Conn.close()

On Error Goto ErrorHandler
oForm = oEvent.Source.Model.Parent
IF oForm.isnew THEN oForm.insertRow() ELSE oForm.updateRow()
ErrorHandler:

End Sub

Sub CalculateBills (oEvent As Object)

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim strSQL as String
Dim Conn
Dim Stmt
Dim DB
Dim TableName as String
Dim DatabaseName as String
	 
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DatabaseName = "LBCN_Giving_Database"
DataSource = DatabaseContext.getByName(DatabaseName)
TableName = "Main_Database_Table"

Connection = DataSource.GetConnection("","")

Dim NumOnes, NumFives, NumTens, NumTwenties, NumFifties, NumHundreds
Dim TotBills as Double

root_form = ThisComponent.Drawpage.Forms
main_frm = root_form.getByName("MainForm")
NumOnes = main_frm.getByName("fmtBills_1")
NumFives = main_frm.getByName("fmtBills_5")
NumTens = main_frm.getByName("fmtBills_10")
NumTwenties = main_frm.getByName("fmtBills_20")
NumFifties = main_frm.getByName("fmtBills_50")
NumHundreds = main_frm.getByName("fmtBills_100")
TotBills = (CInt(NumOnes.text) + (CInt(NumFives.text) * 5) + (CInt(NumTens.text) * 10) + (CInt(NumTwenties.text) * 20) + (CInt(NumFifties.text) * 50) + (CInt(NumHundreds.text) * 100))


DB=DatabaseContext.getByName(DatabaseName)
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL = "UPDATE ""Main_Database_Table"" SET ""Bills_Total"" = '" + CStr(TotBills) + "'"
Stmt.executeUpdate(strSQL)
Conn.close()


On Error Goto ErrorHandler
oForm = oEvent.Source.Model.Parent
IF oForm.isnew THEN oForm.insertRow() ELSE oForm.updateRow()
ErrorHandler:

End Sub

Sub CalculateDesignated (oEvent As Object)

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim strSQL as String
Dim Conn
Dim Stmt
Dim DB
Dim TableName as String
Dim DatabaseName as String
	 
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DatabaseName = "LBCN_Giving_Database"
DataSource = DatabaseContext.getByName(DatabaseName)
TableName = "Main_Database_Table"

Connection = DataSource.GetConnection("","")

Dim LoveOff, EquipOff, SeminaryOff, GrowthOff, CampOff, SSOff, OtherOff
Dim TotDesignatedOff as Double

root_form = ThisComponent.Drawpage.Forms
main_frm = root_form.getByName("MainForm")
LoveOff = main_frm.getByName("fmtLove_Offerings")
EquipOff = main_frm.getByName("fmtEquip_Offerings")
SeminaryOff = main_frm.getByName("fmtSeminary_Offerings")
GrowthOff = main_frm.getByName("fmtGrowth_Offerings")
CampOff = main_frm.getByName("fmtCamp_Offerings")
SSOff = main_frm.getByName("fmtSunday_School_Offerings")
OtherOff = main_frm.getByName("fmtOther_Designations")
TotDesignatedOff = CInt(LoveOff.text) + CInt(EquipOff.text) + CInt(SeminaryOff.text) + CInt(GrowthOff.text) + CInt(CampOff.text) + CInt(SSOff.text) + CInt(OtherOff.text)

DB=DatabaseContext.getByName(DatabaseName)
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL = "UPDATE ""Main_Database_Table"" SET ""Total_Designated_Offerings"" = '" + CStr(TotDesignatedOff) + "'"
Stmt.executeUpdate(strSQL)
Conn.close()

On Error Goto ErrorHandler
oForm = oEvent.Source.Model.Parent
IF oForm.isnew THEN oForm.insertRow() ELSE oForm.updateRow()
ErrorHandler:

End Sub

Sub CalculateChecks (oEvent As Object)

Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim strSQL as String
Dim Conn
Dim Stmt
Dim DB
Dim TableName as String
Dim DatabaseName as String
	 
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DatabaseName = "LBCN_Giving_Database"
DataSource = DatabaseContext.getByName(DatabaseName)
TableName = "Main_Database_Table"

Connection = DataSource.GetConnection("","")

Dim TotChecks, addToTotal
Dim checksGBN as String
Dim varType as String
     	
root_form = ThisComponent.Drawpage.Forms
main_frm = root_form.getByName("MainForm")

For Counter = 0 to 29
	Print "(Loop 1 of 3) About to set the getByName variable"
	checksGBN = "fmtChecks_" + (Counter + 1) ' checksGBN is good
	Print "(Loop 2 of 3) About to retrieve the current check value and place it in addToTotal" 
	addToTotal = main_frm.getByName(checksGBN) ' now working
'	varType = TypeName(addToTotal)
'	Print "addToTotal is type " + varType
'	Print "(Loop 3 of 4) Converting addToTotal to Single into tempVar"
	Print "(Loop 3 of 3) About to add addToTotal to TotChecks"
	TotChecks = TotChecks + addToTotal ' incorrect property value 2nd time
'	varType = TypeName(TotChecks)
'	Print "TotChecks is type " + varType
Next Counter

DB=DatabaseContext.getByName(DatabaseName)
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL = "UPDATE ""Main_Database_Table"" SET ""Checks_Total"" = '" + CStr(TotChecks) + "'"
Stmt.executeUpdate(strSQL)
Conn.close()

On Error Goto ErrorHandler
oForm = oEvent.Source.Model.Parent
IF oForm.isnew THEN oForm.insertRow() ELSE oForm.updateRow()
ErrorHandler:

End Sub

#2

Just looking at the initial routine you have a problem with this line:

TotChecks = TotChecks + addToTotal

addToTotal is the object and you need to access the property within (ie: addToTotal.Text or addToTotal.Value).

Edit: A secondary glance also shows you never initialize total fields. You should always do this. For example, before your loop for check addition - TotChecks = 0.00.

#3

Ah, thank-you so much! I was guessing that it was an issue with addToTotal being an Object, but I couldn’t figure out how to deal with it. This worked perfectly. Initially, it didn’t like “addToTotal.Value” but what worked was doing TotChecks = TotChecks + CDbl(addToTotal.Text).

Thank-you Ratslinger! This LibreOffice community has been fantastic, and has helped me so much to design databases that do exactly what I want them to do. Keep it up, folks!

#4

You are very welcome. The .Text or .Value is dependent upon the control/data type being used. My guess was .Text but included both for safety sake.

#5

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.