Printing a report based on a sqlite query

General
1

Ok… the below code is in a right click menu in my listbox. What it is supposed to do is pass the record (jobnumber) of a listbox item, then query up that jobnumber and pass it to the report. There are 26 fields in that record that I do some simple math on to come up with flow rates and a % increase/decrease between two different tests.

My question is…for example…

flow_050 is a field in the db
flow2_050 is a field in the db

These two represent a “before and after” picture of a flow rate.

I want to do some percentage difference between the two and I can do that in the beforerprinting event, the problem is if I do that, do I have to create a global variable called gFlow_050, assign orders.flow_050’s value to it, then do the math? That seems like alot of work. I’d have to create 26 additional global variables for this. Doesnt “orders” get passed to the report so I can get the value from orders.flow_050 etc etc?

Another question…(not important now…the above question is of a higher priority)

If you’re thinking, why am I opening the db here and not as soon as I run the app… well I am. But for some reason, if I do not open it AGAIN, I get a NILobject error on “orders”.

[code] gLoadRecord = Me.Cell(Me.ListIndex, 0)

Dim dbFile As New FolderItem("/home/themagicm/.portflowanalyzer/portflowdb.sqlite")

mDb = New SQLiteDatabase
mDb.DatabaseFile = dbFile

If mDb.Connect Then
  mIsConnected = True
Else
  mIsConnected = False
  msgbox("Error opening SQLite database: " + mDb.ErrorMessage)
  quit
End If

// Build the SQL statement that will be used to select the records
Dim sql As String 
sql = "SELECT * FROM flow_head where jobnumber=" + gLoadRecord
Dim rpt As New HeadFlowReport

// Now we select the records from the database and add them to the list.
Dim orders As RecordSet
orders = mDB.SQLSelect(sql)

 
If orders = Nil Then
  MsgBox("No records found to print.")
Else
  Dim ps As New PrinterSetup
  
  'gFlow_050 = val(orders.field("flow_050").stringvalue)
  
  // set the resolution to 300 DPI for printing
  ps.MaxHorizontalResolution = 300
  ps.MaxVerticalResolution = 300
  
  If ps.PageSetupDialog Then
    Dim g As Graphics
    
    g = OpenPrinterDialog(ps, Nil)
    
    
    If g <> Nil Then
      // if the report runs successfully
      If rpt.Run(orders, ps) Then
        rpt.Document.Print(g)
      End If
    End If
    
  End If
End If[/code]
2

You can access the dataset used by the report using Self.DataSource.Field(), so an average might be

me.Text = Format(((Self.DataSource.Field("flow_050").value + Self.DataSource.Field("flow2_050").value) / 2), "#.000")

Of course this is untested due to the lack of data, but I’m sure you get the idea.

As for the second part of your question I would look for “Dim mDB as New SQLiteDatabase” in your app open code - it’s a classic mistake when moving a local variable to a property that you (and I) don’t remove the Dim in the original code thus creating a local instance of the object while the new property remains Null.

HTH
Wayne

3

That doesnt work (the self.datasource.field)…

When I compile it tells me flow_050 doesnt exist. It exists in the database though. Is it expecting flow_050 to be a field on the report?

4

It doesn’t have to be a field in the report, but it does need to be part of the recordset passed to the report. If there is a field with flow_050 as the datasource then you can reference that field by name to get it’s value.

5

In my code above, thats what populates the report. My sql query is a select * which gets the record by jobnumber. So it is passed.

My beforeprinting has:

me.text=self.DataSource.field(“flow_050”).value

it fails saying that flow_050 doesnt exist.

6

Alex have a look at what I’ve done to the Gas Report Example here. In particular field14 (average).

HTH
Wayne

7

I see what you’re doing, but it goes back to what I said earlier… in my code snippet above… I dont think the recordset is being passed to the report or something like that…

for example…

me.text = str(Field2)

…works. Field2 is actually a field on the report. If my recordset is being passed, why doesnt Datasource.field(“flow_050”) work? “GasDataSet” on Gas Report Example is probably where Datasource.field(whatever) comes from. I dont have a dataset like that. I read it in from sqlite and I’m assuming this line:

rpt.Run(orders, ps)

…actually builds the report.

8

I can have a look at your project if you like - pm me if you want to keep it private.