Retrieving Results from Query using VBA

[Mearntain]

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?

The user is inputting their employee number.

If a record is found in the search, it needs to be opened in the Timeout form. If no record is found, a new blank record is opened in the Timein form

[June7]

As you already noted, if the process works properly, there should only be one record where time out is not completed for an employee.

Dim intRecID As Integer

intRecID = Nz(DLookup("ID", "table or query name", "EmployeeID=" & Me.Textbox & " AND TimeOut Is Null"), 0)

If intRecID = 0 Then
        DoCmd.OpenForm "Time_IN_Form"
    
Else
    
    MsgBox "You must log out of your current MO first."
    DoCmd.OpenForm "Time_OUT_Form", , , "ID=" & intRecID
    DoCmd.GoToControl "Time_OUT"
    
End If

Use your table, query, field, control names as appropriate.

[Mearntain]

As you already noted, if the process works properly, there should only be one record where time out is not completed for an employee.

Dim intRecID As Integer

intRecID = Nz(DLookup("ID", "table or query name", "EmployeeID=" & Me.Textbox & " AND TimeOut Is Null"), 0)

If intRecID = 0 Then
        DoCmd.OpenForm "Time_IN_Form"
    
Else
    
    MsgBox "You must log out of your current MO first."
    DoCmd.OpenForm "Time_OUT_Form", , , "ID=" & intRecID
    DoCmd.GoToControl "Time_OUT"
    
End If

Use your table, query, field, control names as appropriate.

It's seeming like your code is working. I have a little more playing around to do and I think I have got it from here. Thanks alot!

[June7]

What data must user input into the forms? If all it needs is current date/time, even that can be handled programmatically and users don't even need to see a form.

[Mearntain]

What data must user input into the forms? If all it needs is current date/time, even that can be handled programmatically and users don't even need to see a form.

The users will enter their employee number.

[June7]

On the Time_In and Time_Out forms? The Time_Out form should open to an existing record and therefore already have the employee number. Can programmatically populate the Time_In form with employee number.

If there is no other data to be entered, recording employee and their time in and time out can be done without even opening those two forms.

[Mearntain]

As you already noted, if the process works properly, there should only be one record where time out is not completed for an employee.

Dim intRecID As Integer

intRecID = Nz(DLookup("ID", "table or query name", "EmployeeID=" & Me.Textbox & " AND TimeOut Is Null"), 0)

If intRecID = 0 Then
        DoCmd.OpenForm "Time_IN_Form"
    
Else
    
    MsgBox "You must log out of your current MO first."
    DoCmd.OpenForm "Time_OUT_Form", , , "ID=" & intRecID
    DoCmd.GoToControl "Time_OUT"
    
End If

Use your table, query, field, control names as appropriate.

Using this code here, everything works right except when it goes to the Time_OUT form, it does not open the record that was found. How do I correct that?

[June7]

I don't know your data structure so can't say. The code looks good to me. Is ID actually field name? Is it a number field?

[Mearntain]

I don't know your data structure so can't say. The code looks good to me. Is ID actually field name? Is it a number field?

Emp_ID is actual field name, and yes it is a number. In my database, the edits from ID to Emp_ID have been made to the code. Instead of showing the record that was found, it fills all of the fields on the Time_OUT_Form with "#Field?"

[Mearntain]

Here is a link to my most up to date database if you would be willing to look at it:

https://app.box.com/s/ie0renu8yw4drowss6rjsccye01qp1ol

[June7]

I cannot download from fileshare site (blocked by my network here). I can download file attached to post.

[Mearntain]

Hopefully I attached that right. I had to compress it for it to work for the max size allowed.

LAYOUTlog.zip

[Mearntain]

Start on the home form, click job start and that takes you to query form where you enter the employee number, and then from there it either goes to TimeIN form or TimeOUT form...just so you have an easy idea of the flow without having to look deep into it.

[Mearntain]

In the file that I attached, my VBA says DoCmd.OpenForm "Time_OUT_Form", , , "EMP_ID=" & intRecID . I corrected that to say DoCmd.OpenForm "Time_OUT_Form", , , "ID=" & intRecID and I now have a record populate in the form; however it populates with the wrong record. It is populating now with the first record in the table with a matching employee number, rather than populating with the only record with matching employee number and a blank field in their timeout column.

[June7]

Time_OUT_form does not have a RecordSource, yet the controls are bound to fields and don't show error in design view. Very odd. Set the RecordSource property.

I would not use textbox LostFocus event, instead use AfterUpdate.

Private Sub Text0_AfterUpdate()
Dim intRecID As Integer
intRecID = Nz(DLookup("ID", "Table1", "Emp_ID=" & Me.Text0 & " AND Time_OUT Is Null"), 0)
If intRecID = 0 Then
    DoCmd.OpenForm "Time_IN_Form"
    DoCmd.GoToRecord , , acNewRec
Else
    MsgBox "You must log out of your current MO first."
    DoCmd.OpenForm "Time_OUT_form", , , "ID=" & intRecID
    DoCmd.GoToControl "Time_OUT"
End If
End Sub