VBA select not returning any rows

Question

Below query is not returning any rows into the listbox.There is no error message:

lstDiff.RowSource = "select TestScenario,TestId from tblTesting where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'"

Could anyone help?

Answer 1

Try running the query in your SQL Management Studio. Do you get any row(s) back?

Edit: Just noticed the access-tag. Are you sure your table contains at least one post with supplied ids?

Answer 2

My Access is a bit rusty, but if all else fails try using a recordset to capture the data from the SQL and loop through it adding the values to the list box. Example Code

Answer 3

Personally, whenever I use SQL statements in code, I prefer to store the statement in a variable. While testing, on the line after you assign your statement to a variable, you can use Debug.Print to see what your SQL statement looks like after parsing your txtempid and txtautonumber. It would look something like this.

Dim sSQL as String
sSQL = "select TestScenario,TestId from tblTesting where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'"
Debug.Print sSQL
lstDiff.RowSource = sSQL

Then as long as your immediate window is visible (Ctrl-G), you can see what your SQL statement really is. If it looks right in the immediate window, copy and paste it into the query builder and run it there.

Answer 4

Your values in the field are numeric, so the extra single quotes aren't needed. Code should look like the following:

Me.lstDiff.RowSource = "select TestScenario,TestId from tblTesting where empid= " & Me.txtEmpId & " and testid= " & Me.txtAutoNumber & ";"

I've also dropped .Value from the field references, they're not harmful, but also aren't necessary.

And I've added a semi-colon to the end of your statement.

Depending on when/where you insert this code, you may need to add the following statement as well:

Me.lstDiff.Requery

Answer 5

You keep posting questions about the exact same WHERE clause with exactly the same apparent error in each one. SO users dutifully point out your error and then a few days later, you show up with a related question utilizing the same faulty WHERE clause.

DLookup Problem:

  txtContSunStart1.Value = DLookup("Start1", "tblContract", _
     "TestId = " & _
     lstResults.Value & _
     "" And "Day = 'Sunday'")

VBA Update Query:

  DoCmd.RunSQL (" Update tbltesting set IsDiff ='Yes' where empid= " & Me.txtEmpId.Value & " and testid= " & Me.txtAutoNumber.Value & ";")

VBA SQL Problem

  DoCmd.RunSQL ("insert into tbltesting (IsDiff)values ('Yes') where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'")

And then in the current question:

  lstDiff.RowSource = "select TestScenario,TestId from tblTesting where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'"

You are having difficulties with exactly the same set of problems repeatedly.

Here are the rules for concatenating SQL strings with the correct delimiters in Access:

1. numeric values do not need delimiters:

   "... AND testid= " & Me!txtAutoNumber

2. text values need quote delimiters. In Access, it's general practice to use double quotes, but much easier to use single quotes since it's a pain to type double quotes in a form that will work (typing """ or """" depending on context is counterintuitive and silly to me, so I always define a global constant that holds the double quote symbol and concatenate with that).

   "... AND textfield=" & Chr(34) & Me!MyTextField & Chr(34)

3. date values use the # delimiter:

   "... AND datefield=#" & Me!MyDateField & "#"

4. Boolean fields require no quotes and it works best to use True and False:

   "... AND IsDiff=True"

These rules apply both to WHERE clause criteria and to SET statements in UPDATE queries. The rules apply in writing a SQL string that you pass to DoCmd.RunSQL or CurrentDB.Execute, as well as to writing SQL strings to be used as the recordsource of a form or report or as the rowsource of a combo box or listbox.