Record Selection by drop down list.

Discuss the database features

Record Selection by drop down list.

Postby gmac » Wed Dec 05, 2007 4:40 pm

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.
gmac
 
Posts: 5
Joined: Wed Dec 05, 2007 4:15 pm
Location: Scotland

Re: Record Selection by drop down list.

Postby kabing » Wed Dec 05, 2007 8:14 pm

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
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Record Selection by drop down list.

Postby gmac » Thu Dec 06, 2007 5:49 pm

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?
gmac
 
Posts: 5
Joined: Wed Dec 05, 2007 4:15 pm
Location: Scotland

Re: Record Selection by drop down list.

Postby kabing » Thu Dec 06, 2007 7:44 pm

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
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Record Selection by drop down list.

Postby QuazzieEvil » Thu Dec 06, 2007 9:53 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Record Selection by drop down list.

Postby gmac » Sun Dec 09, 2007 5:10 pm

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?
gmac
 
Posts: 5
Joined: Wed Dec 05, 2007 4:15 pm
Location: Scotland

Re: Record Selection by drop down list.

Postby QuazzieEvil » Mon Dec 10, 2007 1:53 am

that is very strange. I will look around and see what I can find.
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Record Selection by drop down list.

Postby QuazzieEvil » Mon Dec 10, 2007 5:04 am

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")
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Record Selection by drop down list.

Postby gmac » Mon Dec 10, 2007 10:20 am

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
 
Posts: 5
Joined: Wed Dec 05, 2007 4:15 pm
Location: Scotland

Re: Record Selection by drop down list.

Postby gmac » Mon Dec 10, 2007 4:12 pm

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
gmac
 
Posts: 5
Joined: Wed Dec 05, 2007 4:15 pm
Location: Scotland

Re: Record Selection by drop down list.

Postby QuazzieEvil » Mon Dec 10, 2007 4:47 pm

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.
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Record Selection by drop down list.

Postby pauleeee » Mon Jul 21, 2014 2:43 pm

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
OpenOffice 4.1.0 on Windows XP
pauleeee
 
Posts: 1
Joined: Mon Jul 21, 2014 12:53 pm

Re: Record Selection by drop down list.

Postby MTP » Mon Jul 21, 2014 5:42 pm

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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests