comboBox

Creating and using forms
Post Reply
emoskalik
Posts: 3
Joined: Fri Aug 08, 2008 8:33 pm

comboBox

Post by emoskalik »

I have two tables, Tbl_Inventory, and Tbl_Sales.
I currently have a Sales form that has a combo box that lists all of the serial numbers from the Inventory form. I just need the Sales form to fill in the rest of the fields when a Serial Number from the combo box is selected..
Thanks.
OOo 2.4.X on Ms Windows XP + freeBSD
User avatar
foxcole
Volunteer
Posts: 1507
Joined: Mon Oct 08, 2007 1:31 am
Location: Minneapolis, Minnesota

Re: comboBox

Post by foxcole »

What do you mean by "fill in the rest of the fields" exactly? You haven't given much detail. Although someone who works with databases more than I do might guess what you want, it's best not to leave anything up to guesswork but describe exactly what you want to accomplish and exactly what you're working with... and what you have tried that was giving you trouble.
Cheers!
---Fox

OOo 3.2.0 Portable, Windows 7 Home Premium 64-bit
emoskalik
Posts: 3
Joined: Fri Aug 08, 2008 8:33 pm

Re: comboBox

Post by emoskalik »

Sorry about that, i will be more descriptive.
I have two tables Tbl_Inventory and Tbl_Sales.
Tbl_Inventory has two fields (to make things simple) Serial_Number and Description and this data is all filled out.
My Tbl_Sales has the same two fields, but there is no data in this form. What i need to do is when the blank form Tbl_Sales is open, you pick a serial number from a list and have the description automatically get pulled from the Tbl_Inventory information.
I hope this is more clear.
Thanks.
OOo 2.4.X on Ms Windows XP + freeBSD
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: comboBox

Post by voobase »

Hi there,

Have a look at this thread.

http://www.oooforum.org/forum/viewtopic.phtml?t=71594

I show two ways to copy the value from a listbox to a table grid. In the macro the first way copies the current value of the listbox to a field in the table grid. The second way is using a prepared statement to copy the listbox value to the data table that the form is based on. That will get you started with a bit of learning on how to copy things using a macro. Yes, I know this has not answered your question....

Now have a look at this thread...

http://user.services.openoffice.org/en/ ... 309#p40309

In this I show how to copy another field from the table your combobox is based on to the form the combobox is in. In the other thread it is copying an integer field (the Primary Key ID value from the combobox's data table). This was required because a combobox is not like a listbox and does not have a separate bound field. The macro was to make the combobox behave more like a listbox. You could of course change the macro and have it copy more than one thing. I'll repost the macro here with a couple of changes to show how this might be done.

Code: Select all

Rem... Macro to get ID field from Organisation table for combobox selection
Rem... Driven off the text modified event of combobox
Sub Text_Modified_Organisation_Combobox (oEv as object)

dim oForm as object
dim oControl as object
dim strSQL as string
dim varName as string

oControl = oEv.source.model
oForm = oControl.parent
varName = oControl.CurrentValue Rem... This gets the value the combobox is displaying and puts it in a variable for use in the SQL statement

   strSQL="SELECT ""AnIntegerField"", ""TextField1"", ""TextField2"" FROM ""InventoryTable"" WHERE ""SerialNumber"" = '" & varName & "'"
        
        oCreateStatement = oForm.ActiveConnection.CreateStatement
      Result = oCreateStatement.executeQuery(strSQL)

      If Result.Next then        Rem... This actually does a Result.Next. The first time that the value in the combobox is found in the data table the corresponding fields are copied
                                Rem... to the sales data table
oForm.UpdateInt( oForm.FindColumn("AnIntegerFieldInSalesDataTable"), Result.GetInt(1) )
oForm.UpdateString( oForm.FindColumn("TextField1InSalesDataTable"), Result.GetString(2) )
oForm.UpdateString( oForm.FindColumn("TextField2InSalesDataTable"), Result.GetString(3) )
      
Else Rem... The combobox value is not found in its data table as you have typed in something that dosen't match anything.

oForm.UpdateInt( oForm.FindColumn("AnIntegerFieldInSalesDataTable"), 0 )
oForm.UpdateString( oForm.FindColumn("TextField1InSalesDataTable"), "" )
oForm.UpdateString( oForm.FindColumn("TextField2InSalesDataTable"), "" )
         
End if
End sub
Cheers

Voo
OOo 2.3.X on MS Windows Vista
Post Reply