troubleshooting Question

Requery Disconnect Recordset with Table as it's source

Avatar of Veronica Armstrong
Veronica ArmstrongFlag for United States of America asked on
Microsoft Access
2 Comments1 Solution498 ViewsLast Modified:
Can the requery method be used to requery a disconnected recordset that was not based on a SQL query but on simply opening a table?  The code below shows that I open tblB2BRequests, then .addnew  for the data entry only form.  After the user submits a new record, the Process Update sub is called to update the database with the new record and then the sub requeries the recordset in order to retrieve the newest record entered.  The unbound form (multi-user environment) is designed to show only one record at a time, no record selectors, after a new record is inserted by the user, which is why I need a way to get the new record back immediately after insert.  My problem is I can't seem to get the newest record into the form but I don't get an error message either.  
Private Sub Form_Load()
    
      On Error GoTo HandleError
  
        strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & CurrentProject.Path & "\B2BDatabaseBACKEND.accdb;"
    
        'create a new connection instance and open it using the connection string
        Set cnConn = New ADODB.Connection
        cnConn.Open strConnection
            
        'create a new instance of a recordset & object
        Set rsRequests = New ADODB.Recordset
        Set objB2BRequestSubmit = New clsB2BRequestSubmit
 
        'set various properties of the recordset
        With rsRequests
           'specify a cursortype and lock type that will allow updates
         .CursorType = adOpenKeyset
         .CursorLocation = adUseClient
         .LockType = adLockBatchOptimistic
         'open the recordset based on tblContacts table using the existing connection
         .Open "tblB2BRequests", cnConn
         'disconnect the recordset
         .ActiveConnection = Nothing
    End With
         'add new record
         rsRequests.AddNew
 
 
Sub ProcessUpdate(strSQLStatement As String, Optional rsRecordset As ADODB.Recordset)
 
    On Error GoTo HandleError
    
    'This procedure is used to handle updates to the database
    
    'open the connection to the database
    Call OpenDbConnection
    
    'execute the command against the database
    Call ExecuteSQLCommand(strSQLStatement)
    
    If Not rsRecordset Is Nothing Then
        'repopulate the recordset with most current data
        Call RequeryRecordset(rsRecordset)
    End If
    'close the connection to the database
    Call CloseDbConnection
    
    Exit Sub
 
HandleError:
    GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, "ProcessUpdate"
    Exit Sub
 
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros