[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

How do I prevent a new record from displaying

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
athayes
Asked:
athayes
  • 6
  • 6
1 Solution
 
hnasrCommented:
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
 
athayesAuthor Commented:
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
 
hnasrCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
athayesAuthor Commented:
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
 
hnasrCommented:
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
 
athayesAuthor Commented:
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
 
hnasrCommented:
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
 
athayesAuthor Commented:
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
 
athayesAuthor Commented:
See below
0
 
hnasrCommented:
Welcome!
0
 
hnasrCommented:
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
 
athayesAuthor Commented:
Understand what .bookmark does.  Like creating a clone, it just isn't necessary for my application.  Thanks for the help.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now