Solved

How do I prevent a new record from displaying

Posted on 2011-09-27
12
265 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article shows how to make a Windows 7 gadget that extends its U/I with a flyout panel -- a window that pops out next to the gadget.  The example gadget shows several additional techniques:  How to automatically resize a gadget or flyout panel t…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared 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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

20 Experts available now in Live!

Get 1:1 Help Now