listbox filtering value not passing on to next form

[mafhobb]

Hello.

This code displays query results in a listbox:

Private Sub cmdSearch_Click()

' Original Procedure
' please note that the first part of the search (for txtsearch or box 1 in the form) is done directly in the query SearchCustomertxt
    Me.lstSearch.RowSource = "Select [ID], [ISBN], [Title], [Author], [Category], [Editorial], [Read], [Bueno]" & _
                        "From [QrySearchBooks]" & _
                        "Where [ID] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [ISBN] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [Title] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [Author] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [Editorial] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [Category] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [Read] like '*" & Me.txtSearch2 & "*'" & _
                        "OR [Bueno] like '*" & Me.txtSearch2 & "*'" & _
                        "Order By Not IsNull([Title]), [Author], [Editorial];"
    Me.lstSearch.Requery

    Me.txtcount = Me.lstSearch.ListCount
Exit Sub

End Sub

This code is in the double click event for the listbox:

Private Sub lstSearch_DblClick(Cancel As Integer)

If IsNull(Me.lstSearch) Or Me.lstSearch.Value = "" Then
    MsgBox "Ningun libro seleccionado."
    Exit Sub
End If

    Dim stDocName As String
    Dim stLinkCriteria As String

' Procedure
    stDocName = "Detallesfrm"
    stLinkCriteria = "[ID]=" & Me![lstSearch]
MsgBox ID
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub

End Sub

This is in the detallesfrm form:

Private Sub Form_Open(Cancel As Integer)
    If Me.OpenArgs <> "" Then
        With Me.[Detallesfrm].Form
            .Filter = "[ID]=" & Me.OpenArgs
            .FilterOn = True
        End With
    End If
End Sub

The list gets populated fine, but when I double click on any line, the form "detallesfrm" opens in "new record" mode. I added the msgbox you see in the code to see if the [ID] value was getting passed on and all I get is a blank message box.

Anyone knows why?

mafhobb

 

[mafhobb]

I added another message box to the list's double click event:

    stDocName = "Detallesfrm"
    stLinkCriteria = "[ID]=" & Me![lstSearch]
MsgBox Me![lstSearch]
MsgBox ID
    DoCmd.OpenForm stDocName, , , stLinkCriteria

The first Message box returns the appropriate ID from lstSearch, but the second Message box is blank. My guess is that the error is the here:

    stLinkCriteria = "[ID]=" & Me![lstSearch]

mafhobb

 

[mafhobb]

BTW, [ID] is an autonumber field...I do not know if that makes any difference

 

[mafhobb]

I keep working on this. I modified the code on the list's double-click event and on the new form on open event:

Private Sub lstSearch_DblClick(Cancel As Integer)
Dim ID As Integer
If IsNull(Me.lstSearch) Or Me.lstSearch.Value = "" Then
    MsgBox "Ningun libro seleccionado."
    Exit Sub
End If

    Dim stDocName As String
    Dim stLinkCriteria As String

' Procedure
    stDocName = "Detallesfrm"
    stLinkCriteria = "ID=" & Me![lstSearch]
MsgBox Me![lstSearch]
MsgBox ID
MsgBox [ID]
MsgBox stLinkCriteria
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit Sub
End Sub

and

Private Sub Form_Open(Cancel As Integer)
    If Me.OpenArgs <> "" Then
    
MsgBox ID
MsgBox [ID]

        With Me.[Detallesfrm].Form
            .Filter = "[ID]=" & Me.OpenArgs
            .FilterOn = True
        End With
        Exit Sub
    End If
MsgBox "No OpenArgs"
End Sub

What I get when I run this is:

5 ----> Which is the ID for the rowsource picked in the list
0 ----> Saying ID has a value of 0?
0 ----> Saying [ID] has a value of 0?
ID=5 ----> Saying stLinkCriteria has that value
No OpenArgs ---> Efectively telling me that the new form opens does not see any OpenArgs being passed on to it.

So, where is the error?

mafhobb

 

[mafhobb]

All right, I have simplified my code in the listbox event to this:

Private Sub lstSearch_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String

    If IsNull(Me.lstSearch) Or Me.lstSearch.Value = "" Then
        MsgBox "Ningun libro seleccionado."
        Exit Sub
    End If
    
    stLinkCriteria = "[ID]=" & Me![lstSearch]
    MsgBox Me![lstSearch]
    MsgBox stLinkCriteria
    DoCmd.OpenForm "Detallesfrm", acNormal, , , , , stLinkCriteria
    
    Exit Sub
End Sub

I have removed all code from the Detallesfrm form OnLoad and OnOpen events

What I get when I run this is:
3 --->ID value in the rowsource chosen
[ID]=3 --->value for stlinkcriteria

The detallesfrm form opens fine, but in "new record" mode, not in record 3.

Please, help...I am really at a loss here

mafhobb

 

[Taruz]

Hi..

Change the relevant part of this ...:

DoCmd.OpenForm "Detallesfrm", acNormal, , stLinkCriteria

..

 

[mafhobb]

What do you mean by "the relevant part of this"?

I've entered the line as you have it above and I still get the same result. Detallesfrm opens up, but as a new record.

What am I missing?

mafhobb

 

[mafhobb]

Could it be something in the form's properties?

 

[mafhobb]

New Code. Same Problem

Private Sub lstSearch_DblClick(Cancel As Integer)
    
    If IsNull(Me.lstSearch) Or Me.lstSearch.Value = "" Then
        MsgBox "Ningun libro seleccionado."
        Exit Sub
    End If
    
    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = "ID=" & Me.[lstSearch]
    MsgBox Me![lstSearch]
    MsgBox stLinkCriteria
    DoCmd.OpenForm "Detallesfrm", , , stLinkCriteria
            
    
End Sub

The message boxes return "5" and "ID=5" indicating the correct ID on the line chosen

The new form still does not open on record 5

 

[mafhobb]

Thi code on the other form returns "List Openargs ="

Private Sub Form_Load()
MsgBox "List OpenArgs = " & OpenArgs
End Sub

mafhobb

 

[mafhobb]

All right, the code on the listbox is now this:

Private Sub lstSearch_DblClick(Cancel As Integer)
    
    If IsNull(Me.lstSearch) Or Me.lstSearch.Value = "" Then
        MsgBox "Ningun libro seleccionado."
        Exit Sub
    End If
    
    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = Me.lstSearch
    MsgBox Me.lstSearch
    MsgBox stLinkCriteria
    DoCmd.OpenForm "Detallesfrm", , , , , , stLinkCriteria
End Sub

On this is on the Detallesfrm form:

Private Sub Form_Load()
MsgBox "List OpenArgs = " & OpenArgs
DoCmd.GoToRecord acDataForm, "Detallesfrm", acGoTo, OpenArgs
End Sub

The msgbox on this final piece of code returns "List of OpenArgs = 3", pointing to the correct rowsource's correct ID number for the record selected.

However I get error "2501. Can't go to the specified record."

Another dead end...

 

[mafhobb]

Ok, going back to the original thought of not having any code in the opening form, I went back to this:

Private Sub lstSearch_DblClick(Cancel As Integer)
    
    If IsNull(Me.lstSearch) Or Me.lstSearch.Value = "" Then
        MsgBox "Ningun libro seleccionado."
        Exit Sub
    End If
    
    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = "[ID]=" & Me.[lstSearch]
    MsgBox Me.lstSearch
    MsgBox stLinkCriteria
    DoCmd.OpenForm "Detallesfrm", , , , , , stLinkCriteria

End Sub

and a simple msgbox in the opened form

Private Sub Form_Load()
MsgBox "List OpenArgs = " & OpenArgs
end sub

The message box returns "list of openargs = [ID] = 3"

Yet the form still does not open on that record.

mafhobb

 

[grzzlo]

The easiest way to do this is similar to what you had the first time: just use the WhereCondition, not OpenArgs.

Dim strDocName As String
strDocName = "Detallesfrm"
Dim strLinkCriteria As String
strLinkCriteria = "[ID]=" & Me.[lstSearch]
DoCmd.OpenForm strDocName, WhereCondition:=strLinkCriteria, WindowMode:=acWindowNormal

don't put any code in Form_Load()

 

[mafhobb]

OK, I've added your code and got rid of mine. Still, the Detallesfrom form opens without any data in it.

Nothing seems to work to get this done.

I have attached the database to this message. Perhaps you can take a quick look at it?

Open the welcome form and then do a simple search by typing anything in the search boxes and clicking on "buscar". The double click on an item on the list and you'll see..

Thanks

mafhobb

 

[grzzlo]

Encontré la solucion:

The code's all fine.The problem is that you've got Detallesfrm set to Data Entry mode. Try opening Detallesfrm on its own and you'll see that it doesn't show any existing records. Set Data Entry to No and it'll work fine. (Under the Data tab on the Properties sheet.)

 

[mafhobb]

I can't believe it is so simple...aaaaaaahhhhhhhhh


...THANK YOU!

mafhobb