Send records to new form by double-clicking in a listbox (1 Viewer)

[ToucHDowN]

Hi all,

I'm in desperate need of a (simple I guess) code to allow me to send records from my listbox to a new form. Scenario goes as follows:

I use cascading combo boxes to narrow my available choices and finally end up with a listbox presenting the records filtered through cascading process. Now, I want to double-click on a record and load a new form with all this record's fields.

This is the code used to populate the listbox:

Private Sub FilterTypeList()
  
  Dim strRS As String
  
  ' Filter the list box appropriately based on the combo box selection(s)
  strRS = "SELECT qryTaxonomy.Type, qryTaxonomy.Article FROM qryTaxonomy"

  If Not IsNull(Me.cboSubCatID) Then
    strRS = strRS & " WHERE SubCatID = " & Me.cboSubCatID
  ElseIf Not IsNull(Me.cboCatID) Then
    strRS = strRS & " WHERE CatID = " & Me.cboCatID
  End If
  
  strRS = strRS & " ORDER BY qryTaxonomy.Article;"
 
  Me.lstTypeID.RowSource = strRS
  
  Me.lstTypeID.Requery
  

End Sub

Could you please help me with the code needed to open a form with the double-clicked record's fields, as described above?

Thanks in advance,

-george.

 

[ToucHDowN]

Open your listbox in design view and open its properties. Goto the events tab and scroll down to the double click event and press the three little ellipse (...) to the right. Then paste in something like this.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "[COLOR=red]Form To Open Name[/COLOR]"
    
    stLinkCriteria = "[[COLOR=red]SubCatID[/COLOR]]=" & Me![[COLOR=red]Me.cboSubCatID[/COLOR]]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

"Form To Open" The name of the form you want to open goes there. That form needs to have the same record source as the list box.
If this fails upload a striped down version of your database.

Thanks.

CatID and SubCatID (and cboCatID and cboSubCatID respectively) are the cascading combo boxes that filter information down to the lstTypeID listbox. So the listbox in which certain criteria should be met is the lstTypeID.

Your instructions give me this error: "Access can't find the field "Me.cboSubCatID" referred to in your expression". And I guess this is understood, given the above explanation.

So, I'm uploading the file. The main form is "frmTaxonomyLookup". I've already made a test form, "frmtest", I guess you can use it. In this form, I want to get all values from tblType, according to what I double-click in the lstTypeID listbox.

Thanks in advance,

-gm.

 

[ToucHDowN]

Any more help, please??

 

[JHB]

Try the attached database.

 

[ToucHDowN]

Try the attached database.

Hi JHB,

Well...!!! The marvels of the Internet. The only thing that puzzles me now is that for the new form to load, BOTH cboCatID AND subCatID combos MUST have a value. They can't be blank. If so, double-click returns an error. Could we bypass this, somehow??

A million thanks!

 

[JHB]

You can test for null values:
Replace with the below code:

Private Sub lstTypeID_DblClick(Cancel As Integer)
  If Not IsNull(Me.cboSubCatID) And Not IsNull(Me.cboCatID) Then
    DoCmd.OpenForm "tblType", , , "SubCatID =" & Me.cboSubCatID & " AND Type='" & Me.lstTypeID & "'"
  End If
End Sub

 

[ToucHDowN]

You can test for null values:
Replace with the below code:

Private Sub lstTypeID_DblClick(Cancel As Integer)
  If Not IsNull(Me.cboSubCatID) And Not IsNull(Me.cboCatID) Then
    DoCmd.OpenForm "tblType", , , "SubCatID =" & Me.cboSubCatID & " AND Type='" & Me.lstTypeID & "'"
  End If
End Sub

Ok, great! From now on, I only need to find a way NOT to populate the listbox when the main form loads, in order to prevent the user from double-clicking on a record while the above combo boxes are nulls and nothing happens.

I believe I can find a way, again, thank you very much, and AccessBlaster and this hospitable forum!!!