No value given for one or more required parameters adodb access error

I am trying to assign archival id in my database to the corresponding file number column through an Excel userform. It should find the largest alphanumeric string in the column, and increment it by 1 and assign that in the archival id column. This is the VBA code for that:

Sub Archival()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.AutomationSecurity = msoAutomationSecurityLow
        Dim cnn As New ADODB.Connection 'dim the ADO collection class
        Dim rst As New ADODB.Recordset 'dim the ADO recordset class
        Dim dbPath As String
        Dim qry As String
        Dim qry2 As String
        
        dbPath = "\share.company.net@SSLDavWWWRootsitesFileNumberandLDSShared DocumentsGeneralNewDB.accdb"
        
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:Database"
        
        Set rst = New ADODB.Recordset 'assign memory to the recordset
        Set rs = New ADODB.Recordset
    
        qry = "SELECT * FROM FileNumbers WHERE [File_Number]= '" & ArchivalForm.txtFile.Value & "'"
        
        rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
        
        If ArchivalForm.cmbRetention.Value = "A" Then
            qry2 = "SELECT max(val(mid(Archival_id,3))) FROM FileNumbers WHERE [Archival_id] ALIKE 'A%'"
            rs.Open qry2, cnn, adOpenKeyset, adLockOptimistic
            newfile = "A-" & (rs.Fields(0) + 1)
        End If
            
        If ArchivalForm.cmbRetention.Value = "C" Then
            qry2 = "SELECT max(val(mid(Archival_id,3))) FROM FileNumbers WHERE [Archival_id] ALIKE 'C%'"
            rs.Open qry2, cnn, adOpenKeyset, adLockOptimistic
            newfile = "C-" & (rs.Fields(0) + 1)
        End If
        
        With rst
            .Fields("Archival_id").Value = newfile
            .Fields("Remarks").Value = ArchivalForm.txtRemarks.Value
            .Fields("Retention Category").Value = ArchivalForm.cmbRetention.Value
            .Fields("Archived By").Value = Application.UserName
            .Fields("Archived On").Value = Date
            .Update
        End With
        'cnn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
        'Set rs = cnn.Execute("SELECT @@Identity", , adCmdText)
    rst.Close
    rs.Close
    cnn.Close
    
    Set rs = Nothing
    Set rst = Nothing
    Set cnn = Nothing
    MsgBox ("The Archival id is " & newfile)
End Sub

I am getting error “No value given for one or more required” on the line:

rs.Open qry2, cnn, adOpenKeyset, adLockOptimistic

This is what my database looks like: [![enter image description here][1]][1]

Please help [1]: https://i.stack.imgur.com/8baZa.png

Answer

You have an underscore in place of space:

qry2 = "SELECT max(val(mid([Archival id],3))) FROM FileNumbers WHERE [Archival id] ALIKE 'A%'"

Advertisement

Answer