Trying to Work Out A Form On relational Database

keith2390 · Newbie Joined: 30 Dec 2010 Posts: 3

I've used OO for years and base in simple one-table databases.

Now I'm trying to create a relational database for my church's membership records. Despite having read tutorials and help files etc. I can't resolve the problem I have.

I have 2 tables- one for family details- surname, home address etc- and one for individual details such as name, membership status, mobile phone and work details etc.

I have a form for each of these categories. The family form and the individual form. The individual form also has the details from the family table as a subform.

The database is at [url]http://www.new-life.org.au/New-Life- People.odb[/url]

My idea is that when we add a new family or when a family moves house or whatever, the data is entered on the family details form.

New individuals are then entered on the individuals form. The family code which is the link between the tables should appear in the combo box. Enter that and the form should update the details on the subform and allow the user to enter the individual details.

It works OK when I am editing existing members, but adding new individuals does not work. The allowable Family codes do not show up and when I try to enter it manually I get an error.

Could someone please take a look at what I have? The forms are very rough- I'm trying to just make it work before I make it pretty.

Thank you

Keith

RPG · Posted: Thu Dec 30, 2010 2:11 am Post subject:

Hello

It seems me you describe a normal problem.

I follow the next method
a) I have a form where I input new members
b) In an other form I do connect the members to others .

When you add new members then the form for connecting must be reload. New data is only known in the form where you added the new data.

Romke

Villeroy · Posted: Thu Dec 30, 2010 5:30 am Post subject:

The navigation toolbar has 2 refresh buttons.
One for the entire form which interrupts your work on the current record before it can reload everything and jumps to record #1.
The other one gets active when you focus a list box or combo box. It refreshes only the list within the focussed control.
See this topic with attachment: http://user.services.openoffice.org/en/forum/viewtopic.php?t=12125&p=58167#p58043
It reflects a many-to-many relation where you can assign more than one genre to each movie. There is an additional form to enter new genres and then refresh the genre list box.
I believe that this form can not work in OOo 3.2.1 due to a nasty bug.
Base users should work with OOo 3.3 rc8.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

keith2390 · Newbie Joined: 30 Dec 2010 Posts: 3

Thanks for that help. I'll look at installing 3.3 as soon as I can get to it.

MSPhobe · Posted: Sat Jan 01, 2011 12:34 am Post subject:

I can't give you "the answer", but I would like to say that you have built a good database, and stress that this is more important than the "little detail" of not being able to update it quite as you would wish to.

I hope I am right in guessing that you already kbnow that you can work with the tables as you have them? Even if it does mean that when you have a new family to add, you must "go round the houses", i.e....

a) Add the new family to the Family table, giving it a code along the way
b) Maybe you'll then have to... for the moment... close and reopen the "enter details for individuals" form, or maybe just a simple "refresh" will do.

That "works" doesn't it?

Apologies to other posters if their answers already take care of letting the OP do the job more elegantly, but I just didn't understand!

keith2390 · Newbie Joined: 30 Dec 2010 Posts: 3

It turns out that the situation wasn't as bad as I thought it was going to be.

I polished the forms up then started using them. I discovered that after manually entering the required family code on the individual form for the first family member, the code shows up in the combo box on subsequent entries for other family members, at least in that session.

Also clicking on other fields in the sub-form after entering the family code causes the remaining fields to be filled in.

It's not ideal but it works.

Thank you for your help and encouragement.

Keith

MSPhobe · Posted: Wed Jan 05, 2011 3:19 am Post subject:

Saving records you have entered, either with the button for that, or by using the down arrow to move on to start the entry of a new record can work wonders. Even with that, if you switch to a different window, you may need to hit the refresh button in the window you've moved to.

Clumsy... but helps.

More on "saving records": Just because you can see the new record on the screen doesn't mean that it is yet IN the database.... It's a bit like all those "options" screens we use for all sorts of things... you can enter a bunch of stuff, but until you hit the "okay", or "accept" button, the changes you are proposing are not yet in effect.

kelvin · Newbie Joined: 29 Feb 2012 Posts: 2