SQL in VBA

[Kelv]

Hi, I've got a combo box which populates from a table, when I select an
entry from the combo a list box should then filter to my selection. I have
this action attached to the 'On click' event of the combo with the SQL as
follows.

Private Sub cboByManager_Click()
Dim strSQL As String

strSQL = ""
strSQL = "SELECT tblAccreditations.EmployeeNumber, tblAdvisers.Forename ' &
' ' & tblAdvisers.Surname AS Adviser," _
& " tblAccreditations.Date, tblAccreditations.Time,
tblSkill.Skill, tblResultDescriptions.Descritption" _
& " FROM tblSkill INNER JOIN (tblResultDescriptions INNER JOIN
(tblManagers INNER JOIN" _
& " (tblAdvisers INNER JOIN tblAccreditations ON
tblAdvisers.EmployeeNumber=tblAccreditations.EmployeeNumber)" _
& " ON tblManagers.ManagerID=tblAdvisers.ManagerID) ON " _
& "tblResultDescriptions.ResultID=tblAccreditations.Result) ON
tblSkill.SkillID=tblAccreditations.Skill" _
& " WHERE tblManagers.ManagerID='" & cboByManager.Value & "';"


lstAccreds.RowSource = strSQL
lstAccreds.ColumnCount = 7
lstAccreds.ColumnWidths = "0 cm;5.203 cm;1.524 cm;1 cm;2.702 cm;2.6 cm"
lstAccreds.Requery

End Sub


The problem is no records are returned, the list box formats for 7 columns
and reflects the widths etc but no data. I've got a similar action
happening elsewhere which works fine but I can't seem to locate the problem.
Any ideas anyone?

I'm running Access 2003 but the DB is in 2000 format.

 

[Douglas J. Steele]

Are you getting the appropriate value from cboByManager.Value?

Take a look at strSQL. Try running it in the query window. Does it give you
what you expect?

 

[Kelv]

Figured it but it's a little freeky!

Like I said have a very similar query filtering the list box by adviser
name. The WHERE clause on that statement required single quotes ' ' around
the 'cboByAdviser.value' part, so I did the same with the one below and it
didn't work! I removed the single quotes for this one and it worked?!

& " WHERE tblManagers.ManagerID=" & cboByManager.Value & ";" instead of

& " WHERE tblManagers.ManagerID='" & cboByManager.Value & "';"

Any ideas as to why this is?

Kelv

 

[John Vinson]

The problem is no records are returned, the list box formats for 7 columns
and reflects the widths etc but no data. I've got a similar action
happening elsewhere which works fine but I can't seem to locate the problem.
Any ideas anyone?

Try going into Debug mode; step through the code until strSQL is
defined, and type

?strSQL

in the Debug window. Copy and paste the text into a new Query's SQL
window. Does this query return any records? If not, check the tables
which make it up - do they all contain matching records for the joins?

John W. Vinson[MVP]

 

[John Vinson]

Like I said have a very similar query filtering the list box by adviser
name. The WHERE clause on that statement required single quotes ' ' around
the 'cboByAdviser.value' part, so I did the same with the one below and it
didn't work! I removed the single quotes for this one and it worked?!

& " WHERE tblManagers.ManagerID=" & cboByManager.Value & ";" instead of

& " WHERE tblManagers.ManagerID='" & cboByManager.Value & "';"

Any ideas as to why this is?

Text fields *require* delimiters, either ' or ".

Numeric fields must NOT have any delimiters.


It would seem that ManagerID is a numeric field, and Adviser is a text
field.

John W. Vinson[MVP]