Dlookup Query with two variables

Posted on 2008-02-12
Medium Priority
Last Modified: 2013-11-28
I'm trying to do a Dlookup with two variables so that the record brought back from the
query qryAssignation will only be the records 1 to 8 in courseID and GroupID equal to value stored in the cbgroup Combo box but I do not seem to be able to get it to work.
Private Sub cbGroup_AfterUpdate()
Dim i As Integer
'i get the error here
For i = 1 To 8
  Me("lCourse" & i).Caption = Nz(DLookup("Course", "qryAssignation", (("CourseID=" & i) And "GroupID" = Me.cbGroup)))
For i = 1 To 8
  Me("lTeacher" & i).Caption = Nz(DLookup("Teacher", "tblteacher", "TeacherID=" & i))
End Sub

Open in new window

Question by:TOPIO
  • 2
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft Access MVP) earned 2000 total points
ID: 20880676
Try this for the first part:

For i = 1 To 8
  Me("lCourse" & i).Caption = Nz(DLookup("Course", "qryAssignation", "CourseID=" & i & " And [GroupID]  = " & Me.cbGroup))

LVL 75
ID: 20880685
That assumes both CourseID and GroupID are numeric ... are they?

LVL 10

Author Closing Comment

ID: 31430368

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question