Running Query & Displaying Results on Continuous Form (1 Viewer)

[matt164]

Hi,

I am trying to code for a query in vb that will be executed when a certain button is clicked, and the query results will open in a different continuous form...this code has worked in the past I'm not sure why it's giving me trouble now. When I click "OK" to execute the query I get a error in vb stating there is an error in the FROM line. Here is the code from my "GUI" form where the text boxes are and the "OK" button set to execute the search (based on inputs to text boxes).

Option Compare Database
Option Explicit
Public strQuery As String
Private Sub new_Click()
End Sub
Private Sub Text14_Click()
Me.Text14.SelStart = 0
End Sub
Private Sub clear_Click()
Me.creatorcb.Value = Null
Me.assigncb.Value = Null
Me.Text8.Value = Null
Me.Text10.Value = Null
Me.Text12.Value = Null
Me.Text16.Value = Null
Me.Text14.Value = Null
Me.Frame20.Value = Null
End Sub
Private Sub ok_Click()
'Run query
Dim argCount As Integer
If IsNull(creatorcb.Value) And IsNull(assigncb.Value) And IsNull(Frame20) And IsNull(Text10) Then
    MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
    Exit Sub
End If
strQuery = "SELECT * From Lead Where"
If Not IsNull(creatorcb.Value) Then
    strQuery = strQuery & "Creator Like '" & creatorcb.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(assigncb.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "AssignedTo Like '" & assigncb.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text8.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "BorrowerFirstName Like '" & Text8.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text10.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "BorrowerLastName Like '" & Text10.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text12.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "Company Like '" & Text12.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text16.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "PropertyCity Like '" & Text16.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Text14.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "State Like '" & Text14.Value & "'"
    argCount = argCount + 1
End If
If Not IsNull(Frame20.Value) Then
    If argCount > 0 Then strQuery = strQuery & " AND "
    strQuery = strQuery & "Status = '" & Frame20.Value & "'"
    argCount = argCount + 1
End If
DoCmd.OpenForm "GridDisplay", acNormal, , , acFormEdit, acWindowNormal
End Sub

Here is the code from the form where I would like the results to display "GridDisplay"

Option Compare Database
Option Explicit
Private Sub Form_Open(cancel As Integer)
'---- SQL comes from Form_GUI.strQuery ----
Me.RecordSource = Form_GUI.strQuery
End Sub
Private Sub Status_AfterUpdate()
    If Me.Status = 1 Then
        Me.Text22 = "Active Qualified"
    ElseIf Me.Status = 2 Then
        Me.Text22 = "Follow-up Qualified"
    ElseIf Me.Status = 3 Then
        Me.Text22 = "Rejected"
    End If
End Sub

The debugger highlights "Me.RecordSource = Form_GUI.strQuery" in yellow, however I think the problem lies within the query code on the GUI form. Any help would me much appreciated, thank you!

 

[JHB]

You are missing a space at last.

strQuery = "SELECT * From Lead Where"
should be
strQuery = "SELECT * From Lead Where "

 

[matt164]

Thanks! Simple solution fixed the problem. Now my issue is with the continuous form I use to display the query results - "GridDisplay1"

As you can see above, at the end of the query there is a command to open the form, which has been changed to now read:

DoCmd.OpenForm "GridDisplay1", acNormal, , , acFormReadOnly, acDialog

Here is the code for the form object:

Option Compare Database
Option Explicit
Private Sub Command25_Click()
DoCmd.OpenForm "LeadDetails", acNormal, "Point_To", , , acDialog, """"
End Sub
Private Sub Form_Open(cancel As Integer)
'---- SQL comes from Form_GUI.strQuery ----
Me.RecordSource = Form_GUI.strQuery
Me.cursorlock.SetFocus
End Sub
Private Sub Form_Load()
    If Me.Text9 = 1 Then
        Me.actqual.Visible = True
   ElseIf Me.Text9 = 2 Then
       Me.followqual.Visible = True
   ElseIf Me.Text9 = 3 Then
       Me.turndown.Visible = True
    End If
End Sub

Since the database stores the "Status" field of each record as a number (1, 2, 3) and I would like to display that as a color-coded textbox on the search results, I have three different text boxes with 3 different statuses set to not visible, and the form on load should look to the text box containing the 1, 2, or 3 and set my appropriate textbox to visible accordingly. However for some reason whatever the very first record on the form is set too, that result displays for all the records returned by the query. In addition, if you run the query and there are no matching records it gives a bunch of errors in VB. Any input? Thanks in advance!

 

[JHB]

I think you should use Form_Current instead of Form_Load.