Retrieving Results from Query VBA

[drowe005]

I've got a problem that I can't seem to find out, and hopefully someone here can chime in and help.

I've got a query that searches to see if there is a previous record matching an employee number that is entered. If a record is returned from the query, I need the Time_OUT form to be opened. If no record is returned, I need the Time_IN form to be opened.

Is it possible to set the result from a field in the query as a variable to later be used in an if statement? That is my only thoughts to making this work. When it runs the query, if the Time_OUT is NOT null (or if no new records are found), this is when I need the if statement to execute.



Here is my current code that I am using, and I don't seem to have it correct as it reverts to the ELSE statement everytime and always fails the IF statement. I don't know what to call the field in the query for it to look at. Currently in the code listed below, it says Me.MO_ID in that location.

Option Compare Database

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click


    
    DoCmd.OpenQuery "Open MO Evaluation Query", acViewNormal, acEdit
      
    If Me.MO_ID = Null Then
    DoCmd.OpenForm "Time_IN_Form", acNormal, "", "", , acNormal
    
    Else
    
    MsgBox "You must log out out of your current MO first."
    DoCmd.OpenForm "Time_OUT_Form", acNormal, "", "", , acNormal
    DoCmd.GoToControl "Time_OUT"
    
    End If
   
Exit_Command3_Click:
    Exit Sub


Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
    
End Sub

 

[james_lankford]

basically something like this

Option Compare Database
Option Explicit
'**************************************************
Sub doit()
    
    Dim rs As DAO.Recordset
    
    Dim s As String
    s = "blah blah blah"
    
    Dim number_of_records As Long
    
    Dim sql As String
    sql = "select count(*) as number_of_records from Table1 where Field1 = '" & s & "' "
    
    Set rs = CurrentDb().OpenRecordset(sql)
    
    number_of_records = rs("number_of_records")
    
    If number_of_records = 0 Then
        MsgBox "nothing here"
    Else
        MsgBox number_of_records
    End If
    
    rs.Close
    Set rs = Nothing
    
End Sub
'**************************************************

 

[drowe005]

Some reason I coudnt get that to work. It gives an error for object needed. What is the syntax to be used in order to pull results from a query and store them as a variable?

 

[james_lankford]

Some reason I coudnt get that to work. It gives an error for object needed. What is the syntax to be used in order to pull results from a query and store them as a variable?

a recordset object is what is used to store the results of a query

if your first post you said
"I've got a query that searches to see if there is a previous record matching an employee number"

so all you should have to do is rewrite the sql I posted so that it does a 'select count(*) from your table for that employee id'

then it stores the result in the recordset object

and you see the result by using the field name
rs("number_of_records")

if your query was returning more than one row you would do

'**************************************************
Sub doit2()
    
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    
    Dim employee_id As string
    employee_id = "23-plp-34"
    
    Dim number_of_records As Long
    
    Dim sql As String
    sql = "select count(*) as number_of_records from you_table where employee_id = '" & employee_id & "' "
    
    Set rs = CurrentDb().OpenRecordset(sql)
    
    Do While Not rs.EOF
        For Each fld In rs.Fields
            Debug.Print fld.Name & " = " & fld.Value
        Next
        number_of_records = rs("number_of_records")
        rs.MoveNext
    Loop
    
    If number_of_records = 0 Then
        MsgBox "nothing here"
    Else
        MsgBox number_of_records
    End If
    
    rs.Close
    Set rs = Nothing
    
End Sub
'**************************************************

an this line
sql = "select count(*) as number_of_records from you_table where employee_id = '" & employee_id & "' "
is
employee_id = SINGLE_QUOTE DOUBLE_QUOTE & employee_id & DOUBLE_QUOTE SINGLE_QUOTE SPACE DOUBLE_QUOTE