This is another self contained database. In this case the information gathered is used during the course of inspecting contractual work progress in a ship yard.
This table structure is a pure relational model, utilizing only 1 to Many relationships. It also includes a set of basic macros and single table to programmatically store information relating to inspections that must be performed again.
The ship survey database gives a good example of using a couple of small macros to perform a business function that could not otherwise be implemented without a macro. In this case it is the requirement put forward by the user was:
Just to explain you 'the name of the game': during construction we do not accept all the things: for such cases we have conclusions 'REJECTED' and 'OWC'. In both cases same item has to be inspected at least one more time. It would be handy to have information which items have to be re-inspected on one place (separate table?). Query of table tInspection with R or OWC as criteria can not help: doesn't mean that same Item is not accepted on some later date. Of course, situation is more complicated, all records in such (new) table has to be unique. Moreover if one re-inspect an item, there is no guarantee that this item will be accepted - two-three-inspections are not unusual, depending on a Builder. So basically, on insert of new record macro has to check if such record already exist in a 'new' table: if exist and if conclusion is R or OWC, macro suppose to update a date in those new table; if conclusion is something else (not R or OWC) macro suppose to delete that record from the new table. If record doesn't exist and conclusion is R or OWC, macro suppose to add record to the table - Date and Inspection are Ok. However, all records has to be 'intact' in a table tInspection.
To add this functionality the table tFor_Confirmation was added, as you can see in the schema above. A basic library was then added with three macros added.
sub onAfterRecordAction( oEv as object ) dim cncField 'if oEv.Action = com.sun.star.sdb.RowChangeAction.UPDATE then cncField = oEv.Source.getByName( "txtConclusion" ).BoundField if cncField.getString = "R" or cncField.getString = "OWC" then ' insert if needed insertForConfirm( oEv.Source ) else removeForCinfirm( oEv.Source ) end if 'end if end sub sub insertForConfirm( aDataForm as variant ) dim stmt dim prepStmt dim rs ' frist see if it is already in the table prepStmt = aDataForm.ActiveConnection.PrepareStatement( _ "SELECT * FROM ""tFor_Confirmation"" WHERE ""Inspection"" = ? AND ""Type"" = ? ", _ com.sun.star.sdb.CommandType.COMMAND ) prepStmt.setString( 1, aDataForm.GetByname( "txtInspection" ).BoundField.getString ) prepStmt.setString( 2, aDataForm.getByname( "txtType" ).BoundField.getString ) rs = prepStmt.executeQuery if not rs.isBeforeFirst then ' not found so insert it stmt = aDataForm.ActiveConnection.CreateStatement stmt.executeUpdate( _ "INSERT INTO ""tFor_Confirmation"" ( " _ & "SELECT ""Inspection"", ""Date"", ""Type"", ""Conclusion"", ""Id_No"" " _ & "from ""tInspection"" ""tInspection"" where ""Id_No"" = " _ & aDataForm.Columns.getByName( "Id_No" ).getString & ")" ) end if end sub sub removeForCinfirm( aDataForm as variant ) ' here we are not going to check ' it would be quicker just to run ' the delete statement ' if the record is not there - no harm dim prepStmt prepStmt = aDataForm.ActiveConnection.PrepareStatement( _ "DELETE FROM ""tFor_Confirmation"" WHERE ""Inspection"" = ? AND ""Type"" = ? ", _ com.sun.star.sdb.CommandType.COMMAND ) prepStmt.setString( 1, aDataForm.GetByname( "txtInspection" ).BoundField.getString ) prepStmt.setString( 2, aDataForm.getByname( "txtType" ).BoundField.getString ) prepStmt.executeUpdate end sub
These sub procedures are an example of using one of the events on the dataform. The onAfterRecordAction sub is what is actually assigned to the dataforms After Record Action event, while the actual work is seperated out into the subs insertForConfirm abd removeForCinfirm.