Setrecordid() - Dynamic autonumber.

[Snichols]

I am attempting a set record ID to make a number field dynamic. 1st - doe the field have to be an "autonumber" or can it be a number field. Also, when I set the query in the stringobject, it gives me an Expected Identifier ) error... I am using a query named "qry_mergefile Query" and the string field is: "0 Item_Number". Any help is appreciated if this is workable, or if other coding is available.

Function setRecordID(strObject As String, strField As String)
    Dim myWS As DAO.Workspace
    Dim mydb As DAO.Database
    Dim myRS As DAO.Recordset
    On Error GoTo err_setRecordID
 
    Set myWS = DBEngine(0)
    Set mydb = CurrentDb
    Set myRS = mydb.OpenRecordset(strObject, dbOpenDynaset)
 
    myWS.BeginTrans
        ' Do bulk changes to Recordset.
        With myRS
            .MoveFirst
            Do While Not .EOF
                .Edit
                .Fields(strField) = .AbsolutePosition + 1
                .Update
                .MoveNext
            Loop
        End With
        ' If all updates done successfully, commit the transaction.
    myWS.CommitTrans
    Exit Function
 
err_setRecordID:
    Select Case Err.Number
        Case 3061
            'Error in the function arguments
            MsgBox "Error in your Object name or your Field name"
        Case Else
            'If any error occur while making the updates
            'all of the changes will be rolled back (not saved).
            MsgBox "Start the function again, updating problems"
            myWS.Rollback
    End Select
 
    Exit Function
 
End Function

[ADezii]

I am attempting a set record ID to make a number field dynamic. 1st - doe the field have to be an "autonumber" or can it be a number field. Also, when I set the query in the stringobject, it gives me an Expected Identifier ) error... I am using a query named "qry_mergefile Query" and the string field is: "0 Item_Number". Any help is appreciated if this is workable, or if other coding is available.

Function setRecordID(strObject As String, strField As String)
Dim myWS As DAO.Workspace
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
On Error GoTo err_setRecordID

Set myWS = DBEngine(0)
Set mydb = CurrentDb
Set myRS = mydb.OpenRecordset(strObject, dbOpenDynaset)

myWS.BeginTrans
' Do bulk changes to Recordset.
With myRS
.MoveFirst
Do While Not .EOF
.Edit
.Fields(strField) = .AbsolutePosition + 1
.Update
.MoveNext
Loop
End With
' If all updates done successfully, commit the transaction.
myWS.CommitTrans
Exit Function

err_setRecordID:
Select Case Err.Number
Case 3061
'Error in the function arguments
MsgBox "Error in your Object name or your Field name"
Case Else
'If any error occur while making the updates
'all of the changes will be rolled back (not saved).
MsgBox "Start the function again, updating problems"
myWS.Rollback
End Select

Exit Function

End Function

I've taken the liberty and made a few modifications to your code, it works fine. Make sure you are passing a valid Table/Query and Field Name to this Function. Also, make sure the Field that you are passing is not an AutoNumber Field (no can do).

Function setRecordID(strObject As String, strField As String)
Dim myWS As DAO.Workspace
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
On Error GoTo err_setRecordID
 
Set myWS = DBEngine(0)
Set mydb = CurrentDb
Set myRS = mydb.OpenRecordset(strObject, dbOpenDynaset)
 
myWS.BeginTrans
' Do bulk changes to Recordset.
 
With myRS
  .MoveFirst
  Do While Not .EOF
    .Edit
      .Fields(strField) = .AbsolutePosition + 1
    .Update
  .MoveNext
  Loop
End With
' If all updates done successfully, commit the transaction.
myWS.CommitTrans
 
myRS.Close
myWS.Close
Set myRS = Nothing
Set myWS = Nothing
 
Exit_setRecordID:
  Exit Function     'should be a 'single' exit point
 
err_setRecordID:
  Select Case Err.Number
    Case 3061
      'Error in the function arguments
      MsgBox "Error in your Object name or your Field name"
    Case Else
      'If any error occur while making the updates
      'all of the changes will be rolled back (not saved).
      MsgBox "Start the function again, updating problems"
        myWS.Rollback
  End Select
    myRS.Close
    myWS.Close
    Set myRS = Nothing
    Set myWS = Nothing
      Resume Exit_setRecordID
End Function

[Snichols]

Thank you, I will work on it and let you know if it works!! Snichols

I've taken the liberty and made a few modifications to your code, it works fine. Make sure you are passing a valid Table/Query and Field Name to this Function. Also, make sure the Field that you are passing is not an AutoNumber Field (no can do).

Function setRecordID(strObject As String, strField As String)
Dim myWS As DAO.Workspace
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
On Error GoTo err_setRecordID
 
Set myWS = DBEngine(0)
Set mydb = CurrentDb
Set myRS = mydb.OpenRecordset(strObject, dbOpenDynaset)
 
myWS.BeginTrans
' Do bulk changes to Recordset.
 
With myRS
  .MoveFirst
  Do While Not .EOF
    .Edit
      .Fields(strField) = .AbsolutePosition + 1
    .Update
  .MoveNext
  Loop
End With
' If all updates done successfully, commit the transaction.
myWS.CommitTrans
 
myRS.Close
myWS.Close
Set myRS = Nothing
Set myWS = Nothing
 
Exit_setRecordID:
  Exit Function     'should be a 'single' exit point
 
err_setRecordID:
  Select Case Err.Number
    Case 3061
      'Error in the function arguments
      MsgBox "Error in your Object name or your Field name"
    Case Else
      'If any error occur while making the updates
      'all of the changes will be rolled back (not saved).
      MsgBox "Start the function again, updating problems"
        myWS.Rollback
  End Select
    myRS.Close
    myWS.Close
    Set myRS = Nothing
    Set myWS = Nothing
      Resume Exit_setRecordID
End Function