Solved

How do I prevent a new record from displaying

Posted on 2011-09-27
12
267 Views
Last Modified: 2012-05-12
I have built search functionality that moves through the current table looking for a match.  I don't want to display the interium records.  I set a global boolean variable that is supposed to bypass all of the display code in the Current event - If gblnProcess = False Or mblnLoad = False Or gblnShow = False Then GoTo OUT.  Debugging verifys that the variable is set to false and the Current event code is bypassed.  HOWEVER, the first 300 or so records display and then the mouse changes to the current version of the hourglass and nothing displays until a match is found or the end of the table is reached.  I would prefer that none of the interium records.  I would understand if all of the records are displayed.  I can't understand why/how some of the records display.   Thanks!
0
Comment
Question by:athayes
  • 6
  • 6
12 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 36734268
Attach a sample database. Give instruction of what to search for and what to expect.
As far as i understand, this can be achieved by:
-Create a recordsetclone
-Find candidate record.
-If found
           display it
-If not redisplay the current record
           Issue a message if required.
0
 

Author Comment

by:athayes
ID: 36814099
I'm assuming that you mean table rather than database.   Don't I still have to move from the current record in the original table to the record that matches the found record in cloned table.  I neglected to mention that I am in a form when I am doing the search.  If there is a way of moving to a specific record without going through a loop of .movenext commands, I love to hear about it.  Failing that I don' see how displaying records in the first part of the loop can be prevented.  I'd also really like to know why the unwanted display stops about a third of the way through the loop.  I guess this is more of a problem than I thought, so I increased the point value
0
 
LVL 30

Expert Comment

by:hnasr
ID: 36814134
athayes,

I am assuming that you are using access 2007 for this issue.
If so, then create a new database and import the table and form and any necessary objects to demonstrate the issue.
Give instructions to follow to see the problem.
0
 

Author Comment

by:athayes
ID: 36816902
Sorry, can't send the database.  Even the code is classified.  

I was able to recreate the problem in the attached Zip Code database.  To use it you:

Click on the Zip Code Search Label
       the corresponding textbox will be enabled
Type a Zip Code to be searched for
       suggest you use 47202 because the problem will occur
Hit the Tab Key
       the Search function is called in the textbox's Lost Focur event.  Records are displayed for somewhere around the first 1K records then the HourGlass icon appears and no records are displayed until the correct record is found.  Using the debugger shows that the form's Current Event is not accessed unless the requested record is found.
EETest.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 36898681
athayes,

The sample database is not working as suggested.
Clicking label has no effect on enabling the texbox.
No after update code for the mentioned textbox.
Please address the issue and attach.
0
 

Author Comment

by:athayes
ID: 36899580
I'm really confused.  It worked fine for me.  I used the file attached to the question because I don't have the code here at home.  Clicking on the label (lblPT4) changes the label's backcolor to vbBlue and the forecolor to vbWhite.  I don't ever use AfterUpdate.  My code in the the LostFocus event of txt4.  The applicable code is below.  Interestingly, the HourGlass icode didn't show up this time.  I have a more memory here.  Regardless, all I am really looing for a way to prevent the display of a record after a .movenext.


Private Sub txt4_LostFocus()
   mblnShow = False
   If IsNothing(txt4.Value) = True Then GoTo OUT
   Call Search
OUT:
End Sub

Private Function Search() As Boolean
Dim strTest As String
Dim blnShow As Boolean
   strTest = txt4.Value
   cmdExit.SetFocus
   txt4.Enabled = False
   With Me.Recordset
      .MoveFirst
      Do While Not .EOF
         If IsNothing(txt1.Value) = False Then
            If At(txt1.Value, strTest) > 0 Then
               blnShow = True
               Exit Do
            End If
         End If
         .MoveNext
      Loop
      mblnShow = blnShow
      Call Form_Current
   End With
   If blnShow = False And Len(strTest) > 0 Then Call ATHMsgBox("E", strTest & " not Found.")
   Search = blnShow
End Function
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 36901113
Modify the search function by inserting the code between the 2 comments. 'start , 'end
Here is the search function code including the inserted part. Exit function is added just before your current code.
Check blnshow conditions if they are required.

Removed Tab Stop in txt4 field, otherwise it keeps running the search code.
Check the attached database.

 EETest-2.accdb

 
Private Function Search() As Boolean
'start of inserted code
    Dim mySet As Recordset
    Dim myCriteria As String
    Dim msgBoxResult As Integer
        myCriteria = "[strZipCode]" & " = " & " '" & [txt4] & "'"

    Set mySet = Me.RecordsetClone
    
    mySet.FindFirst myCriteria
    If Me.Dirty Then
        Me.Undo
    End If

    If mySet.NoMatch Then
       
        MsgBox "No record was found!", vbOKOnly, "Sorry!"
    Else
        msgBoxResult = MsgBox("Do you want to move to the found record?!", vbYesNo, "Record was found!")
        If msgBoxResult = vbYes Then
            Me.Bookmark = mySet.Bookmark
        Else
        End If
    End If
    mySet.Close
    Exit Function
    ' end of inerted code
Dim strTest As String
Dim blnShow As Boolean
   strTest = txt4.Value
   cmdExit.SetFocus
   txt4.Enabled = False
   With Me.Recordset
      .MoveFirst
      Do While Not .EOF
         If IsNothing(txt1.Value) = False Then
            If At(txt1.Value, strTest) > 0 Then
               blnShow = True
               Exit Do
            End If
         End If
         .MoveNext
      Loop
      mblnShow = blnShow
      Call Form_Current
   End With
   If blnShow = False And Len(strTest) > 0 Then Call ATHMsgBox("E", strTest & " not Found.")
   Search = blnShow
End Function

Open in new window

0
 

Author Comment

by:athayes
ID: 36903157
I'm going to accept this as the solution because I didn't know about .FindFirst (or Seek for that matter) or .Nomatch.  Most of the other code was unnecessary - particularly Cloning.  I don't need to offer the validation of the move to the found record.  My users have to request a search and supply the value to be matched, so I have a sufficiently high level of confidence that they will want to be deposited at the matching record automatically.  I have another bit of functionality in which I will use .FindFirst, so the solution is doubly beneficial.  Thanks!
0
 

Author Closing Comment

by:athayes
ID: 36903829
See below
0
 
LVL 30

Expert Comment

by:hnasr
ID: 36904686
Welcome!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 36904758
The code creates a recordset that represents all the records the form displays.
.FindFirst finds the qualified record in the recordset.
.Nomatch means no qualified record is found. According to this condition the code sets the bookmark of the form to the bookmark of the recordset.
Each record has its bookmark. If you set the form's bookmark to that of a record, the form displays that record.
0
 

Author Comment

by:athayes
ID: 36904786
Understand what .bookmark does.  Like creating a clone, it just isn't necessary for my application.  Thanks for the help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now