Issue Updating a Field in a Record

Question

I am attempting to update a specific field in a record.  The table name is "BlockPurification", and the two columns I need to use are "Operator" and "MixID".  All I want to do is update the operator field (using an operator input box which works) for the most recent record (which will always be the MixID with the maximum value).  The operator value is varchar and MixID is Long.  When I hardcode the maximum MixID (using the variable MaxMixID) as shown in my example below, my field updates correctly.  However, I tried many different ways to update the Operator name field using the most recent MixID record after I sort the records and I can't seem to get it right.  Here is my code that works with the hard coded MixID.  Any suggestions on how to change it to make it work?

Private Sub PRF_OPCD_DL_Op_Click()
On Error GoTo ErrorHandler
    Dim conODBC As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Set conODBC = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
    Dim strQuery As String
    Dim PRF_OPCD_DL_Op As String
    Dim MaxMixID As Long
        
    PRF_OPCD_DL_Op = InputBox("Please Enter Operator Initials")
    MaxMixID = 12052199
    
    strQuery = "SELECT * From BlockPurification where MixID = " & MaxMixID
    conODBC.Open "Provider=SQLOLEDB;Data Source=W0115341\BKMXTEST;Initial Catalog=BKMXTEST;Integrated Security=SSPI;"
    adoRS.Open strQuery, conODBC, adOpenDynamic, adLockPessimistic
     
    adoRS!Operator = PRF_OPCD_DL_Op
    adoRS.Update
    PRF_OPCD.PRF_OPCD_DL_Op.Caption = adoRS!Operator
    
    adoRS.Close
    conODBC.Close
    Set adoRS = Nothing
    Set conODBC = Nothing
    Exit Sub
    
ErrorHandler:
    Set adoRS = Nothing
    Set conODBC = Nothing
    HandleError
End Sub

Answer 1

Wrong forum :) Even if it carries Access in its name.

Can you expain the context a little bit more? What does the "after I sort the records" mean? What do you want to do exactly? Update the current row?

Then it should take the value, e.g. from the current RowSource:

strQuery = "SELECT * From BlockPurification where MixID = " & Me![MixID]

Answer 2

Here is a brief outline of what I am trying to do:

Every time I click a link on my operators page, it will run the attached subroutine above (Private Sub PRF_OPCD_DL_Op_Click()).

Next, the routine will prompt me to input the operators initials, this value will be stored in variable PRF_OPCD_DL_Op. 

Next, I want to sort through all the records in the table and find the most current record (this will be the one with the largest value for MixID).

Lastly, I want to update the Operator field with the operators initials stored in the variable PRF_OPCD_DL_Op in the most current record (the one with the largest value for MixID).

Pretty simple, just getting a little hung up.

Answer 3

I still don't get it. But what about

  strQuery = "SELECT * From BlockPurification where MixID = ( SELECT MAX(MixID) FROM BlockPurification  )" 

Answer 4

Tried it, I would think this would work, but it does not.  The database is not being updated.  I will peck at it again tomorrow.  thx for tyring

Private Sub PRF_OPCD_DL_Op_Click()
On Error GoTo ErrorHandler
    Dim conODBC As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Set conODBC = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
    Dim strQuery As String
    Dim PRF_OPCD_DL_Op As String
    Dim MaxMixID As Long
        
    PRF_OPCD_DL_Op = InputBox("Please Enter Operator Initials")
    strQuery = "SELECT * From BlockPurification where MixID = (SELECT MAX(MixID) FROM BlockPurification)"
    conODBC.Open "Provider=SQLOLEDB;Data Source=W0115341\BKMXTEST;Initial Catalog=BKMXTEST;Integrated Security=SSPI;"
    adoRS.Open strQuery, conODBC, adOpenDynamic, adLockPessimistic
    
    adoRS!Operator = PRF_OPCD_DL_Op
    adoRS.Update
    PRF_OPCD.PRF_OPCD_DL_Op.Caption = adoRS!Operator
    
    adoRS.Close
    conODBC.Close
    Set adoRS = Nothing
    Set conODBC = Nothing
    Exit Sub
    
ErrorHandler:
    Set adoRS = Nothing
    Set conODBC = Nothing
    HandleError
End Sub