Retrieving Results from Query using VBA

[Mearntain]

I've got an access database that I am working on, but I have now ran into a problem where I can not figure out the correct VBA syntax to use. As a sidenote - I am using Access 2003. What I would like to do is find out the syntax to use to retrieve data from a query result.



I have a query, that when ran, searches a table that contains 4 columns. The query prompts the user to enter a number which would be found in column 1. It then searches for every match of that number that was entered, and only returns a result if there is an occurrence where column 4 is empty on the same row. The max number of occurrences where column 4 can be empty is 1. So to summarize, when I run the query, it either returns a blank record (with a 0 in the first 2 columns and last 2 columns blank) or it will return a record that matches the criteria.

What I would like to do is if no records are found, I need to go to the Time_IN form. If 1 result is found, I need to go to the Time_OUT form. Hopefully this is an easy solution and I have most of the code written, but I am unsure of the syntax to use and my IF statement fails every time, reverting to the else statement. Here is a copy of the current code I am using (The Me.MO_ID = Null was my attempt at retrieving the results from the query):

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

[ranman256]

docmd.openquery "qeryName"

[nick404]

Instead of = Null see if
If IsNull(Me.MO_ID) Then

[June7]

Cannot test for = Null. Review item 5 in http://allenbrowne.com/casu-12.html

[Mearntain]

Cannot test for = Null. Review item 5 in http://allenbrowne.com/casu-12.html

This really does not help me get towards a solution. If I knew the correct thing to type in my VBA editor, I wouldn't be asking.

[Mearntain]

docmd.openquery "qeryName"

I wasn't able to get this to work. Would Dlookup do what I am trying to do?

[ItsMe]

Post the SQL for the TimeIn form and the SQL for the TimeOut from.

[Mearntain]

The only thing I am able to view SQL on is my queries. I don't see that option for my forms

[orange]

I wasn't able to get this to work. Would Dlookup do what I am trying to do?

What exactly happened when you tried it? Error????
Can you describe exactly WHAT you are trying to do with DLookup? (see the link for syntax and examples)

What is the logic you are trying to code, and what exactly is the proposed outcome? An example might help.
You haven't shown any revised code, nor descriptions of tests/failures, so we are sort of in the dark and guessing at your requirement, your environment and your knowledge.

[Mearntain]

Here's a link to the file I am working with. I uploaded it to Box.com

https://app.box.com/s/4bhc9e52z6q2l5bc3tdnpr7aakm6wp5h

[orange]

Since our posts have the same timestamp, you probably did not see my previous post.
If you could answer the questions, it might help us understand the issue more clearly.

[Mearntain]

I have a home screen. A user clicks the Job Start button. A prompt comes up asking for the Employee Number to be entered. Once a entered, a query runs to try and find an instance where that employee number exists where the 4th column (Time_OUT column) does not have a time out in it. If a result is returned where the employee does not have a time out, it then forces them to the Time_OUT form. If no record is found, the program then sends the user to the Time_IN form. That is the scope of what I am trying to do.

When I run the query, either 1 record is returned or 0 records are returned. If 1 record is returned, it needs to go to the Time_OUT form. If 0 records are found, go to the Time_IN form.

Hopefully this clarifies.

[ItsMe]

The only thing I am able to view SQL on is my queries. I don't see that option for my forms

What is the Recordsource of your two forms? If it is a query, post the SQL of the query(s).

[Mearntain]

What is the Recordsource of your two forms? If it is a query, post the SQL of the query(s).

The Record Source for my Time_In form, the one in which I am having the issue, is a query named "Open MO Evaluation Query"

SQL is:

SELECT Table1.Emp_ID, Table1.MO_ID, Table1.Time_IN, Table1.Time_OUT
FROM Table1
WHERE (((Table1.Emp_ID)=[Enter the Employee ID]) AND ((Table1.Time_OUT) Is Null));


As I said before, the maximum results that this query will return is 1 result. The purpose of this query is to not allow anyone to be logged in to more than 1 job at a time.

[June7]

Normally, users should not work directly with nor even see tables or queries, only forms and reports. Code as you have structured cannot refer directly to table or query object to pull data from fields so opening query as you have done accomplishes nothing. Must open a recordset based on the table/query or use domain aggregate function (DLookup, DCount, DSum, etc).

I NEVER use dynamic parameterized queries. Have user input number into a textbox on form. Refer to that value as a search parameter. What number is the user inputting?

If DCount("*", "table or query name", "field1name=" & Me.textboxname) = 0 Then
    DoCmd.OpenForm "Time_IN_Form"
    
Else
    
    MsgBox "You must log out of your current MO first."
    DoCmd.OpenForm "Time_OUT_Form"
    DoCmd.GoToControl "Time_OUT"
    
End If

Do you need either form to open to a specific record?