Resolved · High Priority · Version 2007
Marc has attended:
Access VBA course
I recently attended the VBA course where we built a small films database which had an analysis button, which when clicked, would list details of films dependig on which director was chosen in the combo box.
I am doing something similar in trying to list the details of assignments for a particular Adviser.
I have created the combo box to choose a particular Adviser and have attempted to write the code behind the update event procedure to list the assignments for that ADvisers in a separate list box
When I try it I get no records in the lstAssignments field at all, its just blank apart from the column splits
here is my code
Private Sub cmbAdvisers_AfterUpdate()
Dim strAssignments As String
Dim strSQL As String
strAssignments = Me.cmbAdvisers.Value
strSQL = "SELECT SCIID, AdviserName, OrgName, ProjectTitle, FROM qryAdviserAssignments"
strSQL = strSQL & "WHERE AdviserName = " & Chr(34) & strAssignments & Chr(34)
Me.lstAssignments.RowSourceType = "Table/Query"
Me.lstAssignments.ColumnCount = 4
Me.lstAssignments.ColumnHeads = True
Me.lstAssignments.RowSource = strSQL
Me.lstAssignments.ColumnWidths = "4cm; 6cm; 7cm; 7cm"
If Me.lstAssignments.ListCount = 0 Then
MsgBox "There are no records"
Any help where I am going wrong. I assume its in the strSQL string somewhere as it clearly not linking up the records?
For upcoming training course dates see: Pricing & availability
RE: VBA questions
Hi Marc, thanks for your query. Code-wise, that looks good to me so it's a little difficult to advise what else might be going wrong without seeing the whole database. Try creating a query in the query designer to pull out the assignment information you want in the list box for a particular adviser and double check it does actually return some information - compare its underlying SQL with your SQL string. The fact that "There are no records" isn't firing makes me suspect there is something else in the underlying structure of the database affecting this functionality.
I would also try to use a copy of the code to dump some different results in the list box, to eliminate your current code structure from error. If you manage to get other results in that list box you definitely have a problem with your SQL or the tables/relationships.