[Solved] Open a form with a specific record id using macros

[rvguy]

Hello there,

This is my first time on the board and I am a new user of OO. I am an experienced programmer (php/mysql/perl/coldfusion/some java) but no experience with OO macros. I have looked all over the internet for a solution to my problem with no success. Here is my problem.

I have a simple home inventory database called "inventory". It contains one table called "assets" that contains these fields:

InventoryID (Integer, key, <AutoField>)
Name
Manufacturer
Model Number
Serial Number
etc. etc. about 12 or so fields

I have two forms. One is an Asset Detail form that allows me to input and change all the data. It is a bigger form and only shows one record per screen. I also have another form that I call an Asset Summary form. It is a simple DataSheet format that has InventoryID, Name, Manufacturer and Model Number. I use this form to scan multiple inventory items, as I can view multiple records per screen and see which one I want to view and/or change.

What I am trying to do is link the InventoryID on the Asset Summary form so that I can click the InventoryID and it will bring up the Asset Detail form directly to the corresponding asset record. I am able to get this to work except when I click the InventoryID on the summary form it brings up the detail form on the first record in the database. I cannot get it to bring up the detail form for the InventoryID I clicked on. Here is the macro that works, but only to bring up the form to the first record:

Sub OpenForm ()
Dim prop(1) as New com.sun.star.beans.PropertyValue
forms=ThisComponent.Parent.getFormDocuments()
conn=ThisComponent.Parent.DataSource.getConnection("","")
prop(0).Name="ActiveConnection"
prop(0).Value=conn
prop(1).Name="OpenMode"
prop(1).Value="open"
forms.loadComponentFromURL("assets","_blank",0,prop())
End Sub

"assets" is the form I want to open and InventoryID is the key. In the Assets Summary form, for the Properties:Events on the InventoryID field I have "Mouse button pressed - Standard.Module1.OpenForm. As I said, this works but always brings up the first record (InventoryID=1).

I believe the solution will involve filtering, and I have tried probably 10 different solutions found on the internet, but none work. Many times I get errors.

I have explained the problem as best I could, but certainly any questions will be answered promptly. Any help would be very much appreciated. Thank you in advance.

-rvguy

[MTP]

I believe that your loadComponent statement returns the document object, so you could do this:

Code: Select all   Expand viewCollapse view

oDoc = forms.loadComponentFromURL("assets","_blank",0,prop())

Then you can get the form that is in the newly opened document:

Code: Select all   Expand viewCollapse view

oForm = oDoc.drawpage.forms.[YourFormName]

Then you have a couple of options (actually probably more than two, but two came to my mind so far). If you know the row number you want to go to (e.g. the InventoryID corresponds to the row number because there are no deleted records and the rows have not been sorted for the form), you can use

Code: Select all   Expand viewCollapse view

oForm.absolute([RowNumber])

Another option is to make your top-level form a "master" form that connects to one row of a filter table (set the form source to be SQL with code something like SELECT * FROM "Filter" WHERE ID=0), with a linked field to the subform with your main information. Update the filter table with whatever value you are filtering by:

Code: Select all   Expand viewCollapse view

oForm.columns.YourColumnName.updateInt([FilterValue])
If oForm.isModified Then oForm.updateRow
oForm.YourSubFormName.reload

[rvguy]

Thank you for the quick reply. We'll have to take this one step at a time. I changed this one existing line:

oDoc = forms.loadComponentFromURL("assets","_blank",0,prop())

Now I cannot get this new line to work:

oForm = oDoc.drawpage.forms.[YourFormName]

I have tried:
oForm = oDoc.drawpage.forms.assets
oForm = oDoc.drawpage.forms."assets"
oForm = oDoc.drawpage.forms.[assets]
oForm = oDoc.drawpage.forms.["assets"]

Obviously "assets" is the name of the form I want to open. Every time I try it I get an error: <BASIC runtime error. Property or method not found: assets.>

Also, I am going to need to grab the InventoryID on the summary form to open the appropriate record in the detail form. Picture this DataSheet view:

InventoryID - Asset Name - Manufacturer - Model
1 DVD Sony DVD-010
2 TV Sharp ABC-123
3 Computer HP g7-1310

etc. etc for the entire inventory

As I view these records, I want to able to click, for example, InventoryID "2" and have it bring up the detail form for ID # 2 Make sense?

Thanks so far.

rvguy

[MTP]

The name is case sensitive. If your form is named Assets, the code line would be

Code: Select all   Expand viewCollapse view

oForm = oDoc.drawpage.forms.Assets

OR

Code: Select all   Expand viewCollapse view

oForm = oDoc.drawpage.forms.getByName("Assets")

If you were using any language other than StarBasic, you would have to always used the .getByName("Name") method; StarBasic lets you take the shortcut of just doing .Name
Let us know if the case was the problem. If not, the next thing I'd look at would be to double check the Form Navigator and make sure assets is a top-level form.

You can get information on the current selection from either the form columns or the form controls. For the columns it would be something like

Code: Select all   Expand viewCollapse view

i = oMainDocForm.columns.InventoryID.getInt

For the form control the exact language varies depending on what kind of control it is; when creating a new form I use XRay a lot to figure out what the property names are. I think this would work

Code: Select all   Expand viewCollapse view

i = oMainDocForm.TableGridName.IDColumnName.CurrentValue

Note that the name of the InventoryID column is different than the display text - it might be named something like "Numeric Field 1" (you can change this name if you want).

[rvguy]

Case was not the problem. The form name is "assets" (all lower case, obviously without the quotes) as you can see from the forms.loadComponentFromURL statement. I still get that same error when I test - "Property or method not found: assets". Regarding whether assets is a top-level form, I only have 2 forms in the application. "assets" is the detail form I am trying to open, and assets2 is the summary form where I have the InventoryID linked. So I have assets2 open which is listing all my inventory items in a DataSheet format, and the InventoryID field is calling the OpenForm macro upon click, which opens the form "assets". Make sense?

I really do apologize for all this. It's probably something simple, but like anything when you never worked with it before it's, well, not so simple. And I do sincerely appreciate the patience and help.

-rvguy

[rvguy]

Also, when I try this statement instead:

oForm = oDoc.drawpage.forms.getByName("assets")

I get this error:
BASIC runtime error.
An exception occurred
Type: com.sun.star.container. No Such Element Exception
Message:.

[MTP]

Base has two different things that it calls forms: First, form documents, which are usually Writer files (although you can make any OpenOffice application into a form document, with Calc being a popular second choice to Writer). Second, forms/subforms that contain form controls and are embedded inside the form documents.

You are opening the form document "assets". If you created the form with the wizard, the container for this document's form controls is probably named "Form". The easiest way to see the form name is probably by opening the document "assets" in design mode and then opening the Form Navigator (make sure you have the Form Design toolbar visible, the icon looks like a rectangle with a compass in the upper right hand corner, sixth from the left or top).

Does that help?

[rvguy]

Hello MTP,

I was trying a few different statements, and still no-go. Here is what I think I need to do ... basically get one thing working at a time. And I apologize in advance for being so inept at this OO Base stuff. It will come to me, as I've been doing programming for many many years. But sometimes some things take a bit longer than others. So I also thank you in advance for your patience.

As I said, I can get the assets detail form to open when I click on the InventoryID field in the DataSheet view, so I do believe we are very close. I think the next step is to be able to retrieve the InventoryID from the selected record in the summary form and save it in a variable. Then we can use that variable to open up the asset detail form showing the selected record.

Initially, I am having problems getting the InventoryID variable. I thought it would be a good idea to do what you suggested and find out more about my forms. Yes, they were created using the wizard. When I open up the forms in Design mode, I see two "navigator" icons. One is in the right-hand column and looks like a compass. It doesn't really look like a rectangle, however. When i click that icon, all it does is open up a window with these items in it:
Headings
Tables
Text Frames
Graphics
etc. etc.
Comments
Draw Objects

When I click on any of these lines, all I get is "0 Headings", "0 Tables", etc. so I don't think this is the icon you were referring to. The other icon is on the bottom of the screen. It says "Form Navigator" when I mouse-over. It looks like a document with some round graphic in the upper-right-hand corner of the icon (can't make out what that graphic is). I think this is probably the function you wanted me to use.

When I click it, it brings up a small pop-up box. The box contains this:
Forms
.... MainForm
........lblInventoryId
........fmtInventoryID
........lblManufacturer
........txtManufacturer
etc. for all the fields in the table.

Also, in viewing the Controls for the assets table, InventoryID is shown for both the Field Name and Label on the General tab and Data field on the Data tab.

So, in trying to first being able to get the InventoryID, I have tried all these statements and none work:

i = oMainDocForm.columns.InventoryID.getInt ... Error is "Object variable not set"
i = oMainDocForm.columns.fmtInventoryID.getInt ... Error is "Object variable not set"
i = oMainDocForm.TableGridName.InventoryID.CurrentValue ... Error is "Object variable not set"
i = oMainDocForm.TableGridName.fmtInventoryID.CurrentValue ... Error is "Object variable not set"

Just FYI - I have also tried it with these statement included for all tests (which were inserted before the above statements):
Dim i
Dim I as object

Any thoughts? And again, thank you.

-rvguy

[MTP]

Yes, you found the the form navigator - it shows the top-level form in your first document is named MainForm (the top-level form in your second document might have the same name). Is this how you are defining oMainDocForm?

Code: Select all   Expand viewCollapse view

oMainDocForm = ThisComponent.drawpage.forms.MainForm

If you go from the columns side, the program is actually looking at the columns in your table (or the aliases in your query or SQL statement, depending on what the source for your form is) - the name in the Data field on the Data tab of the control. Based on what you describe (and assuming the InventoryID column is of type Integer) this first line you tried should work

Code: Select all   Expand viewCollapse view

i = oMainDocForm.columns.InventoryID.getInt

From your original description I thought you had a tablegrid control. Looking at the fields you show from the form navigator, I see you actually have individual fields, so the code for accessing their values would be different than what I suggested before

Code: Select all   Expand viewCollapse view

i = oMainDocForm.fmtInventoryID.CurrentValue

If those don't work, I think the next debugging step would be to break down the "get object" steps to find out which object assignment the code is stumbling on. So something like

Code: Select all   Expand viewCollapse view

Dim o1 As Object
Dim o2 As Object
Dim o3 As Object

o1 = ThisComponent.drawpage.forms.MainForm
o2 = o1.columns
o3 = o2.InventoryID

o2 = o1.fmtInventoryID
o3 = o2.CurrentValue

When that code fails, you'll be able to tell exactly which step is having the problem, which may help us move forward.

[rvguy]

I think we are making progress (maybe). These statements do not give me any error, and it does in fact open the detail form when clicking the ID in the summary form. But it opens the detail form to the first record (InventoryID=1) no matter which ID I click on:

oDoc = forms.loadComponentFromURL("assets","_blank",0,prop())
oMainDocForm = ThisComponent.drawpage.forms.MainForm
i = oMainDocForm.fmtInventoryID.CurrentValue

But I also believe (if I am reading this correctly) that it already opened the detail form "before" I retrieved the InventoryID from the summary form. Is this correct? Then, when I try to reverse some statements (to retrieve the ID "before" the detail form opens) I get an error:

oMainDocForm = ThisComponent.drawpage.forms.MainForm
i = oMainDocForm.fmtInventoryID.CurrentValue
oDoc = forms.loadComponentFromURL("assets","_blank",0,prop())

Error: Property or method not found: fmtInventoryID

I also tried it with just InventoryID:

oMainDocForm = ThisComponent.drawpage.forms.MainForm
i = oMainDocForm.InventoryID.CurrentValue
oDoc = forms.loadComponentFromURL("assets","_blank",0,prop())

and got the same error, except of course it said "Property or method not found: InventoryID". Thoughts?

-rvguy

[MTP]

You are working with two documents, the one you start in and the one you are opening with the macro. You will need to get the Inventory ID from the form inside the document you start in. You will need to set it in the form inside the document you open. The order of getting the ID and opening the new document doesn't matter (though of course you'll need to get the ID from oldDoc before you can set it in newDoc), but I think your current code is trying to get the ID from the wrong document.

What does your form navigator look like in the document you start with?

[rvguy]

In the summary form (starting form):

Forms
.... MainForm
........MainForm_Grid

In the detail form (form I want to open up when ID is clicked):
Forms
.... MainForm
........lblInventoryID
........fmtInventoryID
........lblManufacturer
........txtManufacturer
etc. etc. for all the fields in the table

[MTP]

Maybe this, then:

Code: Select all   Expand viewCollapse view

oStartDoc = ThisComponent
oStartForm = oStartDoc.drawpage.forms.MainForm
i = oStartForm.columns.InventoryID.getInt

oNewDoc = forms.loadComponentFromURL("assets","_blank",0,prop())
oNewDocForm = oNewDoc.drawpage.forms.MainForm
oNewDocForm.absolute(i)

[rvguy]

OK, here is what I have right now (including your new suggested statements):

Sub OpenForm ()
Dim prop(1) as New com.sun.star.beans.PropertyValue
forms=ThisComponent.Parent.getFormDocuments()
conn=ThisComponent.Parent.DataSource.getConnection("","")
prop(0).Name="ActiveConnection"
prop(0).Value=conn
prop(1).Name="OpenMode"
prop(1).Value="open"

oStartDoc = ThisComponent
oStartForm = oStartDoc.drawpage.forms.MainForm
i = oStartForm.columns.InventoryID.getInt

oNewDoc = forms.loadComponentFromURL("assets","_blank",0,prop())
oNewDocForm = oNewDoc.drawpage.forms.MainForm
oNewDocForm.absolute(i)
End Sub

I get this error:
------------------------------
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: Function sequence error..
------------------------------

On this statement:
-----------------------------
oNewDocForm.absolute(i)
-----------------------------

We are so close I can almost taste it !! I put a "print" statement right after setting the var "i"
------------------------------------
i = oStartForm.columns.InventoryID.getInt
print i
-------------------------------------

It does in fact display the correct InventoryID number (ie. if I click on ID # 3 it displays the number "3", etc). So it is setting the ID variable correctly. Now all we need to do is get it to open the detail form to the asset record with that ID number <smile>

BTW - Thank you so much for hanging in there with me !!

-rvguy

[MTP]

I think what's happening is that the macro tries to set the row pointer before the form finishes loading. Try adding a wait line

Code: Select all   Expand viewCollapse view

wait 100
oNewDocForm.absolute(i)

The wait number is milliseconds, so "wait 100" adds a delay of 0.1 seconds.
You might have to end up using i+1 instead of i; I think the row numbers start from 1, while ID numbers ususally start from 0.

[rvguy]

Hallelujah ... It works !!!! All is now good. It works properly with just i, bringing up the detail record matching the InventoryID number.

I want to sincerely thank you, MPT, for your help. You have helped with more than just a technical solution. Sometimes when I am helping others in PHP or MySQL I get frustrated because the person I am helping seems to be "inept". Well, that was me in this thread .. inept. You not only have helped with a solution, you have taught me to be a bit more patient with someone just learning.

Thanks again, MTP, for your help and patience. Now for your final help, how do I close this thread and mark it [Solved] <smile>

-rvguy

[MTP]

To mark the thread solved, just edit your original post and add that label to the title. It's great that the form is working!

[rvguy]

I guess I spoke too soon, but this should be an easy one. Retrieving the record via "oNewDocForm.absolute(i)" is fine until I deleted one record. Then, of course, the retrieval is one record off. For example selecting ID=6 actually opens with ID=7. I need to be able to retrieve the detail record by InventorID number, I assume via filtering? My SQL would be something like:

SELECT * FROM assets WHERE InventoryID=i

Just for reference this is the macro as it now stands:

----------------------------------------------------
Sub OpenForm ()
Dim prop(1) as New com.sun.star.beans.PropertyValue
forms=ThisComponent.Parent.getFormDocuments()
conn=ThisComponent.Parent.DataSource.getConnection("","")
prop(0).Name="ActiveConnection"
prop(0).Value=conn
prop(1).Name="OpenMode"
prop(1).Value="open"

oStartDoc = ThisComponent
oStartForm = oStartDoc.drawpage.forms.MainForm
i = oStartForm.columns.InventoryID.getInt

oNewDoc = forms.loadComponentFromURL("assets","_blank",0,prop())
oNewDocForm = oNewDoc.drawpage.forms.MainForm
wait 100
oNewDocForm.absolute(i)
End Sub
----------------------------------------------------

the variable "i" does indeed contain the correct ID number to search for. Sorry, I should have caught this before.

-rvguy

[rvguy]

OK, I figured this one out. For anyone following this thread, the solution:

I replaced:
---------------------------------------------------------
oNewDocForm.absolute(i)
--------------------------------------------------------

with this:
--------------------------------------------------------
oNewDocForm.Filter = "InventoryID ='" & i & "'"
oNewDocForm.ApplyFilter = True
oNewDocForm.Reload
------------------------------------------------------

Now it will pull up the detail record matching on InventoryID.

-rvguy

[biarced]

Hello RVGuy: I was following your post and have the exact same issue. Would it be asking too much to possibly post a copy of your .odb file so I can relate to what you did and emulate it for myself?