Intermittent - Form not populating with data

[eking002]

Not sure if this is a query issue, or a form issue.



I've been using a Listbox as a search result window. Edit fields and buttons, user enters criteria, hits button and listbox is populated with query results. A form is generated when the user double clicks on a row item in the listbox.

I've added some new code, and for some reason, when the form loads, it is missing all the data.

The 'old' buttons still work perfect, the 'new' buttons don't load anything in the form.

I basically copied and pasted the buttons from the old code, and edited some values, but the basics are still in place.

This is driving me nuts, seems so simple. This is the code we use to load the new form

Public Sub List0_DblClick(Cancel As Integer)
On Error GoTo Err_List0_DblClick
    Dim stLinkCriteria As String
    Dim stdocname As String
    stdocname = "Combo"
'MsgBox strSQL
    If CurrentProject.AllForms(stdocname).IsLoaded Then
      MsgBox "The form you are attempting to open is already open." & _
      vbNewLine & "Please save what you are working on then" & _
      vbNewLine & "close the form.  Then try this operation again.", vbOKOnly, "Form Already Open"
    Else
    stLinkCriteria = "[ID]=" & Me!
[List0]
        If IsNull(Me!
[List0]) = False Then
            If Me!
[List0] <> 0 Then
                DoCmd.OpenForm stdocname, , , stLinkCriteria
            Else
                MsgBox "Please select an account", vbOKOnly, "WARNING!"
            End If
        End If
    End If
    
Exit_List0_DblClick:
    Exit Sub
Err_List0_DblClick:
    
    Response = MsgBox("Please select an account", vbOKOnly, "WARNING!", "DEMO.HLP", 1000)
    Resume Exit_List0_DblClick
    
End Sub

And this is some sample OLD button code(some redaction)

Public Sub SrchByAddr2_Click()
    stdocname = "COMBO"
    Me!
[List0] = "0"
    Me.List0.RowSource = "SELECT [Main].[ProjectID], [Main].[Status], [Main].[ffff], [Main].[ffff], [Main].[SlsMgr], [Main].[ProjNme], [Main].[PTD], [Main].[Addr1], [Main].[Addr2], [Main].[City], [Main].ID FROM [Main] where [Main].[Addr2] like " & "'*" & Me!ProjectID & "*'" & "ORDER BY [ProjectID] DESC;"
End Sub
Private Sub SrchByProjName_Click()
    stdocname = "COMBO"
    Me!
[List0] = "0"
    Me.List0.RowSource = "SELECT [Main].[ProjectID], [Main].[Status], [Main].[ffff], [Main].[fffff], [Main].[SlsMgr], [Main].[ProjNme], [Main].[PTD], [Main].[Addr1], [Main].[Addr2], [Main].[City], [Main].ID FROM [Main] where [Main].[ProjNme] like " & "'*" & Me!ProjectID & "*'" & "ORDER BY [ProjectID] DESC;"
End Sub

And the new:

Private Sub Search2_Click()
stdocname = "COMBO"
WhereDone = False
strSQL = "SELECT ProjectID, Status, ffff, fffff, SlsMgr, ProjNme, PTD, Addr1, Addr2, City, FDRet, ID FROM [Main]"

If (Check62 = False) And (Check64 = False) And (Check66 = False) And (Check68 = False) And (Check80 = False) And (Check82 = False) And (Check84 = False) And (Check86 = False) Then
    MsgBox "Please select a search filter", vbOKOnly, "Notice:"
    Exit Sub
End If

      
    
    If (Check80 = True) Then 
    If WhereDone Then
          strSQL = strSQL & " or "
       Else
          strSQL = strSQL & " WHERE ("
          WhereDone = True
       End If
       strSQL = strSQL & "Status = 'In process' "
    End If
    
    
    
    If (Check86 = True) Then 
    If WhereDone Then
          strSQL = strSQL & " or "  'or
       Else
          strSQL = strSQL & " WHERE (FDret Is Null) and ("
          WhereDone = True
       End If
       strSQL = strSQL & "Status = 'In process' "
    End If
 
        If (Check84 = True) Then 
    If WhereDone Then
          strSQL = strSQL & " or "  'or
       Else
          strSQL = strSQL & " WHERE ("
          WhereDone = True
       End If
       strSQL = strSQL & "Status = 'aaaaaProgress' "
    End If
    
    If (Check82 = True) Then  'with rep
    If WhereDone Then
          strSQL = strSQL & " or "  'or
       Else
          strSQL = strSQL & " WHERE ("
          WhereDone = True
       End If
       strSQL = strSQL & "Status = 'with rep' "
    End If
    
    If (Check68 = True) Then  '''ALL
    If WhereDone Then
          strSQL = strSQL & " and "
       Else
          strSQL = strSQL & " WHERE ("
          WhereDone = True
       End If
       strSQL = strSQL & "[Main].[ProjectID] like " & "'*" & Me!ProjectID & "*'"   '& ")"
    End If
    
     
   If (Text78 > "") Then  'if NOT blank
        If WhenDone Then
              strSQL = strSQL & " and Staff = '" & Me!Text78 & "' "
        Else
            strSQL = strSQL & ") and ( "
            WhereDone = True
        End If
        strSQL = strSQL & "[Main].[staff] like " & "'*" & Me!Text78 & "*' "
    End If
      
      
  If WhereDone Then
     strSQL = strSQL & ") and ([Main].[ProjectID] like " & "'*" & Me!ProjectID & "*'" & ") "
     'strSQL = strSQL & ") "
  End If
 ' order by project
  strSQL = strSQL & "ORDER BY [ProjectID] DESC;"
    'MsgBox strSQL ' Uncomment to Display SQL for testing
 ' set the rowsource for the listbox and then requery
    Me!
[List0] = "0"
    Me.List0.RowSourceType = "Table/Query"
    Me.List0.RowSource = strSQL
    Me.List0.Requery
End Sub

Any thoughts?

[pbaldy]

This is a valuable tool to use when debugging SQL in code:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post an example of the finished SQL here. Didn't follow it to closely, but I notice you have both And and Or in there. When you have both, you usually need parentheses to clarify the logic.

[eking002]

Thanks for the reply.

I am pretty sure the SQL is working, I have been using a msgbox, versus debug, to display the query each step.
I will be happy to add the debug, but how would that prevent the form from populating data?
The doubleclick uses the rowsource from the listbox, and all rowsource looks clean in the msgbox I've been using.

[pbaldy]

My assumption is that something in the SQL is invalid or causes zero records to be returned in the listbox. Or are you saying the listbox loads fine, but the form opened in the double click event opens empty? If that's the case, I'd check what stLinkCriteria contains, and make sure the listbox isn't multiselect.

[eking002]

the listbox loads fine, but the form opened in the double click event opens empty? If that's the case, I'd check what stLinkCriteria contains, and make sure the listbox isn't multiselect.

Exactly!

Hadn't thought of the stLinkCriteria being bad, I will keep poking around. Don't know how I could be multiselecting the listbox data.


This is difficult to explain.... but the 'old' buttons, the listbox pops the form, with all the data fine. This only seems to be an issue with the 'new' checkbox data in my this piece of code. I can find a specific dataset with the 'new' buttons, copy the number, and use the 'old' buttons to requery and load the form. The double click works when pressing the old buttons, but the doubleclick event does not seem to like the 'new' buttons.

[pbaldy]

It doesn't matter if you're multiselecting, just if it's set to multiselect. If it is, this will be Null:

Me.List0

If you're still stuck, can you post the db here?

[eking002]

I have never had to upload a DB before, so i made a 'dummy' DB and exported the search form to the temp DB file, hope this is what you meant, though don't imagine that the code will work for you.

Attachments can only be 500kb, and this was 648kb so i compressed it. FILE ---> q.zip <---- HERE

[pbaldy]

Without data I can't test why it doesn't work.

[eking002]

Damn. Sorry then, I will be unable to put too much up online. Thanks for your time anyway, I will keep playing around and try to reword-revise my request. I am not sure I am making it as clear as I can.

[pbaldy]

No problem. If you can just put a table with some dummy data or something in there, I just want to be able to track why it fails.

[eking002]

It is a linked table, on an sql server. I am having problems getting sample data, the Main table keeps appearing blank.
I think/hope this is more in line with what you are looking for. ---> q.zip <----

[pbaldy]

That works as I would expect. I entered CCS-PA-B-10-156 and searched on FSR number, it brought up 2 records in the listbox. If I double click on either one, I get that record in the Combo form.

[eking002]

Right! But now use the checkboxes, in different combinations (search2 button).


As an example: Clear the main search edit box, and check the 'install' check box and hit 'search2'. You should get three results listed as installation.

then try to doubleclick... what happens then? I get blank info on the 'combo' form.

Remove the check from install, enter any of those numbers in the main edit field and press 'FSR Number' button.... double click populates as expected, but not when hitting search2.



Same account triggered by two different methods, and I get two different results.

Something in Search2 is messing up the doubleclick function, or not pulling the rowsource data.

[pbaldy]

This give you any ideas?

?stLinkCriteria
[ID]=2/17/2010

[pbaldy]

I'm leaving for a few, so I'll give you a hint. The bound column of the listbox is 11.