By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Shows empty record at the end of the filtered records.

jinalpatel
P: 68
I am using following code for searching records.

Expand|Select|Wrap|Line Numbers
  1. 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  2.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  3.                         we remove the trailing " AND " at the end.
  4.  
  5.     Dim strWhere As String                  'The criteria string.
  6.     Dim lngLen As Long                      'Length of the criteria string to append to.
  7.  
  8.  
  9.     'Look at each search box, and build up the criteria string from the non-blank ones.
  10.     'Text field example. Use quotes around the value in the string.
  11.  
  12.      On Error GoTo Err_cmdFilter_Click
  13.      strWhere = " "
  14.  
  15.      txtlno.Locked = True
  16.      FirmName.Locked = True
  17.      ContactPerson.Locked = True
  18.      LocationAddress.Locked = True
  19.      City.Locked = True
  20.      Telephone.Locked = True
  21.      County.Locked = True
  22.      MailingAddress.Locked = True
  23.      MailingCity.Locked = True
  24.      MailingCity.Locked = True
  25.      MailingZip.Locked = True
  26.      cmdSave.Enabled = False
  27.      subfirm.Locked = True
  28.  
  29.     If Not IsNull(Me.txtlnumber) Then
  30.         strWhere = strWhere & "([LicenseNumber] = """ & Me.txtlnumber & """) AND "
  31.     End If
  32.  
  33.     'Another text field example. Use Like to find anywhere in the field.
  34.     If Not IsNull(Me.txtcname) Then
  35.         strWhere = strWhere & "([FirmName] Like ""*" & Me.txtcname & "*"") AND "
  36.     End If
  37.  
  38.     'Number field example. Do not add the extra quotes.
  39.     If Not IsNull(Me.txtcontact) Then
  40.         strWhere = strWhere & "([ContactPerson] Like ""*" & Me.txtcontact & "*"") AND "
  41.     End If
  42.  
  43.      'Number field example. Do not add the extra quotes.
  44.     If Not IsNull(Me.txtphone) Then
  45.         strWhere = strWhere & "([Telephone] = """ & Me.txtphone & """) "
  46.     End If
  47.  
  48.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  49.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  50.  
  51.     lngLen = Len(strWhere) - 5
  52.  
  53.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  54.  
  55.         MsgBox "Please enter data to search", vbInformation, "Nothing to do."
  56.  
  57.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  58.         strWhere = Left$(strWhere, lngLen)
  59.  
  60.         Me.Filter = strWhere
  61.         Me.FilterOn = True
  62.  
  63.         txtlno.SetFocus
  64.  
  65.         If txtlno.Text = "" Then
  66.         cmdEditDealer.Enabled = False
  67.         MsgBox " The records you are trying to search are either do not exist or you have got incorrect information. ", vbOKOnly
  68.         cmdreset.SetFocus
  69.         Else
  70.         cmdEditDealer.Enabled = True
  71.         End If
  72.  
  73.     End If
  74.  
  75. '        cmdEditDealer.Enabled = True
  76. Exit_cmdFilter_Click:
  77. Exit Sub
  78.  
  79. Err_cmdFilter_Click:
  80. MsgBox Err.Description
  81. Resume Exit_cmdFilter_Click
  82.  
  83.  

My problem:
User enters one of the following field and clicks "Search" command
Licensenumber
ContactName
Comapnyname
TelNo

Search works properly. Suppose I enter ContactName and click Search. There are 5 records filtered. I can browse through all of the 5 records but at the end of 5th record I can still click ">" button and when I click it I get empty record.
How can we not show this empty record at the end of the filtered record?

Please Help!!
Aug 12 '08 #1
Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
The "blank" record is, I suspect, actually a new record. Try setting AllowAdditions to No.

Welcome to Bytes!

Linq ;0)>
Aug 12 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes. The blank record you are referring to is the normal Access facility to add a new record. If you do not want users to be able to add records, but you still want to allow data to be edited or deleted, set the form's Allow Additions property to No.

If you also want to prevent changes to the data altogether - no edits and no deletions - you can either set the Allow Edits and Allow Deletions properties to No as well, or (more simply) change the form's Recordset Type property to Snapshot.

-Stewart
Aug 12 '08 #3

Expert Mod 2.5K+
P: 2,545
Sorry Linq - yours was just in before mine. Simultaneous posts eh?? - S
Aug 12 '08 #4

jinalpatel
P: 68
Thanks for reply.
But When I set AllowAdditions and AllowDeletion to No ..my subform goes away from the form view.
I want to show it as well and would not like to show that empty record at the end.
Aug 12 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
It should only "disappear" if there are no records in the underlying table/query.

Linq ;0)>
Aug 12 '08 #6

jinalpatel
P: 68
It should only "disappear" if there are no records in the underlying table/query.

Linq ;0)>
Thanks again

I am using form header for search fields and form detail section for my search results and subform. When I did that sure it disappeared. but my "Search" command also disappered..is there any other way to do not show the empty recorda t the end..may be via vba coding?
Aug 12 '08 #7

Post your reply

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