OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Contents of one list box dependent upon another's selection?
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Decker87
OOo Enthusiast
OOo Enthusiast


Joined: 21 May 2007
Posts: 163

PostPosted: Tue May 22, 2007 11:58 am    Post subject: Contents of one list box dependent upon another's selection? Reply with quote

Hi. I'm asking for some help with something probably relatively simple. I'm trying to make the contents of one list box on a form depend on the selection the user has made in the previous list box.

For example, like you might see on a site selling cars, whereby you select the make, then select the model, then select the trim, and so on.

At this point I am free to create the tables or fields in any way that is conducive to doing this.
Back to top
View user's profile Send private message
Decker87
OOo Enthusiast
OOo Enthusiast


Joined: 21 May 2007
Posts: 163

PostPosted: Wed May 23, 2007 5:01 am    Post subject: Reply with quote

Out of 12 people, no one knows?
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed May 23, 2007 9:07 am    Post subject: Reply with quote

Hi,

Sorry, it takes a little while to put these kinds of posts together.

There have been a bunch of threads that have touched on this in one way of another - but I think I will try to pull this altogether in one.
Here is the form I will be discussing:



The functionality is:

When a Category is selected the ItemCode listbox is enabled and filled with items in that category.

When an ItemCode is selected the table grid is updated to show all inventory transaction records for the item. The text Box 'Balance' displays the number of items in inventory.

The 'Submit' button is not activated until all required fields are filled in - in this that is all fields.

When the submit button is clicked a record is added to the inventory_transaction table and the table grid is updated to display this new entry, the Balance textbox is updated also.

The Category ListBox has property 'status changed' set to call this function:
Code:

REM  ***** 
REM  *****  onStatusChange_STKOUT_Category( oEv as object )
REM  ***** 
REM  *****  the event handler for the category list box
REM  ***** 
REM  *****  I broke this out as a seperate handler from
REM  *****  the other controls because it must
REM  *****  be the first thing entered as it
REM  *****  is used to populate the itemcode list
REM  ***** 
sub onStatusChange_STKOUT_Category( oEv as object )

   REM  ***** 
   REM  *****  if the user selected a category
   REM  *****  then update the itemcode listbox
   REM  *****  and enable the data entry controls
   REM  ***** 
   if oEv.Source.Model.CurrentValue <> "" then
      limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
   else
      REM  ***** 
      REM  *****  no category so disable
      REM  *****  everything else
      REM  ***** 
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False )
   end if
   
end sub


All other controls on the form, that have a status or value change event are assigned to this single function:

Code:

REM  ***** 
REM  *****  on_STKOUT_ControlValueChanged( oEv as Object )
REM  ***** 
REM  *****  A common event handler shared by
REM  *****  all the data entry controls
REM  ***** 
REM  ***** 
REM  ***** 
sub on_STKOUT_ControlValueChanged( oEv as Object )

   dim oModel

   oModel = oEv.Source.Model
   
   REM  ***** 
   REM  *****  if the handler is called by
   REM  *****  ItemCode list box then update
   REM  *****  the table grid
   REM  ***** 
   if oModel.Name = "lbxitemCode" then
      if oModel.CurrentValue <> "" then
         displayItemGrid( oModel.Parent.Parent.getByname( "DataEntry" ), oModel.CurrentValue )
      endif
   endif

   REM  ***** 
   REM  *****  for all controls call the
   REM  *****  validation routine
   REM  *****  and use the retured value
   REM  *****  to either enable or disable
   REM  *****  the submit button
   REM  ***** 
   oModel.Parent.getByName( "btnSubmit" ).Enabled = isValid_STKIN_STKOUT( oModel.Parent )

end sub


What you are interested is the function limitItems .

This is the function that does the work of limiting the selection items in the ItemCode listbox, based on the selection in Category listbox.

Code:

REM  ***** 
REM  *****  limitItems( aDataForm as variant, aCategory as string )
REM  ***** 
REM  *****  update the itemcodes displayed
REM  *****  in the itemcode listbox
REM  ***** 
REM  *****  I pass in the dataform that owns the
REM  *****  list box, perhaps this should be changed
REM  *****  to pass in the list box directly
REM  ***** 
sub limitItems( aDataForm as variant, aCategory as string )
   Dim new_sql(0) As string
   Dim olist as variant
 
   oList= aDataForm.getbyname("lbxitemCode")
     new_sql(0) = "SELECT ""ItemCode"", ""ItemCode"" FROM ""Store_Code"" WHERE ""ItemCategory"" = '" & aCategory & "'"
   oList.ListSource = new_sql
   
end sub


The important thing to get from this routine is the way I update the control with the new SQL select statement.

Notice that new_sql is declared as an array of strings and the actual SQL statement is added as the first item in this array.

Then I pass the array, not the string, to the ListSource property of the control This triggers the control to update the list of items by executing the SQL statement found in the fist array offset.

The other interesting thing for you to notice is how I refer to the objects. I am using the Application Programming Interface ( API ) to move through the hierarchy of objects.

If you look at the beginning of the API list on the forum you will find a couple of sticky threads that will point you to resources explaining the API and general information on how to use it from a Basic script.

Again, if you want to go over specifics of this code snippet - even if it is line by line, just let me know and we will do so.

Drew

PS

And here is the form when all required data has been entered by the user and the Submit button has been activated.


_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
olegos
General User
General User


Joined: 22 May 2007
Posts: 18

PostPosted: Wed May 23, 2007 9:06 pm    Post subject: Reply with quote

Hey Drew,

Thanks for another great post. Could you show what displayItemGrid function looks like, please? Thanks!

(Waiting for your follow-up in the "Multiple Sub Forms Multiple Tables" thread too, which should be helpful to me)
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed May 23, 2007 9:11 pm    Post subject: Reply with quote

Tell you what, here is the whole library

There are a few superfluous routines, one more pass through the refactor engine ( my head ) and then I will put the whole thing up for download

There are 3 forms, New Item, Stock In, Stock Out
There is the library
And a custom toolbar

The whole this is wrapped up in an OXT file and installed with the extension manager

----------------------------MAIN MODULE----------------
This is simply a few slots that can be called from a custom tool bar button, or menu item.
Code:

REM  *****  BASIC  *****

Sub Main

   OpenForm( "Stock Out Data Entry" )

End Sub

sub OpenNewItemForm
   OpenForm( "New Code Entry" )
end sub

sub OpenStockIn
   OpenForm( "Stock In Data Entry" )
end sub

sub OpenStockOut
   OpenForm( "Stock Out Data Entry" )
end sub

sub OpenForm( aFormName as string )
   dim oFormDoc as Object      
   dim Args(2) as new com.sun.star.beans.PropertyValue
   dim oWindow as Object

   oDBContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

   oDBSource = oDBContext.getRegisteredObject("Inventory2_2")
   
   oDBConnection = oDBSource.getConnection("sa","")
   

   BasicLibraries.LoadLibrary("Inventory_2_2")


   ' get the document container
   oForms = oDBSource.DataBaseDocument.getFormDocuments


   ' set up the property values for the command
   Args(2).name = "WindowAttribute"
   Args(2).value = 2
   Args(1).name = "OpenMode"
   Args(1).value = "open"
   Args(0).name = "ActiveConnection"
   Args(0).value = oDBConnection
   
   oFormWriter = oForms.LoadComponentFromURL(aFormName ,"_Blank" , 0 ,Args() )
   'oWindow = oFormWriter.CurrentController.Frame.Controller.Frame.ContainerWindow
   'oWindow.PosSize.X = 0
   'oWindow.PosSize.Y = 0   
   'oWindow.PosSize.Width = 800
   'oWindow.PosSize.Height = 600
   'reSizeFrame( 0, 0, 990, 764 )         
   'oDBFrame.currentController.Frame.close(False)
end sub


---------------Module 1------------------------

Code:

REM  ***** 
REM  *****  some general purpose routines
REM  *****  used by the data entry forms
REM  ***** 

REM  ***** 
REM  *****  just for testing
REM  ***** 
Sub Main   
   dim aDate as variant
   aDate = now
   print dbTimeStamp( aDate ).seconds
   print aDate
End Sub

REM  ***** 
REM  *****  dbDate( aBasicDate as date ) as variant
REM  ***** 
REM  *****  pass in a BASIC date variable
REM  *****  and get back a
REM  *****  com.sun.star.util.Date structure
REM  *****  used by the dataaccess routines
REM  ***** 
REM  *****  NOTE -   in a production system
REM  *****        this would most likely
REM  *****        by a trigger on the table
REM  *****        so that the date/time is
REM  *****        picked up from the servers
REM  *****         clock and not the workstations
REM  ***** 
function dbDate( aBasicDate as date ) as variant
   dim oDBDate as new com.sun.star.util.Date
   
   oDbDate.Day = Day( aBasicDate )
   oDbDate.Month = month( aBasicDate )
   oDbDate.Year = year( aBasicDate )
   dbDate = oDbDate
   
end function

REM  ***** 
REM  *****  basDate( aDBDate as variant ) as date
REM  ***** 
REM  *****  Pass in a com.sun.star.util.Date structure
REM  *****  and get back a BASIC date variable type
REM  ***** 
function basDate( aDBDate as variant ) as date
   
   basDate = dateSerial( aDbDate.Year, aDbDate.Month, aDbDate.Day )
   
end function

REM  ***** 
REM  *****  dbTimeStamp( aBasicDate as date ) as variant
REM  ***** 
REM  *****  pass in a BASIC date variable
REM  *****  and get back a
REM  *****  com.sun.star.util.DateTime structure
REM  *****  used by the dataaccess routines
REM  *****  as a TimeStamp
REM  ***** 
REM  *****  NOTE -   in a production system
REM  *****        this would most likely
REM  *****        by a trigger on the table
REM  *****        so that the date/time is
REM  *****        picked up from the servers
REM  *****         clock and not the workstations
REM  ***** 
function dbTimeStamp( aBasicDate as date ) as variant
   dim odbTimeStamp as new com.sun.star.util.DateTime
   
   odbTimeStamp.Day = Day( aBasicDate )
   odbTimeStamp.Month = Month( aBasicDate )
   odbTimeStamp.Year = Year( aBasicDate )   
   odbTimeStamp.Hours = hour( aBasicDate )
   odbTimeStamp.Minutes = Minute( aBasicDate )
   odbTimeStamp.Seconds = Second( aBasicDate )
   dbTimeStamp = odbTimeStamp
end function

REM  ***** 
REM  *****  basTimeStamp( aDBTimeStamp as variant ) as date
REM  ***** 
REM  *****  not finished - not needed so far
REM  ***** 
function basTimeStamp( aDBTimeStamp as variant ) as date
   dim aDate as date
   
end function

REM  ***** 
REM  *****  isValid_STKIN_STKOUT( aDataForm as object )
REM  ***** 
REM  *****  aDataform is the parent of all the
REM  *****  data entry controls
REM  ***** 
REM  *****  The routine determins if enough
REM  *****  information has been supplied
REM  *****  for an entry to the table
REM  ***** 
REM  *****  this routine is used by both the
REM  *****  stock in and stock out forms
REM  *****  as they are identical exccept
REM  *****  for the qty being positive or negative
REM  ***** 
function isValid_STKIN_STKOUT( aDataForm as object ) as boolean
   dim bResult as boolean

   bResult = False
   with aDataForm
      if .getByName( "lbxitemCode" ).CurrentValue <> "" then
         if .getByName( "lbxUser" ).CurrentValue <> "" then
            if .getByName( "lbxCategory" ).CurrentValue <> "" then
               if .getByName( "lbxReason" ).CurrentValue <> "" then
                  if .getByname( "tbxQuantity" ).value > -1  then
                     bResult = True
                  end if
               end if
            end if
         end if
      end if
   end with
   isValid_STKIN_STKOUT = bResult
end function

REM  ***** 
REM  *****  enable_STKIN_STKOUT_Controls( aDataForm as variant, onOff as boolean )
REM  ***** 
REM  *****  simply enable or disable a group of controls
REM  ***** 
REM  *****  this routine is used by both the
REM  *****  stock in and stock out forms
REM  *****  as they are identical exccept
REM  *****  for the qty being positive or negative
REM  ***** 
sub enable_STKIN_STKOUT_Controls( aDataForm as variant, onOff as boolean )
   'xray.xray aDataForm.getByName( "lbxitemCode" ).CurrentValue = NULL
   'aDataForm.getByName( "lbxitemCode" ).CurrentValue = NULL
   with aDataForm
      .getByName( "lbxitemCode" ).enabled = onOff
      .getByName( "lbxUser" ).enabled = onOff
      .getByName( "lbxReason" ).enabled = onOff
      .getByName( "tbxQuantity" ).enabled = onOff
      .Parent.getByName( "DataEntry").getByName( "tcDataEntry" ).enabled = onOff
   end with
end sub

REM  ***** 
REM  *****  displayItemGrid( aDataForm as variant, aItemCode as string )
REM  ***** 
REM  *****  aDataForm is the parent control for
REM  *****  table grid control
REM  ***** 
REM  *****  aItemCode is the string to use for the filter
REM  ***** 
REM  *****  the routine sets the query with filter
REM  *****  for the source of the table grid control
REM  ***** 
REM  *****  this is also used to limit the sub_form
REM  *****  control used to retrieve the SUM
REM  *****  of this particular itemcode
REM  ***** 
REM  *****  The sub_form with the sum is automatically
REM  *****  refreshed when the dataform passed in
REM  *****  is reloaded
REM  ***** 
REM  ***** 
REM  *****  this routine is used by both the
REM  *****  stock in and stock out forms
REM  *****  as they are identical exccept
REM  *****  for the qty being positive or negative
REM  ***** 
sub displayItemGrid( aDataForm as variant, aItemCode as string )
   dim sSQL as string

   sSQL = "SELECT * FROM ""Store_DataEntry"" ORDER BY ""Date"" DESC "
   aDataForm.Command = sSQL
   aDataForm.Filter = " ""ItemCode"" = '" & aItemCode & "'"
   aDataForm.ApplyFilter = True
   aDataForm.reload
   
end sub

REM  ***** 
REM  *****  limitItems( aDataForm as variant, aCategory as string )
REM  ***** 
REM  *****  update the itemcodes displayed
REM  *****  in the itemcode listbox
REM  ***** 
REM  *****  I pass in the dataform that owns the
REM  *****  list box, perhaps this should be changed
REM  *****  to pass in the list box directly
REM  ***** 
sub limitItems( aDataForm as variant, aCategory as string )
   Dim new_sql(0) As string
   Dim olist as variant
 
   oList= aDataForm.getbyname("lbxitemCode")
     new_sql(0) = "SELECT ""ItemCode"", ""ItemCode"" FROM ""Store_Code"" WHERE ""ItemCategory"" = '" & aCategory & "'"
   oList.ListSource = new_sql
   
end sub

sub CloseForm( aFrame as variant )
   dim Array()
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dispatcher.executeDispatch(aFrame, ".uno:CloseDoc", "", 0, Array())
end sub

sub clearControls( aDataForm as variant )
   xray.xray aDataForm.getByName( "lbxitemCode" )
   with aDataForm
      .getByName( "lbxitemCode" ).enabled = onOff
      .getByName( "lbxUser" ).enabled = onOff
      .getByName( "lbxReason" ).enabled = onOff
      .getByName( "tbxQuantity" ).enabled = onOff
      .Parent.getByName( "DataEntry").getByName( "tcDataEntry" ).enabled = onOff
   end with
end sub

sub hideBars
   frame = thisComponent.CurrentController.Frame
   layout = frame.LayoutManager
   layout.setVisible(False)
   'xray.xray frame
   Frame.Title = "eventKeeper [ Attendence Screening ]"
end sub


---------------NewItem Module-----------------------------------------
Code:

Rem ***
Rem ***      Routines used by the NewItem form
Rem ***
Rem ***      
Rem ***      User will create a new item code using this form. 
Rem ***      ItemCode and ItemName will have to be keyed in by the user. 
Rem ***      As for the ItemCategory and ItemUOM,
Rem ***      user will have to go thru the selection from the table
Rem ***      Store_Category and Store_UOM respectively. 
Rem ***      When the submit button is pressed,
Rem ***      the new item will be added into the Store_Code table
Rem ***      as will as another data created in the Store_DataEntry
Rem ***      Table with the following:
Rem ***      
Rem ***      1. ID as running number
Rem ***      2. Date as current Date
Rem ***      3. User as “System”
Rem ***      4. ItemCode
Rem ***      5. QTYEntered as 0
Rem ***      6. Reasons as “Create New Code”

sub onClick_SubmitButton( oEv as object )
   
   updateStoreCode( oEv.Source.Model.Parent )
   updateStoreDataEntry( oEv.Source.Model.Parent )
   oev.Source.Model.Parent.Parent.Parent.CurrentController.Frame.close( true )
   
end sub

sub updateStoreCode( aDataForm as variant )
   dim oPrepStatement

   oPrepStatement = aDataForm.ActiveConnection.prepareStatement( "INSERT INTO ""Store_Code"" VALUES( ?,?,?,? ) " )
   oPrepStatement.setString( 1, aDataForm.getByName( "txtItemCode" ).text )
   oPrepStatement.setString( 2, aDataForm.getByName( "txtItemName" ).text )
   oPrepStatement.setString( 3, aDataForm.getByName( "ListBoxItemCategory" ).CurrentValue )
   oPrepStatement.setString( 4, aDataForm.getByName( "ListBoxItemUOM" ).CurrentValue )
   oPrepStatement.executeUpdate
end sub

sub updateStoreDataEntry( aDataForm as variant )
   dim oPrepStatement
   
   oPrepStatement = aDataForm.ActiveConnection.prepareStatement( "INSERT INTO ""Store_DataEntry"" VALUES( ?,?,?,?,?,? ) " )
   oPrepStatement.setNull( 1, 1 ) ' pk
   oPrepStatement.setTimeStamp( 2, dbTimeStamp( now ) ) ' date
   oPrepStatement.setString( 3, "System" ) 'user
   oPrepStatement.setString( 4, aDataForm.getByName( "txtItemCode" ).text ) 'itemcode
   oPrepStatement.setInt( 5, 0 ) 'Qty
   oPrepStatement.setString( 6, "Create New Code" ) 'Reason
   oPrepStatement.executeUpdate
end sub

Rem ***      
Rem ***      isValidInput( aDataForm as object )
Rem ***      
Rem ***      aDataForm is the DataForm object
Rem ***      that owns all the controls on the
Rem ***      NewItem form
Rem ***      
Rem ***      function used to determine if the
Rem ***      newItem form is ready to submit data
Rem ***      
Rem ***      check for data in
Rem ***      ItemCode
Rem ***      ItemName
Rem ***      ItemCategory
Rem ***      ItemUOM
Rem ***      
function isValidInput( aDataForm as object ) as boolean
   dim bResult as boolean

   bResult = False
   with aDataForm
      if .getByName( "txtItemCode" ).text <> "" then
         if .getByName( "txtItemName" ).Text <> "" then
            if .getByName( "ListBoxItemCategory" ).CurrentValue <> "" then
               if .getByname( "ListBoxItemUOM" ).CurrentValue <> "" then
                  bResult = True
               end if
            end if
         end if
      end if
   end with
   isValidInput = bResult
end function

Rem ***      
Rem ***      onControlValueChanged
Rem ***      
Rem ***      simply a convient place for
Rem ***      all the controls to call to
Rem ***      when some change happens to
Rem ***      the value in the control
Rem ***      
Rem ***      
sub onControlValueChanged( oEv as Object )

   oEv.Source.Model.Parent.getByName( "PushButton" ).Enabled = isValidInput( oEv.Source.Model.Parent )

end sub

sub onClick_btnClose( oev as object )
   CloseForm( oev.Source.Model.Parent.Parent.Parent.CurrentController.Frame )
end sub


-----------Stock In Module ----------------------------
Code:

REM  ***** 
REM  *****  Routines used by the Stock In Form
REM  ***** 
REM  ***** 
REM  ***** 
REM  *****  Once the submit button is pressed,
REM  *****  It will create a data in the
REM  *****  Store_DataEntry Table with the following :
REM  ***** 
REM  *****  1.ID as running
REM  *****  2.Date as current date
REM  *****  3.User as User selected
REM  *****  4. ItemCode as Itemcode
REM  *****  5.QTYEntered is positive figure
REM  *****  6. Reasons as Reasons selected.


REM  ***** 
REM  *****  onClick_STKIN_submitbutton( oEv as object )
REM  ***** 
REM  *****  Event handler for the user clicking the submit button
REM  *****  Post the data and close the form
REM  ***** 
sub onClick_STKIN_submitbutton( oEv as object )
   update_STKIN_StoreDataEntry( oEv.Source.Model.Parent )
   oEv.Source.Model.Parent.Parent.getByname( "DataEntry" ).reload
end sub


REM  ***** 
REM  *****  on_STKIN_ControlValueChanged( oEv as Object )
REM  ***** 
REM  *****  A common event handler shared by
REM  *****  all the data entry controls
REM  ***** 
REM  ***** 
REM  ***** 
sub on_STKIN_ControlValueChanged( oEv as Object )

   dim oModel

   oModel = oEv.Source.Model
   
   REM  ***** 
   REM  *****  if the handler is called by
   REM  *****  ItemCode list box then update
   REM  *****  the table grid
   REM  ***** 
   if oModel.Name = "lbxitemCode" then
      if oModel.CurrentValue <> "" then
         displayItemGrid( oModel.Parent.Parent.getByname( "DataEntry" ), oModel.CurrentValue )
      endif
   endif

   REM  ***** 
   REM  *****  for all controls call the
   REM  *****  validation routine
   REM  *****  and use the retured value
   REM  *****  to either enable or disable
   REM  *****  the submit button
   REM  ***** 
   oModel.Parent.getByName( "btnSubmit" ).Enabled = isValid_STKIN_STKOUT( oModel.Parent )

end sub

REM  ***** 
REM  *****  onStatusChange_STKIN_Category( oEv as object )
REM  ***** 
REM  *****  the event handler for the category list box
REM  ***** 
REM  *****  I broke this out as a seperate handler from
REM  *****  the other controls because it must
REM  *****  be the first thing entered as it
REM  *****  is used to populate the itemcode list
REM  ***** 
sub onStatusChange_STKIN_Category( oEv as object )

   REM  ***** 
   REM  *****  if the user selected a category
   REM  *****  then update the itemcode listbox
   REM  *****  and enable the data entry controls
   REM  ***** 
   if oEv.Source.Model.CurrentValue <> "" then
      limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
   else
      REM  ***** 
      REM  *****  no category so disable
      REM  *****  everything else
      REM  ***** 
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False )
   end if
   
end sub

REM  ***** 
REM  *****  update_STKIN_StoreDataEntry( aDataForm as variant )
REM  ***** 
REM  *****  aDataForm is used to supply the connection
REM  *****  object to the dataabse
REM  *****  this could be changed to take the connection
REM  *****  directly
REM  ***** 
REM  *****  perform the actual update to the data table
REM  ***** 
sub update_STKIN_StoreDataEntry( aDataForm as variant )
   dim oPrepStatement
   
   oPrepStatement = aDataForm.ActiveConnection.prepareStatement( "INSERT INTO ""Store_DataEntry"" VALUES( ?,?,?,?,?,? ) " )
   oPrepStatement.setNull( 1, 1 ) ' pk autoincrement
   oPrepStatement.setTimeStamp( 2, dbTimeStamp( now ) ) ' TimeStamp
   oPrepStatement.setString( 3, aDataForm.getByName( "lbxUser" ).CurrentValue ) 'user
   oPrepStatement.setString( 4, aDataForm.getByName( "lbxitemCode" ).CurrentValue ) 'itemcode
   oPrepStatement.setInt( 5, aDataForm.getByname( "tbxQuantity" ).value ) 'Qty
   oPrepStatement.setString( 6, aDataForm.getByName( "lbxReason" ).CurrentValue ) 'Reason
   oPrepStatement.executeUpdate
end sub

sub onClick_btnClose( oEv as object )
   CloseForm( oev.Source.Model.Parent.Parent.Parent.CurrentController.Frame )
end sub


----------------Stock Out Module------------------------------------
Code:

REM  ***** 
REM  *****  Routines used by the Stock In Form
REM  ***** 
REM  ***** 
REM  ***** 
REM  *****  Once the submit button is pressed,
REM  *****  It will create a data in the
REM  *****  Store_DataEntry Table with the following :
REM  ***** 
REM  *****  1.ID as running
REM  *****  2.Date as current date
REM  *****  3.User as User selected
REM  *****  4. ItemCode as Itemcode
REM  *****  5.QTYEntered is positive figure
REM  *****  6. Reasons as Reasons selected.


REM  ***** 
REM  *****  onClick_STKIN_submitbutton( oEv as object )
REM  ***** 
REM  *****  Event handler for the user clicking the submit button
REM  *****  Post the data and close the form
REM  ***** 
sub onClick_STKIN_submitbutton( oEv as object )
   update_STKIN_StoreDataEntry( oEv.Source.Model.Parent )
   oEv.Source.Model.Parent.Parent.getByname( "DataEntry" ).reload
end sub


REM  ***** 
REM  *****  on_STKIN_ControlValueChanged( oEv as Object )
REM  ***** 
REM  *****  A common event handler shared by
REM  *****  all the data entry controls
REM  ***** 
REM  ***** 
REM  ***** 
sub on_STKIN_ControlValueChanged( oEv as Object )

   dim oModel

   oModel = oEv.Source.Model
   
   REM  ***** 
   REM  *****  if the handler is called by
   REM  *****  ItemCode list box then update
   REM  *****  the table grid
   REM  ***** 
   if oModel.Name = "lbxitemCode" then
      if oModel.CurrentValue <> "" then
         displayItemGrid( oModel.Parent.Parent.getByname( "DataEntry" ), oModel.CurrentValue )
      endif
   endif

   REM  ***** 
   REM  *****  for all controls call the
   REM  *****  validation routine
   REM  *****  and use the retured value
   REM  *****  to either enable or disable
   REM  *****  the submit button
   REM  ***** 
   oModel.Parent.getByName( "btnSubmit" ).Enabled = isValid_STKIN_STKOUT( oModel.Parent )

end sub

REM  ***** 
REM  *****  onStatusChange_STKIN_Category( oEv as object )
REM  ***** 
REM  *****  the event handler for the category list box
REM  ***** 
REM  *****  I broke this out as a seperate handler from
REM  *****  the other controls because it must
REM  *****  be the first thing entered as it
REM  *****  is used to populate the itemcode list
REM  ***** 
sub onStatusChange_STKIN_Category( oEv as object )

   REM  ***** 
   REM  *****  if the user selected a category
   REM  *****  then update the itemcode listbox
   REM  *****  and enable the data entry controls
   REM  ***** 
   if oEv.Source.Model.CurrentValue <> "" then
      limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
   else
      REM  ***** 
      REM  *****  no category so disable
      REM  *****  everything else
      REM  ***** 
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False )
   end if
   
end sub

REM  ***** 
REM  *****  update_STKIN_StoreDataEntry( aDataForm as variant )
REM  ***** 
REM  *****  aDataForm is used to supply the connection
REM  *****  object to the dataabse
REM  *****  this could be changed to take the connection
REM  *****  directly
REM  ***** 
REM  *****  perform the actual update to the data table
REM  ***** 
sub update_STKIN_StoreDataEntry( aDataForm as variant )
   dim oPrepStatement
   
   oPrepStatement = aDataForm.ActiveConnection.prepareStatement( "INSERT INTO ""Store_DataEntry"" VALUES( ?,?,?,?,?,? ) " )
   oPrepStatement.setNull( 1, 1 ) ' pk autoincrement
   oPrepStatement.setTimeStamp( 2, dbTimeStamp( now ) ) ' TimeStamp
   oPrepStatement.setString( 3, aDataForm.getByName( "lbxUser" ).CurrentValue ) 'user
   oPrepStatement.setString( 4, aDataForm.getByName( "lbxitemCode" ).CurrentValue ) 'itemcode
   oPrepStatement.setInt( 5, aDataForm.getByname( "tbxQuantity" ).value ) 'Qty
   oPrepStatement.setString( 6, aDataForm.getByName( "lbxReason" ).CurrentValue ) 'Reason
   oPrepStatement.executeUpdate
end sub

sub onClick_btnClose( oEv as object )
   CloseForm( oev.Source.Model.Parent.Parent.Parent.CurrentController.Frame )
end sub


-----------Stock Out Module ------------------------------------

Code:

REM  ***** 
REM  *****  Routines used by the Stock Out Form
REM  ***** 
REM  ***** 
REM  ***** 
REM  *****  Once the submit button is pressed,
REM  *****  It will create a data in the
REM  *****  Store_DataEntry Table with the following :
REM  ***** 
REM  *****  1.ID as running
REM  *****  2.Date as current date
REM  *****  3.User as User selected
REM  *****  4. ItemCode as Itemcode
REM  *****  5.QTYEntered is positive figure
REM  *****  6. Reasons as Reasons selected.


REM  ***** 
REM  *****  onClick_STKOUT_submitbutton( oEv as object )
REM  ***** 
REM  *****  Event handler for the user clicking the submit button
REM  *****  Post the data and close the form
REM  ***** 
sub onClick_STKOUT_submitbutton( oEv as object )
   update_STKOUT_StoreDataEntry( oEv.Source.Model.Parent
   oEv.Source.Model.Parent.Parent.getByname( "DataEntry" ).reload
end sub


REM  ***** 
REM  *****  on_STKOUT_ControlValueChanged( oEv as Object )
REM  ***** 
REM  *****  A common event handler shared by
REM  *****  all the data entry controls
REM  ***** 
REM  ***** 
REM  ***** 
sub on_STKOUT_ControlValueChanged( oEv as Object )

   dim oModel

   oModel = oEv.Source.Model
   
   REM  ***** 
   REM  *****  if the handler is called by
   REM  *****  ItemCode list box then update
   REM  *****  the table grid
   REM  ***** 
   if oModel.Name = "lbxitemCode" then
      if oModel.CurrentValue <> "" then
         displayItemGrid( oModel.Parent.Parent.getByname( "DataEntry" ), oModel.CurrentValue )
      endif
   endif

   REM  ***** 
   REM  *****  for all controls call the
   REM  *****  validation routine
   REM  *****  and use the retured value
   REM  *****  to either enable or disable
   REM  *****  the submit button
   REM  ***** 
   oModel.Parent.getByName( "btnSubmit" ).Enabled = isValid_STKIN_STKOUT( oModel.Parent )

end sub

REM  ***** 
REM  *****  onStatusChange_STKOUT_Category( oEv as object )
REM  ***** 
REM  *****  the event handler for the category list box
REM  ***** 
REM  *****  I broke this out as a seperate handler from
REM  *****  the other controls because it must
REM  *****  be the first thing entered as it
REM  *****  is used to populate the itemcode list
REM  ***** 
sub onStatusChange_STKOUT_Category( oEv as object )

   REM  ***** 
   REM  *****  if the user selected a category
   REM  *****  then update the itemcode listbox
   REM  *****  and enable the data entry controls
   REM  ***** 
   if oEv.Source.Model.CurrentValue <> "" then
      limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
   else
      REM  ***** 
      REM  *****  no category so disable
      REM  *****  everything else
      REM  ***** 
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False )
   end if
   
end sub

REM  ***** 
REM  *****  update_STKOUT_StoreDataEntry( aDataForm as variant )
REM  ***** 
REM  *****  aDataForm is used to supply the connection
REM  *****  object to the dataabse
REM  *****  this could be changed to take the connection
REM  *****  directly
REM  ***** 
REM  *****  perform the actual update to the data table
REM  ***** 
sub update_STKOUT_StoreDataEntry( aDataForm as variant )
   dim oPrepStatement
   
   oPrepStatement = aDataForm.ActiveConnection.prepareStatement( "INSERT INTO ""Store_DataEntry"" VALUES( ?,?,?,?,?,? ) " )
   oPrepStatement.setNull( 1, 1 ) ' pk autoincrement
   oPrepStatement.setTimeStamp( 2, dbTimeStamp( now ) ) ' TimeStamp
   oPrepStatement.setString( 3, aDataForm.getByName( "lbxUser" ).CurrentValue ) 'user
   oPrepStatement.setString( 4, aDataForm.getByName( "lbxitemCode" ).CurrentValue ) 'itemcode
   oPrepStatement.setInt( 5, aDataForm.getByname( "tbxQuantity" ).value * -1 ) 'Qty AS A NEGATIVE NUMBER
   oPrepStatement.setString( 6, aDataForm.getByName( "lbxReason" ).CurrentValue ) 'Reason
   oPrepStatement.executeUpdate
end sub

sub onClick_btnClose( oev as object )
   CloseForm( oev.Source.Model.Parent.Parent.Parent.CurrentController.Frame )
end sub

_________________
Blog - http://baseanswers.spaces.live.com/


Last edited by DrewJensen on Wed May 23, 2007 9:30 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
olegos
General User
General User


Joined: 22 May 2007
Posts: 18

PostPosted: Wed May 23, 2007 9:30 pm    Post subject: Reply with quote

Thanks Drew. Do I understand this correctly, that modifying a filter is much faster than modifying the SQL query, and just changing the filter & reloading the form doesn't re-execute the query in the database? (talking about displayItemGrid function) Thanks!
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed May 23, 2007 9:36 pm    Post subject: Reply with quote

Yes, changing the filter and doing a reload does fire the query again.

Base can work against a multi-user database as well as the single user embedded database, so you would want to run the query again.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Decker87
OOo Enthusiast
OOo Enthusiast


Joined: 21 May 2007
Posts: 163

PostPosted: Thu May 24, 2007 6:55 am    Post subject: Reply with quote

Thanks drew! Great little tutorial!
However, I am unfamiliar with this scripting.

When you do:
Code:

sub onStatusChange_STKOUT_Category( oEv as object )
   if oEv.Source.Model.CurrentValue <> "" then
      limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, True )
   else
      enable_STKIN_STKOUT_Controls( oEv.Source.Model.Parent, False )
   end if
end sub

What does all that mean? I understand what the code does, but what do these things mean? And, are they variables, parameters, or commands?
sub
STKOUT
oEv
All the oEv.Source.Model stuff. What is this referring to?

Also, this coding is specific to that listbox, correct?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu May 24, 2007 9:02 am    Post subject: Reply with quote

Quote:
All the oEv.Source.Model stuff. What is this referring to?

Also, this coding is specific to that listbox, correct?

Your form is a Writer document. Open the form for editing.
Menu:Tools>Macros>Organize>Basic..., button "Organize..."
Browse to your document (bottom of left list box) and add two new modules.
Replace the default contents ("Sub Main") with Drew's code.
Your form and all it's controls can trigger events (edit mode, third tab of properties dialogue).
"onStatusChange_STKOUT_Category" should be assigned to the "Status Changed"-event of a box. oEv is a structure that contains a reference to the calling control (oEv.Source).
The code has some hard coded names like "Stock Out Data Entry","Inventory2_2", which appear red in the IDE. They should be adjusted to your actual implementation.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Decker87
OOo Enthusiast
OOo Enthusiast


Joined: 21 May 2007
Posts: 163

PostPosted: Fri May 25, 2007 5:35 am    Post subject: Reply with quote

If that code is specific to that list box, how does it know which other element to change when something is selected from the listbox?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 25, 2007 5:52 am    Post subject: Reply with quote

It calls sub enable_STKIN_STKOUT_Controls passing over it's form.
sub enable_STKIN_STKOUT_Controls makes some assumptions about specifically named controls:
Code:

...
with aDataForm
      .getByName( "lbxitemCode" ).enabled = onOff
      .getByName( "lbxUser" ).enabled = onOff
      .getByName( "lbxReason" ).enabled = onOff
      .getByName( "tbxQuantity" ).enabled = onOff
      .Parent.getByName( "DataEntry").getByName( "tcDataEntry" ).enabled = onOff
end with
...

Of course this will fail if the calling box is not associated with the other controls which need to be named accordingly. So you have to adjust your control names or the corresponding hard coded names in the macro.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Decker87
OOo Enthusiast
OOo Enthusiast


Joined: 21 May 2007
Posts: 163

PostPosted: Tue May 29, 2007 10:29 am    Post subject: Reply with quote

I decided to test the basic concept, after reading a few times, I think I sort of understand it!

I created a new macro:
Code:

Sub limitItems ( aDataForm as variant )
   Dim new_sql(0) As string
   Dim olist as variant
 
   oList= aDataForm.getbyname("countryk")
     new_sql(0) = "SELECT ""country"" FROM ""COUNTRY"" WHERE ""Currency"" = ""Dollar"""
   oList.ListSource = new_sql(0)
End Sub


I have a form with two list boxes, one containging the data from a column called "Currency", and the other containing the data from a column in the same table called "country".

The listboxes are called currency and countryk, respectively.
I added the macro to the 'status changed' event of the currency listbox.

I was hoping that when I select anything from the currency box, the country box will be limited to countries whose currency is "Dollar".

However, when I use the form and trigger the event, the Basic code box shows up, highlighting the "oList= aDataForm.getbyname("countryk")" line. What does this mean?
I assume that this line is to label oList as whichever element in the current form has the name "countryk". Is this correct?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 29, 2007 10:48 am    Post subject: Reply with quote

An event routine gets an "event-stuct" as argument. This structure contains at least one element "Source" which references the calling object. Possibly your struct has even more elements (don't remember, depends on the type of event). Try this:
Code:

Sub limitItems ( structEvent as variant )
   Dim new_sql(0) As string
   Dim olist as variant
Dim aDataForm   
aDataForm = structEvent.Source
   oList= aDataForm.getbyname("countryk")
     new_sql(0) = "SELECT ""country"" FROM ""COUNTRY"" WHERE ""Currency"" = ""Dollar"""
   oList.ListSource = new_sql(0)
End Sub

You can attach a stop-mark to the Sub ... line (F9). Then the code stops there and you can walk through the code step by step by hitting F8 repeatedly. There is an "observer" at the bottom where you type in some variable names. This thingy is a little buggy but it helps in most cases.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Decker87
OOo Enthusiast
OOo Enthusiast


Joined: 21 May 2007
Posts: 163

PostPosted: Tue May 29, 2007 11:05 am    Post subject: Reply with quote

This is the error I get on the "aDataForm =" line using your code:
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 29, 2007 11:27 am    Post subject: Reply with quote

This means that structEvent is not passed over. You assigned some event of a form's listbox to "limitItems" or do you call it indirectly from another routine? I did not study nor try Drew's code so my comments are not specific to the inner workings of this.
Ah, I see. It's right on top:
Code:

sub onStatusChange_STKOUT_Category( oEv as object )
'stripped comments
   if oEv.Source.Model.CurrentValue <> "" then
      limitItems( oEv.Source.Model.Parent, oEv.Source.Model.CurrentValue )
......

This gets the event and passes over the form (oEv.Source.Model.Parent) as well as the current value of it's model (oEv.Source.Model.CurrentValue)

Or did you change the calling chain? What was the error message in your previous version, passing aDataForm? The same "argument not optional"?

If your sub limitItems is called directly from the box then I'm clueless. It should definitively pass an event-struct like it does in Drew's code.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2, 3  Next
Page 1 of 3

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group