Following Query Form Tutorial, Not Working... Help?

S

slkasulke

Registered User.
Local time
Yesterday, 19:34
Joined
May 31, 2012
Messages
27
Hi, all.

I found the tutorial from this website after doing a google search that shows a method on creating search forms. I am trying to adapt the code for my own, but have hit a road block. Every time I try to run the query, it asks me to enter parameter values. I checked the spelling on everything, and it all seems right... Haven't touched Access for a few years, so I was thinking there might be some form building details that I had forgotten.

Here is the code...

Code: 
Option Compare Database
Option Explicit
Private Sub btnClear_Click()
    Dim intIndex As Integer
 
    ' Clear all search items
   ' Me.txtKeywords = ""
    Me.cmbIndustry = 0
    Me.cmbAudience = 0
 
End Sub
Private Sub btnSearch_Click()
    ' Update the record source
        Me.frmsubItems.Form.RecordSource = "SELECT * FROM qryItemData " & BuildFilter
 
    ' Requery the subform
    Me.frmsubItems.Requery
 
End Sub
 
Private Sub Form_Load()
    ' Clear the search form
    btnClear_Click
 
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
 
    ' Check for LIKE Keywords
    If Me.txtKeywords > "" Then
        varWhere = varWhere & "[itemKeywords] LIKE """ & Me.txtKeywords & "*"" AND "
    End If
 
    ' Check for Industry
    If Me.cmbIndustry > 0 Then
        varWhere = varWhere & "[itemIndustry] = " & Me.cmbIndustry & " AND "
    End If
 
    ' Check for Audience
    If Me.cmbAudience > 0 Then
        varWhere = varWhere & "[itemAudience] = " & Me.cmbAudience & " AND "
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
           varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
 
    End If
 
    BuildFilter = varWhere
 
End Function

I know I have to adjust the code that strips off the last "AND". Was playing with the code when I was troubleshooting, and that's where I left off.

And attached is the DB in question.

Thanks so much for your help!
 

Attachments

Last edited:
Won't let me post the link to the sample DB I was referencing...

It is thread #99353.
 
Still looking for help on this...!
 
slkasulke,

I have seen this issue when an sql query is requesting a table field that is not part of the table structure.

cplmckenzie
 
The essential problem is that the code was originally written for combo boxes that were bound to numeric fields. However, your combo boxes are strictly text values, so you need to add string delimiters to this part of the code. There are also a few other things in there that are not necessary. Could be leftover stuff from before the code was modified.

The original code with some comments (in red);

Code: 
Option Compare Database
Option Explicit

Private Sub btnClear_Click()
    Dim intIndex As Integer[COLOR="Red"] << this variable is never used
                               so there is no reason to declare it.[/COLOR]
 
    ' Clear all search items
   ' Me.txtKeywords = ""
    Me.cmbIndustry = 0[COLOR="red"] << since these combo are text it seems [/COLOR]
    Me.cmbAudience = 0[COLOR="Red"] << (and looks) odd to set them to 0.[/COLOR]
 
End Sub
Private Sub btnSearch_Click()
    ' Update the record source
        Me.frmsubItems.Form.RecordSource = "SELECT * FROM qryItemData " & BuildFilter
 
    ' Requery the subform
    Me.frmsubItems.Requery[COLOR="red"] << this is not necessary as the requery happens
                              automatically when you reset the RecordSource.[/COLOR]
 
End Sub
 
Private Sub Form_Load()
    ' Clear the search form
    btnClear_Click [COLOR="red"] << this is also unneccessary as these are unbound Controls.
                       Their values will not persist once the form is closed.[/COLOR]
 
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant[COLOR="red"] << more unused variables[/COLOR]
    Dim intIndex As Integer[COLOR="red"] <<[/COLOR]
    varWhere = Null  ' Main filter
 
    ' Check for LIKE Keywords
    If Me.txtKeywords > "" Then
        varWhere = varWhere & "[itemKeywords] LIKE """ & Me.txtKeywords & "*"" AND "
    End If
 
    ' Check for Industry
    If Me.cmbIndustry > 0 Then [COLOR="red"] << modify this line and add string delimiters in the following line[/COLOR]
        varWhere = varWhere & "[itemIndustry] = " & Me.cmbIndustry & " AND "
    End If
 
    ' Check for Audience
    If Me.cmbAudience > 0 Then[COLOR="red"] << ditto[/COLOR]
        varWhere = varWhere & "[itemAudience] = " & Me.cmbAudience & " AND "
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
           varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
 
    End If
 
    BuildFilter = varWhere
 
End Function

And revised code;

Code: 
Private Sub btnClear_Click()
    
    ' Clear all search items
    Me.txtKeywords = ""
    Me.cmbIndustry = ""
    Me.cmbAudience = ""
    
End Sub

Private Sub btnSearch_Click()
    
    ' Update the record source
     Me!frmsubItems.Form.RecordSource = "SELECT * FROM qryItemData " & BuildFilter
    
End Sub

Private Function BuildFilter() As Variant

    Dim varWhere As Variant

    varWhere = Null  ' Main filter
    
    ' Check for LIKE Keywords
    If Me.txtKeywords <> "" Then
        varWhere = varWhere & "[itemKeywords] LIKE """ & Me.txtKeywords & "*"" AND "
    End If
    
    ' Check for Industry
    If Me.cmbIndustry <> "" Then
        varWhere = varWhere & "[itemIndustry] = """ & Me.cmbIndustry & """ AND "
    End If
    
    ' Check for Audience
    If Me.cmbAudience <> "" Then
        varWhere = varWhere & "[itemAudience] = """ & Me.cmbAudience & """ AND "
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
           varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
        
    End If
    
    BuildFilter = varWhere
    
End Function
 
Beetle said:
The essential problem is that the code was originally written for combo boxes that were bound to numeric fields. However, your combo boxes are strictly text values, so you need to add string delimiters to this part of the code. There are also a few other things in there that are not necessary. Could be leftover stuff from before the code was modified.

The original code with some comments (in red);

Code: 
Option Compare Database
Option Explicit
 
Private Sub btnClear_Click()
    Dim intIndex As Integer[COLOR=red] << this variable is never used[/COLOR]
[COLOR=red]                              so there is no reason to declare it.[/COLOR]
 
    ' Clear all search items
   ' Me.txtKeywords = ""
    Me.cmbIndustry = 0[COLOR=red] << since these combo are text it seems [/COLOR]
    Me.cmbAudience = 0[COLOR=red] << (and looks) odd to set them to 0.[/COLOR]
 
End Sub
Private Sub btnSearch_Click()
    ' Update the record source
        Me.frmsubItems.Form.RecordSource = "SELECT * FROM qryItemData " & BuildFilter
 
    ' Requery the subform
    Me.frmsubItems.Requery[COLOR=red] << this is not necessary as the requery happens[/COLOR]
[COLOR=red]                             automatically when you reset the RecordSource.[/COLOR]
 
End Sub
 
Private Sub Form_Load()
    ' Clear the search form
    btnClear_Click [COLOR=red]<< this is also unneccessary as these are unbound Controls.[/COLOR]
[COLOR=red]                      Their values will not persist once the form is closed.[/COLOR]
 
End Sub
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant[COLOR=red] << more unused variables[/COLOR]
    Dim intIndex As Integer[COLOR=red] <<[/COLOR]
    varWhere = Null  ' Main filter
 
    ' Check for LIKE Keywords
    If Me.txtKeywords > "" Then
        varWhere = varWhere & "[itemKeywords] LIKE """ & Me.txtKeywords & "*"" AND "
    End If
 
    ' Check for Industry
    If Me.cmbIndustry > 0 Then [COLOR=red]<< modify this line and add string delimiters in the following line[/COLOR]
        varWhere = varWhere & "[itemIndustry] = " & Me.cmbIndustry & " AND "
    End If
 
    ' Check for Audience
    If Me.cmbAudience > 0 Then[COLOR=red] << ditto[/COLOR]
        varWhere = varWhere & "[itemAudience] = " & Me.cmbAudience & " AND "
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
           varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
 
    End If
 
    BuildFilter = varWhere
 
End Function

And revised code;

Code: 
Private Sub btnClear_Click()
 
    ' Clear all search items
    Me.txtKeywords = ""
    Me.cmbIndustry = ""
    Me.cmbAudience = ""
 
End Sub
 
Private Sub btnSearch_Click()
 
    ' Update the record source
     Me!frmsubItems.Form.RecordSource = "SELECT * FROM qryItemData " & BuildFilter
 
End Sub
 
Private Function BuildFilter() As Variant
 
    Dim varWhere As Variant
 
    varWhere = Null  ' Main filter
 
    ' Check for LIKE Keywords
    If Me.txtKeywords <> "" Then
        varWhere = varWhere & "[itemKeywords] LIKE """ & Me.txtKeywords & "*"" AND "
    End If
 
    ' Check for Industry
    If Me.cmbIndustry <> "" Then
        varWhere = varWhere & "[itemIndustry] = """ & Me.cmbIndustry & """ AND "
    End If
 
    ' Check for Audience
    If Me.cmbAudience <> "" Then
        varWhere = varWhere & "[itemAudience] = """ & Me.cmbAudience & """ AND "
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
           varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
 
    End If
 
    BuildFilter = varWhere
 
End Function
Click to expand...

Thank you! This makes a lot of sense. This cleared just about everything up...

cplmckenzie said:
slkasulke,

I have seen this issue when an sql query is requesting a table field that is not part of the table structure.

cplmckenzie
Click to expand...

Cplmckenzie, you reminded me that I needed to check my query for itemKeywords... nowhere to be found! Could've sworn I added it too.

This problem has been solved. Thanks so much!
 
You must log in or register to reply here.

Similar threads

oxicottin
Cant get form filter to work correctly
Replies
8
Views
493
plog
P
oxicottin
Solved Export search form data to excel
Replies
11
Views
988
oxicottin
oxicottin
T
Solved Filter form after update with code
Replies
9
Views
508
arnelgp
arnelgp
J
Search for possible declarations
Replies
9
Views
372
sonic8
S
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
265
Faoineag
F

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom