Subform to choose form record

[SueBK]

I have a form, that has on it the details of a specific incident. Below the details I have a sub-form that lists all the incidents. So (sort of) ...

-------------
Issue: 1
Details: Ladder fell over
Personnel: Joe Blogs
---------------
Issue..Details .............Personnel
1 ......Ladder fell over..Joe Blogs
2. .....Floor was wet....Suzie Q
----------------

The sub-form is in datasheet view. I'd like to be able to click a record in the sub-form and have the main form 'jump' to that record. So, in the above example, when the form opens it will go to Issue 1. If the user click issue 2 in the sub-form, I want the details above to update to that information.

Clear as mud?

 

[MarkK]

Very clear, but this is more commonly and more simply implemented using a listbox.
The subform implementation requires that you respond to the click on the record. Maybe you could handle the subform's Current event, but there is no Click event for a record in a subform. The subform also requires the additional object, which is not so much an issue in smaller projects. And finally, you need to get the subform object to either run code on the main form, or reference the main form and directly make changes to the current record. No one of these is hazardous or difficult, but add them up...
Using a listbox you can set it up using the wizard, and then handle the click event with code like...

private sub lstNav_Click()
[COLOR="Green"]  'navigates to the record selected in lstNav[/COLOR]
  with me.recordsetclone
[COLOR="Green"]    'find the record in the clone - speedy[/COLOR]
    .findfirst "MainFormID = " & me.lstNav
[COLOR="Green"]    'if found, move to that record in the form[/COLOR]
    if not .nomatch then me.bookmark = .bookmark
  end with
end sub

...and you're done. One control, one handled event.
On open, navigate to your first record as follows...

Sub Form_Load()
[COLOR="Green"]  'set the value of the control to the value of the first list item[/COLOR]
  me.lstNav = me.lstNav.ItemData(0)
[COLOR="Green"]  'force a click on the list[/COLOR]
  lstNav_Click
End Sub

That's my 2c.
Cheers,

 

[Galaxiom]

There are several ways to change a current record in response to a change in the subform. Hope I have managed to explain some of them below without making a mistake.
---------------
LINKED SUBFORMS
Create an Unbound form (Record Source blank) and place your record viewing subform.
Place a textbox on the main form with its Control Source bound to the current record in the subform.
In the Control Source property enter:

=[SubformControlName]![FieldName]

This box will then follow the field in the selected record of the subform.
You can make it invisible if you wish but it is sometimes a really useful thing to display as it will be a key field.

Make a second subform control which will hold the editing form.
Set its RecordSource as the table or updateable query you wish to edit.
Set the subform's default view to single form view.
Set the Link Master Field property as the textbox on the main form and the Link Child Fields property as the matching field in the subform. Just use the plain field names.

This subform will display the record matching the textbox on the main form which in turn follows the current Record on the first subform. The links must be able to select a unique record.

If required add more bound textboxes to the main form and Link Fields in the subform control as required to select a unique record.

You can turn off the border, navigation, selectors etc in the editing subform and it will even look like it is part of the main form.

---------------
QUERY BASED ON SUBFORM
The query used as the Record Source for the main form can include criteria drawn from the current record of the subform:

SELECT fieldname1, fieldname2, etc FROM tablename WHERE tablename.fieldname = [Forms]![MainFormName]![Subformname].Form![FieldName];

Your query must be updateable and needs to select a unique record. You must use full addressing of the subform field in a query. Me! doesn't work here.
The OnCurrent Event of the subform has to requery the mainform to update the record. (The OnCurrent event of the subform is triggered when a record is selected.)

Me.Parent.Requery

-------------------
ADVANCED TECHNIQUE
This technique is useful where the data to be updated cannot be held in an updateble query. It allows the information to be handled completely arbitrarily including conditional mandatory fields but every aspect of the record updates must be managed in code. It is a lot more work and not recommended for beginners.

Use the subform OnCurrent event to write the current record's fields to unbound text boxes on the main form.

Me.Parent.txtboxname = Me!Fieldname

Editing these textboxes does not do anything to the records which must then be updated by other VBA commands triggered by a Save button for example.

You need to deal with the possibility of the user moving to another record before saving. This can be easily achieved by disabling the subform until the Save button is pressed.

A more advanced and intuitive (for the user) is by setting a flag when a field is edited. When the user moves to another record, the OnCurrent sequence tests for the flag, prevents the new record from being loaded and pops up a dialog box asking if the current record should be saved. Answering yes initiates a save. Answering No reloads the text boxes. Either procedure completes by resetting the flag.

To do this well also requires bookmarking the record so that it may be returned to if the user chooses Cancel from the Save dialog. There are many catches for the unwary.

 

[gemma-the-husky]

strictly speaking, i would not regarde your setup as a form/subform.

a parent /subform relationship should be the other way to what you describe

for each item in the parent form, there should be one or more corresponding entries in the subform - eg the parent shows customers. and the subform shows orders, relating to the customers.

you are really looking for ways to help you navigate your dataset -
i think the answers already given should help you achieve what you want

 

[SueBK]

We got around this issue by:
- designing a continuous form with the basic details, which were showing on the sub-form. To the side of each record is a command button that opens a 2nd form
- the 2nd form shows all the extra information, which was the main details of the original form.

So (in case that's as clear as mud the user sees a list of issues, with just basic details. If they want to add additional details they simply click the "edit record" button to the right.

 

[heypetray]

Galaxiom:

Set the Link Master Field property as the textbox on the main form and the Link Child Fields property as the matching field in the subform. Just use the plain field names.

It's saying I can't do this because the parent form is unbound. I tried getting around this by giving it a dummy record source query, but it won't work. How can I link my subform to an unbound parent text box?

EDIT:
Nevermind! The dialog that popped up to select the link was giving me issues. When I just typed in the properties manually with the name of the parent text box, and the name of the child, it worked great. This has been the greatest success in internet access discovery! Thanks immensely!