Populating a form by double-clicking a record in a subform...

[jesusoneez]

I have a form with subform...they are not linked (the form shows the table data directly and the subform shows a query of the table).

The subform is set as a continuous form. I want to double-click on the unique field to open that record in the parent form.

I have something similar in another database, that when double-clicking on the unique field, it opens a seperate form for editing the record.

This is the code use for that:

Private Sub UserID_DblClick(Cancel As Integer)
    Dim stDocName As String                 'Target form name
    Dim stLinkCriteria As String            'Link field on current form (UserID)
    Dim UserID As String                    'Linked field on target form (UserID)
    
    stDocName = "frmBannedUserEdit"
    stLinkCriteria = "[UserID]=" & "'" & Me.UserID & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        
Exit_UserID_OnClick:
    stDocName = vbNullString
    stLinkCriteria = vbNullString
    
DoCmd.Close acForm, "frmBannedUserManagement"
End Sub

Obviously I want to adapt this, and my first thought was to replace;

DoCmd.OpenForm stDocName, , , stLinkCriteria

with;

Me.stDocName.Refresh, , , stLinkCriteria

But that didn't work and I can't find anything that'll work.

Any help (as usual) much appreciated.

 

[MarkK]

What I would do is run a FindFirst operation on the recordset exposed by the parent form.

Private Sub UniqueField_DblClick()
  'reference the recordset of the form in which navigation is to occur
  With Forms("YourTarget").Recordset
    'run a findfirst against that recordset
    .FindFirst "IdField = " & Me.UniqueField
    'if item not found issue warning
    If .NoMatch Then MsgBox Me.UniqueField & " not found"
  End With
End Sub

 

[jesusoneez]

OK, I've popper this code in;

Private Sub txtBanID_DblClick(Cancel As Integer)

With Forms("frmBannedUsers").Recordset
FindFirst "BanID = " & Me.txtBanID
If .NoMatch Then MsgBox Me.txtBanID & " not found"
End With

End Sub

Now, if I'm reading this right, the first line says "work on the records in frmBannedUsers (parent form).

The second line says "find the first record that contains the value of txtBanID". txtBanID is the name of the textbox we double click in the subform, and also the name of the textbox in the parent form.

The third line says "if this ban id doesn't exist in the parent form, display an error message box".

Is that right?

I get a compile error (sub or function not defined) highlighting txtBanID in the FindFirst line..?

 

[jesusoneez]

Erm...thanks, I missed out the full-stop before FindFirst and it works fine/



Thanks again.