Subform record based on Listbox selection (1 Viewer)

mjdemaris · Sep 3, 2015

Hello everyone.
Today I am working on creating a Main form with tab controls and subforms. I have a search feature using an option group to select one of three fields to search in, and a text box to type the search criteria. The list box uses SQL to generate matching records from one table based on the search criteria.
I would like the user to click on a row in the listbox to select it, store a value from that row, and then click on a button that uses that value to populate the subform with additional data from a couple of tables and a query or two.
The subform only has six controls, atm, an image, part number, description, location, category and quantity. the image will be an OLE object; the quantity is a calculated field from a query, and the rest are fields from tables but put together using queries.

Ideas?

I am currently thinking that creating another query to bring all this together, and use a parameter for the correct record's information.

Thanks.

GrandMasterTuck · Sep 4, 2015

mjdemaris said:
Hello everyone.
I would like the user to click on a row in the listbox to select it, store a value from that row,

What I would do is on the OnClick event of the row, store the field's value into a TempVar

mjdemaris said:
and then click on a button that uses that value to populate the subform

When you click the button, add some code to the OnClick of the button that runs a SetValue command on the field you want to insert that value from before. I use Access's Macro to do this, but you can also do it with VBA (I'd have to research the syntax). So for instance, if you have Form1 with Row1 and Column1 set to "Fred", when you CLICK Row1, it stores the value "Fred" into TempVars!TheName. Then, when you click the button to open Form2, the OnClick will set the value of MyNewFieldOnForm2 to "Fred" by setting it's value to TempVars!TheName.

AccessBlaster · Sep 5, 2015

mjdemaris said:
.... I would like the user to click on a row in the listbox to select it, store a value from that row, and then click on a button that uses that value to populate the subform with additional data from a couple of tables and a query or two.
Thanks.

You can use the double click event on the listbox to open the selected record in a new form. The new form can have as much detail as the record contains.

Code:

Private Sub SearchResults_DblClick(Cancel As Integer)
    On Error GoTo Form_Err
    
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmEdit"                               ' Edit form
    stLinkCriteria = "[ID]=" & Me![SearchResults]       ' Listbox
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
 Form_Exit:
    Exit Sub
 
 Form_Err:
    MsgBox err.Description
    Resume Form_Exit
End Sub

Subform record based on Listbox selection (1 Viewer)

mjdemaris

Working on it...

GrandMasterTuck

In need of medication

AccessBlaster

Registered User.

Similar threads

Users who are viewing this thread