By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Dropdown Report Menu on a form

P: 68
Hi,
I have a database with school information. On the reporting form, I have a dropdown combo that contains list of teachers by schools. The problem I am having is, when I select the dropdown, instead of listing individual school, it list a school five times, for an example, meaning there are five teachers at that school.
Is is possible to have my dropdown list just the school even if it has more than one teacher?

This is what I have so far: ADezii helped me with this some time ago.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command84_Click()
  2.  
  3. If Me![cboSchoolCode] = "--select--" Then Exit Sub
  4.  
  5. DoCmd.OpenReport "rptInServiceIndividualSchoolAndTeachersInformation", acViewReport, , "lngSchoolCode = '" & Me.cboSchoolCode & "'"
  6.  
  7. cboSchoolCode = "--select--"
  8.  
  9. End Sub
Sep 20 '12 #1

✓ answered by Seth Schrock

Did you try the SELECT DISTINCT in there? What were the results?

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,932
If I'm understanding you correctly, only the school is listed, but each school is being listed once for each teacher at the school. This means that the row source needs changed. Usually, you would have a table of schools separate from the table of teachers, in which case you would need the row source to query the schools table. If for some reason you don't have the separate schools table, then you would query your table for DISTINCT records. For example:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [School]
  2. FROM [table]
  3. WHERE [if needed]
If you need further help on this, post the code that is in your row source. If it is the name of a query, then open that query in SQL view and post that code. Make sure to use code tags (the button that says <code/>)
Sep 20 '12 #2

P: 68
Thanks for the add.
This is what I have:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    tblTeachersProfile.lngSchoolCode,
  3.    tblTeachersProfile.strSchoolName 
  4. FROM tblTeachersProfile 
  5. ORDER BY tblTeachersProfile.strSchoolName;
Sep 20 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,932
Did you try the SELECT DISTINCT in there? What were the results?
Sep 20 '12 #4

P: 68
Thanks Seth,
It works just as I wanted.
Sep 20 '12 #5

Seth Schrock
Expert 2.5K+
P: 2,932
Not a problem. Please click on the Choose as Best Answer.
Sep 20 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.