Why is my search form returning 1 record in my subform when there should be multiple?

[Kelly Warden]

I have a search form with a sub data form. When i search for "john" for example I should get a bunch of records in my subform, however it is just showing the first one from the table.

Here is the VBA.

 Option Compare Database
Option Explicit
 
Private Sub btnClear_Click()
    Dim intIndex As Integer
 
    ' Clear all search items
    Me.txtID = ""
    Me.txtLast = ""
    Me.txtFirst = ""
 
 
End Sub
 
Private Sub btnSearch_Click()
Dim test As String
 
 
    ' Update the record source
    Me.FrmTest5.Form.RecordSource = "SELECT * FROM tblcastmemberinfo " & BuildFilter
 
 
    ' Requery the subform
    Me.FrmTest5.Requery
End Sub
 
 
Private Sub Form_Load()
 
    ' Clear the search form
    btnClear_Click
 
End Sub
 
Private Function BuildFilter() As Variant
   Dim varWhere As Variant
      varWhere = Null  ' Main filter
 
   ' Check for LIKE Perner
   If Me.txtID > "" Then
       varWhere = varWhere & "[Learner ID] LIKE '*" & Me.txtID & "*' AND "
   End If
 
   ' Check for LIKE Last Name
   If Me.txtLast > "" Then
       varWhere = varWhere & "[Last] LIKE '*" & Me.txtLast & "*' AND "
   End If
 
    ' Check for LIKE First Name
   If Me.txtFirst > "" Then
       varWhere = varWhere & "[First] LIKE '*" & Me.txtFirst & "*' 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 

[nico5038]

I normally don't code this as I explain the user the right-click pop-up menu. Saved me a lot of time...

Best to run this code and place a breakpoint (click in left "ruler" to get a "dot") when the " AND " is truncated.

Use:

?varWhere 
 

in the immediate window to see the build expression.

Using the query appended with this expression you can copy/paste it in the query editor's SQL-text mode and see what happens.

Nic;o)

[munkee]

Ensure your subform is allowed to display more than one record also. Set it to be a continuous form or atleast have the record navigation buttons there.

I know this is obvious but sometimes it can be the simplest of things.

[Kelly Warden]

My subform is a tabbed subform; It has 3 tabs and the two last tabs show more then one. However the first record just shows one. I have it set to single form. I tried other forms but it they don't work or look right.

[munkee]

The first will only show one because it is set to single form. This means it will only show 1 record at a time. In order to view the rest you need to use record navigation buttons.

You can either add these via the control wizard (insert a command buttong and go through the record navigation wizard) or by setting the properties of the subform to "Record Navigation: yes" or true I forget the exact. However this will show a small toolbar at the bottom of the subform and will detail something like showing record 1 of 100 etc. You can then navigate through the other records using the arrow buttons.


If you wish to see all of the records you can try continuous form and set your textboxes etc all in one horizontal line. Then resize the forms detail section to be a similar height to the controls. When you view the subform in form view you will then see your records all listed.

I have attached an example of this with a before (in design view) and the after (when in form view)