vba questions

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » VBA questions

VBA questions

resolvedResolved · High Priority · Version 2007

Marc has attended:
Access VBA course

VBA questions


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)

Call ClearlstAssignments

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"

End If

Exit Sub

Any help where I am going wrong. I assume its in the strSQL string somewhere as it clearly not linking up the records?

Marc Lupson

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.



Access tip:

How To Display An '&' In A Label In A Form

To have your label display a sentence with an &(ampersand) in it, like Fox & Hound simply type it as:

Fox && Hound

View all Access hints and tips

Server loaded in 0.07 secs.