OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Auto-insert value of Master/slave in subform
Goto page Previous  1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
PilotsGuide
General User
General User


Joined: 21 Nov 2003
Posts: 38
Location: London, UK

PostPosted: Fri Mar 24, 2006 7:16 am    Post subject: Reply with quote

Any progress?

I have the same problem with OOo 2.02, MySQL 4.0.18 and using MySQL/ODBC connector.
Back to top
View user's profile Send private message
pierdeux
Power User
Power User


Joined: 08 Mar 2006
Posts: 51

PostPosted: Fri Mar 24, 2006 9:23 am    Post subject: Reply with quote

DrewJensen wrote:
I insert a new record in Table1 and enter some data. I save the record. The grid is blank, no entry for an insert row. [...] If you step off the record on Table1 and back, now you have a new blank record in the Subform, and on my system it is poplulated with the linked value from table1.

If this is the problem you can solve it by adding a simple macro to the MainForm's On Record Action event, this macro simply calls 'SubForm.Reload'.


(Complete macro-novice, here!)

When you say that the macro "simply calls 'SubForm.Reload'", I presume that there are more lines than just that one in the macro. In particular, you have to get hold of that "subform object", don't you? That's the bit where I expect some oDark.Magic() incantation... I imagine that you first need to get hold of the current form object, then get a list of all its subforms, and then loop through them, applying the reload() method to each, is that right?

Would it be too much to ask for an explicit example?
Back to top
View user's profile Send private message
C Shore
Newbie
Newbie


Joined: 17 May 2006
Posts: 2

PostPosted: Wed May 17, 2006 5:24 am    Post subject: A workaround for the non-forwarded master primary key bug Reply with quote

I figured out a way to deal with the bug in Base 2.0.2 that means that a detail (slave) form is not automatically updated with the appropriate master ID. It's a bit of a hack, but it works.

First you need to have a text field on the master form (I set the colours so it's effectively a hidden control) and another in the the slave form for the appropriate index field(s) (in my case for CATNO in the master and Category in the slave, which are the same fields used in the master/slave links for subform).

I also use a save push button instead of saving from a navbar. Then I assign the following macro to the Before Commencing Event:

Code:
Sub CopyCategoryOnNewItemTypeSave

Dim oControlModel as Object
Dim oControlModel2 as Object

if fnFindControlModel( thisComponent.DrawPage.forms, "HiddenItemTypeCategoryTextBox", oControlModel2 )  then
   if fnFindControlModel( thisComponent.DrawPage.forms, "HiddenCategoryNumberTextBox", oControlModel )  then
      oControlModel2.Text = oControlModel.Text
      oControlModel2.commit
   else
      MsgBox(   "No Control with the name '" & "HiddenCategoryNumberTextBox" & "' found" , 16, GetProductName())
   endif
else
      MsgBox(   "No Control with the name '" & "HiddenItemTypeCategoryTextBox" & "' found" , 16, GetProductName())
end if

End Sub


This macro depends on the following functions (copied verbatim) found in another thread on this forum:

Code:


'
'      fnFindControlModel
'
' inForm - dataform to be traversed looking for
'          a control named Cname
'         
' CName - the name of the control to search for
'
' outControl - If the control is found this will hold the
'              model of the control
'              If not found outControl will be unchanged
'
' returns - True if found else False
'
function fnFindControlModel( inForm as Object,_
                     CName as String, _
                     outControl as variant) as Boolean
   dim aControl as object
   static amDone as boolean

   for i = 0 to inForm.Count -1
      if amDone then
         ' be sure to pass back our boolan check value
         ' else it will be reset to false
         ' if we are more then one level deep for the
         ' search
         fnFindControlModel = amDone
         exit function
         endif
      aControl = inForm.GetByIndex( i )
         ' I am not sure if using serviceName is
         ' considered good coding practice in OOoBasic or not
         ' but here I used it
      if aControl.ServiceName = "stardiv.one.form.component.Form" then
         amDone = fnFindControlModel( aControl, CName, outControl )
         endif
      Next i
      if not amDone then
         amDone = fnFormHasControl( inForm , cName, aControl)
         if amDone then
            outControl = aControl
            fnFindControlModel = amDone
            exit function
            end if '
         end if ' not amDone
      ' and we need to pass this back one last time
      ' just in case we have more then one DataForm at the top level
   fnFindControlModel = amDone   
      ' finally the last one out needs to turn off the lights
      ' or we will not know to start next time
      ' we get called from outside ourself
   amDone = False
end function

'
'         fnFormHasControl
'      Routine called by fnFindControlModel to check for
'      a control on THIS form
'
' oForm - dataform to check
'
' CName - the name of the control to search for
'
' outControl - If the control is found this will be the
'              model of the control
'              If not found outControl will be unchanged
'
' returns - True if found else False
'
function fnFormHasControl( oForm as object, _
                     cName as String,_
                     oControl as Object) as Boolean
   if oForm.HasByName(CName) then
      oControl = oForm.GetByName(CName )
      fnFormHasControl = True
      exit function
   endif
   fnFormHasControl = False
end function



Hope this helps!
Back to top
View user's profile Send private message
C Shore
Newbie
Newbie


Joined: 17 May 2006
Posts: 2

PostPosted: Wed May 17, 2006 5:27 am    Post subject: Addendum to the previous Reply with quote

I forgot to mention that I'm having the problem mentioned above, and that I am using the built-in HSQL engine under Debian/testing
Back to top
View user's profile Send private message
maynard J
General User
General User


Joined: 20 Apr 2006
Posts: 12

PostPosted: Wed Jun 21, 2006 9:22 pm    Post subject: nada...actually 0 ( a zero) Reply with quote

DrewJensen wrote:
I was reading this and wondering if you are not talking about this.

I have a dataabse in MySQL 5.0.18 I craete a Base form. There is a master form and a sub form. The link is correct - let's say it is

Table1( ID, Name)
Table2( ID, PersonID, Account )

Now the Main form is ID and Name from Table1 and the sub form is a grid of Table2, linked Table2.PersonID = Table1.ID. All pretty standard stuff - right.

I insert a new record in Table1 and enter some data. I save the record. The grid is blank, no entry for an insert row. That is because the sub_form does not reload itself based on the record save on table1, rather it waits for a record movement on table1. So if you step off the record on Table1 and back, now you have a new blank record in the Subform, and on my system it is poplulated with the linked value from table1.

If this is the problem you can solve it by adding a simple macro to the MainForm's On Record Action event, this macro simply calls 'SubForm.Reload'.


Hi Drew....I read all your post religiously...always helpful!
( brown nose mode off)

But i get the same result from my subform ( the FK is always 0 ) even after using your small macro to update the subform.

I've tried/searched for days with no joy. I always have to fill in the primary key value manually(into the FK field) to move on to the next record ( the ref integrity is there .. i can't move off the record until i correct it.

Even using your simple example above ..Table1,Table2.....the FK doesn't retrieve the PK value it's linked to.

I've used Mysql Administrator to check the relationship AND checked it from a Mysql command line.
The fields are identical types..i've checked that 57 times already.

I don't really mind typing in the PK->FK value.....but i have another form linked to my subform and that throws another stinky stick in the form.

Can you .point me to the joy i deserve after 2 days of frustration?
Back to top
View user's profile Send private message
xcallejas
Newbie
Newbie


Joined: 12 Jul 2006
Posts: 3

PostPosted: Wed Jul 12, 2006 7:56 pm    Post subject: the same problem ... MYSQL & OpenOffice 2.0.2 SUBFORMS Reply with quote

Hi,

I have the same problem, using mysql subforms key link field don't get updated with the key of ther master form. I have to enter manually.

OpenOffice 2.0.2
SUSE 10.1
mysql-5.0.18-20.4

Sorry but at the end of this posts I do not understud if is ther a solution for this or not yet?

Thank you.
Back to top
View user's profile Send private message
paolOOo
General User
General User


Joined: 30 Jan 2006
Posts: 23
Location: Italy (near Milan)

PostPosted: Wed Jul 12, 2006 11:53 pm    Post subject: Reply with quote

I think there is no solution till now for a mere user (like I am), it's a real pity because this is the only thing that prevent me to use OOBase at work.
_________________
Non ci sto capendo un ca##o!!!
Back to top
View user's profile Send private message MSN Messenger
xcallejas
Newbie
Newbie


Joined: 12 Jul 2006
Posts: 3

PostPosted: Thu Jul 13, 2006 10:01 am    Post subject: Reply with quote

paolOOo wrote:
I think there is no solution till now for a mere user (like I am), it's a real pity because this is the only thing that prevent me to use OOBase at work.


Tha same case :-s ..
Back to top
View user's profile Send private message
maynard J
General User
General User


Joined: 20 Apr 2006
Posts: 12

PostPosted: Fri Jul 14, 2006 12:15 pm    Post subject: amazed Reply with quote

paolOOo wrote:
I think there is no solution till now for a mere user (like I am), it's a real pity because this is the only thing that prevent me to use OOBase at work.


I'm neither a database guru or programming geek.....but this is a laughable defiency for OO or MySql,,, to attract "mere users" (95% of the target audience) we don't want/need to learn macro programming to keep table information related.

I would sacrifice features for function 10 times daily. I can't understand why this would not be a priority on the to-do list of either camp.

Flame away Exclamation
Back to top
View user's profile Send private message
Z. L. Brown
General User
General User


Joined: 21 Jul 2006
Posts: 22

PostPosted: Sun Jul 23, 2006 5:50 am    Post subject: Reply with quote

It's interesting that so many people seem to be having the same problem under the same circumstances when using MySQL as the data source for their database. Maybe the problem is in MySQL as one poster suggests. It certainly seems this needs looking into. However, before laying all the blame on the program, let's first see if part of the problem might lay in the way some users have created their main form(s) and subform(s).

I've recently learned that if you're trying to setup your relationship for your tables before building your form and subform, you will have problems not only in the structure of your form, but also in the way the data is displayed.

(Note: Poster assumes the reader is using OpenOffice 2.0.3 on WindowsXP operating system.)

Example: Most people will logically create their tables, create their relationship between their tables, and then build their form(s) and insert their subform within their main form in that order and then link the parent form to the child form in design view. In OpenOffice, for some strange reason you can't build your relationships beforehand between the main form and the associated subforms w/o risking having your data display oddly or in some cases not at all. I have no idea why this would matter as I am not a programmer just a user, but it does. But don't worry; there is a work around. I have learned that it is best to use the forms wizard and allow the system to link your tables on your primary key and forgien key for your main form and subform. This way the data for the linked (lookup) fields will display automatically from your main form into your subform. (And yes, you can create a lookup on at least two fields, i.e., you could have your CustomerID number and the customer's Last Name fields display in your subform IF you set it up the right way using the forms wizard.)

Three points of interest:

1. The above method is best suited for those who do NOT require calculated fields in their subform. If all you want in your subform is straight data, using the forms wizard to link your subform to your main form is the easiest way to go! However, if you want calculating fields in your subform, I suggest the following:

Example: Let's say you've created a family budget database where you have a table for your "Creditors" and a table for your "Payment History" toward your creditors, and all you want to do is capture historical information in your subform of how you have made payments to your creditors (i.e., total debt, amount due, amount paid, date of payment, method of payment, ending balance, etc.).

I've learned that one way to make a calculating subform work is to build a query based on your "Payment History" table - the table that would serve as the data source for your subform - and build your calculations on the required field(s), i.e., the "ending balace" field (e.g., "total debt - amount paid = ending balance"). You would then create your main form where your "Creditors" table is your parent table and the "Payment History Query" is your child. This is necessary because as most of use know you can't create calculating fields in a table and in OpenOffice you can't create them in forms either. As far as I know, this can only be done in queries (but I believe it can also be done in views and reports as well; haven't tried it yet so I'm not sure.).

(Note: Don't be confused as to what a "view" is. This is OpenOffice's equivilent to looking at a table in "data view" in MS Access or to be more precise looking at a report in "print preview". View is NOT the same as looking at your database objects - table, query, form or report - in design view.)

2. If all you're trying to do is display "historical" information in your subform, you would be much better served using a table layout with your subform. In this way, you will be able to see the entire history of your primary record (in the case of the example given, your "creditors") at a glance.

3. I'm still having problems with this auto-number issue in subforms, but I think I've figured it out. My experience has been that each time I enter a new record in my subform, the new record isn't numbered automatically. One way I've found to fix this is as follows:

a. Enter a record or two in your subform WITHOUT trying to fix the numbering yourself.
b. Close the form.
c. Open the table for your subform (to do this, right-click on the table and select "Edit"), and then renumber the records.
d. Return to your form and the records in your subform should now be numbered properly.

(Note: As of this posting, I haven't tried implementing the "reload" feature as suggested by a previous poster. I'll give it a shot on my next subform design and see if it does in fact work and get back to share my results.)

Hope this helps those who have been scratching their heads trying to figure out some of the quarks in building subforms.
_________________
Thanks.

Sincerely,
/s/Z. L. Brown
Back to top
View user's profile Send private message
Lucio
Newbie
Newbie


Joined: 07 Aug 2006
Posts: 1

PostPosted: Mon Aug 07, 2006 3:57 pm    Post subject: Reply with quote

pierdeux wrote:
DrewJensen wrote:
I insert a new record in Table1 and enter some data. I save the record. The grid is blank, no entry for an insert row. [...] If you step off the record on Table1 and back, now you have a new blank record in the Subform, and on my system it is poplulated with the linked value from table1.

If this is the problem you can solve it by adding a simple macro to the MainForm's On Record Action event, this macro simply calls 'SubForm.Reload'.


(Complete macro-novice, here!)

When you say that the macro "simply calls 'SubForm.Reload'", I presume that there are more lines than just that one in the macro. In particular, you have to get hold of that "subform object", don't you? That's the bit where I expect some oDark.Magic() incantation... I imagine that you first need to get hold of the current form object, then get a list of all its subforms, and then loop through them, applying the reload() method to each, is that right?

Would it be too much to ask for an explicit example?


Hello, first post here!

I'm a total macro-noob too, but I think I found something that might help you.

First, on an italian speaking website a simple macro using "SubForm.Reload", i.e.

Code:
Sub CambiaSelezione
        oDoc = ThisComponent
' La maschera principale
        Form = oDoc.DrawPage.Forms.GetByName("MainForm")
' La sottomaschera
        SubForm = Form.GetByName("SubForm")
' La casella
        ComboBox = Form.GetByName("ComboBox")
' Il filtro per le due maschere
        Form.Filter = "IDGenere="&ComboBox.Text
        SubForm.Filter = "IDGenere="&ComboBox.Text
' Aggiorno la visuale
        Form.reload()
        SubForm.reload()
End Sub

http://www.mail-archive.com/utenti@it.openoffice.org/msg05152.html

I tried out this one but it has the same problem as with pressing the "reload" button on the navigation bar: it will get back to entry #1 of your database (or at least it does for me).

My problem - for which I'm browsing this forum - was the following.
I have on my form: Quantity, Price, Quantity*Price and SUM(Quantity*Price), the last two come from two views, I guess... or queries? They both look the same, anyway.
So, I was very disappointed not having them refreshed each and every time I change Quantity or Price, but I noticed that swapping to the next entry and then back would do the trick... so I recorded the macro of myself clicking on "next" and then on "previous" (please don't laugh).
The code, kindly commented by oOO itself, is this one
Code:

sub Refresh
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:NextRecord", "", 0, Array())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:PrevRecord", "", 0, Array())
end sub

Quite rough, but effective! Very Happy
Back to top
View user's profile Send private message
dbrodbeck
Newbie
Newbie


Joined: 14 Aug 2006
Posts: 2

PostPosted: Mon Aug 14, 2006 3:17 pm    Post subject: Reply with quote

I'm having the same problem with the linked field in a subform not being updated properly when a record is added. It's very frustrating...I'm trying to set up a fairly simple database and this has me stalled. In Access it always "just worked." It's not a MySQL specific problem because I'm using HSQL. My OpenOffice version is 2.0.2.

Is there some reason I'm missing why a macro is needed to do this? Is this a difference in philosophy, instead of a bug? It seems like a pretty basic piece of functionality.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Aug 14, 2006 9:25 pm    Post subject: Reply with quote

dbrodbeck wrote:

Is there some reason I'm missing why a macro is needed to do this? Is this a difference in philosophy, instead of a bug? It seems like a pretty basic piece of functionality.


Can I get an Amen from the congregation!

Persoanlly I believe it is more a problem of pedigree then philosophy. But for now let's just leave this aside and accept that things are the way they are.

One can use a very generic macro to fix this oversight. The code really needs to do only a few simple things. This generic macro would then be called from the "After Record Action" for any dataform control that is linked as the master to some sub-form dataform control.

In this thread let's just do it in english, and then I will put up the actual code in the code-snippet section of this forum.

The event calls the macro, which then does:

1) Create a bookmark for the dataform control that triggered the event. ( So that we can get back to this record )
2) Turn off screen updates. ( So that we look nice ans smooth to the user )
3) Force a reload of the dataform control that triggered this event.
4) Move to the saved bookmark from step 1.
5) Turn screen updates back on

As for how long we will need to use this generic macro..I can't say. I am sure at some point this oversight will be corrected.

Now I will trundle off to Base, make a nice clean verson of this and post it to the code snippet list.

Drew
Back to top
View user's profile Send private message Send e-mail Visit poster's website
dbrodbeck
Newbie
Newbie


Joined: 14 Aug 2006
Posts: 2

PostPosted: Tue Aug 15, 2006 11:13 am    Post subject: Reply with quote

That sounds good to me. I'm not really fluent in OO's macro language yet, unfortunately.

EDIT: On rereading this, I'm not sure it would completely solve my problem. I can see how it would fix the problem of changes not being reflected in the subform until the record is reloaded. But will it fix the case where adding a record doesn't result in the linked field having the correct value? Maybe I should use an example to clarify my problem:

I have a database with two tables, TASKS and NOTES. TASKS has a primary key, TaskID. NOTES has a TaskID field that relates a note to a task. Notes appear in a subform, as a datasheet, with TaskID being the linked field between the subform and main form. When I add a note, the note's TaskID should automatically be set to the value linked from the main form. (This is what happens in Access.) However, it defaults to 0 instead, breaking the relationship between the note and the task unless I manually enter the correct TaskID.
Back to top
View user's profile Send private message
frazelle09
OOo Enthusiast
OOo Enthusiast


Joined: 24 May 2004
Posts: 142
Location: Mexicali, Baja California

PostPosted: Tue Aug 15, 2006 3:52 pm    Post subject: Reply with quote

Hi guys!

i've been having about the same one to many table problem for quite some time now. In my case i'm trying to use OOo to access a Postgresql database of two tables on a one to many relationship (patients and their visits to the clinic).

The posts that i have seen in the OOo Base forums pretty much indicate that this is not possible at the moment -- getting a second, dependent table to update automatically. The developers say that (at least this is what i understand, which is not much) the OOo drivers just don't have this capacity at the moment. i was using a driver made specifically for Postgresql, available here

http://dba.openoffice.org/drivers/postgresql/index.html

, but as you can see this is version 0.7.2 and also has the same difficulties.

There is a "call" for developers to work on this very issue here...

http://dba.openoffice.org/servlets/ReadMsg?listName=dev&msgNo=2290

and since this is so important an issue i have decided to advertise on several volunteer websites, such as Volunteer Match, for a C++ programmer to work on this issue. We have had a couple of replies and have referred a couple of them to Ocke, who is in charge of this issue for him to contact and see if they can help out. If any of you can think of other places to advertise for volunteers, please feel free to do so.

So, i'm just waiting. Patiently. i hope that this throws some light on this issue. Have a great evening! Smile
_________________
Now using 3.2.1 on 4 PCLinuxOS 2010 boxes (two are laptops).

"The earth is one country and mankind its citizens."
Bahá'u'lláh

"La tierra es un sólo país y la humanidad sus ciudadanos."
Bahá'u'lláh
Back to top
View user's profile Send private message Send e-mail Visit poster's website Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page Previous  1, 2, 3  Next
Page 2 of 3

 
Jump to:  
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