How to create a subform on a Browse & Edit Form

[icoso]

I'm trying.... I'm really trying. Im searching the forums, Im reading the wikis, Im scanning the interwebs, Im calling some old friends... AND I'm reading thru the OLD manual because I don't see where its covered step-by-step in the NEW manual. So Im reading the OLD manual starting on page 55. Because this is basically what Im trying to do. Create a "subform" so I can be looking at a "customer" Using my regular customer Browse & Edit form and have a subform of that customers "services performed".

Here's what I did to prepare: I cloned my existing Customer Form so that I had a form that If I messed it up I wouldn't mess up what I already had working. I changed the code to CUS_LOOKUP and the Description to "Customer Lookup" I then Cloned my "Services" form for the same reason and changed the code to SRV_SUB and description to "Services Sub". SO Now I have two forms that work just like my originals. I tested them to make sure.

Page 56: I followed those instructions:
Form Tab = Main -> Services Sub
Type: Lookup
LAbel: Customers Lookups
ID: Srv_SSN (this is the field in the Services table that will be used as the index back to the Customers Table, right?
On the Lookup Tab: From: CUS_LOOKUP -> Customer Lookup
code: cus_SSN ( This is the Customers SSN field which is what the Services table will link to via the Srv_SSN field., right?)
Description: cus_lastname ( this is the Descripotion field that comes from the Customers table.
I click SAVE and get: This ID already used on this form???

After thinking about this for a few minutes I realized that the the form I was linking in had the srv_SSN field on it so I went back and deleted it off the form object list. Because thats the ONLY way to remove it from the form. SO I went back and redid everything again and tada, It saved. I looked at my Customer Lookup form and guess what? No List, just a BUTTON tht says Customer Lookup. That when clicked opens up a NEW customer lookup search and allows me to Lookup ANOTHER CUSTOMER not the services... SO it seems I created and endless loop... Hmmmmmmmmmm.... I guess by following the manual its BACKWARDS on what I'm supposed to put in the lookup form tab.... Back to the drawing board.

SO then I continue to read on in the OLD manual - NOT the NEW one because NONE of this is in the NEW Manual so all the buttons, fields, etc. references don't EXACTLY line up with the screens Im seeing, but hey Im pretty smart so a trudge on and the OLD Manual says: "In order to make this application a little more useful, we are now going to put a Subform on the
‘Organization’ Screen which shows all of the ‘Contacts’ that are linked to the ‘Organization’." AHA!!! Thats what I WANT! and figure 71 shows EXACTLY what Im looking for. A Customer with all their contacts listed in a nice concise what looks like a search form. I read on and follow the instructions....

I add an Object:
Form tab = Main -> Customer Lookup
Type: Subform
Label: Service List
ID: (WAIT!!! - The manual is showing "course_contact_subform" Where did this NAME come from. This field seems to be a selectable list of all the FIELD names of the Customer Table. NO WHERE is there a cus_something_subform. The OLD manual doesn't even have the word "course_contact_subform" in it ANYWHERE!!!! SO where did this come from????? I don't know. So I see that I can actually just type something in here - assuming that it might just create this somewhere in its vast list of tables.... I type "Service_Subform" and move on.

Then on the Subform tab:
Form: SRV_SUB -> Services Sub
foreign key: Srv_SSN (this is the index thats in my services table (many) that matches up (relational) to my customer table (one)
addable: Yes
deleteable: Yes
Type: Grid
and I click SAVE. it SAVED!!!! WOW maybe Im in luck!..........................................NO Im not.

I Open my Customer Lookup form and I have a subform, but it looks like Horizontal scrollable field listing from my PHPAdmin of the entire table structure that has BLANK Records in it. NOTHING. There's nothing there. I look at my table just to make sure I had the SSNS matching in both my Customer table AND my services table. They match. Type, Size, data all the same.

ANYONE have ANY idea's where I went wrong here??? The manual has a pretty picture in it. Mine's not so pretty... Excruciating....my head hurts..

That's my question. ^^^^^^^ Im sure I have more to come...

[kev1n]

Could you add some screenshots to illustrate the issue? I have some difficulty picking out the relevant bits from the text.

[icoso]

See Attached. It has screenshots of the Objects and the results. I thought I stepped through each step just as the OLD manual does but using my own forms. Maybe I missed a step.

Thanks for the guidance.

[kev1n]

Can you describe what you want to do in a few simple sentences? Without all the extra stuff that is rather confusing and not relevant. Thanks.

[icoso]

My whole database is very simple. My customer currently has a flat database (they might as well be using a spreadsheet) to keep a list of customer services. Currently each customer service has all the customer info duplicated. Im trying to turn it into a more relational DB where the services are broken out separately from the customers but linked relationally by the SSN. I need two tables Customers and Services Performed. The customer table (one) includes the customers SSN. My Services table uses the customers SSN and the current date as its keys (many). This is a simple relational database.

I want to:

1. Search for customers via the SSN then be able to edit them OR add new customers in a form - I created the Fast Form for customers and the form, etc. IT works. Check easily accomplished
2. I then needed my Services table. I did them same thing. I used Fast Forms to create, created an edit form, etc. separately from customers. I can add new services manually using this form. - Check.
3. Now I want to create a form of my customers that I first get to by searching for customers (like in item 1 above, but now when I find my customer via their SSN and click it I want the form to be the Customer form with a SUBFORM in it that lists all the services for that customer. At least some fields from it (like the search form I get when i open the Service table form from item 2 above.) This is nothing different than your Customer -> Orders example in the manuals. Its just when I followed that example and apply it to what I want there must be numerous steps missing or not documented that Im not doing.
4 On this Customer form with the subform of a list of services for my customer based on the SSN of the customer, when I click on one of the services, I would want it to open a new form OR in the subform section at least open the form for that service that shows the details of the service (just like in item 2 above. ) SO effectively the form steps would look like:

1. Search for Customers - click customer to open form:
(NOTE: If I'm looking at an EXISTING Customer, I do NOT want ANY of the customer fields to be editable, UNLESS I click the [Edit Customer Details Button].)
----------------------------------------
Customer Info
Name SSN etc.
Address...
Some other customer info....

[Edit Customer Details Button] [Add New Service Button]
----------------------------------------
Service 1 Date Other info
Service 2 Date Other Info
Service 3 Date Other Info
----------------------------------------

2. Then when I click on a service it should look like the following with that services details OR if I click the Add New Service Button (shown above) it should be blank:
(NOTE: If I'm looking at an EXISTING service, I do NOT want ANY of the fields to be editable, UNLESS I click the [Edit Service Details Button].)
----------------------------------------
Customer Info
Name SSN etc.
Address...
Some other customer info....
[Edit Service Details Button]
----------------------------------------
Service 1 Details
Date
Details
Cost 1 Cost 2
Some check boxes, etc...
----------------------------------------
Click Save and Im done. (It should exit out of the form completely Back to Forms Screen preferably....)

This is pretty simple and a typical relational DB setup. Nothing fancy.

I probably should just keep it one big record that keeps all the customer data in each record.... I'd be done with it by now except for needing to encrypt the SSN field as it rests in the dB table.

Then I have to create some reports for the customer to run.

[icoso]

Im still lost here. I still cant get my One to Many relational form setup properly. Not sure what I'm doing wrong.

[apmuthu]

Provide an ERD Diagram of the user tables without any data. You can do it online as well at https://app.sqldbm.com/MySQL/Draft/

Have a look at this post to clarify any missing steps.