Access query need to bypass "enter parameter value" error with a msg box saying field not found

Question

I have a simple query tied to a command button that shows a summary of the values in a particular field. It's running on a table that changes with each use of the database, so sometimes the table will contain this field and sometimes it won't. When the field (called Language) is not in the file, the user clicks the command button and gets the "Enter Parameter Value" message box. If they hit cancel they then get my message box explaining the field is not present in the file. I would like to bypass the "Enter Parameter Value" and go straight to the message if the field is not found. Here is my code:

Private Sub LangCount_Click()
DoCmd.SetWarnings False

On Error GoTo Err_LangCount_Click

    Dim stDocName As String

    stDocName = "LanguageCount"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Err_LangCount_Click:
    MsgBox "No Language field found in Scrubbed file"
Exit_LangCount_Click:
    Exit Sub

     DoCmd.SetWarnings True
End Sub

Answer 1

You can attempt to open a recordset based on the query before you run the query:

Set rs = CurrentDb.QueryDefs("query1").OpenRecordset

This will go straight to the error coding if anything is wrong with the query.

Alternatively, if it is always the language field and always in the same table, you can:

sSQL = "select language from table1 where 1=2"
CurrentDb.OpenRecordset sSQL

This will also fail and go to your error coding, but if it does not fail, you will have a much smaller recordset, one with zero records.

You can easily enough get a list of fields in a table with ADO Schemas:

Dim cn As Object ''ADODB.Connection
Dim i As Integer, msg As String

    Set cn = CurrentProject.Connection
    Set rs = cn.OpenSchema(adSchemaColumns, Array(Null, Null, "Scrubbed"))

    While Not rs.EOF
        i = i + 1
        msg = msg & rs!COLUMN_NAME & vbCrLf
        rs.MoveNext
    Wend

    msg = "Fields: " & i & vbCrLf & msg

    MsgBox msg

More info: http://support.microsoft.com/kb/186246

Answer 2

You have a command button named LangCount. It's click event has to deal with the possibility that a field named Language is not present in your Scrubbed table.

So then consider why a user should be able to click that command button when the Language field is not present. When the field is not present, you know the OpenQuery won't work (right?) ... so just disable the command button.

See if the following approach points you to something useful.

Private Sub Form_Load()
    Me.LangCount.Enabled = FieldExists("Language", "Scrubbed")
End Sub

That could work if the structure of Scrubbed doesn't change after your form is opened. If the form also includes an option to revise Scrubbed structure, update LangCount.Enabled from that operation.

Here is a quick & dirty (minimally tested, no error handling) FieldExists() function to get you started.

Public Function FieldExists(ByVal pField As String, _
        ByVal pTable As String) As Boolean
    Dim blnReturn As Boolean
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    ' next line will throw error #3265 (Item not found in this collection) '
    ' if table named by pTable does not exist in current database '
    Set tdf = db.TableDefs(pTable)
    'next line is not actually needed '
    blnReturn = False
    For Each fld In tdf.Fields
        If fld.Name = pField Then
            blnReturn = True
            Exit For
        End If
    Next fld
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    FieldExists = blnReturn
End Function