Help with requery on button click

[sambo]

I have the following code working behind my button click. It works great when there is only one user.
The problem arises when the DB goes up on the network and multiple users begin to use it.

The problem is that the search function does not always seem to be querying form the most up to date information.

I added the ctl.Requery line to try and remedy this. The purpose of this line is to requery the active form before searching for the record. Although the form is requeried, the search sometimes fails to work until the user manually requeries the form (Shift + F9). After using this method it always works properly.

Can anybody tell me what I could do to remedy my requery problem.

Public Function myLookUp(intWhatSearch As Integer, whatValue As Variant, whatRecSet As String, ctl As Control)
'This function gets a value to search for in a table and populates the current form with
'that value's information
'Variable Defs: intWhatSearch -- Arbitrary, 1 for serialNum search (type integer) 2 for Rma (type String)
'               whatValue -- the value (from a txtbox) that is to be searched for
'               whatRecSet -- the recordset that holds the info being searched
'               ctl -- the form that is to be requeried (retrieve latest recordset)
'Example Call (in the onclick of a button):
'=myLookUp("1",[Forms]![MainForm]![Update Records].[Form]![searchSerial],"Update Records",Forms!MainForm!RmaCheckIn)
'Created by Sam Owre 9/26/02

On Error GoTo LookUp_Err

    Dim db As DAO.Database  'dbsNorthwind
    Dim rstLog As DAO.Recordset  'rstCustomers
    Dim intSerial As Integer   'Dim strCountry As String
    Dim strSearch As String
    Dim varBookmark As Variant
    Dim strMessage As String
    Dim intCommand As Integer

    ctl.Requery   'Requery the current form to get the most current info

    Set db = CurrentDb
    Set rstLog = db.OpenRecordset(whatRecSet, dbOpenSnapshot)

    Do While True
    
        If intWhatSearch = 1 Then   '1 for serial search (arbitrary) integer value syntax
            strSearch = "[SerialNum] = " & whatValue  'intSerial
        ElseIf intWhatSearch = 2 Then  '2 for rma search (arbitrary) string value syntax
            strSearch = "[RmaNum] = '" & whatValue & "'"
        ElseIf intWhatSearch = 3 Then  '3 for Trimble Rma search (arbitrary) string value syntax
            strSearch = "[TrimbleRma] = '" & whatValue & "'"
        
        End If
        
        With rstLog
            ' Populate recordset.
            .MoveLast
            ' Find first record satisfying search string. Exit
            ' loop if no such record exists.
            .FindFirst strSearch '.FindFirst strSearch
            If .NoMatch Then
                MsgBox ("Record Does Not Exist")
                Exit Function
            End If
            
            Dim myPos As Long
            Do While True
                ' Store bookmark of current record.
                varBookmark = .Bookmark
                myPos = .AbsolutePosition + 1   'myPos = .AbsolutePosition + 1
                ' Get user choice of which method to use.
                    DoCmd.GoToRecord , , acGoTo, myPos  'Switch the form record
                    Exit Do
            Loop

        End With

        Exit Do
    Loop

    rstLog.Close
    db.Close
    Call ClearSearchBox  'Clear all search boxes to avoid confusion
Lookup_Exit:
  Exit Function
    
LookUp_Err:
  MsgBox "Enter a Value"
  'MsgBox Err.Description & " " & Err.Number
  Resume Lookup_Exit
  
End Function

 

[DALeffler]

Try:

ctl.Form.Requery

I have noticed a difference when requerying controls-vs-forms.

If my code is:

Me!MainFormSbFrm.Requery 'requery the sub form control

I'll see my sub form keep the same current record as before the requery.

Where as:

Me!MainFormSbFrm.Form.Requery

always makes the first record in the recordset for the sub form the curent record.

HTH,

Doug.