reference a field in unbound subform with double click event

[lacigol]

Brief: I want to double click a record in an unbound subform (sfPartsList) and it be added to another subform (sfProject). I am open to alternative solutions.



I have a text box (txtSearch) in the main form that filters the records (parts) of an unbound subform (sfPartsList) on keyup via sql. The record count rarely equals one because the search (sql) spans multiple fields within the PartsList table with a short search string. I want to be able to populate a second subform (sfProject) with records that a user double clicks on in sfPartsList, essentially choosing parts for a project after a search and double click. A bill of materials solution?

I do not remember how to reference records in a subform in order to copy or address their fields to another table or control.

[andy49]

Are both subforms from the same form?


Sent from my iPhone using Tapatalk

[lacigol]

Yes.567890

[Micron]

Maybe you want to use the RecordsetClone property to copy over the records, but I'm not sure because you say the form isn't bound to anything, yet it seems to have records. The only other way I can think of how this is possible is that you went to all the trouble to loop through all the fields of a record set and assigned them to the form controls. In that case, you ought to have a usable recordset?? Maybe by "unbound sub form" you mean that it's not linked to the main form...

[lacigol]

Here's what populates my sfPartsList.

Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
  
  Dim sql, search As String

  // replace ' with '' and spaces with *
  search = Replace(Replace(txtSearch.Text, "'", "''"), " ", "*")
  
  sql = "SELECT * from PartsList " _
            & "WHERE sheet like '*" & search & "*' " _
            & "OR vendor like '*" & search & "*' " _
            & "OR desc like '*" & search & "*' " _
            & "OR cat like '*" & search & "*' " _
            & "OR subcat like '*" & search & "*'" _
            & "OR partNum like '*" & search & "*' "
  
  Me.sfPartsList.Form.RecordSource = sql


End Sub

So it's updated every keypress to narrow down search results. I might be searching for a 2'x2' something and it may give me several records containing 2'x2', but when I double click on one of those records I want it to add or append that record to another subform. I just can't recall a way to reference the record double clicked on...

[lacigol]

So I press ESC when a wizard is triggered (like putting a subform on a form). I decided to go through the wizard to try a different approach.

Although the subform wizard adds a "PartsList subform" to Access Objects (small frown), the double click event is available in that subform.

I saw a thread about someone wanting to double click a record to open that record in a new form and their reference was simple enough. Test with msgbox below:

Private Sub Form_DblClick(Cancel As Integer)
  'from https://bytes.com/topic/access/answers/192500-double-click-record-subform-open-record-new-form
  'DoCmd.OpenForm "NameOfDifferentForm",,,"[OrderDetailsID] = " & Me!OrderDetailsID
  MsgBox Me!ID
  
End Sub

[Micron]

so you figured out how to do what you wanted?
edit: ignore - just noticed you had marked this as solved.

[lacigol]

I did not, but I did find a satisfying work-around.