Help with Query Criteria

[RunTime91]

Hello...

I am trying to run an update query based on a selected record in a ListBox. However I am having difficulty getting Access to like what I am trying to put into the Criteria field in the design mode

This is what I am trying to get to work.

[Forms]![frmTempTimeTest]![LstTempTimeView].Column(0)

What I get back is the error: Undefined function'[Forms]![frmTempTimeTest]![LstTempTimeView].Column' in expression

I have reviewed other snipets of code and it looks similar to this but obviously I am doing something wrong..

[June7]

Can't reference Column(x) in query. It's not needed in this case anyway since you just want the value of the control. Just remove it.

[RunTime91]

Okay, but without the column reference will the query know I want to perform the update on the selected record in the ListBox?

Thanks June...

[RunTime91]

Below is the SQL statement without the column references... I do not get errors but I also do not get results.

As I'm sure you will see I am trying to update the HoursWorked field based on the employee's ID and the date of the entry

I'm not getting any errors so I 'think' I'm close...but I'm still the newbie...

StrSQL = "UPDATE tblDailyHoursWorked SET tblDailyHoursWorked.HoursWorked = [Forms]![frmTempTimeTest]![Text6] " & _
          "WHERE (((tblDailyHoursWorked.DB_ID)=[Forms]![frmTempTimeTest]![LstTempTimeView]) AND ((tblDailyHoursWorked.Entered)=[Forms]![frmTempTimeTest]![LstTempTimeView]));"

[June7]

Concatenate the variable references. Put a space in front of WHERE so string does not run together.

StrSQL = "UPDATE tblDailyHoursWorked SET tblDailyHoursWorked.HoursWorked = " & [Forms]![frmTempTimeTest]![Text6] & _
" WHERE tblDailyHoursWorked.DB_ID=" & [Forms]![frmTempTimeTest]![LstTempTimeView] & " AND tblDailyHoursWorked.Entered=" & [Forms]![frmTempTimeTest]![LstTempTimeView]

If code is behind frmTempTimeTest can use Me instead.

StrSQL = "UPDATE tblDailyHoursWorked SET tblDailyHoursWorked.HoursWorked = " & Me.Text6 & _
" WHERE tblDailyHoursWorked.DB_ID=" & Me.LstTempTimeView & " AND tblDailyHoursWorked.Entered=" & Me.LstTempTimeView

However, it makes no sense for two fields to reference the same listbox value as criteria. Why would you need to use Entered as criteria when you have the DB_ID criteria?

[RunTime91]

Hey June ~

Always appreciate your help...

The reason for needing two references (ID & Entered (which is short for date entered)) is because the query results will almost always populate the ListBox with more than one record for the same DB_ID (which is an employee's database ID as opposed to a unique record ID).
Thus the need for a second identifier to be sure the correct record is being edited. This is the reason I was trying to incorporate the Column() reference in the criteria so the Update would know I want to change the hours for this DB_ID on this Entered (date) from... to...
I hope that makes sense, but if that still doesn't make sense and you can offer a better way to accomplish this... I'm always open to learning...

As for the code, it is still not pulling any results. When I view the InteliSense it is showing the same variable for both criteria which is the DB_ID. In my mind, when I hover over the "...DB_ID=" & [Forms]..." it should show the DB_ID
But when I hover over the "...Entered=" &[Forms]..." it should show the date.

I tried changing the Bound column... no difference.

At the end of the day all I want to do is offer the end-user the ability to select an employee, enter a date range, run a query and populate a ListBox with the results. Then, allow the end-user to simply select a record in that ListBox and change the hours an employee input on their timecard. Seems incredibly straight forward and simple, and I've done this exact same thing in Excel but now I am wondering if this can be done in Access.

If you (or for that matter anyone) have a better way of doing this, as always, I am open to learning.

Thanks June

[June7]

It doesn't pull results because the criteria is looking for the same value in both fields.

I originally thought you were using dynamic parameterized query object, not building SQL string in VBA.

So, if the two values you want are in separate listbox (or combobox) columns, then pull them by referencing the column index. If value is in column 2, its index is 1. Since Entered is a date/time field, use # delimiters on the parameter. As long as DB_ID is a number type, no delimiters.

StrSQL = "UPDATE tblDailyHoursWorked SET HoursWorked = " & Me.Text6 & _
" WHERE DB_ID=" & Me.LstTempTimeView.Column(0) & " AND Entered=#" & Me.LstTempTimeView.Column(1) & "#"

Is HoursWorked an aggregate value? Saving aggregate data is usually not necessary and not advised. Calculate when needed.

[RunTime91]

Works Great, June ~ Thanks...

No, HoursWorked is not an aggregate value, but I understand what you are saying...

Now onto the next step of incorporating this into a stored procedure.