[SOLVED] Data Type Mismatch on SQL statement

Data Type Mismatch on SQL statement

I am trying to pull in column data from a table into a timer in VBA. In my table I have IntervalSeconds as a number. I'm trying to query the number of seconds to determine how long to set my counter for.

Dim timeRemaining As Long (Form Variable) - used in multiple functionss

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
    
    Me.Visible = False
    
    strSQL = "SELECT AccessControl.IntervalSeconds FROM AccessControl WHERE AccessControl.DatabaseName = '" & CurrentDb.Name & "'"
    
    timeRemaining = CLng(strSQL)
    DoCmd.OpenForm ("frmForceLogout")

End Sub

Every time I run the form I get a Type Mismatch error when I hit the timeRemaining = cLng(strSQL) line. Am I missing something?

Solution

You can use DLookup for such simple tasks:

Private Sub Form_Open(Cancel As Integer)

    Dim Criteria As String
    
    Me.Visible = False

    Criteria = "DatabaseName = '" & CurrentDb.Name & "'"
    timeRemaining = DLookup("IntervalSeconds", "AccessControl", Criteria)
    
    DoCmd.OpenForm ("frmForceLogout")

End Sub