Requery Does Not Update Listbox Row Source

[szucker1]

I have a form with a listbox, combobox, and textbox.

When the form opens:
listbox Row Source type = Table/Query
listbox Row Source = ""

On combobox update:
textbox set to a default value
listbox Row Source = SQL statement (query)
the SQL statement has the combox as a criteria and the textbox as a variable in a calculated expression field and the listbox fills correctly



On textbox update:
I have tried using me.listbox.requery or using listbox Row Source = SQL statement (exactly the same as in combobox update). Me.listbox.requery does not update the listbox based on the new textbox value enterd but using listbox Row Source = SQL statement does work. Shouldn't they both work?

[Rod]

My immediate reaction is: Yes. We need to see the SQL. Try debugging to see exactly what the RowSource is at all stages.

[szucker1]

My immediate reaction is: Yes. We need to see the SQL. Try debugging to see exactly what the RowSource is at all stages.

Here is my SQL statement; it is kind of complicated. I tried to keep things simple in my original post.
Also, I haven't used debugging before so I am not sure what to do there.

list_Attendees.RowSource = "SELECT table_Attendence.Qualification AS [Q?], " & _
        "table_Attendence.Last_Name, table_Attendence.First_Name, table_Attendence.Email, " & _
        "JustifyString(""form_Qualification"",""list_Attendees""," & _
        "Format([table_Attendence]![Interest_Rating]/100,""percent""),4,-1) AS [IR%], " & _
        "JustifyString(""form_Qualification"",""list_Attendees""," & _
        "Format(24*60*([table_Attendence]![Leave_Time]-[table_Attendence]![Join_Time])/" & _
        Val(text_Class_Length) & ",""percent""),5,-1) AS [TIS%], " & _
        "JustifyString(""form_Qualification"",""list_Attendees""," & _
        "Format([table_Attendence]![Poll_Answered]/" & _
        DLookup("[Poll_Asked]", "table_Session", "[Webinar_ID] = """ & combo_Webinar.Column(1) & """ " & _
        "And [Start] = #" & combo_Webinar.Column(2) & "#") & ",""percent""),6,-1) AS [PQA%] " & _
        "FROM table_Attendence WHERE (((table_Attendence.Webinar_ID)= """ & combo_Webinar.Column(1) & """) " & _
        "AND ((table_Attendence.Start)= #" & combo_Webinar.Column(2) & "#)) " & _
        "ORDER BY table_Attendence.Last_Name, table_Attendence.First_Name, table_Attendence.Email"

[Rod]

Yes, you're correct: it's complicated.

I anticipated that the list box value would play a role in the WHERE clause, but not so. It seem you are passing the name of the control, not the value, to a user function called JustifyString. Here's what I think a fragment of the SQL would look like:

JustifyString("form_Qualification","list_Attendees ", Format([Interest_Rating]/100,"percent"),4,-1) AS [IR%]

It is unlikely that the function is misbehaving since you say it works for 'listbox Row Source = SQL statement' but not for requery.

There are a number of ways to debug. Perhaps the simplest is to place a checkpoint at the start of the routine and then step through the code by pressing F8. At any time you can interrogate a value by typing '?' and then the name of the variable (or type an expression) in the immediate window. Another way is to place a Debug.Print ... statement in your code. This automates the process and the result is shown in the immediate window. The watch window is very useful if you want to watch (what else?) a value as you step through the code.

I would suggest that for the first debugging pass you simply step through the code and make sure the program counter (or whatever) is taking the path you expect. Then you may get more exploratory. Your main interest will be the state of list_Attendees and what JustifyString is doing.

[szucker1]

Rod,

First of all I would like to thank you for taking the time to help me understand Access better.

Now on to the issue at hand. I created a very simple table and form for testing and was able to recreate the issue from my original post and find a way to resolve it.

Table1 has two fields [text] (formatted as text) and [number] (formatted as number) with two records:
aaa, 111
bbb, 222

Form1 has a textbox (Text6) and a listbox (List4).

For my first trial Text6 is formated as General Number and the form has the following code:

Private Sub Form_Open(Cancel As Integer)
    List4.RowSource = "SELECT Table1.text, Table1.number FROM Table1 WHERE Table1.number=" & Text6End Sub
Private Sub Text6_AfterUpdate()
    Me.List4.Requery
End Sub

Unwanted Results. Default 111 in Text6 gets aaa, 111 in List4 on form open. Enter 222 in Text6 still get aaa, 111 in List4.

For my second trial Text6 is formated as General Number and the form has the following code:

Private Sub Form_Open(Cancel As Integer)
    List4.RowSource = "SELECT Table1.text, Table1.number FROM Table1 WHERE Table1.number=" & Text6
End Sub
Private Sub Text6_AfterUpdate()
    List4.RowSource = "SELECT Table1.text, Table1.number FROM Table1 WHERE Table1.number=" & Text6
End Sub

Desired Results. Default 111 in Text6 gets aaa, 111 in List4 on form open. Enter 222 in Text6 get bbb, 222 in List4.

The above recreated the same issue, in a very simple way, as mentioned in my original post. The way I resolved it was to change the Row Source SQL statement to the following:

List4.RowSource = "SELECT Table1.text, Table1.number FROM Table1 WHERE Table1.number=Text6"

With this change (removing '" & ' and putting '"' at the end I was able to get the desired results using either the Me.List4.Requery or the List4.RowSource=SQL statement in Sub Text6_AfterUpdate(). I think the first SQL statement doesn't work with the Requery because what you are actually putting into the Row Source Property when you have '...WHERE Table1.number=" & Text6' is the actual value (i.e. 111) as opposed to the variable (i.e. Text6) while the second puts the actual variable into the Row Source Property. Does that make sense? Seems like only a subtle difference in writing the SQL statement can make a big difference. I wonder how many folks are aware of this specific coding issue?

All this leads me to a variation on this issue that I do NOT know how to resolve. All the above used Text6 as a Number, but if it is Text then first SQL statement becomes:

List4.RowSource = "SELECT Table1.text, Table1.number FROM Table1 WHERE Table1.text=""" & Text6 & """"

The results with this SQL statement are just like the first SQL statement above (when Text6 was a Number); it doesn't work with Requery but does work with the SQL statement in Sub Text6_AfterUpdate(). I could not figure out how to rewrite the SQL statement to work with Requery. Any ideas?

Thank You
Scott

Yes, you're correct: it's complicated.

I anticipated that the list box value would play a role in the WHERE clause, but not so. It seem you are passing the name of the control, not the value, to a user function called JustifyString. Here's what I think a fragment of the SQL would look like:

JustifyString("form_Qualification","list_Attendees ", Format([Interest_Rating]/100,"percent"),4,-1) AS [IR%]

It is unlikely that the function is misbehaving since you say it works for 'listbox Row Source = SQL statement' but not for requery.

There are a number of ways to debug. Perhaps the simplest is to place a checkpoint at the start of the routine and then step through the code by pressing F8. At any time you can interrogate a value by typing '?' and then the name of the variable (or type an expression) in the immediate window. Another way is to place a Debug.Print ... statement in your code. This automates the process and the result is shown in the immediate window. The watch window is very useful if you want to watch (what else?) a value as you step through the code.

I would suggest that for the first debugging pass you simply step through the code and make sure the program counter (or whatever) is taking the path you expect. Then you may get more exploratory. Your main interest will be the state of list_Attendees and what JustifyString is doing.

[Rod]

I think the first SQL statement doesn't work with the Requery because what you are actually putting into the Row Source Property when you have '...WHERE Table1.number=" & Text6' is the actual value (i.e. 111) as opposed to the variable (i.e. Text6) while the second puts the actual variable into the Row Source Property.

Well done and well investigated. I was able to replicate your result and moreover demonstrate that with the " ... Table1.number=" & Text6 syntax the row source remained hard and fast as ... Table1.number = 111. I believe your conclusions are correct. The " ... Table1.number = Text6" syntax obviously forces the SQL parser to re-examine the value of the control on the form.

All this leads me to a variation on this issue that I do NOT know how to resolve. All the above used Text6 as a Number, but if it is Text ...

I had success leaving the SQL syntax exactly the same!

We all live and learn.

[szucker1]

Rod,

You are brilliant. For the textbox as text (instead of number) I never thought of using the same SQL statement. I always thought when you used text variables they had to be surrounded by quotes. I am glad you solved this for me, but I guess I wish it made more sense to me.

Scott

[Shrout1]

Scott,

A quick explanation is as follows:

When something is within quotation marks it is looked at as LITERALLY being that value. An example...

Textbox1.text = 4

You create a message box:

Msgbox "The value of text box 1 is Textboxt1.text"

When that message is displayed it will appear EXACTLY as I just typed it. You will not find the number 4 in that message.

Instead if I were to format the message in the mesagebox as follows:

Msgbox "The value of text box 1 is " & Textbox1.text

Then the message box would read "The value of text box 1 is 4".

Everything in quotes is considered to be a "string" and is taken at face value. The computer will not substitute your textbox value because you specifically told it not to. Strings can be very useful if you need to inject an SQL statement into a rowsource, etc. You don't want the computer to look at all those fancy words and think that it should do something with them. Instead, they are wrapped like a package in between those quotation marks and delivered directly to their source.

By ending the quotation marks and putting an ampersand symbol (&) you are basically taking a glue stick and telling the computer, "Go ahead and append that value! Substitute in that thing that I am referencing!"

So a final example:

FirstName.Text = "John"
LastName.Text = "Smith"

Msgbox "Your name is " & FirstName.Text & " " & LastName.Text

This will display "Your name is John Smith"

If I typed it as follows:

Msgbox "Your name is FirstName.Text  LastName.Text"

Then it will display as "Your name is FirstName.Text LastName.Text" because that is what you told the computer to do!

It's all about substitution! I hope this helps clarify the specifics of this problem!