Programatically re-write sql statement to row source (1 Viewer)

[Wegets7]

write sql statement to row source

I'm trying to use an on click event in one list box to change the contents of another list box. The code I'm using re-writes the sql code to the row source property if I enclose the table names in square brackets, but doesn't generate known results. Similar SQL without the square brackets in the row source will generate the known results, won't write from the vba code.

This may be similar to what Alistair69 was trying to do.

Any help will be appreciated
Rich

Here is the code:

Private Sub TitleList_Click()
On Error GoTo Err_TitleList_Click

Dim vSearchString, x As String
vSearchString = Me.TitleList.Column(1)

Dim abc As String
abc = "SELECT [COPY].CopyKey,"
abc = abc & "[COPY].CopyID,"
abc = abc & "[TITLE].Title,"
abc = abc & "[TITLE].Edition"
abc = abc & " FROM ([COPY] INNER JOIN ON [COPY].FK_ISBN=[TITLE].PK_ISBN WHERE ((([COPY].CopyKey) 
Not In (SELECT FK_CopyKey From ISSUES_to_STUDENT Where DateIn Is NULL)))"
abc = abc & "AND (([Title].Title Like " & vSearchString & "));"Me.FK_CopyKey.RowSource = abc
Me.FK_CopyKey.Requery
Me.Refresh

Exit_TitleList_Click:
Exit Sub

Err_TitleList_Click:
MsgBox Err.Description
Resume Exit_TitleList_Click

End Sub

Using Access 2003/2000 winxp

 

[Wegets7]

Since my query doesn't really change...

...is it possible to just pass the value selected by the user to a query at the prompt(newbee)?

Rich

 

[Jon K]

The code I'm using ....... doesn't generate known results.

You have left out the Title table in the Inner Join.

Since you don't use wildcard characters, you can change Like to =, which is more efficient. And as Title is a text field, its value needs to be surrounded by quotes.

And I don't think the Requery and Refresh are necessary.

You can try:-

Private Sub TitleList_Click()
On Error GoTo Err_TitleList_Click

  Dim abc As String

  abc = "SELECT [COPY].CopyKey,"
  abc = abc & "[COPY].CopyID,"
  abc = abc & "[TITLE].Title,"
  abc = abc & "[TITLE].Edition"
  abc = abc & " FROM [COPY] INNER JOIN [b][Title][/b]"
  abc = abc & " ON [COPY].FK_ISBN=[TITLE].PK_ISBN"
  abc = abc & " WHERE [COPY].CopyKey Not In"
  abc = abc & " (SELECT FK_CopyKey From ISSUES_to_STUDENT"
  abc = abc & " Where DateIn Is NULL)"
  abc = abc & " AND [Title].Title = """ & Me.TitleList.Column(1) & """;"
  
  Me.FK_CopyKey.RowSource = abc
'  Me.FK_CopyKey.Requery
'  Me.Refresh

Exit_TitleList_Click:
   Exit Sub

Err_TitleList_Click:
   MsgBox Err.Description
   Resume Exit_TitleList_Click

End Sub

.

...is it possible to just pass the value selected by the user to a query at the prompt(newbee)?

It's possible. The criteria for the Title field in the query is [Forms]![FormName]![TitleList]

Since Title is the second column in the TitleList list box (as you used Me.TitleList.Column(1) in your code), you need to set the list box's bound column to 2 for the query.

Then change the list box's On Click event to:-

Private Sub TitleList_Click()

   Me.FK_CopyKey.RowSource = "QueryName"

End Sub

.

 

[Wegets7]

Thanks...

Thanks Jon
It really came down to getting quotation marks around the """ & variable & """ and getting spaces in the right place.

Lesson Learned: Access will only give syntax error if the SQL statement is in the row source when the form opens. If you write it in later, as I was doing from a VBA script Access is mum.

Thanks again. Rich