Record Selection by drop down list.

[gmac]

Please help,
I have been trying to produce an SQL programme in Query, which will allow me to extract all fields of a specific record from an existing table by selecting a"PartNo" from a drop down list in a form. I would also want to see the other fields of the associated record of the PartNo selected, appear on the form.
Basically I have a database of partnos and their associated descriptions, etc.
For each project I need to be able to produce a parts list drawn from the parts in the database.

I started by creating a table "PLC Partnumbers",with fields "PartNo", "Description" "Rating".
I then created a form using "PLC Partnumber" entering the 3 fields above.
I changed the box for "PartNo" to a drop down list and tied it to "PLCPartnumber" using List Content "SELECT "PartNo", "PartNo" FROM "PLC Partnumbers"

After a few variations to above, the nearest I came to achieving a result was the form providing me with the drop down list, but the "Description" the form selected was the first in the table field, rather than the one related to the PartNo selected.

[kabing]

Take a look at this page in the NeoOffice Wiki.

Since NeoOffice is based on OpenOffice.org, the instructions should work fine for OpenOffice.org, if you keep the following things in mind:
1) references to the command key in a Neo tutorial are the equivalent to the control key in OOo. (i.e. Command-C becomes Control-C) Note that I'm not sure if this is true with OOo for X11.
2) control-clicking is the same as right clicking
3) icons sometimes look different, as NeoOffice 2.2.2 has a custom icon set.

If you look at the bottom of the article, there is a link to a thread at oooforums.org, which was the source of my information for the neowiki article. That thread would be OpenOffice.org specific.

HTH

kabing

[gmac]

Thanks for reply.
I have tried your suggestions and this takes me further than I had achieved, however when I run the Form it filters the Table to the single selection I make and on the next selection it deletes the first. What I would like to achieve is for it to add each selection to a Table which I can then transfer the result to another document (calc spreadsheet) or report.
The information that appears in the "results" box in the form - how do I send it to a new table?

[kabing]

I'm afraid that's beyond my skill level, but no doubt someone who is experienced with macros will be able to help you.

kabing

[QuazzieEvil]

you can clone the underlying result set, and use Basic to copy to new table--see code below which is bound to a push button

Code: Select all

Sub pbCopy_OnClick(Event As Object)
	Dim Form As Object
	Dim rst As Object
	
	Form=Event.Source.Model.Parent	
	rst=Form.createResultSet() REM copy underlying resultset--this is the filtered rst
	copyResultToTable(rst,"OOFORUMTESTS","LEADSCOPY")
	
End Sub

The function copyResultToTble takes three parameters. the first is the resultset clone. the second the name ofthe database you want to copy to, and the third is the actual table. see code below for function def.

Code: Select all

Function copyResultToTable(ResultSet As Object,DataSourceName As String, TableName As String) As Boolean
	REM COPY A result set to another table
	Dim RowSet As Object
	Dim SourceCol As Object
	Dim TargetCol As Object
	Dim I As Integer
	Dim tmpVal As Variant
	
	RowSet=createUnoService("com.sun.star.sdb.RowSet")
	With RowSet
		.DataSourceName=DataSourceName
		.CommandType=com.sun.star.sdb.CommandType.TABLE
		.Command=TableName
		.IgnoreResult=True
		.execute()
	End With	
	ResultSet.first() 
	Do
		RowSet.moveToInsertRow() REM move to insert row
		For I=0 To ResultSet.Columns.Count-1
			SourceCol=ResultSet.Columns.getByIndex(I)
			tmpVal=getXXX(SourceCol) REM  user generic getXXX method to get value
			TargetCol=RowSet.Columns.getByIndex(I) REM get the Ith column
			If SourceCol.TypeName="DATE" Then
				updateColXXX(TargetCol,tmpVal.Year & "-" & tmpVal.Month & "-" & tmpVal.Day)
			Else
				updateColXXX(TargetCol,tmpVal) REM use generic functo update value
			End If
		Next I
		RowSet.insertRow()
	Loop While ResultSet.next()
End Function

this function assumes that the target and source tables have the same exact definition. Also, uses two additional funcs--getXXX(...) and updateColXXX(...). to get data out of a resultset column, you must use a different funciton for every data type. what this function will do for you is call the appropriate function based on the columns data type--that way you do not have to do it. the updateColXXX(...) works the same way. when updating a column y ou must use a different method for every data type. so this func will do it for you based on the column's data type. function defs are below

if you know that you will only be working with one or a few datatypes, you can just code it yourself--up to you.

Code: Select all

Function updateColXXX(col As Object,colVal As Variant)
 REM col is a column object/Service.
 REM SELECT CASE gets property that corresponds to datatype passed

 Select Case col.TypeName
 	Case "BOOLEAN": col.updateBoolean(colVal)
	 Case "BYTE": col.UpdateByte(colVal)
	 Case "BYTES": col.updateBytes(colVal)
	 Case "DATE": col.updateString(colVal)
	 Case "DOUBLE": col.updateDouble(colVal)
	 Case "INTEGER": col.updateInt(colVal)
	 Case "LONG": col.updateLong(colVal)
	 Case "NULL": col.updateNull(colVal)
	 Case "SHORT": col.updateShort(colVal)
	 Case "VARCHAR": col.updateString(colVal)
	 Case "TIME": col.updateString(colVal)
	 Case "TIMESTAMP": col.updateString(colVal)
	 Case Else: col.updateString(colVal) 'GIVE STRING A TRY
 End Select
End Function
Function getXXX(col)
 REM col is a column object/serivce.
 REM SELECT CASE gets property that corresponds to datatype passed
 Dim ret

 Select Case col.TypeName
	Case "ARRAY": ret=col.Array
	Case "BLOB": ret=col.Blob
	Case "BOOLEAN": ret=col.Boolean
	 Case "BYTE": ret=col.Byte
	 Case "BYTES": ret=col.Bytes
	 Case "BLOB": ret=col.Clob
	 Case "DATE": ret=col.Date	
	 Case "DOUBLE": ret=col.Double
	 Case "INTEGER": ret=col.Int
	 Case "LONG": ret=col.Long
	 Case "DECIMAL": ret=col.Double
	 Case "NULL": ret=col.Null
	 Case "OBJECT": ret=col.Object
	 Case "REF": ret=col.Ref
	 Case "SHORT": ret=col.Short
	 Case "VARCHAR": ret=col.String
	 Case "TIME": ret=col.Time
	 Case "TIMESTAMP": ret=col.TimeStamp
	 Case Else: ret=col.String 'GIVE STRING A TRY
 End Select
 getXXX=ret
End Function

SO... copy these four funcs into one of your libraires, and bind the first to a form push button. make sure to change the database and table names on the driver sub routine--pbCopy_OnClick(Event As Object)

hope this helps

[gmac]

Thank you for your reply QuazzieEvil.
I tried your four functions and bound the first to a pushbutton.
The functions worked fine and I was able to obtain, in a separate Table, the selections I made from a drop down list in the Form.
At the end of the exercise I saved the two databases and shut down my computer.

When I re-started my computer, opened the two databases again, and ran the functions by pressing the pushbutton, a message appeared in the macro and the macro stopped at the Function copyResultToTable, row ".execute()".
The message read:-
Basic runtime error
An exception occurred
Type:com.sun.star.sdbc.SQLException
Message:.

I tried uninstalling Open Office and re-installing. I then re-created the second database, deleted the macro and re-loaded all the functions.

The message still appears.

I notice that when I shut down my computer now it displays a DDE Server dialogue box telling me that the programme is still running and asks if I want to end it.

Can anyone help?

[QuazzieEvil]

that is very strange. I will look around and see what I can find.

[QuazzieEvil]

the only way i can recreate a similar error is to pass an invalid database name. if you are trying to pass a registered database name, name sure it is registered, and that the name is spelled correctly. If you are passing a path, make sure you convert it to url first.

example: DBURL=ConvertToURL("c:\mydb.odb")

[gmac]

Problem solved.
When you suggested that there may be a problem with the database name/registration I had a closer look at the second database and found that for some reason the primary key field was defined as having zero length. I changed it to match the table in the first database and the programme is working again.

Thanks for your help QuazzieEvil.

Gmac

[gmac]

Although the programme is now working, the last posting's solution may not be correct.
There was a fault in as much as the primary key length was expressed as zero, but in re-creating the second database I gave it the name GM1parts. The previous second database was named GM1 parts, i.e., a space in the name.
I have subsequently tried it both ways and the database name with the space does repeat the error.
Seems that spaces are unacceptable when referred to in the macro.

Gmac

[QuazzieEvil]

spaces are ok. However, you must quote it. """DB NAME WITH SPACES""" for that reason I try, when possible, to have all my table and column names in all upper case, alphabetic characters.

[pauleeee]

Is there anybody out there! I'm as green as green can be.
I have spent almost all day to
On a form
create a drop down box
that list the records in my table (In this case the field containing the contact name)
and allowing me to select from the drop down box
and given my selection
will populate the form.

For example:
On one form there is a drop down box that has a listing of all contacts
I then scroll down to the contact name, select it, and having done that will fill
all the other fields with that selected contact name - once again, everything is on one form.

Anyone.

Many Thanks

[MTP]

A couple of things for pauleeee:
Take a look at [Example #1] Filter/Search with Forms (leveraging SubForms)
You have a new issue, if you have questions after looking at that link please make a new post. We appreciate that you searched for an answer before posting, and when you post it may be helpful to link to this thread ("this thread "Record Selection by drop down list" is the closest I found but it doesn't solve my problem because..."). But please make a new post instead of "reviving" this one from 2007.