Results 1 to 14 of 14

Form ComboBox not populating correctly

  1. #1
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72

    Form ComboBox not populating correctly

    Hello, I only started using Access on Sunday so please bear with me. I have (am) making a form that I'm hoping will fill in the query data to automate production of a report. At the moment I can't seem to get my ComboBoxes to populate correctly. My record source statement reads "SELECT DISTINCT Behaviour.[Pupil Name] FROM Behaviour;" but it just leaves an empty ComboBox. Whereas if I run this statement in a standalone SQL query I get exactly what I expect. My form is unbound (I don't know what that means though) if that makes a difference.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,343

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,904
    The fact that when run as an independent Query it works proves that your SQL statement is valid, so the question becomes where do you have it located when trying to use it to populate your Combobox?

    BTW, in reference to a Combobox, it would be RowSource, not 'RecordSource.'

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Thank you both. I watched that video and followed the instructions. After pressing the run button a table appears with exactly the details that I would like....however running the form, the ComboBox is empty! Where I have it located? I'm not sure I understand the question but you are right my SQL statement is in RowSource but works as an independant query but not within a form?!

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,343
    If you look at the property sheet of the combo, and the Format tab, does it shoe a 0" length for the field?
    It sounds that it may be a formatting issue since you have a working query; have it in the row source.....

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,904
    And since we're investigating this kind of thing, go to the Data Tab in Properties and make sure that the Row Source Type Property for the Combobox is set to Table/Query.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I can't see anything that says field in the Format Tab but the Row Source Type is definitely set to Table/Query. A twist is that it will show something if the query reads SELECT Behaviour.ID, Behaviour.[Pupil Name] FROM Behaviour; but shows duplicates etc.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,343
    It would appear that you may have a Normalization issue (table design).
    However to address the duplicates. change the RowSource to

    SELECT DISTINCT Behaviour.ID, Behaviour.[Pupil Name] FROM Behaviour;

  9. #9
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Yeah, I tried the SELECT DISTINCT method which worked in the stand alone query but not in the form!

  10. #10
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Basically, I have two databases. Behaviour Data for the back end and Behaviour Log for the front end where staff will actually enter data.

    When you open Behaviour Log it comes up with a form that acts as a menu that is working well. The Enter Behaviour Record is working fine too. Compile Report is calling the form I want (frm_Report_Builder). The intention was that this would feed into the Action Query (qry_Behaviour_Action) which would create a table with the filtered data (qry_FilterResults). There is a report linked to this (rpt_Behaviour) that I will eventually make a pdf.

    Simply, I would like the staff to use this to enter discipline records or compile reports. I would like the user to specific date range and then choose which report they want. So, enter a date range and leave everything blank - they would see all records between those dates. Enter a date range and a pupil name - they would see that pupils records between that date range. Enter a date range and a year group - they would see all records for that year group between that date range. You get the idea. But I'm falling (at the moment!) on this combobox not populating and when it does the query doesn't seem to work anyway!!

    I've attached the files if anyone has the time and inclination to have a look and point me in the right direction!
    Attached Files Attached Files

  11. #11
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    I deleted the ComboBoxes, made them again and they work! No difference at all that I can see!

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,343

  13. #13
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,904
    Although we usually think of Forms or entire Databases when we speak of corruption, individual Controls, such as Comboboxes, can and do become corrupted. The test and the fix are one and the same; deleting the Control then re-creating it! If it was corrupt, you've verified the fact and solved the problem! If not, you've only lost a minute or two!

    Good luck with your project!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  14. #14
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72
    Thanks! That makes sense!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Number Not Populating Control Correctly
    By libraccess in forum Forms
    Replies: 6
    Last Post: 08-30-2012, 02:13 AM
  2. Replies: 4
    Last Post: 04-26-2012, 11:04 AM
  3. Form / combobox not populating
    By Auracle in forum Forms
    Replies: 7
    Last Post: 09-20-2011, 11:01 AM
  4. Replies: 5
    Last Post: 07-23-2011, 11:48 PM
  5. Replies: 0
    Last Post: 08-24-2010, 06:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums