| View previous topic :: View next topic |
| Author |
Message |
Decker87
OOo Enthusiast
Joined: 21 May 2007
Posts: 163
|
 Posted: Tue May 22, 2007 11:58 am Post subject: Contents of one list box dependent upon another's selection? |
 |
|
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 |
|
 |
Decker87
OOo Enthusiast
Joined: 21 May 2007
Posts: 163
|
| Posted: Wed May 23, 2007 5:01 am Post subject: |
|
|
| Out of 12 people, no one knows? |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Wed May 23, 2007 9:07 am Post subject: |
|
|
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 |
|
|
olegos
General User
Joined: 22 May 2007
Posts: 18
|
| Posted: Wed May 23, 2007 9:06 pm Post subject: |
|
|
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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Wed May 23, 2007 9:11 pm Post subject: |
|
|
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 |
|
|
olegos
General User
Joined: 22 May 2007
Posts: 18
|
| Posted: Wed May 23, 2007 9:30 pm Post subject: |
|
|
| 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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Wed May 23, 2007 9:36 pm Post subject: |
|
|
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 |
|
|
Decker87
OOo Enthusiast
Joined: 21 May 2007
Posts: 163
|
| Posted: Thu May 24, 2007 6:55 am Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu May 24, 2007 9:02 am Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
Decker87
OOo Enthusiast
Joined: 21 May 2007
Posts: 163
|
| Posted: Fri May 25, 2007 5:35 am Post subject: |
|
|
| 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri May 25, 2007 5:52 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
Decker87
OOo Enthusiast
Joined: 21 May 2007
Posts: 163
|
| Posted: Tue May 29, 2007 10:29 am Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 29, 2007 10:48 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
Decker87
OOo Enthusiast
Joined: 21 May 2007
Posts: 163
|
| Posted: Tue May 29, 2007 11:05 am Post subject: |
|
|
This is the error I get on the "aDataForm =" line using your code:
 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 29, 2007 11:27 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
|
|
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
|
