Link Form & Subform to display the same record

[Culby]

I'm having a problem similar to one address before in this forum but I don't understand all the stuff you guys (and gals)were talking about. Any help would be appreciated greatly!

Here is the goal: I have created a form to display data (photos, and long comments) from a table. I have created a subform based on a query that is based on the same table to display the other data in a data sheet view looking similar to a spread sheet.

My problem is I want to display the correct record on the main form when a click on a record on the subform. Perhaps this is not the best way to set this up. I'm open to any suggestions.

Thanks,
Culby

 

[zerosandones]

The Form/Subform model you are trying to implement is an overly-complicated approach for the goal you are trying to achieve.

Recapping that you intended to reference the same single table as the recordsource for your main form and subform. I am guessing that you thought a form/subform model was the answer since part of your data included photo's and large memo fields as compared the the rest of the data you wanted to display in a spreadsheet style.

Form/Subform models are designed and traditionally used to handle one-to-many entity relationships i.e. the main form would typicall contain the parent record whereas the subform, would contain multiple child records all related to a key value in the parent record. A good example might be a Purchase Order. The parent record would contain fields such as the PO #, Vendor, Buyer etc. The child record would contain the PO #, PO Line #, Part Number, Quantity, Unit Price etc. In this example, the PO # would be used as the "Link Field" to synchronize the main form with the subform.

The main form would display one parent record at a time. The subform coud display more than one record associated with the current parent record and is typically setup as a continous form or datasheet form with a vertical scroll bar to scroll through the multiple child records while the main form fields remain visible on the screen i.e. the PO #, Vendor and Buyer would probably appear static at the top of the form while you scroll through the PO line items in the subform near the bottom of the form.

Having said all that about the form/subform approach, let's move on to what you really need here.

Since your single table contains all the fields you wish to display on the form, including the photos and fields you wish to display in a spreadsheet style, table relationships, much less a one-to-many relationship, don't even come into play.

Simply create one query which contains all the fields you want on your form.

Next, create a form using the Form Wizard with that query as its recordsource. NOTE: Most of the other prompts you'll receive in the Form Wizard are of little value here since you will ultimately have to do most of the design work manually while in Form Design View after the Wizard is done.

For simplicity sake, we'll position all the fields in the Form's Detail section.

In Form Design View, position the Photo and memo fields towards the upper region of the Detail Section.

Finally, position all the fields you want to appear in spreadsheet style i.e. each field might be formatted with a border outline and be aligned adjaecent to the next field. You would layout additional rows of fields beneath the first row if necessary. In doing so, you would create the appearance of spreadsheet rows and columns.

I hope this at least points you in the right direction.

 

[Culby]

zerosandones:

Thanks for the reply. I see what you are saying and I'll try this out, but won't this result in the photos and large comment areas displaying between each line of the other text since it is in the detail section?

I'm trying to get the spreadsheet like data together with the photos and large comments appearing in the header so it is stationary as you scroll through the grid. The comments I'm putting in a tab control so you can pick which comments you want displayed as you click on the grid.

I've had some success by pointing the control source property in the photos and large comments to the query used for creating the subform. Actually, I selected the fields I wanted in the subform, let the wizard create it (I had to keep the number of fields from getting to numerous since Access will only create a form 22 inches wide. Then, I added the additional fields to the query in order to get them by refering to them with the control source property from the header of the main form.

PROBLEM: It works but is slow. Any additional ideas? I'll try what you suggested to see if I can get it to work. What do you think?

Culby