[Solved] Help with form and subform (1-to-n rltnshp)

[however]

Hello again,
I have a main form for the address table. I would like to add a subform of patients living at that address (1 address for many patients, but one patient can only have one address).
I have understood how to work with the Form Navigator button and it has helped me a lot when it comes to associate fields or grid to a table etc.
I have also read and reread and reread the tutorial on listbox; copied step-by-step the relation2listboxes.odb examples kindly offered by villeroy in this forum; tried all the possible filter options I could come across through the navigation bar or via the Form Control Pane; but I still can't get my simple form to work (to show me the patients living at the same address).
Any hints would gladly be appreciated.
Regards,

p.s.: the crumbled picture is due to upload size restriction

[DACM]

That setup should work. Are you sure the Table Control grid is on the SubForm?

Oh wait...It appears you're using two SubForms...? Are those cascaded? What's the MainForm used for?

[however]

Hi DACM,
thanks for your input.

I've just rechecked. I don't have two subforms. The beige form with labels and txt fields is the main form; the grid is in the subform.

May I ask why you thought that I had two subforms? the two Form Control Panes are displayed together to indicate what I tried each time; they belong to the same subform grid, (a little photoshop/GIMP touch to create a smaller file-size). I tried both as 'Table' and as SQL Command' in the content and content type of the Form Control Pane; same result: NOT working.

I have found a previous post of yours, showing exactly what I need (billingDatabase2.odb) and I noticed that you also used 'table' as content and content type in the Form Control Pane; and still it won't work for me.

I doubt it matters, but I am wondering whether it would have anything to do with the fact that my DB is actually not registered yet as mySQL in my slackware box is, for some reason, broken (I will look in to this soon).

All the posts and tutorial I have read just simply use that single filter option and I have wasted almost a week reading around and trying and retrying.

Please, anyone, let me know if I'm really dumb or something is missing somewhere.

[DACM]

May I ask why you thought that I had two subforms? the two Form Control Panes are displayed together to indicate what I tried each time; they belong to the same subform grid...

That threw me off. I saw the "L" (link fields) in both dialogs and thought they represented different SubForms.

[path32]

yes youre right DACM It has "L" in his form

can i ask something? (To be clear)
you had a two tables?

(what's the field names of that table)

what is the mainform and a subform of your table?

if yes ( the relationship of the two tables is AddressID? )


Or we can check your base if you upload it

[however]

Hello path32, and thanks for your input.
I would love for you to check my Base but, unfortunately the file is too big to be uploaded here, ( unless you have any suggestions on how to upload big files)
I guess I should apologize for not being clear in my explanation, and perhaps causing confusion.
I have a patient table, 'patientID' as a PK and 'addressID' as a FK then
I have an address table, 'addressID' as a PK
The two table are linked by a 1:n relationship, with 1 on the address table and many on the patient table; (one patient has one address, but one address may have many patients)
I created a form where I can input/record patient's addresses; in that form I added a subform to see, at a glance, whether other patients live at the same address, i.e. husband and wife, or even just housemates.
I hope this helps you to help me, cos I really don't know what else to try.
Regards,

[however]

Am I going KUKU? (replying to my own post!

Anyway, I am constantly trying to get this thing to work and I'm pretty sure that the problem is assigning PK to FK fields.
A visible field (index #0) and a hidden field (bound field, mostly index #1). The visible field lets you pick another table's item by name, the bound field is the respective primary key of the selected item which gets written into the foreign key of the form's current record.
For a few days, I have been reading, day and night non-stop, this cpl of sentences (maybe i need to get some sleep) and can't figure out how to define the visible field and the bound field.
I guess I need to replace one of the columns in my subform grid with a listbox, and I have done it. Attached is my subform grid and the Form Control Pane of the last column 'addressID' which I have now replaced with a list box. What do i need to input as a bound field? and where is the visible field?
I would be very grateful if someone could explain this to me as if I was a 6yr old child (from the movie Philadelphia), as I have at least 6 or 7 forms which I would like to design in the same way (between tables on 1:n relationships). I'm sure that once I grasp it I should be on my way.
Regards,

[RPG]

Hello

I think activate the wizard for listboxes. Then insert a listbox.

Romke

[however]

thanks for your input RPG,

I know how and, have already, inserted a list box in the grid (as you can see from the Titlebar in the Form Control Pane, that indicates it's a listbox) without the help of a wizard, as this throws me off. The main point is how to have this listbox to assign PKs to FK fields.

The form control pane requires instructions as follow:
Data field,
Input required,
Type of list contents,
List content,
Bound field.
How do I get the listbox to assign PKs from a table to FK fields in another table?

Regards,

p.s.: as I am typing, I am alos looking at explanation from one of DACM's reply to a similar post. HOpe it will help

[DACM]

Here's a demo of a SubForm used to display information on other patients at the same address -- as you've outlined/requested.

Note that the "Patients..." field in the Table Control is a List Box. This is a technique used to display the actual Patient Name rather than a cryptic PatientID. So this is a backwards use of a List Box. The same technique is used to display all of the Address fields on the Patient Form. Each is bound to AddressID with their individual SQL-content setup to display the desired information from the Address table. Otherwise, note that I allowed modifications of the SubForm's data-source (specifically the BirthDate, Mobile and Email fields in the Table Control on the SubForm).

I doubt it matters...my DB is actually not registered yet as mySQL

That doesn't matter unless you're using standalone forms. Are you using a MySQL back-end? That's great but users have run into various Form issues with MySQL in the past, although as I understand the situation these bugs were all fixed with the 3.3.0 release of OOo. If these Forms don't work with MySQL (drag-&-drop; with appropriate catering for your particular field names), then it could be revealing a bug which might be cleared-up by changing the connection driver (SDBC, ODBC, etc).

BTW, we do like to steer users away from Base Combo Boxes in general, because they promote data redundancy, which reveals normalization issues with the underlying Table. But when we do choose to de-normalize a Table (redundant Town, Country, Postal Code), Combo Boxes are used instead of List Boxes to maintain data consistency as an input aid. Tight consistency is required in this case because you're interested in displaying other patients at the same address. Two addresses that differ by even a single character ("UK" vs. "U.K.") simply won't provide the desired function. So based on your Address table design, there's probably as many Combo Boxes as List Boxes in this demo to highlight the differences including usage scenarios for both types.
...

[RPG]

Hello

When you do not understand a listbox then read this:
http://user.services.openoffice.org/en/ ... 83&t=28235

Listbox in a TableControl.
Most of the time the listbox use a PK for displaying an other value or selecting a value and storing an other value in the table. This is for starters difficult to understand but when you use the wizard then the wizards guides you. The wizard for a listbox works not in a gridcontrol. But make a listbox in the same form and copy the values in the listbox in the gridcontrol. When you use the standards then it is easy that you changes values by accident.

I have nothing to add to the post of DACM

Romke

[path32]

try this...

you should read how to use this list box.. with sql

[however]

another BIG thank you to all:
DACM for your sublime example and outstanding explanation;
path32, RPG and everyone else for your fantastic assistance and input,
I believe that finally i grasped how to use listbox to assign PK to FK fields and will start right away to finish all the other forms
thank thanks, thanks

[however]

Hi DACM,
I had read your previous explanation and thought again to be a straight forward process and, something really good to know once you master it.
Since I have several forms which have similar design, I would like to understand and learn the process (also to avoid returning here and ask for help about the same thing over and over, which also make me sound/feel like a dumb ).
So, I simply didn't want to copy your example but rather practice/do it on my DB. Following your instructions and example I tried to reproduce the same, unsuccessfully.
I have been banging my head on the sharpest corner in my house but I can't find any setting differences from your example, and still it won't work.
I should say that I tried to do only one thing different with which I am left to suspect: I have eliminated a few forms and integrated them as subform withing a main form. For example, before I had a form for a NewPatient, a form for a NewAddress and another form for a NewGP; now I have designed a NewPatient main form with NewAddress and NewGP as individual subforms.
Could this be the reason for not working? everything else I believe it to be identical to your example, (if you spot some inequalities I would be happy but I would also be sad).
I have attached both your example and mine.
Regards,