Solved How do I set main form record by selecting sub form field? (1 Viewer)

[5hadow]

Basically my subform is a glorified list box. I'd like to be able to select a record on it to display all of the relevant data on the main form. Subform is based on a much smaller query, but same data as main form.

Parent / Child relationships don't work because main form filters the subform (Need it to be the other way around).

I've tried this:

Private Sub Form_Current()
   If Not Me.NewRecord Then
      Me.Parent.Recordset.FindFirst "ID = " & Me.Recordset.Fields("ID")
      If Not Me.Parent.Recordset.NoMatch Then
         Me.Parent.Bookmark = Me.Parent.Recordset.Bookmark
      End If
   End If
End Sub

But I get run-time error 3021 - No current record.

When stepping trough the code there is a current record, and I know there is a matching record on main form.

Anyone know what's going on?

 

[theDBguy]

Hi. Have you considered using a split form or an emulated one?

 

[Josef P.]

... and I know there is a matching record on main form.

Is the subform bound to the main form? .. then check call of Form.Current

I would be careful when using Recordset directly.
Me.Parent.Recordset.FindFirst already moves the record in the form.

A little better in my opinion is:

Dim rst As DAO.Recordset

If Not Me.NewRecord Then

   Set rst = Me.Parent.RecordsetClone '<-- use Clone
   rst.FindFirst "ID = " & Me.Recordset.Fields("ID")
   If Not rst.NoMatch Then
      Me.Parent.Bookmark = rst.Bookmark
   End If

End If

Note: I do not like accesses from the subform to the main form. I would control the record navigation in the main form also in the main form. Maybe even with an extra event that is raised by code in the subform.

 

[Uncle Gizmo]

I prefer a combo box on the main form.

I demonstrate the method on my blog here:-

Northwind Problem and Solution - Nifty Access

Northwind Problem and Solution If you use the Northwind database as a basis for your business application then there is a problem that you should be aware of. The sample Northwind database supplied by Microsoft provides some excellent insights in how you should program an MS Access application...

www.niftyaccess.com

The blog is actually about correcting a problem with the early version of the Northwind sample database, a problem which if not corrected, could result in you recording incorrect information.

 

[bastanu]

I'd use RecordsetClone as suggested and also add .Form after Me.Parent. Another option would be to use Docmd.FindRecord:

Me.Parent.Form.Controls("ID").SetFocus
DoCmd.FindRecord Me.ID

Cheers,

 

[5hadow]

Ok everyone, thank you for all of your help. After doing everything that everyone has suggested, I've given up after none of it worked. What I ended up doing was making a hidden textbox on main form, changing it's query ID field to filter based on that text box, and set oncurrent event on the subform to pass on the user ID.
This now works just fine.

Again, thanks for help everybody!

 

[Uncle Gizmo]

I've given up after none of it worked

Looks like you've come up with a good solution to your problem!

I thought it was worth mentioning that I'm not surprised you've had problems.Interactions between forms and subforms can be problematic as MS Access can randomly alter things and mess up what you're trying to do.

 

[gemma-the-husky]

Just an observation. You shouldn't expect a subform to have any influence on its parent, although your description of subform may mean something else.

 

[MajP]

What you are doing is a "split form." A main form in single form view and an non-linked subform in continuous form view. You can do what you want without code using the splitform wizard.

However, many people here prefer

Emulating the split form

Like many users of this forum, I abandoned split forms a long time ago as I found them difficult to adapt if you want to modify them in any way In a recent thread another user wanted to keep the idea but to emulate it using a single form with a continuous subform. See this thread...

www.access-programmers.co.uk