select the wright record

[TonC]

Hello,



I’ve got the next question,
I have a form called just “Main”, within that form is a subform called “SubMain”. The subform is in DataSheet View.
On the mainform, there is command button to open a popupform.

What Im seeking is a vba code, when I click on the command button in the mainform, the popform opens directly
an select the record, that was highlighted in the subform.

Now when I open the popupform he goes straith away tot he first record in my subform, thats not my intention.
I want users not to make any changes in the subform, only select the record. The changes must be made in the popupfrm I’ve designed.


Sorry form my english, but Ill hope you can help. I'am using access 2016

[OBP]

There are a variaty of ways to filter the pop up form when opening it with the DoCmd.OpenForm statement.
See the microsoft examples here
https://msdn.microsoft.com/en-us/lib.../ff820845.aspx

[TonC]

Hello,

I did read the the website, but it is not clear for me.

All I have is a command to open the popupform;

Private Sub cmdMutatiesBewerken_Click()
Dim BewBA As String
BewBA = "popupfrm_bewerken BA"
On Error GoTo HandleError
    DoCmd.OpenForm BewBA, acNormal, , , acFormEdit
ExitHere:
Exit Sub
HandleError:
    msgbox "Fout in toepassing"
        Resume ExitHere
End Sub

When I click, the popupform opens en goes straigth to the first record. Thats not what I had in mind. When the poupform opens, he most show me the record I've selected in de subform.
That's all.

[OBP]

What is the mainform key index field name?

[TonC]

The mainform has no key index field name. The subform has no key index field only a

field named "BA_Id". (No primary key, no autonumber. just number)

That subform is in datasheet view. When I select a record on that subform, and I click the commandbutton on my mainform to open my popupform, then the popup opens and shows me the first record form my subform, and not the record I selected in my subform.

[OBP]

The table and form should have an indexed key field for exactly this kind of situation.
You could try using the BA_Id if that will control the record you need to display.
Change the line of code DoCmd.OpenForm BewBA, acNormal, , , acFormEdit to

DoCmd.OpenForm "BewBA",acNormal , , "BA_Id"=" & BA_Id.Value, acFormEdit

[TonC]

I think we almost there. I put your code in the procedure. I selected a record in my subform, clicked the command button, the popup form opened, but all fields in the form were empty.
There was no error message. Perhaps, I'am not sure, is it possible there must be some code in the popup form on open event. Like a recordset or a bookmark for a record. I do not how to write this code. The recordsource of the popupform is a query.

[OBP]

As you are using a query as the record source of the pop up you can control the record shown quite simply, change the VBA that opens the form back to it's original form.
However to do so we must identify what controls the records in the main and subform which can also be used to control the pop up.
What currently controls the subform, is it a Split Form or have you set master/child links?

[TonC]

The recordsource of the subform is the same as the popupform. The subform in the mainform is only in datasheetview, no split form, or something else. Your question about link master/child makes me curious, because, I do not know what this link is. On my mainform, there is a commandbutton to open the popupform. In the subform are fields which are mentioned in the query, the first field in my subform is the BA_Id field, is also the first field in my query.

I changed my code as you told me,

DoCmd.OpenForm BewBA, acNormal, , , acFormEdit

Now when I click the commandbutton on my mainform to open the popupform, the fields in the popupform are occupied, but not the record I've selected in the subform.

I set in the query the field BA_Id sort order, Ascending. That is not good I think. I removed the sort order in the field BA_id, But still not the correct record I selected in the subform.

[OBP]

To work correctly a Main & Subform is normally linked via the Master/Child Links, these can be found with the subform in Design mode in the Property Sheet under the Data Tab.
When we have established what this is you can copy the Subform Query and add a Parameter so that it only brings up the Record currently shown in the Subform.

[TonC]

Solved!
Code below works likes a charm!!!

Private Sub cmdMutatiesBewerken_Click()
Dim rs As Object
Dim lngBookmark As Long
 
'set a variable to the current record
lngBookmark = Me.SubMain.Form.BA_Id
 
On Error GoTo HandleError
 
    DoCmd.OpenForm "popupfrm_bewerkenBA"
 
        'take it to the selected record
        Set rs = Forms!popupfrm_bewerkenBA.RecordsetClone
        rs.FindFirst "BA_Id = " & lngBookmark
 
        If Not rs.NoMatch = True Then
        Forms!popupfrm_bewerkenBA.Bookmark = rs.Bookmark
 
    End If
Set rs = Nothing
   
ExitHere:
Exit Sub
 
HandleError:
    msgbox "Fout in toepassing"
        Resume ExitHere
 
End Sub

With many to http://www.baldyweb.com/Bookmark.htm and of course OBP who set me in the right direction to solve this problem.