Executing SQL upon selection of combobox value

[Artemus06]

From an access form, I am trying to select records from a table based on the value selected in the combobox. I am not having luck.

Current code...
Private Sub cmbSelectDirector_Change()
Dim mySQLstmt As String

mySQLstmt = "SELECT * FROM qryDirectorReviews1" & _
"WHERE DirectorDisplayName = Nz(Me.cmbSelectDirector)" & _
"ORDER BY ReviewID DESC"



qryDirectorReviews1.OpenRecordset mySQLstmt

End Sub

Looking for some assistance on how to select the set of records to be displayed on the form.

Thank YOU.
Art

[Micron]

So what is the problem or error message? Is cmbSelectDirector a number?
This doesn't look like the right syntax to open a recordset (qryDirectorReviews1.OpenRecordset mySQLstmt) - CurrentDb.OpenRecordset mySQLstmt

You don't want to open a recordset anyway - you need to set the form's record source to your sql statement or a query that references the combo control, but not on the Change event. This event fires for each character you add or delete from the control. Move the code to the AfterUpdate event and set the form record source as mentioned. You may have to follow that assignment with Me.Requery.

[Artemus06]

Thanks. I removed the event, but now getting a different error: "The expression On Load entered as the event property setting produced the following error: Member already exists in an object module from which the object model derives." This all started when I added the combobox.

From your note, seems the data source for the form and combo should be the same. What I trying to do is have the user select a value (a director name) and then use that name to extract the records from another table.

The combox is looking at tblDirectors (unique listing of directors) and the form is looking at a query that is a join of the tblDirectors and tblDirectorReviews - key being director id (a number).

[Artemus06]

Just a quick update. I found the issue creating the error message, was initializing a value when I shouldn't have. Still working on loading form.

[rpeare]

look at your string creator

"SELECT * FROM qryDirectorReviews1" & "WHERE DirectorDisplayName = Nz(Me.cmbSelectDirector)" & "ORDER BY ReviewID DESC"

this would yield the string

SELECT * FROM qryDirectorReviews1WHERE DirectorDisplayname = Nz(me.cmbselectdirector)ORDER BY ReviewID DESC

note the missing spaces between the WHERE and ORDER by clause, you are also not calling the criteria correctly I don't believe. You likely want

"SELECT * FROM qryDirectorReviews1 " & _
              "WHERE DirectorDisplayName = '" & Nz(Me.cmbSelectDirector) & "' " & _
              "ORDER BY ReviewID DESC"

Note the extra spaces and the correct calling of a form value in the query statement.

[Micron]

From your note, seems the data source for the form and combo should be the same.
No, I had no idea where your combo row source was coming from before your reply, and to be used as a search form control, it needs to be unbound anyway.
Data source is a term that doesn't really apply to define a row source or record source. Not to be rude, but if you use your own terms to describe things, it requires us to make assumptions. So I'm assuming your combo is unbound (it has no control source) but it has a row source as you described. So as I mentioned, something like

Private Sub cmbSelectDirector_AfterUpdate()
  Dim mySQLstmt As String
  
  mySQLstmt = "SELECT * FROM qryDirectorReviews1" & _
              "WHERE DirectorDisplayName = Nz(Me.cmbSelectDirector)" & _
              "ORDER BY ReviewID DESC"
Me.Recordsource = mySQLstmt
Me.Requery

End Sub

Since you don't specify a default value for Nz, it could return either a zero or zero length string, which could be problematic.

[Micron]

Thanks, rpeare. I didn't scrutinize the sql construct, just copied it.
This reminds me of one reason why I don't use line continuation characters.

[pbaldy]

This reminds me of one reason why I don't use line continuation characters.

Really? I don't use them with simple SQL statements, but I like to keep it all visible without scrolling the code window to the right. I can't imagine trying to read complicated SQL all on one line. Personal preference I suppose.

[Artemus06]

Thanks!

Just to clarify, the data source for the combobox is the same as the form source (qryDirectorReviews1). The value being returned by the combobox is the name of a director (e.g., "Art Drake"). I made your recommended changes and received an error and the debug is highlighting the 'Me.Recordsource = mySQLstmt'. I noticed that the revised sql does not end with a ';', so I tried that and getting the same error.

I apologize for the 'rookey' issues, but I certainly appreciate the support.

[Micron]

Glad you asked (sort of) because I don't do this either I can't imagine trying to read complicated SQL all on one line
sql = "SELECT * FROM qryDirectorReviews1 WHERE "
sql = sql & "DirectorDisplayName = Nz(Me.cmbSelectDirector) "
sql = sql & "ORDER BY ReviewID DESC"

is what I do though I might not have broken this into 3 lines, and the necessary spaces are ALWAYS at the end. I seem to have too much trouble with lc characters. I have even taken code right off of MS pages and tried to use it and it wouldn't work - not even if I dumped into and copied from Notepad. As soon as I removed the lc characters and used my own formatting, no problem with the same code. The way I do it is the way I learned it, so naturally I stick with what works for me.

[pbaldy]

Here's a commonly used method to debug the SQL:

BaldyWeb-Immediate window

@Micron, gotcha. Theoretically the extra concatenation would be less efficient, but it wouldn't be noticeable.

[Micron]

@Artemus06: as the first line in my signature points out 'doesn't work doesn't help'. Nor does 'I still receive an error...'
What? "Data type mismatch..."? Syntax error? A 'can't find' error? These messages point us in the right direction.
BTW, Paul's suggestion is a good one that I often employ when having these issues. Respectfully, what I think that page should do is elaborate on pasting the output into a new query in sql view and choosing datasheet view. If it's an action query, it won't do anything if it runs, which is good. If there's any issue, the view remains sql view and the offending portion is often highlighted, allowing you to zero in. I would add that sometimes, the highlighted portion can be just left or right of the missing or incorrect part, but it's a good way to find mistakes.

[rpeare]

You will have to re-post your SQL statement builder because it's not clear who's version you're using.

If you are using Micron's in last post (post 10 in this thread) I would suggest you are not encapsulating the manager name correctly as per my post (post 5 in this thread)

Micron's suggestion from post 10 of this thread:

sql = "SELECT * FROM qryDirectorReviews1 WHERE "
sql = sql & "DirectorDisplayName = Nz(Me.cmbSelectDirector) "
sql = sql & "ORDER BY ReviewID DESC"

should be

sql = "SELECT * FROM qryDirectorReviews1 WHERE "
sql = sql & "DirectorDisplayName = '" & Nz(Me.cmbSelectDirector) & "' "
sql = sql & "ORDER BY ReviewID DESC"

Lastly, if a director is not chosen this will be looking for records where the cmbselectdirector is an empty string which is NOT the same as a null value and will likely give you unreliable results.

[Micron]

Thanks rpeare. I am getting sloppy - involved in too many threads at one time perhaps (between here and Mr Excel).