Go To a Record by Primary Key (1 Viewer)

[music_al]

Hi

I have a Single Form and a Datasheet on a form which hold the same data. I sometimes need to filter the datasheet and select a record and then have the Single Form jump to that record. This isn't working using ...

DoCmd.GoToRecord , , acGoTo, Selected_Candidate

...so I want to return the Primary Key when I select the record in the datasheet and then go to that record in the single form.

How would I do this ?

Thank you in advance

 

[The_Doc_Man]

Here's a suggestion: Create a combo box with the control wizards enabled. One of the options allows you to select a value and use it to navigate to a particular record. Build one of those and see how that code works, then try to do something similar using whatever record selection method you wanted to use.

Hint: It involves using .Recordset, .RecordsetClone and the .Bookmark property of a recordset.

 

[Micron]

Single Form and a Datasheet on a form

These are 2 separate forms?
You can get the value of any field using the Current event of a form and if filtering a different form, apply that filter to it in that event.


If there's only 1 form involved, you could use a split form, but I confess I've not used them beyond just playing around so I don't know if they have any drawbacks. However, you can jump to any "main" form record simply by choosing a record in the datasheet portion. Also, navigating to a main record selects the datasheet record.
I'm curious as to why the same records are on different forms.

 

[music_al]

Yes they are 2 separate forms on the same parent form. Each form (a datasheet and a single form) have the same data source.

This is because the users wanted to see an Excel/table type view and when they select a particular record on the datasheet, they want the single form below it to jump to that record.

I managed to get this to work using GoTo Record, but when the datasheet is filtered, it doesn't work.

 

[isladogs]

As Micron suggested, a split form is one way of doing this. They work well provided you don't want/need to customise them much.
Alternatively try the emulated split form (ESF) which overcomes the limitations of the built in split form.
See https://www.access-programmers.co.uk/forums/showthread.php?t=294421

 

[music_al]

I created a split form which opens fine on its own, but when I drag it onto a tab, its only showing the single form and not the datasheet. Any ideas why ?

 

[isladogs]

The built-in split form doesn't work as a subform e.g. in a tabbed form
If you look at your split form in design view, you will see it is just a single form.
It has some clever trickery behind the scenes to make it work as a split form (BUT NOT when used as a subform)

However the good news is the emulated split form DOES work as a subform / in a tabbed form. See link in my last post.

 

[Cahyanto]

You just need couples of vba code
in the datasheet form module create function like this

Function LinkData2Forms(DataSheetPrimaryCtl As Control, SingleFormName As String, PrimaryFieldName As String)
    Dim frm As Form, rc As Recordset
    If Not IsLoaded(SingleFormName) Then Exit Function
    Set frm = Forms(SingleFormName)
    Set rc = frm.RecordsetClone
    rc.FindFirst BuildCriteria(PrimaryFieldName, rc.Fields(PrimaryFieldName).Type, "=" & DataSheetPrimaryCtl.Value)
    If rc.NoMatch Then DoEvents Else frm.Bookmark = rc.Bookmark
End Function

'And then put this code in the "On Current" event in the datasheet
LinkData2Forms Me("DatasheetPrimaryControl"), "SingleFormName", "PrimaryFieldName"

hopefully can help