469,337 Members | 6,157 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,337 developers. It's quick & easy.

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

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.

Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnClear_Click()
  5.     Dim intIndex As Integer
  6.  
  7.     ' Clear all search items
  8.     Me.txtID = ""
  9.     Me.txtLast = ""
  10.     Me.txtFirst = ""
  11.  
  12.  
  13. End Sub
  14.  
  15. Private Sub btnSearch_Click()
  16. Dim test As String
  17.  
  18.  
  19.     ' Update the record source
  20.     Me.FrmTest5.Form.RecordSource = "SELECT * FROM tblcastmemberinfo " & BuildFilter
  21.  
  22.  
  23.     ' Requery the subform
  24.     Me.FrmTest5.Requery
  25. End Sub
  26.  
  27.  
  28. Private Sub Form_Load()
  29.  
  30.     ' Clear the search form
  31.     btnClear_Click
  32.  
  33. End Sub
  34.  
  35. Private Function BuildFilter() As Variant
  36.    Dim varWhere As Variant
  37.       varWhere = Null  ' Main filter
  38.  
  39.    ' Check for LIKE Perner
  40.    If Me.txtID > "" Then
  41.        varWhere = varWhere & "[Learner ID] LIKE '*" & Me.txtID & "*' AND "
  42.    End If
  43.  
  44.    ' Check for LIKE Last Name
  45.    If Me.txtLast > "" Then
  46.        varWhere = varWhere & "[Last] LIKE '*" & Me.txtLast & "*' AND "
  47.    End If
  48.  
  49.     ' Check for LIKE First Name
  50.    If Me.txtFirst > "" Then
  51.        varWhere = varWhere & "[First] LIKE '*" & Me.txtFirst & "*' AND "
  52.    End If
  53.  
  54.    ' Check if there is a filter to return...
  55.    If IsNull(varWhere) Then
  56.        varWhere = ""
  57.    Else
  58.        varWhere = "WHERE " & varWhere
  59.  
  60.        ' strip off last "AND" in the filter
  61.        If Right(varWhere, 5) = " AND " Then
  62.            varWhere = Left(varWhere, Len(varWhere) - 5)
  63.        End If
  64.    End If
  65.  
  66.    BuildFilter = varWhere
  67.  
  68. End Function 
Oct 18 '10 #1
4 1538
nico5038
3,080 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. ?varWhere 
  2.  
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)
Oct 18 '10 #2
munkee
374 256MB
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.
Oct 19 '10 #3
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.
Oct 19 '10 #4
munkee
374 256MB
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)
Attached Images
File Type: jpg subfrmshow.jpg (33.1 KB, 132 views)
File Type: jpg after.jpg (21.1 KB, 132 views)
Oct 19 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.