If Value in Listbox = this open this Form if = That op that form

[naturalsn]

Good Morning

I have a form where data is being filtered in an unbound listbox. When the data is filtered I will then double click on the record and will open it in an additional form for viewing etc.

I now however in my listbox have a new field called status.
The status can be, Active, Pending, Inactive or Archive.

I am trying to amend my on "Double click" code so that if the status, is
Active, Pending, Inactive open Form A
and if the status is
Archive it will open Form B

The working code without any errors

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset
                  
DoCmd.OpenForm "FrmA"

Set rst = Forms!FrmA.Recordset.Clone
rst.FindFirst "[ID] = " & Me.SearchList
Forms![FrmA ].Bookmark = rst.Bookmark
DoCmd.Close acForm, Me.Name
                 
Exit_SearchList_DblClick:
    Exit Sub
Err_SearchList_DblClick:
    MsgBox Err.Description
    Resume Exit_SearchList_DblClick
End Sub

I currently get error "Method or Data Member not Found" By Status with the following code

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset

If Me.SearchList.Status = "Archive" Then
                  
DoCmd.OpenForm "FrmB"
Set rst = Forms!FrmB.Recordset.Clone
rst.FindFirst "[ID] = " & Me.SearchList
 
Forms!FrmB.Bookmark = rst.Bookmark
DoCmd.Close acForm, Me.Name

Else
                  
DoCmd.OpenForm "FrmA"
Set rst = Forms!FrmA.Recordset.Clone
rst.FindFirst "[ID] = " & Me.SearchList

Forms!FrmA .Bookmark = rst.Bookmark
DoCmd.Close acForm, Me.Name

Any help would be appreciated

 

[Remou]

Status is the name of a field refernced by the listbox, but it is not a property of the lisbox. You can refer to columns:

[tt]If Me.SearchList.Column(1) = "Archive" Then[/tt]

Would mean that the second column was Status because the count starts from zero (0).

 

[naturalsn]

Hi Remou

Thanks you so much for the help. Working Perfectly
;-)