Maddie Leigh's Art Sales

This database is not one actually in use. It comes from some help sessions on the OOOForum. It might be a good base to work up from however.

The database used was kept as simple as possible to support the development of one form and a few macros. The schema is

The form that was developed is this. ( notice the name will change, as Juliana was the users name and I thought it best to use a different one - so I will get to making that change if necessary. )

The macros created do a lot of filling of data when a button is clicked, or an item in a list box is selected. The Form uses a number of sub forms to generate the required calculated fields. The two buttons named 'new' currently do nothing, but would be updated to bring up a data entry screen ( dialog box ) for entering new customer information. The execution of the dialog would trigger a validation routine to insure both completeness and to stop any duplicate entries.

The database should lend itself to the creation of a couple of reasonable reports. Pretty much the other screens that would be needed would require little or no macro coding.

The only item of note for this schema is the two forieng key relationships from Customer to Purchase Order. In order to do this the person creating the database must enter the actual ALTER TABLE commands from the SQL window, but everything else is done via the GUI.

Also, as I mentioned my idea would be to implement the new customer buttons by calling a dialog box. I think this is a good thing to include in the example. It is also, IMO, a good example of showing where from a UI one would want to use a dialog box - as the workflow of starting an order screen and then needing to add a customer recored would benefit from the modal quality of the dialog.

A second item would be the use of sub selects and views. The macros, such as this one, currently execute statements with sub selects, requiring the creation of a view, assigned to the Item Status Change event for one of the list boxes:

sub ShipToStatusChange( oev as variant )
'
' Fill in the following fields in
' Purchase_Order table
'  SHIP_TO_CUSTOMER_ID
'  SHIP_TO_CUSTOMER_NAME
'  SHIP_TO_COMPANY_NAME
'  SHIP_TO_ADDRESS
'  SHIP_TO_CITY
'  SHIP_TO_PROVINCE_STATE
'  SHIP_TO_POSTALCODE_ZIPCODE
'
dim CustomerID as integer
dim stmt as variant       ' a statement for the SQL to 
                          ' to run in
dim PO_Rowset as variant  ' actually this is the embedded dataform
dim Cust_ResultSet as variant ' CUSTOMERS table record
dim strSQL as String      ' the select statement we need

  PO_Rowset = oEv.Source.Model.Parent
  Stmt = PO_Rowset.ActiveConnection.CreateStatement
  ' get the customer record
  strSQL = "SELECT * from ""CUSTOMERS"" "_
               & " WHERE ""ID"" = "_
               &"(SELECT ""vCustomerList"".""ID"" "_
               &"FROM ""vCustomerList"" "_
               &"WHERE ""vCustomerList"".""NAME"" = '"_
               & oEv.Source.Text & "')"     
  Cust_ResultSet = stmt.executeQuery( strSQL )
    ' check that we got a record
  if not Cust_ResultSet.isBeforeFirst then
      msgBox( "Customer record not found" )
      goto ShipToStatusChangeExit
  else
  	Cust_ResultSet.Next
  endif
  with PO_Rowset.Columns
    .getByName("SHIP_TO_CUSTOMER_ID").updateInt(_
        Cust_ResultSet.columns.getByName("ID").getInt )
    .getByName("SHIP_TO_CUSTOMER_NAME").updateString(_
        oEv.Source.Text )
    .getByName("SHIP_TO_COMPANY_NAME").updateString(_
        Cust_ResultSet.columns.getByName("COMPANY").getString )
    .getByName("SHIP_TO_ADDRESS").updateString(_
        Cust_ResultSet.columns.getByName("STREET").getString )
    .getByName("SHIP_TO_CITY").updateString(_
        Cust_ResultSet.columns.getByName("CITY").getString )
    .getByName("SHIP_TO_PROVINCE_STATE").updateString(_
        Cust_ResultSet.columns.getByName("PROVINCE_STATE").getString )
	.getByName("SHIP_TO_POSTALCODE_ZIPCODE").updateString(_
	    Cust_ResultSet.columns.getByName("POSTAL_ZIP_CODE").getString )
  end with						
ShipToStatusChangeExit:
  Cust_ResultSet.Dispose

end sub

Now if I where to move forward with this database I would go back and change these to work with the QiQ build and utilize derived queries.